Unit 3: Relational Operations

Why This Chapter Pays Your Salary

JOINs and subqueries are the #1 topic in SQL interviews. Every data analyst, backend developer, and DBA writes multi-table queries daily. If Unit 2 taught you to build one table, this unit teaches you to connect the entire database into meaningful reports. A Flipkart analyst writing a "Top 10 products by revenue per category per month" report uses JOINs, GROUP BY, HAVING, window functions, and CTEs — all in one query.

📦 Schema Setup — E-Commerce System (Flipkart-style)

All examples in this chapter use an e-commerce domain — familiar and interview-relevant.

SQL — Schema Setup
CREATE TABLE categories (
    category_id   NUMBER(5)     PRIMARY KEY,
    category_name VARCHAR2(50)  NOT NULL UNIQUE
);
CREATE TABLE customers (
    customer_id   NUMBER(10)    PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    city          VARCHAR2(50),
    reg_date      DATE           DEFAULT SYSDATE
);
CREATE TABLE products (
    product_id    NUMBER(10)    PRIMARY KEY,
    product_name  VARCHAR2(100) NOT NULL,
    category_id   NUMBER(5)     REFERENCES categories(category_id),
    price         NUMBER(10,2)  CHECK (price > 0)
);
CREATE TABLE orders (
    order_id      NUMBER(10)    PRIMARY KEY,
    customer_id   NUMBER(10)    REFERENCES customers(customer_id),
    order_date    DATE           DEFAULT SYSDATE,
    status        VARCHAR2(15)  CHECK (status IN ('PENDING','SHIPPED','DELIVERED','CANCELLED'))
);
CREATE TABLE order_items (
    item_id       NUMBER(10)    PRIMARY KEY,
    order_id      NUMBER(10)    REFERENCES orders(order_id),
    product_id    NUMBER(10)    REFERENCES products(product_id),
    quantity      NUMBER(5)     CHECK (quantity > 0),
    unit_price    NUMBER(10,2)  NOT NULL
);

-- Sample Data
INSERT INTO categories VALUES (1,'Electronics');
INSERT INTO categories VALUES (2,'Clothing');
INSERT INTO categories VALUES (3,'Books');
INSERT INTO categories VALUES (4,'Home & Kitchen');

INSERT INTO customers VALUES (1,'Rahul Sharma','Mumbai',DATE '2023-01-15');
INSERT INTO customers VALUES (2,'Priya Patel','Pune',DATE '2023-03-20');
INSERT INTO customers VALUES (3,'Amit Joshi','Delhi',DATE '2023-06-10');
INSERT INTO customers VALUES (4,'Sneha Kulkarni','Bangalore',DATE '2024-01-05');
INSERT INTO customers VALUES (5,'Rajesh Gupta','Mumbai',DATE '2024-02-28');

INSERT INTO products VALUES (101,'iPhone 15',1,79999);
INSERT INTO products VALUES (102,'Samsung Galaxy S24',1,69999);
INSERT INTO products VALUES (103,'Levi''s Jeans',2,2499);
INSERT INTO products VALUES (104,'DBMS by Navathe',3,650);
INSERT INTO products VALUES (105,'Prestige Cooker',4,1850);
INSERT INTO products VALUES (106,'Allen Solly Shirt',2,1299);

INSERT INTO orders VALUES (1001,1,DATE '2024-11-15','DELIVERED');
INSERT INTO orders VALUES (1002,2,DATE '2024-11-20','DELIVERED');
INSERT INTO orders VALUES (1003,1,DATE '2024-12-01','SHIPPED');
INSERT INTO orders VALUES (1004,3,DATE '2024-12-10','CANCELLED');
INSERT INTO orders VALUES (1005,2,DATE '2025-01-05','PENDING');

INSERT INTO order_items VALUES (1,1001,101,1,79999);
INSERT INTO order_items VALUES (2,1001,104,2,650);
INSERT INTO order_items VALUES (3,1002,103,3,2499);
INSERT INTO order_items VALUES (4,1002,106,1,1299);
INSERT INTO order_items VALUES (5,1003,102,1,69999);
INSERT INTO order_items VALUES (6,1004,105,2,1850);
INSERT INTO order_items VALUES (7,1005,101,1,79999);
COMMIT;

Learning Outcomes — Bloom's Taxonomy

Concept Explanations

3.1 Aggregate Functions & GROUP BY

