bookstore.sql

-- Create the Books Table
CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY AUTOINCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255),
    Genre VARCHAR(100),
    Price DECIMAL(10, 2) NOT NULL,
    Stock INT NOT NULL
);

-- Create the Customers Table
CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name VARCHAR(255) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    Phone VARCHAR(15)
);

-- Create the Sales Table
CREATE TABLE Sales (
    SaleID INTEGER PRIMARY KEY AUTOINCREMENT,
    BookID INTEGER,
    CustomerID INTEGER,
    SaleDate DATE NOT NULL,
    Quantity INT NOT NULL,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert Sample Data into Books Table
INSERT INTO Books (Title, Author, Genre, Price, Stock)
VALUES
('Book A', 'Author 1', 'Fiction', 19.99, 100),
('Book B', 'Author 2', 'Non-Fiction', 24.99, 50),
('Book C', 'Author 3', 'Science Fiction', 15.99, 75),
('Book D', 'Author 4', 'Mystery', 12.99, 30),
('Book E', 'Author 5', 'Romance', 9.99, 20);

-- Insert Sample Data into Customers Table
INSERT INTO Customers (Name, Email, Phone)
VALUES
('John Doe', 'johndoe@example.com', '123-456-7890'),
('Jane Smith', 'janesmith@example.com', '234-567-8901'),
('Emily Davis', 'emilydavis@example.com', '345-678-9012'),
('Michael Brown', 'michaelbrown@example.com', '456-789-0123'),
('Sarah Wilson', 'sarahwilson@example.com', '567-890-1234');

-- Insert Sample Data into Sales Table
INSERT INTO Sales (BookID, CustomerID, SaleDate, Quantity, TotalAmount)
VALUES
(1, 1, '2025-01-01', 2, 39.98),
(2, 2, '2025-01-02', 1, 24.99),
(3, 1, '2025-01-03', 3, 47.97),
(1, 3, '2025-01-04', 1, 19.99),
(4, 4, '2025-01-05', 2, 25.98),
(5, 5, '2025-01-06', 1, 9.99);