CREATE TABLE Publishers (
    publisher_id INT PRIMARY KEY,
    name VARCHAR(100),
    address TEXT,
    contact VARCHAR(100)
);
CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    ISBN VARCHAR(20),
    publiscation_year INT,
    genre_id INT,
    publisher_id INT,
    q_available INT,
    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id)
);
CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    biography TEXT,
    birth_date DATE,
    nationality VARCHAR(50)
);
CREATE TABLE Library_Staff (
    employee_id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255),
    staff_first_name VARCHAR(50),
    staff_last_name VARCHAR(50)
);
CREATE TABLE Borrowers (
    borrower_id INT PRIMARY KEY,
    student_id INT,
    employee_id INT,
    checout_date DATE,
    return_date DATE,
    FOREIGN KEY (employee_id) REFERENCES Library_Staff(employee_id) 
);
CREATE TABLE Transactions (
    transaction_id INT PRIMARY KEY,
    book_id INT,
    borrower_id INT,
    checout_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (borrower_id) REFERENCES Borrowers(borrower_id)
);
CREATE TABLE Late_Fees (
    penalty_id INT PRIMARY KEY,
    transaction_id INT,
    due_date DATE,
    amount DECIMAL (10, 2),
    FOREIGN KEY (transaction_id) REFERENCES Transactions(transaction_id)
);
INSERT INTO Publishers (publisher_id, name, address, contact) VALUES
    (1, 'Little, Brown and Company', '123 Book St., New York', 'contact@littlebrown.com'),
    (2, 'Harper and Brothers', '456 Pulishing Ave., Chicago, IL', 'info@harperbrothers.com'),
    (3, 'Secker and Warburg', '789 Literature Lane, London, UK', 'contact@seckerand warburg.com'),
    (4, 'Scribner', '101 Publishers Place, Boston, MA', 'info@scribner.com'),
    (5, 'Bloomsbury Publishing', '567 Story Street, London, UK', 'contact@bloomsburu.com');
INSERT INTO Books (book_id, title, ISBN, publiscation_year, genre_id, publisher_id, q_available) VALUES
    (1, 'The Catcher in the Rye', '974', 1951, 1, 1, 10),
    (2, 'To Kill a Mockingbird', '975', 1960, 2, 2, 8),
    (3, '1984', '976', 1949, 3, 3, 12),
    (4, 'The Great Gatsby', '977', 1925, 4, 4, 7),
    (5, 'Harry Potter and the Philosopher''s Stone', '978', 1997, 5, 5, 15);
INSERT INTO Authors (author_id, first_name, last_name, biography, birth_date, nationality) VALUES
    (1, 'J.D', 'Salinger', 'Biography of J.D. Salinger', '1919-01-01', 'American'),
    (2, 'Harper', 'Lee', 'Biography of Harper Lee', '1926-04-28', 'American'),
    (3, 'George', 'Orwell', 'Biography of George Orwell', '1903-06-25', 'English'),
    (4, 'F. Scott', 'Fitzgerald', 'Biography of F. Scott Fitzgerald', '1896-09-24', 'American'),
    (5, 'J.K.', 'Rowling', 'Biography of J.K. Rowling', '1965-01-31', 'British');
INSERT INTO Library_Staff (employee_id, username, password, staff_first_name, staff_last_name) VALUES
    (2001, 'librarian1', 'hashed_password1', 'John', 'Smith'),
    (2002, 'librarian2', 'hashed_password2', 'Alice', 'Johnson'),
    (2003, 'librarian3', 'hashed_password3', 'David', 'Brown'),
    (2004, 'librarian4', 'hashed_password4', 'Sarah', 'Davis'),
    (2005, 'librarian5', 'hashed_password5', 'Michael', 'Wilson');
INSERT INTO Borrowers (borrower_id, student_id, employee_id, checout_date, return_date) VALUES
    (1, 1001, 2001, '2023-10-01', '2023-10-15'),
    (2, 1002, 2002, '2023-09-15', '2023-09-30'),
    (3, 1003, 2003, '2023-09-20', '2023-10-05'),
    (4, 1004, 2004, '2023-10-10', '2023-10-25'),
    (5, 1005, 2005, '2023-09-05', '2023-09-20');