SQL — Aggregate Functions
-- ═══════════════════════════════════════
-- Example 1: Basic aggregates on e-commerce data
-- Business Context: Dashboard KPIs for an e-commerce platform
-- ═══════════════════════════════════════

SELECT
    COUNT(*)                          AS total_orders,
    COUNT(DISTINCT customer_id)       AS unique_customers,
    SUM(oi.quantity * oi.unit_price)  AS total_revenue,
    AVG(oi.quantity * oi.unit_price)  AS avg_order_value,
    MIN(o.order_date)                 AS first_order,
    MAX(o.order_date)                 AS last_order
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status != 'CANCELLED';

SQL — GROUP BY & HAVING
-- ═══════════════════════════════════════
-- Example 2: Revenue per category (only categories with revenue > ₹5000)
-- Business Context: Identify top-performing product categories
-- ═══════════════════════════════════════

SELECT 
    c.category_name,
    COUNT(DISTINCT o.order_id)             AS total_orders,
    SUM(oi.quantity)                        AS units_sold,
    SUM(oi.quantity * oi.unit_price)        AS total_revenue,
    ROUND(AVG(oi.unit_price), 2)            AS avg_price
FROM categories c
JOIN products p   ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o     ON oi.order_id = o.order_id
WHERE o.status != 'CANCELLED'       -- WHERE filters rows BEFORE grouping
GROUP BY c.category_name
HAVING SUM(oi.quantity * oi.unit_price) > 5000  -- HAVING filters groups AFTER
ORDER BY total_revenue DESC;

-- Output:
-- category_name | orders | units_sold | revenue  | avg_price
-- Electronics   | 3      | 3          | 229997   | 76666.33
-- Clothing      | 1      | 4          | 8796     | 2199.00

ROLLUP, CUBE & GROUPING SETS

SQL — Advanced Grouping
-- ROLLUP: Hierarchical subtotals + grand total
SELECT 
    c.category_name,
    TO_CHAR(o.order_date, 'YYYY-MM') AS order_month,
    SUM(oi.quantity * oi.unit_price)   AS revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
GROUP BY ROLLUP(c.category_name, TO_CHAR(o.order_date, 'YYYY-MM'));

-- Output includes: category+month rows, category subtotals (month=NULL),
-- and grand total row (both NULL). Perfect for financial reports.

-- CUBE: All possible subtotal combinations
GROUP BY CUBE(category_name, city);
-- Generates: (cat,city), (cat,NULL), (NULL,city), (NULL,NULL)

-- GROUPING SETS: Custom subset of groupings
GROUP BY GROUPING SETS((category_name), (city), ());
-- Only: by category, by city, and grand total — no cross combos

3.2 SQL JOINs — Connecting Tables

JOIN Types — Visual Summary
Table A (orders)        Table B (customers)
┌────┬─────┐            ┌────┬──────────┐
│ o_id│c_id │            │c_id│ name     │
├────┼─────┤            ├────┼──────────┤
│1001│  1  │            │  1 │ Rahul    │
│1002│  2  │            │  2 │ Priya    │
│1003│  1  │            │  3 │ Amit     │
│1004│  3  │            │  4 │ Sneha    │← No orders
│1005│  2  │            │  5 │ Rajesh   │← No orders
└────┴─────┘            └────┴──────────┘

INNER JOIN:  Only matching rows (1001-Rahul, 1002-Priya, etc.) — 5 rows
LEFT JOIN:   All A rows + matching B (same 5 rows, all orders have customers)
RIGHT JOIN:  All B rows + matching A — includes Sneha(NULL), Rajesh(NULL) — 7 rows
FULL JOIN:   All rows from both — 7 rows (all orders + all customers)
CROSS JOIN:  Every A × every B — 5 × 5 = 25 rows (Cartesian product)

INNER JOIN

SQL
-- ═══════════════════════════════════════
-- Example 3: INNER JOIN — Orders with customer details
-- Business Context: Order listing with customer names
-- ═══════════════════════════════════════

-- SQL:1999 Standard (RECOMMENDED)
SELECT o.order_id, c.customer_name, c.city, o.order_date, o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

-- Output:
-- order_id | customer_name  | city      | order_date | status
-- 1005     | Priya Patel    | Pune      | 2025-01-05 | PENDING
-- 1004     | Amit Joshi     | Delhi     | 2024-12-10 | CANCELLED
-- 1003     | Rahul Sharma   | Mumbai    | 2024-12-01 | SHIPPED
-- 1002     | Priya Patel    | Pune      | 2024-11-20 | DELIVERED
-- 1001     | Rahul Sharma   | Mumbai    | 2024-11-15 | DELIVERED

