CREATE TABLE Author(
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(100),
    BirthYear INT,
    Nationality VARCHAR(50)
);
INSERT INTO Author VALUES
    (1, "Mark Twain", 1835, "American"),
    (2, "Jane Austin", 1777, "British"),
    (3, "George Orwell", 1903, "British");
-- SELECT * FROM Author;
CREATE TABLE Publisher(
    PublisherID INT PRIMARY KEY,
    PublisherName VARCHAR(100),
    Address VARCHAR(200),
    ContactInfo VARCHAR(100)
);
INSERT INTO Publisher VALUES
    (1, "Harper Collins", "789 Print DR, New York", "Contact@harpercollins.com"),
    (2, "Random House", "101 Publisher Lane, London", "Contact@randomhouse.co.uk"),
    (3, "Penguin Books", "456 Publish Ave, London", "Contact@penguinbooks.co.uk");
-- SELECT * FROM Publisher;
CREATE TABLE Books(
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    ISBN VARCHAR(13),
    PublisherID INT,
    PublicationYear INT,
    Genre VARCHAR(50),
    Available BOOLEAN,
    FOREIGN KEY (AuthorID) REFERENCES Author (AuthorID),
    FOREIGN KEY (PublisherID) REFERENCES Publisher (PublisherID)
);
INSERT INTO Books VALUES
    (1, "The Adventures of Huckleberry Finn", 1, "975", 1, 1884, "Adventure", TRUE),
    (2, "Pride and Prejudice", 2, "978", 2, 1813, "Romance", TRUE),
    (3, "1984", 3, "976", 3, 1949, "Dystopian", TRUE),
    (4, "The Great Gatsby", NULL, "979", NULL, 1925, "Drama", FALSE);
-- SELECT * FROM Books;
CREATE TABLE Borrowers(
    BorrowerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Grade INT,
    ContactInfo VARCHAR(100)
);
INSERT INTO Borrowers VALUES
    (1, "Micheal", 5, "micheal@email.com"),
    (2, "Austin", 7, "austin@email.com"),
    (3, "William", 12, "william@email.com");
-- SELECT * FROM Borrowers;
CREATE TABLE BorrowedBooks(
    BorrowedID INT PRIMARY KEY,
    BookID INT,
    BorrowerID INT,
    BorrowDate DATE,
    DueDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books (BookID),
    FOREIGN KEY (BorrowerID) REFERENCES Borrowers (BorrowerID)
);
INSERT INTO BorrowedBooks VALUES
    (1, 1, 1, "2026-03-30", "2026-04-30", "2026-04-02"),
    (2, 2, 2, "2026-03-30", "2026-04-30", "2026-04-13"),
    (3, 3, 3, "2026-03-30", "2026-04-30", "2026-04-29");
-- SELECT * FROM BorrowedBooks;
-- SELECT Books.BookID, Title, BorrowDate, DueDate FROM Books LEFT JOIN BorrowedBooks ON Books.BookID = BorrowedBooks.BookID; 
-- SELECT Title, Borrowers.Name, BorrowedBooks.BorrowDate FROM (Books LEFT JOIN BorrowedBooks ON Books.BookID = BorrowedBooks.BookID) LEFT JOIN Borrowers ON BorrowedBooks.BorrowerID = Borrowers.BorrowerID; 
SELECT Title, PublicationYear FROM Books WHERE PublicationYear = 
    (SELECT MAX(PublicationYear) FROM Books)