INSERT INTO Transactions (transaction_id, book_id, borrower_id, checout_date, return_date) VALUES
    (1, 1, 1, '2023-10-01', '2023-10-15'),
    (2, 2, 2, '2023-09-15', '2023-09-30'),
    (3, 3, 3, '2023-09-20', '2023-10-05'),
    (4, 4, 4, '2023-10-10', '2023-10-25'),
    (5, 5, 5, '2023-09-05', '2023-09-20');
INSERT INTO Late_Fees (penalty_id, transaction_id, due_date, amount) VALUES
    (1, 1, '2023-10-16', 5.00),
    (2, 2, '2023-10-01', 3.00),
    (3, 3, '2023-10-06', 4.50),
    (4, 4, '2023-10-26', 6.25),
    (5, 5, '2023-09-21', 2.50);
-- SELECT book_id, title, ISBN, publiscation_year, genre_id, publisher_id, q_available
-- FROM Books
-- WHERE Books.title='The Great Gatsby'
-- SELECT title
-- FROM Books
-- JOIN Publishers ON Publishers.publisher_id=Books.publisher_id
-- WHERE Publishers.name='Little, Brown and Company'
-- SELECT employee_id, username, password, staff_first_name, staff_last_name
-- FROM Library_Staff
-- WHERE Library_Staff.username='librarian1'
-- SELECT SUM(Late_Fees.amount) AS total_late_fees
-- FROM Late_Fees
-- SELECT Books.title, Borrowers.checout_date, Borrowers.return_date
-- FROM Transactions
-- JOIN Books ON Books.book_id=Transactions.book_id
-- JOIN Borrowers ON Borrowers.borrower_id=Transactions.borrower_id
-- SELECT Books.title, Transactions.checout_date,Transactions.return_date, (DATE(Transactions.return_date)-(DATE(Transactions.checout_date))) AS borrow_days
-- FROM Transactions
-- JOIN Books ON Books.book_id=Transactions.book_id
-- JOIN Borrowers ON Borrowers.borrower_id=Transactions.borrower_id
-- SELECT Library_Staff.staff_first_name, Library_Staff.staff_last_name, Borrowers.checout_date, Borrowers.return_date
-- FROM Library_Staff
-- JOIN Borrowers ON Borrowers.employee_id=Library_Staff.employee_id
-- WHERE Library_Staff.staff_first_name='John' AND Library_Staff.staff_last_name='Smith'
-- SELECT Library_Staff.staff_first_name, Library_Staff.staff_last_name, Borrowers.checout_date, Borrowers.return_date
-- FROM Library_Staff
-- JOIN Borrowers ON Borrowers.employee_id=Library_Staff.employee_id
-- JOIN Transactions ON Transactions.checout_date=Borrowers.checout_date
-- JOIN Books ON Books.book_id=Transactions.book_id
-- WHERE Books.title='To Kill a Mockingbird'
-- SELECT Books.title, Transactions.checout_date, Transactions.return_date
-- FROM Books
-- JOIN Transactions ON Transactions.book_id=Books.book_id
-- JOIN Borrowers ON Transactions.checout_date=Borrowers.checout_date
-- JOIN Library_Staff ON Borrowers.employee_id=Library_Staff.employee_id
-- WHERE Library_Staff.staff_first_name='John' AND Library_Staff.staff_last_name='Smith'
-- SELECT title, publiscation_year
-- FROM Books
-- WHERE publiscation_year=1960
-- SELECT title, q_available
-- FROM Books
-- ORDER BY q_available DESC LIMIT 1
-- SELECT Publishers.name, COUNT(*)total_books
-- FROM Books
-- JOIN Publishers ON Publishers.publisher_id=Books.publisher_id
-- GROUP BY Publishers.name
-- SELECT first_name, last_name, birth_date
-- FROM Authors
-- WHERE YEAR(birth_date)<1920
-- SELECT Late_Fees.penalty_id, Books.title, Late_Fees.amount
-- FROM Late_Fees
-- JOIN Transactions ON Transactions.transaction_id=Late_Fees.transaction_id
-- JOIN Books ON Books.book_id=Transactions.book_id
-- WHERE Late_Fees.amount > 4.00 
SELECT Books.title, Publishers.name
FROM Books
JOIN Publishers ON Publishers.publisher_id=Books.publisher_id