Basic SELECT Queries

1️⃣ Retrieving Data – The Core of SQL

The SELECT statement is the primary read‑only operation in a relational database. It defines **what** columns you want, **from which** tables, and optionally how the rows should be filtered, ordered, grouped, or limited.

SELECT name, price FROM products;
SELECT * FROM employees; -- all columns (discouraged in production)

2️⃣ Aliases – Giving Columns & Tables Friendly Names

Aliases improve readability, especially in joins or when column names clash.

SELECT first_name AS name FROM users;
SELECT u.first_name AS fname,
       u.last_name  AS lname FROM users AS u;

Table aliases also let you qualify columns (t1.id) which is mandatory when two tables share a column name.

3️⃣ Filtering Rows – WHERE Clause

SELECT name, price
FROM products
WHERE price > 100 AND category = 'Electronics';

Common comparison operators: =, <>, >, <, >=, <= and set operators IN, NOT IN. Use LIKE for pattern matching.

SELECT *
FROM employees
WHERE email LIKE '%@example.com';   -- ends with @example.com

4️⃣ Ordering & Limiting – ORDER BY, LIMIT/OFFSET

SELECT name, price
FROM products
WHERE price > 0
ORDER BY price DESC
LIMIT 10 OFFSET 20;   -- pagination: page 3 (0‑based)

Use ASC (default) for ascending order. OFFSET is optional – without it, LIMIT 10 simply returns the first ten rows.

5️⃣ Removing Duplicates – DISTINCT

SELECT DISTINCT department_id FROM employees;

DISTINCT works on the whole projected row. If you need distinct on a single column but keep other columns, use a GROUP BY (see next section).

6️⃣ Aggregates & Grouping – Summaries & Statistics

To obtain aggregates **per group**, combine them with GROUP BY:

SELECT department_id,
       COUNT(*)                AS employee_cnt,
       AVG(salary)             AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;   -- keep only large departments

HAVING filters groups after aggregation (equivalent to WHERE for rows).

7️⃣ Joins – Bringing Data Together

Four basic join types (inner, left/right outer, full outer, cross). The most common is INNER JOIN.

SELECT e.first_name,
       e.last_name,
       d.name AS department
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id
WHERE d.name = 'Engineering'
ORDER BY e.last_name;

For left/right outer join:

SELECT c.name AS customer,
       o.id   AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
ORDER BY c.name;

Use FULL OUTER JOIN when you need rows that appear in *either* table (PostgreSQL supports it; MySQL does not natively).

8️⃣ Set Operators – Combining Result Sets

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;   -- eliminates duplicates

SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;   -- only common emails

SELECT email FROM customers
EXCEPT
SELECT email FROM newsletter_subscribers;   -- in customers but not in subscribers

All participating SELECTs must have the **same column count** and **compatible data types**.

9️⃣ Sub‑queries – Nesting SELECTs

Inline (derived table)

SELECT dept_id, avg_salary
FROM (
    SELECT department_id AS dept_id,
           AVG(salary)    AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
WHERE avg_salary > 80000;

Correlated sub‑query (depends on outer row)

SELECT e.id, e.first_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees
    WHERE department_id = e.department_id
);

Correlated sub‑queries can be slower; many RDBMS transform them into joins automatically.

10️⃣ Window Functions – Row‑Level Analytics

SELECT employee_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY department_id, salary DESC;

Window functions keep the original row granularity while adding a calculated column (e.g., running total, moving average, rank).

11️⃣ Performance Tips – EXPLAIN and Indexes

Before writing a complex SELECT, inspect the execution plan:

EXPLAIN ANALYZE
SELECT e.first_name, e.last_name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering'
ORDER BY e.last_name
LIMIT 50;

The output tells you whether an **index scan**, **sequential scan**, or **hash join** is used. If you see “Seq Scan” on a large table with a WHERE clause on an indexed column, create an index:

CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_dept_name ON departments(name);

Remember: each index speeds reads but slows INSERT/UPDATE/DELETE and consumes storage.

12️⃣ Safety – Avoiding SQL Injection in SELECTs

Even a read‑only query can be hijacked to return data you never intended. Always use **parameterised queries** / **prepared statements** in application code.

import psycopg2

conn = psycopg2.connect(dsn='dbname=mydb user=app')
cur  = conn.cursor()
cur.execute(
    "SELECT name, price FROM products WHERE price > %s AND category = %s;",
    (100, 'Electronics')
)
rows = cur.fetchall()
for r in rows:
    print(r)

Never concatenate user‑input directly into the SQL string.

13️⃣ Cheat‑Sheet – SELECT Syntax Overview

14️⃣ Sample Schema – Customers, Orders, Products

15️⃣ Lab Exercises (Extended)

16️⃣ Knowledge‑Check Quiz (7 questions)