LEFT OUTER JOIN

SQL
-- ═══════════════════════════════════════
-- Example 4: LEFT JOIN — All customers, even those with no orders
-- Business Context: Find customers who haven't ordered yet (marketing target)
-- ═══════════════════════════════════════

SELECT c.customer_name, c.city, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_name;

-- Output:
-- customer_name  | city      | order_id | status
-- Amit Joshi     | Delhi     | 1004     | CANCELLED
-- Priya Patel    | Pune      | 1002     | DELIVERED
-- Priya Patel    | Pune      | 1005     | PENDING
-- Rahul Sharma   | Mumbai    | 1001     | DELIVERED
-- Rahul Sharma   | Mumbai    | 1003     | SHIPPED
-- Rajesh Gupta   | Mumbai    | NULL     | NULL      ← No orders!
-- Sneha Kulkarni | Bangalore | NULL     | NULL      ← No orders!

-- Find ONLY customers with NO orders:
SELECT c.customer_name, c.city
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;  -- The NULL-check trick

SELF JOIN & Multi-Table JOINs

SQL
-- ═══════════════════════════════════════
-- Example 5: SELF JOIN — Customers from the same city
-- Business Context: "People who live near you also bought..."
-- ═══════════════════════════════════════

SELECT c1.customer_name AS customer_a, 
       c2.customer_name AS customer_b, 
       c1.city
FROM customers c1
JOIN customers c2 ON c1.city = c2.city 
    AND c1.customer_id < c2.customer_id;  -- Avoid duplicates (Rahul-Rajesh, not Rajesh-Rahul)

-- 4-table JOIN: Order details with customer, product, and category
SELECT c.customer_name, p.product_name, cat.category_name,
       oi.quantity, oi.unit_price, (oi.quantity * oi.unit_price) AS line_total
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
JOIN categories cat ON p.category_id = cat.category_id
WHERE o.status != 'CANCELLED'
ORDER BY c.customer_name, o.order_date;

JOIN Summary Table

3.3 Set Operators — UNION, INTERSECT, MINUS/EXCEPT

SQL — Set Operators
-- ═══════════════════════════════════════
-- Example 6: Combine active + archived customer records (bank audit)
-- ═══════════════════════════════════════

-- UNION: combines and removes duplicates
SELECT customer_name, city FROM customers WHERE city = 'Mumbai'
UNION
SELECT customer_name, city FROM customers WHERE reg_date > DATE '2024-01-01';

-- UNION ALL: keeps duplicates — faster (no sort needed)
SELECT customer_name FROM active_customers
UNION ALL
SELECT customer_name FROM archived_customers;

-- INTERSECT: rows present in BOTH queries
SELECT customer_id FROM orders WHERE status = 'DELIVERED'
INTERSECT
SELECT customer_id FROM orders WHERE order_date > DATE '2024-12-01';
-- Customers with delivered orders AND orders after Dec 2024

-- MINUS (Oracle) / EXCEPT (PostgreSQL): rows in first NOT in second
SELECT customer_id FROM customers
MINUS  -- PostgreSQL: EXCEPT
SELECT customer_id FROM orders;
-- Customers who have NEVER placed an order (same as LEFT JOIN + IS NULL)

3.4 Views — Virtual Tables

SQL — Views
-- ═══════════════════════════════════════
-- Example 7: Views for different user roles
-- Business Context: Dashboard views for sales team vs management
-- ═══════════════════════════════════════

-- Simple view: Order summary (hide internal IDs)
CREATE OR REPLACE VIEW v_order_summary AS
SELECT o.order_id, c.customer_name, c.city,
       o.order_date, o.status,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, c.customer_name, c.city, o.order_date, o.status;

-- Now use it like a table:
SELECT * FROM v_order_summary WHERE status = 'DELIVERED';

-- Read-only view (prevent DML)
CREATE VIEW v_product_catalog AS
SELECT p.product_name, c.category_name, p.price
FROM products p JOIN categories c ON p.category_id = c.category_id
WITH READ ONLY;

-- View with CHECK OPTION (prevent invisible row inserts)
CREATE VIEW v_mumbai_customers AS
SELECT * FROM customers WHERE city = 'Mumbai'
WITH CHECK OPTION;
-- INSERT into v_mumbai_customers with city='Delhi' is REJECTED
-- because the row wouldn't be visible through the view

