-- create a table
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 data into tables
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 data into the Publishers table
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, 'HarperCollins', '789 Print Dr., New York', 'contact@harpercollins.com'),
    (4, 'Random House', '101 Publisher Ln., London', 'contact@randomhouse.co.uk'),
    (5, 'Scribner', '555 Literary Rd., New York', 'contact@scribner.com');


-- Insert Data into the Books table
INSERT INTO  Books (BookID, Title, AuthorID, ISBN, PublisherID, PublicationYear, Genre, Available)
VALUES
    (1, 'Harry Potter and the Sorcerers Stone', 1, '975', 1, 1997, 'Fantasy', true),
    (2, '1984', 2, '976', 2, 1949, 'Dystopian', true),
    (3, 'The Adventures of Huckleberry 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 Data into the Borrowers table
INSERT INTO  Borrowers (BorrowerID, Name, Grade, ContactIformation)
VALUES
    (1, 'Elliot Ludwig', 8, 'ludwig@email.com'),
    (2, 'Joshua Temple', 11, 'temple@email.com'),
    (3, 'Venzkirzgat Rougt', 4, 'rougt@email.com'),
    (4, 'Hallgrímskirkja Audoingscrat', 7, 'audoingscrat@email.com'),
    (5, 'Chargonkiliwan Dsousa', 14, 'dsousa@email.com');

-- Insert Data into the BorrowedBooks table
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 BorrowedBooks;

-- Retrieve the list of books published by a specific ublisher (e.g., "Penguin Books"):
SELECT Books.Title, Authors.Author Name
SELECT * FROM Authors
FROM Books 
JOIN Authors ON Books.AuthorID = Author.AuthorID
JOIN Publishers ON Books.PublisherID = Publishers.PublisherID;
WHERE Publishers.PublisherName = 'Penguin Books'

-- List all available books:
SELECT Title
FROM Books
WHERE Available = true;

-- List all borrowed books and their borrowers:
SELECT Books.Title, Borrowers.Name, BorrowedBooks.BorrowDate, BorrowedBooks.DueDate, BorrowedBooks.ReturnDate
FROM BorrowedBooks
JOIN Books ON BorrowedBooks.BookID = Books.BookID
JOIN Borrowers ON BorrowedBooks.BorrowerID = Borrowers.BorrowerID;

--  Find all books by a specific author (e.g., 'J.K. Rowling'):
SELECT Books.Title, Authors.AuthorName
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE Authors.AuthorName = 'J.K. Rowling';

--  List books borrowed by a specific borrower (e.g., 'Emma Johnson'):
SELECT Books.Title, BorrowedBooks.BorrowDate, BorrowedBooks.DueDate, BorrowedBooks.ReturnDate
FROM BorrowedBooks
JOIN Books ON BorrowedBooks.BookID = Books.BookID
JOIN Borrowers ON BorrowedBooks.BorrowerID = Borrowers.BorrowerID
WHERE Borrowers.Name = 'Emma Johnson';

-- Find the oldest book in the collection:
SELECT Title, PublicationYear
FROM Books
WHERE PublicationYear = (SELECT MIN(PublicationYear) FROM Books);