17️⃣ Further Reading & Resources

• Books
  • SQL Fundamentals – John J. L. (O'Reilly, 2022)
  • PostgreSQL Up and Running – Regina O. Obe (3rd ed., 2023)
  • SQL Antipatterns – Bill Karwin (Addison‑Wesley, 2010)
  • Mastering SQL Queries – Mark S. (Packt, 2021)
• Official Docs
  • PostgreSQL SELECT Documentation
  • MySQL SELECT Syntax
  • SQLite SELECT Overview
• Interactive Practice
  • SQLZoo – hands‑on queries
  • Mode Analytics SQL Tutorial (real‑world data sets)
  • Hack The Box – “SQL Injection” machines

Filtering & Sorting

1️⃣ The WHERE Clause – Selecting the Right Rows

WHERE is the gate‑keeper of a SELECT statement. Every row that satisfies the Boolean expression after WHERE is passed downstream to ORDER BY, LIMIT, etc.

1.1 Comparison & Logical Operators

1.2 Practical WHERE Samples

SELECT *
FROM products
WHERE price > 100
  AND stock >= 10
  AND category IN ('Electronics','Appliances')
  AND discontinued IS NULL;

SELECT *
FROM users
WHERE country = 'USA'
  AND age >= 18
  AND email LIKE '%@example.com';

SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d WHERE d.id = e.department_id
    AND d.name = 'Engineering'
);

2️⃣ Ordering Results – ORDER BY

2.1 Basic Syntax

Sort by one or more columns, each optionally marked ASC (default) or DESC.

SELECT *
FROM products
ORDER BY price DESC;   -- expensive first

SELECT *
FROM employees
ORDER BY department_id ASC, last_name ASC;

2.2 Controlling NULL Placement

Some engines put NULL values first, others last. Use the explicit NULLS FIRST or NULLS LAST clause.

SELECT *
FROM users
ORDER BY last_login DESC NULLS LAST;

2.3 Case‑Insensitive / Locale‑Aware Sorting

PostgreSQL lets you attach a collation directly in the ORDER BY clause:

SELECT name
FROM customers
ORDER BY name COLLATE \"C\";   -- case‑insensitive, language‑aware

2.4 Ordering by an Expression (Custom Sort)

Advanced use‑cases often need a derived value, e.g., sorting by the length of a string:

SELECT product_name, price
FROM products
ORDER BY LENGTH(product_name) ASC, price DESC;

2.5 Random Ordering (caution!)

For testing or sampling you can randomise rows, but it forces a full‑table sort and is expensive on large tables.

SELECT *
FROM quotes
ORDER BY RANDOM()
LIMIT 1;


SELECT *
FROM quotes
ORDER BY RAND()
LIMIT 1;

3️⃣ Pagination – LIMIT & OFFSET

Web applications often need “pages” of data. The canonical pattern is:

SELECT *
FROM products
ORDER BY product_id
LIMIT 20          -- page size
OFFSET 40;        -- skip first two pages (0‑based)

Tip: Combine pagination with a stable ordering column (e.g., primary key) to get deterministic results.

Keyset Pagination (Seek Method)

When a table grows, OFFSET becomes slower. Use “keyset pagination”:

SELECT *
FROM products
WHERE product_id > 120   -- last ID from previous page
ORDER BY product_id
LIMIT 20;

4️⃣ Performance – How Filtering & Sorting Interact with Indexes

4.1 Index Usage Basics

• A B‑tree index can satisfy both WHERE predicates and ORDER BY if the sort order matches the index definition.
• Example: CREATE INDEX idx_price ON products(price DESC); lets the engine fetch rows already sorted – eliminating a separate sort phase.

4.2 EXPLAIN – Visualising the Plan

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price > 100
ORDER BY price DESC
LIMIT 10;

The output shows whether the planner uses an Index Scan (good) or a Seq Scan + Sort (costly).

4.3 Common Pitfalls

• Applying a function to an indexed column (WHERE LOWER(email) = 'alice@example.com') disables index usage. Instead create a functional index: CREATE INDEX idx_email_lower ON users (LOWER(email));
• Using OR across unrelated columns often prevents the optimizer from using indexes. Split the query with UNION ALL if possible.
• Sorting on a calculated column (e.g., ORDER BY price * tax_rate) forces a temporary file sort.

5️⃣ Security – Safe Filtering & Sorting

Even read‑only queries can be abused for data exfiltration if user input is concatenated unchecked.

const { Client } = require('pg');
const client = new Client(/* connection params */);

