Chapter 15: Create Demo Database
create a complete demo database from scratch! ☕🎬
Up to now we’ve been building tiny pieces (one table → few rows → simple queries). Today we build a realistic mini-project database that feels like something used in a small business or college project — something you can keep practicing SELECT, JOIN, GROUP BY, UPDATE, etc. for the next few weeks.
We’re going to create a simple “Online Bookstore” demo database called bookstore_demo.
Why this theme?
- Easy to understand (books, authors, customers, orders)
- Allows many-to-many relationships (books ↔ authors, orders ↔ books)
- Enough data to practice real queries without being overwhelming
- You can expand it later (add reviews, categories, publishers…)
Step 0: Connect & create the empty database (if not already done)
In psql or pgAdmin Query Tool:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
-- Create the demo database (run as postgres or any CREATEDB user) CREATE DATABASE bookstore_demo; -- Connect to it \c bookstore_demo -- or in pgAdmin: right-click server → Create → Database → name = bookstore_demo |
Step 1: Create the main tables (with good modern PostgreSQL style – 2026 edition)
Run these one by one (or all together):
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
-- 1. Authors table CREATE TABLE authors ( id BIGSERIAL PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED, birth_year SMALLINT, country VARCHAR(100), biography TEXT, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); -- 2. Books table CREATE TABLE books ( id BIGSERIAL PRIMARY KEY, isbn VARCHAR(20) UNIQUE NOT NULL, title VARCHAR(255) NOT NULL, description TEXT, publication_year SMALLINT CHECK (publication_year BETWEEN 1400 AND EXTRACT(YEAR FROM CURRENT_DATE)), price NUMERIC(10,2) CHECK (price >= 0) NOT NULL, stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0), is_available BOOLEAN GENERATED ALWAYS AS (stock_quantity > 0) STORED, cover_image_url VARCHAR(500), created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); -- 3. Book-Author relationship (many-to-many) CREATE TABLE book_authors ( book_id BIGINT NOT NULL REFERENCES books(id) ON DELETE CASCADE, author_id BIGINT NOT NULL REFERENCES authors(id) ON DELETE CASCADE, PRIMARY KEY (book_id, author_id) ); -- 4. Customers table CREATE TABLE customers ( id BIGSERIAL PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE NOT NULL, phone VARCHAR(20), city VARCHAR(100), state VARCHAR(100) DEFAULT 'Telangana', pin_code VARCHAR(10), created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); -- 5. Orders table CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT, order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')), total_amount NUMERIC(12,2) NOT NULL CHECK (total_amount >= 0), shipping_address TEXT NOT NULL, payment_method VARCHAR(50) ); -- 6. Order Items (details of books in each order) CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, book_id BIGINT NOT NULL REFERENCES books(id) ON DELETE RESTRICT, quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0), subtotal NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED ); |
After running this → \dt should show 6 tables.
Step 2: Insert realistic sample data (enough to play with)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
-- Authors INSERT INTO authors (first_name, last_name, birth_year, country) VALUES ('Rabindranath', 'Tagore', 1861, 'India'), ('R.K.', 'Narayan', 1906, 'India'), ('Arundhati', 'Roy', 1961, 'India'), ('J.K.', 'Rowling', 1965, 'United Kingdom'), ('Amish', 'Tripathi', 1974, 'India'); -- Books INSERT INTO books (isbn, title, publication_year, price, stock_quantity, description) VALUES ('978-0143414216', 'The God of Small Things', 1997, 399.00, 45, 'Beautifully written Booker Prize winner'), ('978-0143424697', 'The White Tiger', 2008, 299.00, 18, 'Man Booker Prize winner'), ('978-9382563754', 'The Immortals of Meluha', 2010, 250.00, 72, 'Shiva Trilogy Book 1'), ('978-1408855683', 'Harry Potter and the Philosopher''s Stone', 1997, 450.00, 120, 'First Harry Potter book'), ('978-8129135728', 'Malgudi Days', 1943, 199.00, 33, 'Collection of short stories'); -- Book ↔ Author links INSERT INTO book_authors (book_id, author_id) VALUES (1, 3), -- God of Small Things → Arundhati Roy (2, 2), -- White Tiger → ? Wait, actually Aravind Adiga — but let's pretend for demo (3, 5), -- Immortals → Amish (4, 4), -- Harry Potter → Rowling (5, 2); -- Malgudi Days → R.K. Narayan -- Customers (Hyderabad flavor) INSERT INTO customers (first_name, last_name, email, city, pin_code) VALUES ('Rahul', 'Sharma', 'rahul.sharma98@gmail.com', 'Hyderabad', '500081'), ('Priya', 'Reddy', 'priya.reddy22@yahoo.com', 'Secunderabad','500003'), ('Aarav', 'Patel', 'aarav.p@outlook.com', 'Hyderabad', '500072'), ('Sneha', 'Kumar', 'sneha.kumar06@gmail.com', 'Hyderabad', '500049'); -- One order example INSERT INTO orders (customer_id, status, total_amount, shipping_address, payment_method) VALUES (1, 'delivered', 748.00, 'Plot 45, Kukatpally, Hyderabad - 500081', 'UPI'); -- Order items for that order INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES (1, 1, 1, 399.00), -- God of Small Things (1, 3, 1, 250.00), -- Immortals of Meluha (1, 5, 1, 99.00); -- Malgudi Days (discounted?) |
Step 3: Quick “It works!” demo queries (playground time)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- All books with their author(s) SELECT b.title, b.price, b.stock_quantity, string_agg(a.full_name, ', ') AS authors FROM books b JOIN book_authors ba ON ba.book_id = b.id JOIN authors a ON a.id = ba.author_id GROUP BY b.id ORDER BY b.title; -- Customers who ordered something + total spent SELECT c.first_name || ' ' || c.last_name AS customer, COUNT(o.id) AS orders_count, COALESCE(SUM(o.total_amount), 0) AS total_spent FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id ORDER BY total_spent DESC; -- Low stock alert SELECT title, stock_quantity FROM books WHERE stock_quantity <= 20 ORDER BY stock_quantity; |
Summary – what we just built
| Table | Purpose | Key relationships |
|---|---|---|
| authors | Store writer info | — |
| books | Main product catalog | — |
| book_authors | Many-to-many link | books ↔ authors |
| customers | Buyers | — |
| orders | Purchase headers | customers → orders |
| order_items | Line items of each order | orders → books (via order_items) |
This tiny bookstore database is now your personal playground!
Want to go further?
Tell me:
- Add more realistic data (10+ books, 20+ customers)?
- Create views / functions for reports?
- Add reviews table + ratings?
- Import from CSV / pagila / dvdrental instead?
- Or practice complex queries on this schema?
Your call — let’s keep building! 🚀
