library_management.sql

-- Library Management Database SQL Script

-- 1. Create table for Books
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author VARCHAR(50),
    Category VARCHAR(50),
    AvailableCopies INT
);

-- 2. Create table for Members
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    MemberName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    JoinDate DATE
);

-- 3. Create table for Issued Books
CREATE TABLE IssuedBooks (
    IssueID INT PRIMARY KEY,
    BookID INT,
    MemberID INT,
    IssueDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

-- 4. Insert sample data into Books
INSERT INTO Books VALUES
(1, 'Java Basics', 'John Smith', 'Programming', 5),
(2, 'SQL Made Easy', 'Anna Lee', 'Database', 3),
(3, 'Python for Data Science', 'Robert Brown', 'Data Science', 4),
(4, 'Web Development Guide', 'Emily White', 'Web Development', 2);

-- 5. Insert sample data into Members
INSERT INTO Members VALUES
(101, 'Alice Johnson', 'alice@example.com', '2024-06-10'),
(102, 'Michael Davis', 'michael@example.com', '2024-07-01'),
(103, 'Sarah Lee', 'sarah@example.com', '2024-07-15');

-- 6. Insert sample data into IssuedBooks
INSERT INTO IssuedBooks VALUES
(1001, 1, 101, '2024-07-20', '2024-08-05'),
(1002, 2, 102, '2024-07-25', '2024-08-10'),
(1003, 3, 103, '2024-07-28', NULL);

-- 7. Queries to demonstrate functionality

-- Show all books
SELECT * FROM Books;

-- Show all members
SELECT * FROM Members;

-- Find issued books with member details
SELECT ib.IssueID, b.Title, m.MemberName, ib.IssueDate, ib.ReturnDate
FROM IssuedBooks ib
JOIN Books b ON ib.BookID = b.BookID
JOIN Members m ON ib.MemberID = m.MemberID;

-- Count books by category
SELECT Category, COUNT(*) AS TotalBooks
FROM Books
GROUP BY Category;

-- Show books that are currently available
SELECT Title, AvailableCopies
FROM Books
WHERE AvailableCopies > 0;