SQL Beginner Project: Build a Real-World Supermarket Database with PostgreSQL

If you are learning SQL and want a project that feels practical, this Sunrise Supermarket database is a great place to start. Instead of only reading theory, this project shows how to design tables, insert real sample data, clean and update records, filter business data, summarize results, and connect tables with joins.

In this article, I will walk through the SQL concepts I practiced while building a small supermarket database from scratch.

What is SQL?

SQL stands for Structured Query Language. It is the language used to communicate with relational databases. With SQL, you can create database structures, store data, update records, delete records, and ask questions about the data using queries.

SQL is widely used in software development, data analysis, backend engineering, business intelligence, and reporting. For example, a supermarket can use SQL to manage customers, products, orders, stock levels, and sales reports.

SQL Areas Covered in This Project

This project covers the following beginner-friendly SQL topics:

1. Database creation and schema selection
2. Creating tables with primary keys and foreign keys
3. Using constraints such as UNIQUE, CHECK, and DEFAULT
4. Inserting data into related tables
5. Altering table structure with ALTER TABLE
6. Updating and deleting records
7. Filtering data with WHERE, IN, BETWEEN, and LIKE
8. Sorting and limiting results with ORDER BY and LIMIT
9. Grouping data with GROUP BY and HAVING
10. Joining multiple tables with INNER JOIN and LEFT JOIN

Project Scenario: Sunrise Supermarket

Sunrise Supermarket wants to move its records into a proper database. The database needs four connected tables:

• customers
• products
• orders
• order_items

These tables help answer questions like:

• Which customers placed orders?
• Which products cost more than a certain amount?
• Which orders are still pending?
• What products were ordered and in what quantity?
• How can customer, order, and product data be joined together?

1. Creating the Database Schema

A schema helps organize database objects such as tables. In this project, I created a schema called sunrise and set it as the active search path.

-- Create the schema for the supermarket database
CREATE SCHEMA sunrise;

-- Use the sunrise schema for the rest of the project
SET search_path TO sunrise;

2. Creating the Customers Table

The customers table stores customer details. Each customer has a unique customer_id, and the email and phone number should not be repeated.

-- Create the customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(50),
    email VARCHAR(50) UNIQUE,
    phone_number VARCHAR(50),
    county VARCHAR(50)
);

-- Add a unique constraint to phone_number
ALTER TABLE customers
    ALTER COLUMN phone_number TYPE VARCHAR(50),
    ADD CONSTRAINT unique_phone UNIQUE (phone_number);

3. Inserting Customers

After creating the table, I inserted four customer records.

-- Insert customer records
INSERT INTO customers (full_name, email, phone_number, county)
VALUES
    ('Grace Wambui', '[e-mail]', '0711223344', 'Nairobi'),
    ('Kevin Mutiso', '[e-mail]', '0722334455', 'Nakuru'),
    ('Faith Chebet', '[e-mail]', '0733445566', 'Eldoret'),
    ('Ibrahim Noor', '[e-mail]', '0744556677', 'Mombasa');

-- View all customers
SELECT * FROM customers;

Expected output:

customer_id	full_name	email	phone_number	county
1	Grace Wambui	[e-mail]	0711223344	Nairobi
2	Kevin Mutiso	[e-mail]	0722334455	Nakuru
3	Faith Chebet	[e-mail]	0733445566	Eldoret
4	Ibrahim Noor	[e-mail]	0744556677	Mombasa

4. Creating the Products Table

The products table stores supermarket products. The unit_price must be greater than zero, and stock starts at 0 if no value is provided.

-- Create the products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(50),
    unit_price NUMERIC(10,2) CHECK (unit_price > 0),
    stock INT DEFAULT 0
);

5. Inserting Products

-- Insert product records
INSERT INTO products (product_name, category, unit_price, stock)
VALUES
    ('Maize Flour 2kg', 'Groceries', 180, 50),
    ('Cooking Oil 1L', 'Groceries', 320, 30),
    ('Bathing Soap', 'Toiletries', 85, 100),
    ('Notebook A4', 'Stationery', 60, 200);

-- View all products
SELECT * FROM products;

Expected output:

product_id	product_name	category	unit_price	stock
1	Maize Flour 2kg	Groceries	180.00	50
2	Cooking Oil 1L	Groceries	320.00	30
3	Bathing Soap	Toiletries	85.00	100
4	Notebook A4	Stationery	60.00	200

6. Creating Orders and Order Items

The orders table connects each order to a customer. The order_items table connects each order to the products bought.

-- Create the orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(50) DEFAULT 'Pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

-- Create the order_items table
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT CHECK (quantity > 0),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

7. Inserting Orders and Order Items

Because orders references customers, customers must already exist before orders are inserted. Because order_items references both orders and products, both tables must already contain data.

-- Insert order records
INSERT INTO orders (customer_id, order_date, status)
VALUES
    (1, '2024-03-01', 'Delivered'),
    (2, '2024-03-02', 'Pending'),
    (1, '2024-03-03', 'Delivered'),
    (3, '2024-03-04', 'Cancelled');

-- Insert order item records
INSERT INTO order_items (order_id, product_id, quantity)
VALUES
    (1, 1, 2),
    (1, 3, 1),
    (2, 2, 1),
    (3, 4, 5);

8. Altering Table Structure

After creating the first version of the tables, I made three structure changes:

