--Create Books table
CREATE TABLE Books(
ISBN varchar (50) PRIMARY KEY, 
Title varchar (150) NOT NULL,
Author varchar (150) NOT NULL, 
Genre varchar (50) NOT NULL,
Quantity  int );

--Create Members table
CREATE TABLE Members(
MemberID  int PRIMARY KEY,  
Name varchar(150) NOT NULL, 
Email varchar  (150) ,
Phone varchar(30));

--Create Loan table
CREATE TABLE Loan(
LoanID int PRIMARY KEY, 
MemberID  int, 
ISBN varchar (50),
LoanDate DATE NOT NULL,
ReturnDate DATE,FOREIGN KEY (MemberID) 
    REFERENCES Members(MemberID) ON DELETE CASCADE,
FOREIGN KEY (ISBN) REFERENCES Books(ISBN) 
    ON DELETE CASCADE   
);
--Insert values into Books table, Members table and Loan table
INSERT INTO Books  VALUES 
    ('9780135988046', "Campbell Biology", 
    " Lisa Urry, Micheal Cain,","Educational", 160),
    (' 9780133647495', "Conceptual  Physics ", 
    "Paul G. Hewitt ","Educational", 100),
    (' 9780470049907', "Environmental Science: Earth as a living planet ",
    " Daniel B. Botkin and Edward A. Keller","Educational", 180);


INSERT INTO Members  VALUES ('01', "Franca",
    " +234803241","Email1"), 
    ('02', "Pearl", " +234812243","Email2"),
    ('03', "Eva", "+234901501 ","Email3");

INSERT INTO Loan  VALUES ('01', "9780135988046 ",
    "01"," 2026-09-28"," 2026-10-28" ), 
    ('02', " 9780133647495", " 02",
    " 2026-09-28"," 2026-11-28" ), 
    ('03', " 9780470049907", " 03",
    " 2026-09-28"," 2026-12-28" );

--Retrieve Books borrowed by member 3
SELECT b.ISBN, b.Title, b.Author, b.Genre, 
    l.LoanDate, l.ReturnDate FROM Books b 
    JOIN Loan l ON  b.ISBN = l.ISBN 
    WHERE l.MemberID=3;

--Update Books quantity
UPDATE Books SET Quantity = 300
    WHERE ISBN ='9780135988046';
--Delete a Member
DELETE FROM Members WHERE MemberID = 2;

SELECT * FROM Books;
SELECT * FROM Members;
SELECT * FROM Loan;