-- LIBRARY MANAGEMENT DATABASE
-- This database contains three tables:
-- Books stores information about library books.
-- Members stores information about library members.
-- Loans records books borrowed by members.


-- 1. Create the Books table

CREATE TABLE Books (
    ISBN TEXT PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    Genre TEXT,
    Quantity INTEGER NOT NULL
);


-- 2. Create the Members table

CREATE TABLE Members (
    MemberID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Email TEXT,
    Phone TEXT
);


-- 3. Create the Loans table

CREATE TABLE Loans (
    LoanID INTEGER PRIMARY KEY,
    MemberID INTEGER NOT NULL,
    ISBN TEXT NOT NULL,
    LoanDate TEXT,
    ReturnDate TEXT,
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);


-- 4. Insert records into the Books table

INSERT INTO Books VALUES
('978001', 'Things Fall Apart', 'Chinua Achebe', 'Fiction', 5);

INSERT INTO Books VALUES
('978002', 'The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', 4);

INSERT INTO Books VALUES
('978003', 'Animal Farm', 'George Orwell', 'Political Fiction', 6);

INSERT INTO Books VALUES
('978004', 'The Alchemist', 'Paulo Coelho', 'Fiction', 3);

Insert into Books values 
    ('978005','Woman Down', 'Coleen Hoover','thriller',7);


-- 5. Insert records into the Members table

INSERT INTO Members VALUES
(1, 'Tendai Moyo', 'tendai@example.com', '0771234567');

INSERT INTO Members VALUES
(2, 'Rudo Ncube', 'rudo@example.com', '0772345678');

INSERT INTO Members VALUES
(3, 'Brian Dube', 'brian@example.com', '0773456789');

INSERT INTO Members VALUES
(4, 'Sarah Sibanda', 'sarah@example.com', '0774567890');




-- 6. Insert records into the Loans table

INSERT INTO Loans VALUES
(1, 1, '978001', '2026-09-20', '2026-10-04');

INSERT INTO Loans VALUES
(2, 2, '978003', '2026-09-21', '2026-10-05');

INSERT INTO Loans VALUES
(3, 1, '978002', '2026-09-22', '2026-10-06');

INSERT INTO Loans VALUES
(4, 3, '978004', '2026-09-23', '2026-10-07');


-- 7. Display all records in the Books table

SELECT * FROM Books;


-- 8. Display all records in the Members table

SELECT * FROM Members;


-- 9. Display all records in the Loans table

SELECT * FROM Loans;


-- 10. Retrieve all information about books borrowed by member 1

SELECT
    Members.MemberID,
    Members.Name,
    Members.Email,
    Members.Phone,
    Books.ISBN,
    Books.Title,
    Books.Author,
    Books.Genre,
    Loans.LoanDate,
    Loans.ReturnDate
FROM Members
JOIN Loans ON Members.MemberID = Loans.MemberID
JOIN Books ON Loans.ISBN = Books.ISBN
WHERE Members.MemberID = 1;


-- 11. Update the quantity of a particular book

UPDATE Books
SET Quantity = 10
WHERE ISBN = '978003';


-- 12. Display the Books table after updating the quantity

SELECT * FROM Books;


-- 13. Delete a member record

DELETE FROM Members
WHERE MemberID = 4;


-- 14. Display the Members table after deleting the member

SELECT * FROM Members;

SELECT * FROM Books;


-- Display all members
SELECT * FROM Members;

SELECT * FROM Loans;

-- Retrieve all information about books borrowed by Member 1
SELECT
    Members.MemberID,
    Members.Name,
    Members.Email,
    Members.Phone,
    Books.ISBN,
    Books.Title,
    Books.Author,
    Books.Genre,
    Loans.LoanDate,
    Loans.ReturnDate
FROM Members
JOIN Loans ON Members.MemberID = Loans.MemberID
JOIN Books ON Loans.ISBN = Books.ISBN
WHERE Members.MemberID = 1;

SELECT * FROM Books
WHERE ISBN = '978001';

SELECT
    Members.MemberID,
    Members.Name,
    Members.Email,
    Members.Phone,
    Books.ISBN,
    Books.Title,
    Books.Author,
    Books.Genre,
    Loans.LoanDate,
    Loans.ReturnDate
FROM Members
JOIN Loans ON Members.MemberID = Loans.MemberID
JOIN Books ON Loans.ISBN = Books.ISBN
WHERE Members.MemberID = 1;