SQL — Materialized View (Oracle)
CREATE MATERIALIZED VIEW mv_category_sales
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT c.category_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_name;

-- Refresh manually when needed:
-- EXEC DBMS_MVIEW.REFRESH('mv_category_sales');

3.5 Subqueries & CTEs

SQL — Subquery Types
-- ═══════════════════════════════════════
-- Example 8: Single-row subquery
-- "Find products priced above the average"
-- ═══════════════════════════════════════

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Multiple-row subquery (IN)
-- "Find customers who ordered Electronics"
SELECT customer_name FROM customers
WHERE customer_id IN (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.category_id = 1
);

-- Correlated subquery (inner query references outer query)
-- "Find products priced above their category average"
SELECT p.product_name, p.price, p.category_id
FROM products p
WHERE p.price > (
    SELECT AVG(p2.price) FROM products p2
    WHERE p2.category_id = p.category_id  -- References outer p!
);

-- EXISTS (often faster than IN for large datasets)
-- "Customers who have at least one delivered order"
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id AND o.status = 'DELIVERED'
);

-- Scalar subquery in SELECT clause
SELECT p.product_name, p.price,
    (SELECT AVG(price) FROM products) AS overall_avg,
    p.price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products p;

WITH Clause — Common Table Expressions (CTEs)

SQL — CTEs
-- ═══════════════════════════════════════
-- Example 9: CTE for readable complex queries
-- Business Context: Top customer per city by total spend
-- ═══════════════════════════════════════

WITH customer_spend AS (
    SELECT c.customer_id, c.customer_name, c.city,
           SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'CANCELLED'
    GROUP BY c.customer_id, c.customer_name, c.city
),
ranked AS (
    SELECT cs.*,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY total_spent DESC) AS rn
    FROM customer_spend cs
)
SELECT customer_name, city, total_spent
FROM ranked WHERE rn = 1;

-- Recursive CTE: Category hierarchy (Electronics → Mobiles → Smartphones)
WITH RECURSIVE cat_tree AS (  -- PostgreSQL; Oracle: no RECURSIVE keyword
    SELECT category_id, category_name, parent_id, 1 AS level
    FROM categories WHERE parent_id IS NULL  -- Root categories
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.category_id
)
SELECT LPAD(' ', (level-1)*4) || category_name AS hierarchy
FROM cat_tree ORDER BY level;

3.6 Relational Algebra — The Mathematical Foundation

Relational Algebra → SQL Mapping
Query: "Names and cities of customers from Mumbai who have placed orders"

Relational Algebra:
  πcustomer_name, city(σcity='Mumbai'(customers ⋈ orders))

Step by step:
  1. ⋈ (Natural Join):    customers JOIN orders ON customer_id
  2. σ (Selection):       WHERE city = 'Mumbai'
  3. π (Projection):      SELECT customer_name, city

SQL:
  SELECT DISTINCT c.customer_name, c.city
  FROM customers c JOIN orders o ON c.customer_id = o.customer_id
  WHERE c.city = 'Mumbai';

Industry Problems

SQL
CREATE TABLE upi_transactions (
    txn_id       NUMBER(15)    PRIMARY KEY,
    sender_vpa   VARCHAR2(50)  NOT NULL,
    receiver_vpa VARCHAR2(50)  NOT NULL,
    amount       NUMBER(12,2)  CHECK (amount > 0),
    status       VARCHAR2(10)  CHECK (status IN ('SUCCESS','FAILED','PENDING')),
    txn_date     DATE,
    receiver_bank VARCHAR2(30)
);

SQL
WITH monthly_stats AS (
    SELECT
        TO_CHAR(txn_date, 'YYYY-MM')  AS txn_month,
        COUNT(*)                       AS total_txn,
        SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS success_count,
        SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END)  AS failed_count,
        SUM(CASE WHEN status = 'SUCCESS' THEN amount ELSE 0 END) AS total_volume,
        ROUND(AVG(CASE WHEN status = 'SUCCESS' THEN amount END), 2) AS avg_txn_value,
        ROUND(SUM(CASE WHEN status='SUCCESS' THEN 1 ELSE 0 END)*100.0/COUNT(*),2) AS success_rate
    FROM upi_transactions
    GROUP BY TO_CHAR(txn_date, 'YYYY-MM')
)
SELECT * FROM monthly_stats ORDER BY txn_month DESC;

