CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(100),
    BirthYear INT,
    Nationality VARCHAR(50)
);
CREATE TABLE Publishers (
    PublisherID INT PRIMARY KEY,
    PublisherName VARCHAR(100),
    Address VARCHAR (200),
    ContactInformation VARCHAR(100)
);
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    ISBN VARCHAR(13),
    PublisherID INT,
    PublicationYear INT,
    Genre VARCHAR(50),
    Available BOOLEAN
);
CREATE TABLE Borrowers (
    BorrowerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Grade INT,
    ContactInformation VARCHAR(100)
);
CREATE TABLE BorrowedBooks (
    BorrowedID INT PRIMARY KEY,
    BookID INT,
    BorrowerID INT,
    BorrowDate DATE,
    DueDate DATE,
    ReturnDate DATE
);
INSERT INTO Authors (AuthorID, AuthorName, BirthYear, Nationality) VALUES
    (1, 'J.K. Rowling', 1965, 'British'),
    (2, 'George Orwell', 1903, 'British'),
    (3, 'Mark twain', 1835, 'American'),
    (4, 'Jane Austen', 1775, 'British'),
    (5, 'F. Scott Fitzgerald', 1896, 'American');
INSERT INTO Publishers (PublisherID, PublisherName, Address, ContactInformation) VALUES
    (1, 'Scholastic', '123 Book St., New York', 'contact@scholastic.com'),
    (2, 'Penguin Books', '456 Publish Ave., London', 'contact@penguinbooks.co.uk'),
    (3, 'Harper Collins', '789 Print Dr., New York', 'contact@harpercollin.com'),
    (4, 'Random House', '101 Publisher Ln., London', 'contact@randomhouse.co.uk'),
    (5, 'Scribner', '555 Literary Rd., New York', 'contact@scribner.com');
INSERT INTO Books (BookID, Title, AuthorID, ISBN, PublisherID, PublicationYear, Genre, Available) VALUES
    (1, 'Harry Potter and the Sorcerer''s Stone', 1, '975', 1, 1997, 'Fantasy', true),
    (2, '1984', 2, '976', 2, 1949, 'Dystopian', true),
    (3, 'The Adventure of Hucleberry Finn', 3, '977', 3, 1884, 'Adventure', true),
    (4, 'Pride and Prejudice', 4, '978', 4, 1813, 'Romance', true),
    (5, 'The Great Gatsby', 5, '979', 5, 1925, 'Drama', true);
INSERT INTO Borrowers (BorrowerID, Name, Grade, ContactInformation) VALUES
    (1, 'Emma Johnson', 7, 'emma@email.com'),
    (2, 'Michael Smith', 7, 'michael@email.com'),
    (3, 'Olivia Brown', 7, 'olivia@email.com'),
    (4, 'William Jines', 7, 'william@email.com'),
    (5, 'Sophia Davis', 7, 'sophia@email.com');
INSERT INTO BorrowedBooks (BorrowedID, BookID, BorrowerID, BorrowDate, DueDate, ReturnDate) VALUES
    (1, 1, 1, '2023-10-10', '2023-10-24', '2023-10-15'),
    (2, 2, 2, '2023-10-12', '2023-10-26', NULL),
    (3, 3, 3, '2023-10-14', '2023-10-28', NULL),
    (4, 4, 4, '2023-10-15', '2023-10-29', NULL),
    (5, 5, 5, '2023-10-17', '2023-10-31', NULL);
-- SELECT *FROM Authors;
-- SELECT Title
-- FROM Books
-- WHERE Available=true;
-- SELECT Books.Title, Authors.AuthorName
-- FROM Books JOIN Authors ON Books.AuthorID=Authors.AuthorID
-- WHERE AuthorName='J.K. Rowling';
-- SELECT Books.Title, Authors.AuthorName
-- FROM Books
-- JOIN Authors ON Books.AuthorID=Authors.AuthorID
-- JOIN Publishers ON Books.PublisherID=Publishers.PublisherID
-- WHERE Publishers.PublisherName='Penguin Books';
-- SELECT Books.Title, Borrowers.Name, BorrowedBooks.BorrowDate, BorrowedBooks.DueDate 
-- FROM BorrowedBooks
-- JOIN Books ON BorrowedBooks.BookID=Books.BookID
-- JOIN Borrowers ON BorrowedBooks.BorrowerID=Borrowers.BorrowerID;
-- SELECT Books.Title, BorrowedBooks.BorrowDate, BorrowedBooks.DueDate, BorrowedBooks.ReturnDate
-- FROM BorrowedBooks
-- JOIN Books ON BorrowedBooks.BookID=Books.BookID
-- JOIN Borrowers ON Borrowers.BorrowerID=BorrowedBooks.BorrowerID
-- WHERE Borrowers.Name='Emma Johnson';
-- SELECT MIN(PublicationYear)
-- FROM Books;
SELECT Title, PublicationYear
FROM Books
WHERE Books.PublicationYear=(SELECT MIN(PublicationYear)FROM Books);