CREATE TABLE LibraryBook( 
    BookID INT AUTO_INCREMENT PRIMARY KEY, 
    Title VARCHAR(150), 
    Author VARCHAR(150),
    Genre VARCHAR(50), 
    PublishedYear INT, 
    ISBN VARCHAR (13) UNIQUE, 
    IsAvailable BOOLEAN DEFAULT TRUE
    );

CREATE TABLE BookLoan ( 
    LoanID INT AUTO_INCREMENT PRIMARY KEY, 
    StudentName VARCHAR(100),
    BookID INT, 
    LoanDate DATE, 
    ReturnDate DATE, 
    FOREIGN KEY (BookID) REFERENCES LibraryBook(BookID)
    );

INSERT INTO LibraryBook (BookID, Title, Author, Genre, PublishedYear, ISBN, IsAvailable)VALUES
    (1, 'Bridge to Terabithia', 'Tom Welling', 'Adventure', 2001, '2319039000001', TRUE), 
    (2, 'Sunrise on the Reaping', 'Tyler Hoechlin', 'Drama', 2025,'3319303999901', FALSE);

INSERT INTO BookLoan (LoanID, StudentName, BookID, LoanDate, ReturnDate)VALUES
    (1, 'Jessica Dethloff', 1, '2025-06-20', NULL),
    (2, 'Michael Rosenbaum', 2, '2025-01-20', '2025-01-22');

SELECT * 
FROM LibraryBook;

SELECT * 
FROM BookLoan; 


SELECT MIN(PublishedYear) AS EarliestPublished 
FROM LibraryBook; 

SELECT MAX(PublishedYear) AS LastestPublished 
FROM LibraryBook; 

SELECT MIN(LoanDate) AS FirstLoanDate
FROM BookLoan; 

SELECT MAX(LoanDate) AS MostRecentReturn 
FROM BookLoan; 


SELECT Title, Author 
FROM LibraryBook 
ORDER BY PublishedYear DESC;