Create table books(
    book_id INT PRIMARY KEY,
    title VARCHAR(50),
    author VARCHAR(100),
    price DECIMAL(10,2),
    genre VARCHAR(50)
);
CREATE TABLE customers(
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(50),
    address VARCHAR(50)
);
CREATE TABLE orders(
    order_id INT PRIMARY KEY,
    book_id INT,
    customer_id INT,
    order_date DATE,
    quantity INT,
    total_price INT,
    FOREIGN KEY(customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY(book_id) REFERENCES books(book_id)   
);

CREATE TABLE inventory(
    book_id Int,
    quantity INT,
    last_updated DATE,
    FOREIGN KEY(book_id) REFERENCES books(book_id)   
);
INSERT INTO books VALUES
(101,'The Alchemist','Paulo Coelho',350.00,'Fiction'),
(102,'Twisted games','Ana Huang',550.00,'Drama'),
(103,'Twisted Lies','Ana Huang',450.00,'Fiction'),
(104,'Twisted Hate','Ana Huang',490.00,'Drama');

INSERT INTO customers VALUES
(201,"Freida Mcfadden","freida234@gmail.com","9843567892","Dover lane"),
(202,"Maria Simon","maria43@gmail.com","9345623162","Square Doe road"),
(203,"Ravi Sharma","sharmaravi64@gmail.com","9984657892","Jason lane");

INSERT INTO orders VALUES
(301,101,201,'2026-09-23',3,"1050"),
(302,102,202,'2026-09-04',6,"3300"),
(303,104,202,'2026-08-01',5,"2450"),
(304,103,203,'2026-07-04',8,"3600");

INSERT INTO inventory VALUES
(101,25,'2026-09-01'),
(102,30,'2026-06-01'),
(103,67,'2026-07-21'),
(104,47,'2026-02-28');

#SELECT c.customer_name,o.order_date,o.quantity
-- FROM customers as c
-- JOIN orders as o
-- ON c.customer_id=o.customer_id
#JOIN books as b
#ON o.book_id=b.book_id
#WHERE b.title='The Alchemist';

SELECT b.title,o.order_date,o.quantity
FROM books as b
JOIN orders as o
ON b.book_id=o.book_id
JOIN customers as c
ON o.customer_id=c.customer_id
WHERE b.title='The alchemist';