CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR (100),
    ISBN VARCHAR (100),
    publication_year INT,
    publisher_id INT,
    genre_id INT,
    q_available INT
);
INSERT INTO Books (book_id, title, ISBN, publication_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);

SELECT * FROM Books;
-- 1. Retrieve Book Information for a Specific Title:
SELECT *
FROM Books
WHERE title = 'The Great Gatsby';


CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    biography TEXT,
    birth_date DATE,
    nationality VARCHAR(50)
);

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-07-31', 'British');

select *from Authors;

CREATE TABLE Borrowers(
    borrower_id INT PRIMARY KEY,
    student_id INT,
    employee_id INT,
    checkout_date DATE,
    return_date DATE
);
-- Insert records into the Borrowers table
INSERT INTO Borrowers (borrower_id, student_id, employee_id, checkout_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');

CREATE TABLE Transactions(
    transaction_id INT PRIMARY KEY,
     book_id INT,
    borrower_id INT,
    checkout_date DATE,
    return_date DATE
);
-- Insert records into the Transactions table
INSERT INTO Transactions (transaction_id, book_id, borrower_id, checkout_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');

CREATE TABLE Library_Staff (
    employee_id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255),
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);


CREATE TABLE Late_Fees (
    penalty_id INT PRIMARY KEY,
    transaction_id INT,
    due_date DATE,
    amount DECIMAL(10, 2)
);
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);

CREATE TABLE Publishers(
    publishers_id INT PRIMARY KEY,
    name VARCHAR (70),
    address VARCHAR(50),
    contact VARCHAR (60)

-- Insert records into the Publishers table
INSERT INTO Publishers (publisher_id, name, address, contact)
VALUES
    (1, 'Little, Brown and Company', '123 Book St., New York, NY', 'contact@littlebrown.com'),
    (2, 'Harper & Brothers', '456 Publishing Ave., Chicago, IL', 'info@harperbrothers.com'),
    (3, 'Secker & Warburg', '789 Literature Lane, London, UK', 'contact@seckerwarburg.com'),
    (4, 'Scribner', '101 Publishers Place, Boston, MA', 'info@scribner.com'),
    (5, 'Bloomsbury Publishing', '567 Story Street, London, UK', 'contact@bloomsbury.com');

-- 2. Find Books Published by a Specific Publisher:
SELECT b.title
FROM Books AS b
JOIN Publishers AS p ON b.publisher_id = p.publisher_id
WHERE p.name = 'Little, Brown and Company'