PostgreSQL Sample Database

PostgreSQL Sample Database

A sample database is useful for testing queries, learning SQL, and developing applications. Below, you'll find a step-by-step guide to creating and loading a sample PostgreSQL database.

1. Download & Install PostgreSQL

If you haven’t installed PostgreSQL yet, download it from:
🔗 https://www.postgresql.org/download/

Verify Installation

After installing, check the version:

psql --version

2. Create a New Database

To create a sample database, open psql or pgAdmin and run:

CREATE DATABASE sampledb;

Switch to the new database:

\c sampledb

3. Create Sample Tables

customers Table

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    city VARCHAR(50),
    country VARCHAR(50)
);

products Table

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2) CHECK (price > 0),
    stock_quantity INT CHECK (stock_quantity >= 0)
);

orders Table

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2) CHECK (total_amount >= 0)
);

order_items Table

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT REFERENCES products(product_id),
    quantity INT CHECK (quantity > 0),
    subtotal DECIMAL(10,2)
);

4. Insert Sample Data

Customers

INSERT INTO customers (first_name, last_name, email, phone, city, country)
VALUES 
    ('John', 'Doe', 'john.doe@email.com', '123-456-7890', 'New York', 'USA'),
    ('Jane', 'Smith', 'jane.smith@email.com', '987-654-3210', 'Los Angeles', 'USA'),
    ('Alice', 'Johnson', 'alice.j@email.com', '555-123-4567', 'London', 'UK');

Products

INSERT INTO products (product_name, category, price, stock_quantity)
VALUES 
    ('Laptop', 'Electronics', 1200.00, 10),
    ('Smartphone', 'Electronics', 800.00, 20),
    ('Tablet', 'Electronics', 500.00, 15);

Orders

INSERT INTO orders (customer_id, total_amount)
VALUES 
    (1, 2000.00),
    (2, 1500.00);

Order Items

INSERT INTO order_items (order_id, product_id, quantity, subtotal)
VALUES 
    (1, 1, 1, 1200.00),
    (1, 2, 1, 800.00),
    (2, 3, 3, 1500.00);

5. Query Sample Data

View All Customers

SELECT * FROM customers;

Get Orders with Customer Details

SELECT o.order_id, c.first_name, c.last_name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Check Product Stock

SELECT product_name, stock_quantity FROM products;

Find Top Customers by Spending

SELECT c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY total_spent DESC;

6. Export and Import Sample Database

Export Database

To export the database as a backup file:

pg_dump -U postgres -d sampledb -f sampledb.sql

Import Database

To restore from a backup file:

psql -U postgres -d sampledb -f sampledb.sql

7. Summary

Action	Command
Create Database	CREATE DATABASE sampledb;
Create Table	CREATE TABLE customers (...);
Insert Data	INSERT INTO customers (...) VALUES (...);
View Data	SELECT * FROM customers;
Export Database	pg_dump -U postgres -d sampledb -f sampledb.sql
Import Database	psql -U postgres -d sampledb -f sampledb.sql

Would you like a sample database with more data for testing? 🚀