1. Introduction
PostgreSQL (Postgres) is a powerful, open‑source relational database known for its reliability and strong SQL compliance. If you understand how to:
- Design tables
- Insert and query data
- Join tables together
- Aggregate and filter results
you already have the tools needed for most production systems.
We'll learn these concepts using:
- Customers
- Products
- Orders
- Order items
- Promotions
2. Exercise Setup - Core Tables
Customers Table
Stores customer information.
CREATE TABLE Customers (
CustomerID BIGSERIAL PRIMARY KEY,
LastName VARCHAR(100) NOT NULL,
FirstName VARCHAR(100) NOT NULL,
DOB DATE NOT NULL,
IsDeleted BOOLEAN NOT NULL DEFAULT FALSE,
CreateBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate TIMESTAMP NOT NULL DEFAULT NOW(),
ModifyBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate TIMESTAMP NOT NULL DEFAULT NOW(),
CHECK (EXTRACT(YEAR FROM DOB) >= 1900)
);
Products Table
Stores products available for purchase.
CREATE TABLE Products (
ProductID BIGSERIAL PRIMARY KEY,
ProductName VARCHAR(1000) NOT NULL,
ProductCode VARCHAR(1000) NOT NULL,
AvailableQuantity INT NOT NULL,
IsDeleted BOOLEAN NOT NULL DEFAULT FALSE,
CreateBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate TIMESTAMP NOT NULL DEFAULT NOW(),
ModifyBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate TIMESTAMP NOT NULL DEFAULT NOW(),
CHECK (AvailableQuantity >= 0)
);
Orders Table
Represents a purchase made by a customer.
CREATE TABLE Orders (
OrderID BIGSERIAL PRIMARY KEY,
CustomerID BIGINT,
OrderNumber VARCHAR(1000) NOT NULL,
OrderDate TIMESTAMP NOT NULL,
IsDeleted BOOLEAN NOT NULL DEFAULT FALSE,
CreateBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate TIMESTAMP NOT NULL DEFAULT NOW(),
ModifyBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate TIMESTAMP NOT NULL DEFAULT NOW(),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
OrderItems Table
Breaks orders into product line items.
CREATE TABLE OrderItems (
OrderItemID BIGSERIAL PRIMARY KEY,
OrderID BIGINT,
ProductID BIGINT,
Quantity INT NOT NULL,
IsDeleted BOOLEAN NOT NULL DEFAULT FALSE,
CreateBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate TIMESTAMP NOT NULL DEFAULT NOW(),
ModifyBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate TIMESTAMP NOT NULL DEFAULT NOW(),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
CHECK (Quantity > 0)
);
3. Promotions Setup
Promotions Table
CREATE TABLE Promotions (
PromotionID BIGSERIAL PRIMARY KEY,
PromotionName VARCHAR(1000),
PromotionCode VARCHAR(1000),
ActiveFrom TIMESTAMP NOT NULL DEFAULT '1900-01-01',
ActiveTo TIMESTAMP NOT NULL DEFAULT '2999-12-31',
IsActive BOOLEAN NOT NULL DEFAULT TRUE,
IsDeleted BOOLEAN NOT NULL DEFAULT FALSE,
CreateBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate TIMESTAMP NOT NULL DEFAULT NOW(),
ModifyBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate TIMESTAMP NOT NULL DEFAULT NOW()
);
OrderPromotions Table
(One order can apply multiple promotions)
CREATE TABLE OrderPromotions (
OrderPromotionID BIGSERIAL PRIMARY KEY,
OrderID BIGINT,
PromotionID BIGINT,
IsDeleted BOOLEAN NOT NULL DEFAULT FALSE,
CreateBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
CreateDate TIMESTAMP NOT NULL DEFAULT NOW(),
ModifyBy VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',
ModifyDate TIMESTAMP NOT NULL DEFAULT NOW(),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (PromotionID) REFERENCES Promotions(PromotionID)
);
4. Insert Sample Data
Customers
INSERT INTO Customers (LastName, FirstName, DOB) VALUES
('Au Yeung', 'David', '1980-12-31'),
('Chan', 'Peter', '1982-01-15');
Products
INSERT INTO Products (ProductName, ProductCode, AvailableQuantity) VALUES
('Android Phone', 'A0001', 100),
('iPhone', 'I0001', 100),
('iPad', 'I0002', 100);
Orders and Order Items
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate)
VALUES (1, 'ORD0001', NOW());
INSERT INTO OrderItems (OrderID, ProductID, Quantity)
VALUES (1, 2, 10);
INSERT INTO Orders (CustomerID, OrderNumber, OrderDate)
VALUES (2, 'ORD0002', NOW());
INSERT INTO OrderItems (OrderID, ProductID, Quantity)
VALUES (2, 1, 5);
Promotions
INSERT INTO Promotions (PromotionName, PromotionCode, ActiveFrom, ActiveTo) VALUES
('New Year Discount', 'NY2025', '2026-01-01', '2026-01-31'),
('VIP Customer Promo', 'VIP10', '2026-01-01', '2999-12-31');
Order Promotions
INSERT INTO OrderPromotions (OrderID, PromotionID) VALUES
(1, 1),
(1, 2);
INSERT INTO OrderPromotions (OrderID, PromotionID) VALUES
(2, 1);
Inspect the Data
SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM OrderItems;
SELECT * FROM Promotions;
SELECT * FROM OrderPromotions;
5. Ten Foundational SQL Questions
- Which promotions are currently active?
- Which promotions have never been used?
- How many promotions are applied to each order?
- Which orders have more than one promotion?
- List all customers and their orders (including customers with none).
- What is the total quantity ordered per product?
- Which products have never been ordered?
- What is the latest order for each customer?
- Which orders were created in the last 30 days?
- Which promotion is used the most?
6. Answers
1. Active Promotions
SELECT *
FROM Promotions
WHERE IsActive = TRUE
AND IsDeleted = FALSE
AND NOW() BETWEEN ActiveFrom AND ActiveTo;
2. Promotions Never Used
SELECT p.*
FROM Promotions p
LEFT JOIN OrderPromotions op
ON p.PromotionID = op.PromotionID
AND op.IsDeleted = FALSE
WHERE op.OrderPromotionID IS NULL;
3. Promotion Count per Order
SELECT OrderID, COUNT(*) AS PromotionCount
FROM OrderPromotions
WHERE IsDeleted = FALSE
GROUP BY OrderID;
4. Orders with Multiple Promotions
SELECT OrderID
FROM OrderPromotions
WHERE IsDeleted = FALSE
GROUP BY OrderID
HAVING COUNT(*) > 1;
5. Customers and Their Orders
SELECT c.CustomerID, c.FirstName, c.LastName, o.OrderID, o.OrderDate
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
AND o.IsDeleted = FALSE;
6. Total Quantity per Product
SELECT p.ProductName, SUM(oi.Quantity) AS TotalOrdered
FROM Products p
JOIN OrderItems oi ON p.ProductID = oi.ProductID
WHERE oi.IsDeleted = FALSE
GROUP BY p.ProductName;
7. Products Never Ordered
SELECT p.*
FROM Products p
LEFT JOIN OrderItems oi
ON p.ProductID = oi.ProductID
AND oi.IsDeleted = FALSE
WHERE oi.OrderItemID IS NULL;
8. Latest Order per Customer
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY CustomerID
ORDER BY OrderDate DESC
) AS rn
FROM Orders
WHERE IsDeleted = FALSE
)
SELECT *
FROM cte
WHERE rn = 1;
9. Orders in the Last 30 Days
SELECT *
FROM Orders
WHERE OrderDate >= NOW() - INTERVAL '30 days'
AND IsDeleted = FALSE;
10. Most Used Promotion
SELECT p.PromotionName, COUNT(*) AS UsageCount
FROM Promotions p
JOIN OrderPromotions op
ON p.PromotionID = op.PromotionID
WHERE op.IsDeleted = FALSE
GROUP BY p.PromotionName
ORDER BY UsageCount DESC
LIMIT 1;
7. Conclusion
This PostgreSQL recap covered the most important SQL foundations:
- Table design with primary and foreign keys
- One‑to‑many and many‑to‑many relationships
-
JOIN,GROUP BY, andHAVING - Window functions for real business problems
- Writing SQL that reflects real‑world questions
If you can comfortably understand and write these queries, you already have strong SQL fundamentals, regardless of database size or system complexity.
Feel free to extend this schema, add more data, or challenge yourself with new questions.
That's how real SQL mastery is built.
SOCIAL SHARE CARD GENERATOR