CREATE TABLE author(
    author_id INT PRIMARY KEY,
    author_name VARCHAR(50),
    birth_year INT,
    nationality VARCHAR(50)
);
CREATE TABLE publisher(
    publisher_id INT PRIMARY KEY,
    publisher_name VARCHAR(50),
    publisher_address VARCHAR(200),
    publisher_contact VARCHAR(70) 
);
CREATE TABLE books(
    book_id INT PRIMARY KEY,
    book_title VARCHAR(50),
    author_id INT,
    isbn_value VARCHAR(50),
    publisher_id INT,
    publication_year INT,
    genre VARCHAR(60),
    available BOOLEAN   
);
CREATE TABLE borrower(
    borrower_id INT PRIMARY KEY,
    borrower_name VARCHAR(50),
    contact VARCHAR(50),
    grade INT
    
);
CREATE TABLE borrowed_books(
    borrowed_id INT PRIMARY KEY,
    book_id INT,
    borrower_id INT,
    borrow_date DATE,
    due_date DATE,
    return_date DATE,
    FOREIGN KEY(book_id) REFERENCES books(book_id),
    FOREIGN KEY(borrower_id) REFERENCES borrower(borrower_id)
);

INSERT INTO author VALUES
(1,"Agatha Christie",1890,'British'),
(2,"William Shakespeare",1564,'British'),
(3,"Charles Dickens",1812,'British'),
(4,"Edgar Allan Poe",1809,'American');

INSERT INTO publisher VALUES
(1,"Penguin Random House",'One Embassy Gardens,London','penguinrandom.com'),
(2,"HarperCollins",'1290 Avenue','enquiries@harpercollins.co.uk'),
(3,"Simon and Schuster","120 Broadway",'simonschuster.com');

INSERT INTO books VALUES
(1,"A Tale of Two Cities",1,"978-0141439600",2,1859,"Historical Fiction",true),
(2,"The Alchemist",2,"978-011224154",3,1988,"Fantasy",false),
(3,"To Kill a Mockingbird",3,"978-0112339600",1,1960,"Classic Fiction",true),
(4,"1984",4,"978-0141437890",1,1949,"Dystopian",true);

INSERT INTO borrower VALUES
(1,"Priya Sharma","priyasharma@college.edu",8),
(2,"Neha Das","nehadas@college.edu",5),
(3,"Amit Singh","amitsingh@college.edu",8);

INSERT INTO borrowed_books VALUES
(1,3,2,'2026-11-5','2026-3-26','2026-8-21'),
(2,2,1,'2026-7-6','2026-12-20','2026-2-12'),
(3,4,3,'2026-4-5','2026-9-4','2026-1-16');

-- SELECT * FROM author;
-- SELECT * FROM publisher;
-- SELECT * FROM books;
-- SELECT * FROM borrower;
-- SELECT * FROM borrowed_books;

-- SELECT *
-- FROM publisher as p
-- JOIN books as b
-- ON p.publisher_id=b.publisher_id
-- WHERE publisher_name="Penguin Random House";

-- SELECT p.publisher_name,b.book_title
-- FROM publisher as p
-- JOIN books as b
-- ON p.publisher_id=b.publisher_id
-- WHERE publisher_name="Penguin Random House";

-- SELECT b.book_title,a.author_name
-- FROM books as b 
-- JOIN author as a ON b.author_id=a.author_id
-- JOIN publisher as p ON b.publisher_id=p.publisher_id
-- WHERE publisher_name="Penguin Random House";


-- SELECT book_title FROM books WHERE available=true;

-- SELECT book_title
-- FROM books as b 
-- JOIN borrowed_books as br
-- ON b.book_id=br.book_id;

-- SELECT book_title
-- FROM books as b 
-- JOIN author as a
-- ON b.author_id=a.author_id
-- WHERE author_name="Agatha Christie";


-- SELECT b.book_title,bw.borrower_name
-- FROM books as b 
-- JOIN borrowed_books as br ON b.book_id=br.book_id
-- JOIN borrower as bw ON bw.borrower_id=br.borrower_id;

-- SELECT b.book_title,br.borrow_date,br.due_date,br.return_date
-- FROM borrower as bw 
-- JOIN borrowed_books as br ON bw.borrower_id=br.borrower_id
-- JOIN books as b ON br.book_id=b.book_id
-- WHERE borrower_name="Priya Sharma";

-- SELECT book_title,publication_year FROM books;

SELECT book_title,publication_year FROM books
WHERE publication_year=(SELECT MIN(publication_year) FROM books);

-- SELECT MIN(publication_year) FROM books;