• Renamed stock to stock_quantity
• Added loyalty_points to customers
• Increased product_name to VARCHAR(150)

-- Rename stock column to stock_quantity
ALTER TABLE products RENAME COLUMN stock TO stock_quantity;

-- Add loyalty_points to customers with a default value of 0
ALTER TABLE customers ADD COLUMN loyalty_points INT DEFAULT 0;

-- Allow longer product names
ALTER TABLE products ALTER COLUMN product_name TYPE VARCHAR(150);

9. Updating and Deleting Data

The project also required changing an order status and deleting a cancelled order.

-- Update order_id 2 so its status becomes Delivered
UPDATE orders
SET status = 'Delivered'
WHERE order_id = 2;

-- Delete the cancelled order
DELETE FROM orders
WHERE order_id = 4;

10. Filtering Products and Customers

Filtering is one of the most common SQL skills. Here are examples using comparison operators, IN, BETWEEN, and LIKE.

Products priced above 100

-- Show every product priced above 100
SELECT product_name, unit_price
FROM products
WHERE unit_price > 100;

Expected output:

product_name	unit_price
Maize Flour 2kg	180.00
Cooking Oil 1L	320.00

Customers not based in Nairobi

-- Show every customer not based in Nairobi
SELECT full_name, county
FROM customers
WHERE county <> 'Nairobi';

Expected output:

full_name	county
Kevin Mutiso	Nakuru
Faith Chebet	Eldoret
Ibrahim Noor	Mombasa

Products priced between 60 and 200

-- Show products priced between 60 and 200, inclusive
SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 60 AND 200
ORDER BY unit_price;

Expected output:

product_name	unit_price
Notebook A4	60.00
Bathing Soap	85.00
Maize Flour 2kg	180.00

Customers in selected counties

-- Show customers who live in Nairobi, Nakuru, or Mombasa
SELECT full_name, county
FROM customers
WHERE county IN ('Nairobi', 'Nakuru', 'Mombasa');

Expected output:

full_name	county
Grace Wambui	Nairobi
Kevin Mutiso	Nakuru
Ibrahim Noor	Mombasa

Products with Oil in the name

-- Show products whose name contains Oil
SELECT product_name
FROM products
WHERE product_name LIKE '%Oil%';

Expected output:

product_name
Cooking Oil 1L

11. Sorting and Limiting Results

Sorting helps organize query results, while LIMIT controls how many rows are returned.

-- Show orders that are still Pending, sorted by earliest order_date first
SELECT status, order_date
FROM orders
WHERE status = 'Pending'
ORDER BY order_date ASC;

Expected output after updating order 2 to Delivered:

status	order_date

-- Show the two most expensive products
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 2;

Expected output:

product_name	unit_price
Cooking Oil 1L	320.00
Maize Flour 2kg	180.00

12. Grouping and Aggregates

Aggregate functions summarize data. In this project, I used grouping to count how many orders each customer placed.

-- Count how many orders each customer has placed
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

Expected output after deleting order 4:

customer_id	total_orders
1	2
2	1

-- Show only customers who placed more than one order
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

Expected output:

customer_id	total_orders
1	2

13. Joining Tables

Joins are used to combine related information from multiple tables.

Inner join customers with orders

-- Show each customer's name next to their order_id and status
SELECT c.full_name, o.order_id, o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Expected output:

full_name	order_id	status
Grace Wambui	1	Delivered
Kevin Mutiso	2	Delivered
Grace Wambui	3	Delivered

Left join orders with order items

-- Show every order with its order items
SELECT *
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id;

Join order items with products

-- Show each order item with the product name and quantity
SELECT oi.order_item_id, p.product_name, oi.quantity
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.product_id;

Expected output:

order_item_id	product_name	quantity
1	Maize Flour 2kg	2
2	Bathing Soap	1
3	Cooking Oil 1L	1
4	Notebook A4	5

Join all four tables

This query combines customers, orders, order items, and products to show who ordered what.

-- Join all four tables to show one row per item ordered
SELECT c.full_name, o.order_id, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

Expected output:

full_name	order_id	product_name	quantity
Grace Wambui	1	Maize Flour 2kg	2
Grace Wambui	1	Bathing Soap	1
Kevin Mutiso	2	Cooking Oil 1L	1
Grace Wambui	3	Notebook A4	5

14. Total Quantity Ordered Per Product

The final challenge was to group the joined data and calculate total quantity ordered per product.

-- Show total quantity ordered per product across all customers
SELECT p.product_name, SUM(oi.quantity) AS total_quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name;

Expected output:

product_name	total_quantity
Maize Flour 2kg	2
Bathing Soap	1
Cooking Oil 1L	1
Notebook A4	5

Key Lessons from This SQL Project

This project helped me practice how relational databases work in a real-world example. The biggest lessons were:

• Tables should be created in the correct order when foreign keys are involved.
• Constraints help protect data quality.
• ALTER TABLE is useful when database requirements change.
• Filtering helps answer specific business questions.
• Aggregates and grouping turn raw records into summaries.
• Joins are powerful because they connect data from multiple related tables.

Final Thoughts

Building the Sunrise Supermarket database was a helpful beginner SQL project because it covered both database design and practical querying. By the end, I had practiced creating tables, inserting data, updating records, filtering results, summarizing data, and joining tables together.

If you are learning SQL, I recommend building a small project like this because it helps you understand how SQL is used in real business situations.