async function getProducts(minPrice, maxPrice, sortCol, sortDir) {
    // whitelist column names & sort direction to prevent injection
    const allowedCols = ['price', 'name', 'created_at'];
    const allowedDir = ['ASC', 'DESC'];

    if (!allowedCols.includes(sortCol) || !allowedDir.includes(sortDir)) {
        throw new Error('Invalid sort parameters');
    }

    const sql = `SELECT *
                 FROM products
                 WHERE price BETWEEN $1 AND $2
                 ORDER BY ${sortCol} ${sortDir}
                 LIMIT 20`;
    const res = await client.query(sql, [minPrice, maxPrice]);
    return res.rows;
}`;
    

**Key take‑aways**:

• Never interpolate raw user input directly into SQL (even for ORDER BY).
• Whitelist column names / directions, or map them to an enum.
• Use prepared statements for values ($1, $2 in PostgreSQL, ? in MySQL).

6️⃣ Cheat‑Sheet – Filtering & Sorting Summary

7️⃣ Sample Schema – Customers, Orders, Products

8️⃣ Lab Exercises (Hands‑On)

9️⃣ Knowledge‑Check Quiz (7 questions)

10️⃣ Further Reading & Resources

• Books
  • SQL Performance Explained – Markus Winand (2nd ed., 2021)
  • SQL Antipatterns – Bill Karwin (Addison‑Wesley, 2010)
  • PostgreSQL Up and Running – Regina O. Obe (3rd ed., 2023)
• Official Docs
  • PostgreSQL SELECT reference
  • MySQL SELECT syntax
  • SQLite SELECT documentation
• Interactive Practice
  • SQLZoo – hands‑on exercises
  • Mode Analytics SQL Tutorial (real‑world data)
  • Hack The Box – “SQL Injection” and “SQL Filtering” rooms

Joins – Combining Data From Multiple Tables

0️⃣ Why Joins Matter

Real‑world databases are normalized: data is split across related tables to avoid redundancy. A join stitches those pieces back together so a security analyst (or any data‑consumer) can answer questions such as:

• Which user bought the most‑expensive product?
• What is the total revenue per country?
• Are there any “orphaned” orders without a matching user?

1️⃣ Sample Schema (Orders, Users, Products, Order_Items)

Below is the DDL you can paste into any RDBMS (PostgreSQL/MySQL/SQLite) to create the demo data.

CREATE TABLE users (
    id          SERIAL      PRIMARY KEY,
    name        VARCHAR(50)   NOT NULL,
    email       VARCHAR(100)  UNIQUE,
    country     VARCHAR(50)
);

CREATE TABLE products (
    id          SERIAL      PRIMARY KEY,
    sku         VARCHAR(30)   UNIQUE,
    name        VARCHAR(100)  NOT NULL,
    price       NUMERIC(9,2) NOT NULL
);

CREATE TABLE orders (
    id          SERIAL      PRIMARY KEY,
    user_id     INT         REFERENCES users(id),
    order_date  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id          SERIAL      PRIMARY KEY,
    order_id    INT         REFERENCES orders(id),
    product_id  INT         REFERENCES products(id),
    quantity    INT         DEFAULT 1
);

2️⃣ Join Types – Theory (with Venn diagrams)

Below are simple Venn‑diagram placeholders for each join type. Replace them with real SVG/PNG graphics in your LMS.

3️⃣ Practical Join Syntax – Variations & Shortcuts

3.1 Using USING (when column names match)

SELECT o.id, u.name
FROM orders o
INNER JOIN users u USING(user_id);   -- automatically matches on o.user_id = u.id

3.2 Table Aliases – make queries readable

SELECT o.id AS order_id,
       u.name AS buyer,
       p.name AS product,
       oi.quantity
FROM order_items oi
JOIN orders o   ON oi.order_id   = o.id
JOIN users u    ON o.user_id    = u.id
JOIN products p ON oi.product_id = p.id
WHERE p.price > 1000
ORDER BY o.id;

3.3 Self‑Join – hierarchical data (e.g., employee‑manager)

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.name;

3.4 Joining More Than Two Tables

The same JOIN keyword can be chained; the DB engine builds an internal join tree.

SELECT u.name,
       SUM(p.price * oi.quantity) AS total_spent
FROM users u
JOIN orders o       ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p    ON oi.product_id = p.id
GROUP BY u.name
HAVING total_spent > 5000
ORDER BY total_spent DESC;

4️⃣ Common Pitfalls & Performance Tips

• Cartesian product – forgetting the ON clause (or using CROSS JOIN) creates huge result sets that can crash the DB or hide the real problem.
• Duplicate rows – when joining a one‑to‑many relationship, SELECT DISTINCT may be needed, but better to aggregate (GROUP BY) rather than hide the symptom.
• Missing indexes on foreign‑key columns – each JOIN ideally uses an index on the joining column (e.g., CREATE INDEX idx_orders_user_id ON orders(user_id);).
• Ordering before joining – give the optimizer the chance to push ORDER BY down to the smallest sub‑plan. Usually you order after the final join.
• Implicit joins (comma‑separated FROM) – legacy syntax FROM a, b WHERE a.id=b.id is harder to read and more error‑prone.

4.1 How the Optimizer Builds a Join Tree

Modern engines (PostgreSQL, MySQL, SQL‑Server) evaluate joins in the order that yields the lowest estimated cost. You can view the plan with EXPLAIN (or EXPLAIN ANALYZE for real timing).

EXPLAIN ANALYZE
SELECT u.name, o.id, p.name, oi.quantity
FROM users u
JOIN orders o       ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p    ON oi.product_id = p.id
WHERE p.price > 1000
LIMIT 20;

The output will show whether each join is using an Index Scan, a Hash Join, or a Nested Loop. If you see a Seq Scan on a large table where a foreign key exists, add an index and re‑run.

5️⃣ Hands‑On Lab – Join Challenges

INSERT INTO users (name,email,country) VALUES
('Alice','alice@example.com','USA'),
('Bob','bob@example.com','India'),
('Charlie','charlie@example.com','UK');

INSERT INTO products (sku,name,price) VALUES
('SKU-001','Gaming Laptop',2500.00),
('SKU-002','Office Chair',150.00),
('SKU-003','USB‑C Hub',45.99);

INSERT INTO orders (user_id,order_date) VALUES
(1,'2024-04-01'),(2,'2024-04-03'),(1,'2024-04-05');

INSERT INTO order_items (order_id,product_id,quantity) VALUES
(1,1,1),(1,3,2),
(2,2,1),
(3,1,1),(3,2,1);

6️⃣ Cheat‑Sheet – Join Syntax at a Glance

7️⃣ Knowledge‑Check Quiz (7 questions)

8️⃣ Further Reading & Templates

• Books
  • SQL Antipatterns – Bill Karwin (covers misuse of joins).
  • Learning SQL – Alan Beaulieu (excellent for beginners).
  • SQL Performance Explained – Markus Winand (deep dive on join optimization).
• Online Guides
  • Use‑the‑Index‑Luke – “Joins” chapter
  • GeeksforGeeks – Join Types Overview
  • PostgreSQL – Join Documentation
• Cheat‑Sheet PDF (download) SQL Join Cheat‑Sheet (PDF)

Aggregation & GROUP BY

0️⃣ Why Aggregation Is a Core Skill

Aggregation turns rows into business‑level numbers. In a penetration‑test report you’ll often need to say:

• “142 hosts responded on port 22.”
• “The average CVSS score of all critical findings is 9.2.”
• “Total data exfiltrated: 4.3 GB.”

1️⃣ Sample Schema (Orders ↔ Users ↔ Products)

Copy‑paste the DDL below into any RDBMS (PostgreSQL, MySQL or SQLite) and populate it with a few rows – you’ll need it for all the lab exercises.

CREATE TABLE users (
    id      SERIAL      PRIMARY KEY,
    name    VARCHAR(50)   NOT NULL,
    country VARCHAR(30)
);

CREATE TABLE products (
    id    SERIAL      PRIMARY KEY,
    sku   VARCHAR(30)   UNIQUE,
    name  VARCHAR(100)  NOT NULL,
    price NUMERIC(9,2) NOT NULL,
    stock INT         DEFAULT 0
);

CREATE TABLE orders (
    id         SERIAL      PRIMARY KEY,
    user_id    INT         REFERENCES users(id),
    order_date TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id         SERIAL      PRIMARY KEY,
    order_id   INT         REFERENCES orders(id),
    product_id INT         REFERENCES products(id),
    qty        INT         DEFAULT 1
);

2️⃣ Core Aggregate (Summary) Functions

All aggregate functions ignore NULL values automatically (except COUNT(*) which counts rows regardless of NULL).

3️⃣ Basic GROUP BY Syntax

SELECT category,
       COUNT(*)            AS product_cnt,
       AVG(price)          AS avg_price,
       SUM(stock * price) AS inventory_value
FROM products
GROUP BY category
ORDER BY inventory_value DESC;

Rules to remember:

• Every column in the SELECT list must either be an aggregate or appear in the GROUP BY clause.
• Aliases (AS …) are optional but make later HAVING or ORDER BY clauses clearer.
• The grouping columns are evaluated first; aggregates are then calculated per group.

4️⃣ Filtering Groups – HAVING

HAVING works like WHERE but **after** aggregation.

SELECT country,
       COUNT(*)            AS user_cnt
FROM users
GROUP BY country
HAVING COUNT(*) >= 5
ORDER BY user_cnt DESC;

Typical security‑report use‑cases:

• Show only assets that have **≥ 3** high‑severity findings.
• List countries where the *average* CVSS of discovered vulns exceeds 7.

5️⃣ Advanced Grouping – ROLLUP, CUBE, GROUPING SETS

These constructs let you produce **multiple aggregation levels** in a single query.

5.1 ROLLUP – Hierarchical Sub‑Totals

SELECT u.country,
       EXTRACT(YEAR FROM o.order_date) AS yr,
       SUM(p.price * oi.qty) AS revenue
FROM orders o
JOIN users u           ON o.user_id = u.id
JOIN order_items oi  ON o.id = oi.order_id
JOIN products p       ON oi.product_id = p.id
GROUP BY ROLLUP(u.country, EXTRACT(YEAR FROM o.order_date))
ORDER BY u.country, yr;

Result rows include:

• Revenue per (country, year).
• Yearly subtotal per country (country, NULL).
• Grand total (both columns NULL).

5.2 CUBE – All Possible Combinations

SELECT u.country,
       EXTRACT(YEAR FROM o.order_date) AS yr,
       COUNT(DISTINCT o.id) AS orders_cnt
FROM orders o
JOIN users u ON o.user_id = u.id
GROUP BY CUBE(u.country, EXTRACT(YEAR FROM o.order_date))
ORDER BY u.country, yr;

CUBE returns every combination, i.e., per country, per year, per country‑year, plus the grand total.

5.3 GROUPING SETS – Hand‑Picked Mix

SELECT u.country,
       EXTRACT(MONTH FROM o.order_date) AS month,
       SUM(p.price * oi.qty) AS rev
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY GROUPING SETS(
    (u.country, EXTRACT(MONTH FROM o.order_date)),
    (u.country),
    (EXTRACT(MONTH FROM o.order_date))
)
ORDER BY u.country, month;

Only the three specified groupings appear – no grand total.

6️⃣ Window (Analytic) Functions – Aggregates Without Collapse

Sometimes you need a running total or a rank while still seeing each individual order. Window functions let you do that.

6.1 Syntax Overview

SELECT o.id,
       u.name,
       p.name AS product,
       oi.qty,
       p.price,
       SUM(p.price * oi.qty) OVER(
           PARTITION BY u.id
           ORDER BY o.order_date
       ) AS cumulative_spend
FROM orders o
JOIN users u          ON o.user_id = u.id
JOIN order_items oi   ON o.id = oi.order_id
JOIN products p       ON oi.product_id = p.id
ORDER BY u.id, o.order_date;

6.2 Real‑World Example – Top‑3 Products per Country

SELECT u.country,
       p.name AS product,
       SUM(p.price * oi.qty) AS revenue,
       RANK() OVER(PARTITION BY u.country
                     ORDER BY SUM(p.price * oi.qty) DESC) AS rnk
FROM order_items oi
JOIN orders o      ON oi.order_id = o.id
JOIN users u       ON o.user_id = u.id
JOIN products p    ON oi.product_id = p.id
GROUP BY u.country, p.name
HAVING SUM(p.price * oi.qty) > 0
ORDER BY u.country, rnk
LIMIT 30;   -- 10 countries × top‑3 each

Window functions are **not** listed in GROUP BY; they are calculated after the grouping step.

7️⃣ Performance Tips for Aggregations

• Index the grouping columns. An index on products.category speeds GROUP BY category.
• Covering indexes (e.g., CREATE INDEX idx_products_cat_price ON products(category, price);) let the engine satisfy both the grouping and the aggregation from the index alone.
• Materialized views for heavy daily reports (e.g., CREATE MATERIALIZED VIEW daily_sales AS …).
• Filter early. Put restrictive WHERE clauses before GROUP BY to reduce rows that need to be aggregated.
• Avoid SELECT * in aggregated queries – fetching unnecessary columns inflates I/O.
• Watch for data skew – a single value dominating a group can cause sub‑optimal join plans. Consider partitioning or separate statistics.

7.1 Index Impact Demo

EXPLAIN ANALYZE
SELECT category,
       COUNT(*)       AS cnt,
       AVG(price)   AS avg_price
FROM products
GROUP BY category;

If the plan shows a Seq Scan on a million‑row table, create an index:

CREATE INDEX idx_products_category ON products(category);

Rerun the EXPLAIN ANALYZE – you should see an Index Scan and a dramatically lower total cost.

8️⃣ Common Pitfalls & How to Avoid Them

• Missing GROUP BY clause – “column must appear in the GROUP BY clause” error.
• Counting with COUNT(col) when you need total rows – COUNT(col) skips NULLs.
• Using HAVING without GROUP BY – it works but usually means you meant WHERE.
• Accidentally creating a Cartesian product – forgetting the ON clause in a join will cause GROUP BY to process massive rowsets.
• NULL groups – rows where the grouping column is NULL become a separate group. Use COALESCE(col,'N/A') if you prefer them merged.
• Rounding errors with monetary aggregates – cast to a higher precision type (e.g., price::numeric(12,4)) before summing.

9️⃣ Hands‑On Lab – Aggregation Challenges

10️⃣ Cheat‑Sheet – Aggregation & GROUP BY Quick Reference

11️⃣ Knowledge‑Check Quiz (7 questions)

12️⃣ Further Reading & Templates

• Books
  • SQL Performance Explained – Markus Winand (excellent chapter on aggregation).
  • SQL Antipatterns – Bill Karwin (covers common aggregation mistakes).
  • Learning SQL – Alan Beaulieu (good for beginners).
• Official Docs
  • PostgreSQL – Aggregate Functions
  • MySQL – GROUP BY Functions
  • SQLite – Aggregate Functions
• Cheat‑Sheet (PDF) – Aggregation Cheat‑Sheet (PDF)
• Interactive Playground
  • DB‑Fiddle – run PostgreSQL/MySQL/SQLite snippets online.
  • Mode Analytics SQL Tutorial (real‑world data sets)

Subqueries & CTEs

Introduction to Subqueries

A subquery is a query written inside another SQL query. Subqueries allow developers to break complex problems into smaller logical steps.

Subqueries are commonly used for:

• Filtering data
• Comparing values
• Generating temporary result sets
• Performing calculations
• Building advanced reports

Basic Subquery Example

A subquery can be placed inside a WHERE clause.

SELECT name
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the average salary, while the outer query returns employees earning above that average.

How Subqueries Work

SQL executes the inner query first, then uses the returned result in the outer query.

This allows queries to become dynamic and data-driven.

Subqueries in the SELECT Clause

Subqueries can also appear inside the SELECT statement.

SELECT
    name,
    (
        SELECT department_name
        FROM departments
        WHERE departments.id = employees.department_id
    ) AS department
FROM employees;

This query retrieves employee names along with department names.

Subqueries in the FROM Clause

A subquery inside the FROM clause acts like a temporary table.

SELECT avg_salary.department_id,
       avg_salary.average
FROM (
    SELECT department_id,
           AVG(salary) AS average
    FROM employees
    GROUP BY department_id
) AS avg_salary;

This approach improves readability for complex calculations.

Single-Row vs Multi-Row Subqueries

Some subqueries return one value, while others return multiple rows.

Single-row subqueries are often used with operators such as:

• =
• >
• <

Multi-row subqueries are commonly used with:

• IN
• ANY
• ALL

Using IN with Subqueries

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE location = 'London'
);

This query finds employees working in departments located in London.

Correlated Subqueries

A correlated subquery depends on values from the outer query.

SELECT name,
       salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

The inner query runs once for every row processed by the outer query.

Introduction to CTEs

A Common Table Expression (CTE) is a temporary named result set created using the WITH keyword.

CTEs improve readability and make complex queries easier to manage.

Common Table Expressions (CTE)

WITH high_value_orders AS (

    SELECT *
    FROM orders
    WHERE total > 1000
)

SELECT *
FROM high_value_orders;

The CTE creates a temporary result called high_value_orders.

Why Use CTEs?

CTEs provide several advantages:

• Improve query readability.
• Reduce repeated logic.
• Make debugging easier.
• Organize complex SQL statements.
• Support recursive queries.

Multiple CTEs

You can define multiple CTEs in a single query.

WITH sales_total AS (

    SELECT customer_id,
           SUM(total) AS total_sales
    FROM orders
    GROUP BY customer_id
),

top_customers AS (

    SELECT *
    FROM sales_total
    WHERE total_sales > 5000
)

SELECT *
FROM top_customers;

This structure makes long queries much easier to understand.

Recursive CTEs

Recursive CTEs are used for hierarchical or tree-structured data.

WITH RECURSIVE numbers AS (

    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM numbers
    WHERE n < 5
)

SELECT *
FROM numbers;

This query generates numbers from 1 to 5 recursively.

CTEs vs Subqueries

Both CTEs and subqueries solve similar problems, but they differ in readability and structure.

• Subqueries are compact for small operations.
• CTEs are better for complex multi-step logic.
• CTEs improve maintainability.
• Recursive operations require CTEs.

Performance Considerations

In some databases, CTEs may improve readability but not necessarily performance.

Database engines optimize queries differently, so performance testing is important for large datasets.

Real-World Use Cases

Subqueries and CTEs are heavily used in:

• Business reporting
• Analytics dashboards
• Financial systems
• Inventory management
• Data warehousing
• Hierarchical organizational data

Common Beginner Mistakes

• Using subqueries when joins are simpler.
• Writing deeply nested unreadable queries.
• Forgetting aliases for subqueries.
• Creating inefficient correlated subqueries.
• Confusing recursive and non-recursive CTEs.

Best Practices

• Use meaningful CTE names.
• Keep queries readable and modular.
• Use CTEs for multi-step transformations.
• Test performance on large datasets.
• Avoid unnecessary nested queries.

Practice

Summary

Subqueries and Common Table Expressions are powerful SQL tools used to build advanced and organized queries. Subqueries help solve problems step-by-step, while CTEs improve readability and support recursive operations.

Mastering these concepts is essential for writing professional SQL queries used in real-world applications and enterprise databases.

Data Modification

Introduction to Data Modification

Databases are not only used for retrieving data — they are also used to create, modify, and remove information.

SQL provides several commands for changing database records safely and efficiently.

• INSERT — adds new data
• UPDATE — modifies existing data
• DELETE — removes data

INSERT, UPDATE, DELETE

INSERT INTO users (name, email)
VALUES ('Bob', 'bob@example.com');

UPDATE products
SET price = price * 1.1
WHERE category = 'Tech';

DELETE FROM logs
WHERE created_at < '2023-01-01';

Understanding INSERT

The INSERT INTO statement adds new rows into a table.

This command is commonly used in:

• User registration systems
• Order management systems
• Inventory applications
• Blog and CMS platforms

Basic INSERT Syntax

INSERT INTO students (name, age)
VALUES ('Alice', 21);

Here, a new row is inserted into the students table.

Inserting Multiple Rows

SQL allows inserting multiple records in a single query.

INSERT INTO products (name, price)
VALUES
('Mouse', 500),
('Keyboard', 1200),
('Monitor', 9000);

INSERT Without Specifying Columns

If values are provided in exact table order, column names can be omitted.

INSERT INTO users
VALUES (1, 'Ahmed', 'ahmed@example.com');

However, specifying column names is considered a better practice.

Understanding UPDATE

The UPDATE statement modifies existing records.

UPDATE employees
SET salary = 50000
WHERE id = 1;

This query updates the salary of the employee with ID 1.

Updating Multiple Columns

UPDATE users
SET
    city = 'Delhi',
    status = 'Active'
WHERE id = 5;

Importance of WHERE Clause

The WHERE clause determines which rows are affected.

UPDATE users
SET status = 'Inactive';

Without a WHERE clause, all rows in the table will be updated.

Understanding DELETE

The DELETE statement removes records from a table.

DELETE FROM customers
WHERE id = 10;

This removes the customer whose ID equals 10.

Deleting Multiple Records

DELETE FROM orders
WHERE status = 'Cancelled';

Deleting All Rows

Removing the WHERE clause deletes all records.

DELETE FROM logs;

Use this carefully because deleted rows may not be recoverable.

TRUNCATE vs DELETE

Using Conditions in Queries

Conditions help target specific rows during modification.

UPDATE products
SET stock = stock - 1
WHERE product_id = 100;

Working with NULL Values

UPDATE users
SET phone = NULL
WHERE id = 3;

Using Arithmetic Operations

SQL can perform calculations during updates.

UPDATE employees
SET salary = salary + 5000
WHERE department = 'IT';

Transactions and Safety

Transactions help ensure database consistency.

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

COMMIT;

Transactions are especially useful in banking and payment systems.

Rollback Example

ROLLBACK;

Rollback cancels changes before they are permanently saved.

Best Practices

• Always use WHERE with UPDATE and DELETE
• Back up important data before modifications
• Test queries on small datasets first
• Use transactions for critical operations
• Validate user input before database changes

Common Mistakes

• Forgetting the WHERE clause
• Inserting duplicate data accidentally
• Deleting records without backup
• Using incorrect column order
• Ignoring transaction handling

Real-World Examples

• User account registration
• Updating product inventory
• Deleting expired logs
• Processing customer orders
• Managing employee databases

Mini Practice

Try writing queries for the following:

• Insert a new student record
• Update a product price
• Delete inactive users
• Increase salaries by 10%
• Use transactions with COMMIT and ROLLBACK

Constraints & Indexes

Introduction to Constraints

Constraints are rules applied to database tables to ensure data accuracy, consistency, and integrity.

They help prevent invalid or duplicate data from being inserted into a database.

Constraints are essential in professional database systems because they enforce business rules directly at the database level.

Why Constraints Matter

Without constraints, databases may contain:

• Duplicate records
• Missing required values
• Invalid relationships
• Incorrect data formats
• Orphaned records

Constraints help maintain reliable and trustworthy data.

Ensuring Data Integrity

CREATE TABLE authors (

    id SERIAL PRIMARY KEY,

    name TEXT NOT NULL,

    email TEXT UNIQUE
);

This table demonstrates multiple important constraints:

• PRIMARY KEY ensures unique row identification.
• NOT NULL prevents empty values.
• UNIQUE prevents duplicate emails.

Primary Key Constraint

A primary key uniquely identifies each row in a table.

CREATE TABLE students (

    student_id INT PRIMARY KEY,

    name TEXT
);

Primary keys cannot contain duplicate or NULL values.

Auto-Incrementing Keys

Databases often generate primary key values automatically.

id SERIAL PRIMARY KEY

The SERIAL keyword automatically increases values for each new row.

NOT NULL Constraint

The NOT NULL constraint requires a column to always contain a value.

CREATE TABLE products (

    name TEXT NOT NULL,

    price DECIMAL
);

This prevents incomplete records from being inserted.

UNIQUE Constraint

The UNIQUE constraint ensures all values in a column are different.

CREATE TABLE users (

    username TEXT UNIQUE
);

This is commonly used for usernames, emails, and account IDs.

DEFAULT Constraint

The DEFAULT constraint assigns automatic values when no value is provided.

CREATE TABLE orders (

    status TEXT DEFAULT 'Pending'
);

If no status is specified, the database automatically inserts Pending.

CHECK Constraint

The CHECK constraint validates data using conditions.

CREATE TABLE employees (

    age INT CHECK (age >= 18)
);

This prevents invalid age values from being inserted.

Foreign Key Constraint

Foreign keys create relationships between tables.

CREATE TABLE books (

    id SERIAL PRIMARY KEY,

    author_id INT REFERENCES authors(id)
);

The author_id must match an existing value in the authors table.

Referential Integrity

Foreign keys help maintain referential integrity by ensuring related records remain valid.

This prevents orphaned records and broken relationships.

Composite Keys

A composite key combines multiple columns into a single primary key.

CREATE TABLE enrollments (

    student_id INT,

    course_id INT,

    PRIMARY KEY (student_id, course_id)
);

This ensures each student-course combination is unique.

Introduction to Indexes

Indexes improve database query performance by allowing faster data retrieval.

Without indexes, databases may need to scan entire tables to locate records.

How Indexes Work

Indexes function similarly to a book index. Instead of reading every page, the database quickly locates the required information.

Indexes significantly improve search speed for large datasets.

Creating an Index

CREATE INDEX idx_author_name
ON authors(name);

This creates an index on the name column.

Indexes on Multiple Columns

Indexes can include multiple columns.

CREATE INDEX idx_employee
ON employees(last_name, first_name);

Multi-column indexes improve queries involving multiple search conditions.

Unique Indexes

A unique index prevents duplicate values while also improving search performance.

CREATE UNIQUE INDEX idx_email
ON users(email);

When to Use Indexes

Indexes are useful for columns frequently used in:

• WHERE clauses
• JOIN operations
• ORDER BY clauses
• GROUP BY statements

When Too Many Indexes Become a Problem

Although indexes improve reading speed, they can slow down insert, update, and delete operations.

Each index must also be updated whenever table data changes.

Clustered vs Non-Clustered Indexes

Some database systems support different index types:

• Clustered Index: Organizes physical table data.
• Non-Clustered Index: Stores separate index structures.

The exact behavior depends on the database system being used.

Viewing Existing Indexes

Most databases provide commands to inspect indexes.

SELECT *
FROM pg_indexes
WHERE tablename = 'authors';

Dropping an Index

Indexes can be removed if no longer needed.

DROP INDEX idx_author_name;

Real-World Uses

Constraints and indexes are critical in:

• Banking systems
• E-commerce applications
• Inventory systems
• Authentication systems
• Large enterprise databases
• Analytics platforms

Benefits of Constraints & Indexes

• Improve data integrity.
• Prevent invalid records.
• Enforce business rules.
• Increase query performance.
• Support scalable applications.

Common Beginner Mistakes

• Creating too many unnecessary indexes.
• Forgetting foreign key relationships.
• Using NULL values incorrectly.
• Ignoring unique constraints on important fields.
• Adding indexes to very small tables unnecessarily.

Best Practices

• Always define primary keys.
• Use foreign keys for related tables.
• Index frequently searched columns.
• Validate important business rules using constraints.
• Regularly monitor query performance.

Practice

Summary

Constraints and indexes are essential database features used to maintain reliable data and improve performance. Constraints enforce data integrity rules, while indexes optimize query execution speed.

Understanding these concepts is critical for designing scalable, secure, and efficient database systems.

Transactions & ACID

All or Nothing

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Stored Procedures

Functions and procedures allow you to save SQL logic on the database server.

Window Functions

Advanced Analytics

SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as ranking
FROM employees;

Capstone Project: E-commerce DB

Design and implement a database schema for an e-commerce platform, including products, users, orders, and reviews.

-- Project goals:
-- 1. Create tables with proper constraints
-- 2. Populate with sample data
-- 3. Write complex queries for sales reports
-- 4. Optimize queries with indexes

Database Design & Normalization

Content coming soon...

Performance & Query Optimization

Content coming soon...

Security & Access Control

Content coming soon...

Final Project — Enterprise Database

Content coming soon...