CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(15)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT,
    BookID INT,
    order_date DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

CREATE TABLE Inventory (
    InventoryID INT PRIMARY KEY AUTO_INCREMENT,
    BookID INT,
    stock INT NOT NULL,
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

INSERT INTO Books (title, author, price)
VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
('1984', 'George Orwell', 8.99),
('To Kill a Mockingbird', 'Harper Lee', 12.99),
('The Catcher in the Rye', 'J.D. Salinger', 9.99),
('Moby Dick', 'Herman Melville', 14.99);

INSERT INTO Customers (name, email, phone)
VALUES
('John Doe', 'john@example.com', '123-456-7890'),
('Jane Smith', 'jane@example.com', '987-654-3210'),
('Alice Johnson', 'alice@example.com', '555-123-4567'),
('Bob Brown', 'bob@example.com', '444-555-6789'),
('Charlie Davis', 'charlie@example.com', '333-666-9876');

INSERT INTO Orders (CustomerID, BookID, order_date)
VALUES
(1, 1, '2023-02-01'),
(2, 2, '2023-02-02'),
(3, 3, '2023-02-03'),
(4, 4, '2023-02-04'),
(5, 5, '2023-02-05'),
(1, 2, '2023-02-10'),
(3, 1, '2023-02-12'),
(4, 3, '2023-02-13');

INSERT INTO Inventory (BookID, stock)
VALUES
(1, 50),
(2, 30),
(3, 20),
(4, 40),
(5, 60);

SELECT * FROM Books;

SELECT * FROM Books
WHERE author = 'George Orwell';

SELECT b.title, o.CustomerID, o.order_date
FROM Orders o
JOIN Books b ON o.BookID = b.BookID
WHERE o.CustomerID = 1;

SELECT b.title, SUM(i.stock) AS total_copies
FROM Books b
JOIN Inventory i ON b.BookID = i.BookID
GROUP BY b.BookID;

SELECT b.title, COUNT(o.BookID) AS borrow_count
FROM Orders o
JOIN Books b ON o.BookID = b.BookID
GROUP BY o.BookID
ORDER BY borrow_count DESC
LIMIT 1;