PostgreSQL SQL Recap: Building Strong Foundational SQL Skills

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

1. Which promotions are currently active?
2. Which promotions have never been used?
3. How many promotions are applied to each order?
4. Which orders have more than one promotion?
5. List all customers and their orders (including customers with none).
6. What is the total quantity ordered per product?
7. Which products have never been ordered?
8. What is the latest order for each customer?
9. Which orders were created in the last 30 days?
10. 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, and HAVING
• 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.