DB-TASK-003
Online Book Store
A customer can place multiple orders, but each order belongs to one customer.
A book can appear in multiple orders, and an order can contain multiple books.
Each order consists of one or more order details entries, specifying the quantity and price of each book in that order.
The stock quantity of a book should decrease when an order is placed.
A book’s price is stored at the time of the order to maintain historical pricing.
Customer Table
CREATE TABLE customer (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
address TEXT,
phone_number VARCHAR(15)
);
Book Table
CREATE TABLE book (
book_id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author VARCHAR(100),
isbn VARCHAR(20) UNIQUE,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT NOT NULL
);
Order Table
CREATE TABLE order_table (
order_id SERIAL PRIMARY KEY ,
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
customer_id INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);OrderDetails Table
CREATE TABLE order_details (
order_details_id SERIAL PRIMARY KEY ,
order_id INT NOT NULL,
book_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES order_table (order_id),
FOREIGN KEY (book_id) REFERENCES book(book_id)
);Insert Sample Data
Insert Customers:
INSERT INTO customer (name, email, address, phone_number) VALUES
('Alice Johnson', '[email protected]', '123 Elm Street, City A', '1234567890'),
('Bob Smith', '[email protected]', '456 Oak Avenue, City B', '0987654321');
Insert Books:
INSERT INTO book (title, author, isbn, price, stock_quantity) VALUES
('Physics Fundamentals', 'John Doe', '978-1234567890', 50.00, 10),
('Advanced Physics', 'Jane Roe', '978-0987654321', 60.00, 5),
('Organic Chemistry', 'Emily Stone', '978-1122334455', 45.00, 8),
('Inorganic Chemistry', 'Michael Green', '978-6677889900', 55.00, 12);
Insert Orders:
INSERT INTO order_table (order_date, customer_id, total_amount) VALUES
('2024-12-01 10:00:00', 1, 155.00),
('2024-12-02 15:30:00', 2, 105.00); Insert Order Details:
INSERT INTO order_details (order_id, book_id, quantity, price) VALUES
(1, 1, 2, 100.00),
(1, 3, 1, 45.00),
(2, 2, 1, 60.00),
(2, 4, 1, 55.00);List all Physics and Chemistry books:
SELECT *
FROM book
WHERE title LIKE '%Physics%' OR title LIKE '%Chemistry%';Get total stock of Physics and Chemistry books:
SELECT title, stock_quantity
FROM book
WHERE title LIKE '%Physics%' OR title LIKE '%Chemistry%';Find the total sales for Physics books:
SELECT SUM(price * quantity) AS total_sales
FROM order_details od
JOIN book b ON od.book_id = b.book_id
WHERE b.title LIKE '%Physics%';Library Management System
A member can borrow multiple books, but each loan belongs to one member.
A book can be loaned to multiple members over time, but only to one member at a time.
The return date should be recorded when the book is returned.
School Management System
A student can enroll in multiple classrooms, and each classroom can have multiple students.
Each classroom is assigned one teacher.
A teacher can teach multiple classrooms.
E-Commerce System
A customer can place multiple orders, and each order can have multiple products via OrderDetails.
A product can belong to multiple categories via ProductCategory.
Each order must be associated with a payment.
Last updated