-- Top 5 receiver banks by volume per month (using window function)
WITH bank_volume AS (
    SELECT TO_CHAR(txn_date,'YYYY-MM') AS month, receiver_bank,
           SUM(amount) AS vol,
           DENSE_RANK() OVER (PARTITION BY TO_CHAR(txn_date,'YYYY-MM')
                              ORDER BY SUM(amount) DESC) AS rnk
    FROM upi_transactions WHERE status='SUCCESS'
    GROUP BY TO_CHAR(txn_date,'YYYY-MM'), receiver_bank
)
SELECT * FROM bank_volume WHERE rnk <= 5;

SQL
-- Students with multiple offers
SELECT s.student_name, COUNT(po.offer_id) AS num_offers,
       MAX(po.ctc_lpa) AS highest_ctc
FROM students s
JOIN placement_offers po ON s.student_id = po.student_id
GROUP BY s.student_name
HAVING COUNT(po.offer_id) > 1
ORDER BY highest_ctc DESC;

-- Department-wise placement rate
SELECT d.dept_name,
       COUNT(DISTINCT s.student_id) AS total_students,
       COUNT(DISTINCT po.student_id) AS placed,
       ROUND(COUNT(DISTINCT po.student_id)*100.0/COUNT(DISTINCT s.student_id),1) AS placement_pct
FROM departments d
JOIN students s ON d.dept_id = s.dept_id
LEFT JOIN placement_offers po ON s.student_id = po.student_id
GROUP BY d.dept_name
ORDER BY placement_pct DESC;

SQL
-- Find products frequently bought WITH iPhone 15 (product_id=101)
SELECT p.product_name, COUNT(*) AS co_purchase_count
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id   -- Same order
    AND oi1.product_id != oi2.product_id             -- Different product
JOIN products p ON oi2.product_id = p.product_id
WHERE oi1.product_id = 101  -- iPhone 15
GROUP BY p.product_name
ORDER BY co_purchase_count DESC
FETCH FIRST 5 ROWS ONLY;

-- Output:
-- product_name     | co_purchase_count
-- DBMS by Navathe  | 1   (bought together in order 1001)

Lab Exercises

MCQ Assessment Bank — 15 Questions

Hover to reveal answer and explanation.

Chapter Summary

JOINS:
  FROM a INNER JOIN b ON a.id = b.a_id       -- Matching only
  FROM a LEFT JOIN b ON a.id = b.a_id        -- All a + matching b
  FROM a FULL OUTER JOIN b ON a.id = b.a_id  -- All from both
  FROM a CROSS JOIN b                         -- Cartesian product
  FROM a a1 JOIN a a2 ON a1.col = a2.col     -- Self join

AGGREGATES:
  SELECT col, COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x)
  FROM t GROUP BY col HAVING COUNT(*) > 5
  GROUP BY ROLLUP(col1, col2)  -- Subtotals + grand total

SET OPS:
  query1 UNION query2          -- Dedup
  query1 UNION ALL query2      -- Keep dupes (faster)
  query1 INTERSECT query2      -- Common rows
  query1 MINUS query2          -- Oracle (EXCEPT in PG)

SUBQUERIES:
  WHERE col > (SELECT AVG(col) FROM t)            -- Single-row
  WHERE col IN (SELECT col FROM t2)                -- Multi-row
  WHERE col > (SELECT AVG(x) FROM t2 WHERE t2.id = t.id)  -- Correlated
  WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id)      -- EXISTS

VIEWS:
  CREATE [OR REPLACE] VIEW v AS SELECT ...
  CREATE VIEW v AS ... WITH CHECK OPTION
  CREATE VIEW v AS ... WITH READ ONLY
  CREATE MATERIALIZED VIEW mv AS ... REFRESH ON DEMAND

RELATIONAL ALGEBRA:
  σ = WHERE | π = SELECT cols | ⋈ = JOIN | ∪ = UNION
  − = MINUS/EXCEPT | × = CROSS JOIN | ρ = AS alias

Interview & Career Preparation

🎓 Practice Platforms

• LeetCode SQL 50 — 50 curated SQL problems. Covers JOINs, subqueries, window functions.
• HackerRank SQL — Free challenges from Easy to Hard. Earn SQL certificates.
• pgexercises.com — PostgreSQL-specific exercises with a real schema.
• SQLZoo — Interactive SQL tutorials with quizzes.
• Oracle Live SQL — Free Oracle environment in the browser.