Foundations · Module 04

SQL Foundations.

Learn the core SQL patterns analysts use every day—starting from spreadsheet ideas, then learning how rows logically flow through a query so you can write cleaner, more explainable SQL.

Core idea

SQL becomes much easier when you stop memorizing syntax and ask five questions: which tables create the rows, which rows survive, what is the grain, which calculations exist at that point, and what should the final output represent?

Mental model

SQL is a sequence of questions about rows.

01 SELECT

Choose the columns the answer needs.

Spreadsheet: choose visible columns

02 WHERE

Keep only rows inside the business scope.

Spreadsheet: filter

03 ORDER BY

Control how the result is ranked or sequenced.

Spreadsheet: sort

04 GROUP BY

Roll detailed rows up to a business grain.

Spreadsheet: PivotTable rows

05 CASE

Encode a business rule into a derived field.

Spreadsheet: IF / IFS

Spreadsheet → SQL

You already know the ideas. A filter becomes WHERE, sorting becomes ORDER BY, a PivotTable becomes GROUP BY, and spreadsheet IF logic becomes CASE WHEN.

Execution order

Read SQL in the order the data logically becomes available.

Written order SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Logical processing order FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

01 FROM / JOIN Build the source row set

Which tables and relationships create the rows?

02 WHERE Filter individual rows

Which raw rows are allowed to continue?

03 GROUP BY Create groups

What should one summarized output row represent?

04 HAVING Filter groups

Which aggregated groups are allowed to continue?

05 SELECT Calculate output columns

Which columns, aliases and expressions should be returned?

06 DISTINCT Remove duplicate result rows

Do identical output rows need to collapse?

07 ORDER BY Sort the finished result

How should the answer be ranked or sequenced?

08 LIMIT Trim the final result

How many rows should the user receive?

Why this matters

WHERE cannot filter an aggregate that does not exist yet.

-- Wrong
SELECT division, SUM(amount_bdt)
FROM orders
WHERE SUM(amount_bdt) > 20000
GROUP BY division;

Use HAVING after groups exist

Filter rows with WHERE; filter aggregated groups with HAVING.

SELECT division, SUM(amount_bdt) AS sales
FROM orders
WHERE status = 'Delivered'
GROUP BY division
HAVING SUM(amount_bdt) > 20000;

Optimization note

Logical query order is a reasoning model, not a promise about the database's physical plan. Query optimizers can reorder joins, push predicates and choose different access paths. For better SQL, think about correct grain, reducing unnecessary rows early, avoiding unnecessary DISTINCT/ORDER BY, and selecting only useful columns. When performance really matters, inspect the database's EXPLAIN plan instead of guessing.

Live practice

Write SQL. Run it. Inspect the rows.

QUICKCART · ORDERS TABLE

Start by selecting the columns you need.

The practice engine supports the core syntax in this lesson. Change the query, run it, and inspect how the result changes.

orders order_idtext customer_nametext divisiontext amount_bdtnumber statustext promo_codetext / null order_datedate text

SQL editor Try changing values, columns, sort direction or grouping.

READY

Run the query to see the result.

0 rowsResult preview

Filtering & wildcards

Make the business scope explicit—and know what your pattern actually matches.

Filter rows

=<>>>=<<=ANDORNOT

Match sets & text

INBETWEENLIKENOT LIKE%_

Handle missing values

IS NULLIS NOT NULLCOALESCE

Summarize

COUNTSUMAVGMINMAXGROUP BYHAVING

Shape output

DISTINCTASORDER BYLIMITCASE WHEN

Readable filters beat clever filters

SELECT order_id, customer_name, division, amount_bdt
FROM orders
WHERE status = 'Delivered'
  AND division IN ('Dhaka', 'Chattogram')
  AND amount_bdt BETWEEN 3000 AND 12000
  AND customer_name LIKE 'S%'
ORDER BY amount_bdt DESC;

S% Starts with S

customer_name LIKE 'S%'

%mart% Contains “mart” anywhere

merchant_name LIKE '%mart%'

017________ 017 + exactly 8 more characters

phone LIKE '017________'

%10 Ends with 10

promo_code LIKE '%10'

% means any number of characters; _ means exactly one character. Case sensitivity depends on the database and collation. PostgreSQL also has ILIKE for case-insensitive matching, but that is not portable SQL.

NULL is not an empty string and it is not zero. Use IS NULL and IS NOT NULL. A comparison such as promo_code = NULL does not mean what beginners usually expect.

For timestamps, prefer a clear range such as created_at >= '2026-10-01' AND created_at < '2026-11-01'. It avoids accidentally excluding records late on the final day.

Map & group data

Turn raw rows into business categories, then summarize at the right grain.

COUNT(*)How many rows?

Useful for order counts when one row truly represents one order.

SUM(amount_bdt)How much?

Total sales or value across the selected population.

AVG(amount_bdt)What is typical?

Average order value—after checking the grain and exclusions.

GROUP BY divisionAt what grain?

One result row per division instead of one result row overall.

HAVINGWhich groups survive?

Filter after aggregation, such as divisions with at least 100 orders.

Step 1 · map raw values

Create a business-friendly order band.

SELECT
  order_id,
  amount_bdt,
  CASE
    WHEN amount_bdt < 5000 THEN 'Low'
    WHEN amount_bdt < 10000 THEN 'Mid'
    ELSE 'High'
  END AS order_band
FROM orders;

Step 2 · group the mapped result

Count orders and sales by that new category.

SELECT
  order_band,
  COUNT(*) AS orders,
  SUM(amount_bdt) AS sales_bdt
FROM (
  SELECT amount_bdt,
    CASE
      WHEN amount_bdt < 5000 THEN 'Low'
      WHEN amount_bdt < 10000 THEN 'Mid'
      ELSE 'High'
    END AS order_band
  FROM orders
  WHERE status = 'Delivered'
) mapped
GROUP BY order_band
ORDER BY sales_bdt DESC;

Division performance at one clear grain

SELECT
  division,
  COUNT(*) AS orders,
  SUM(amount_bdt) AS sales_bdt,
  AVG(amount_bdt) AS avg_order_value
FROM orders
WHERE status = 'Delivered'
GROUP BY division
HAVING COUNT(*) >= 10
ORDER BY sales_bdt DESC;

Grain check

Before writing GROUP BY, say the intended result out loud: “one row per division”, “one row per customer”, or “one row per month and category.” If you cannot describe one output row, the query is not ready.

Business rules

Translate IF logic into CASE.

Create an order band without changing the raw data

SELECT
  order_id,
  amount_bdt,
  CASE
    WHEN amount_bdt >= 10000 THEN 'High value'
    WHEN amount_bdt >= 5000 THEN 'Mid value'
    ELSE 'Standard'
  END AS order_band
FROM orders;

Spreadsheet ideaSQL ideaQuestion

FilterWHEREWhich rows count?

SortORDER BYWhat should appear first?

Pivot rowsGROUP BYWhat is one result row?

IF / IFSCASEHow do we classify a row?

Blank checkIS NULLWhat is missing?

VLOOKUP / XLOOKUPJOINWhich other table adds context?

Subquery or JOIN?

Choose based on the job you are asking SQL to do.

Use a JOIN when…

You need columns from another related table.

Typical analyst task: enrich each order with customer segment, campaign, product or merchant information.

SELECT
  o.order_id,
  o.amount_bdt,
  c.segment
FROM orders o
LEFT JOIN customers c
  ON c.customer_id = o.customer_id;

Always check the key and relationship cardinality before trusting the row count.

Use a subquery when…

You need an intermediate value or temporary result to filter against.

Typical analyst task: find orders above the overall average without permanently joining another table.

SELECT order_id, amount_bdt
FROM orders
WHERE amount_bdt > (
  SELECT AVG(amount_bdt)
  FROM orders
);

For longer multi-step logic, the Advanced SQL mission introduces CTEs because they are often easier to read.

Important

JOIN and subquery are not always performance opposites. Modern optimizers can rewrite equivalent forms. Prefer the form that makes the grain, relationship and business logic easiest to verify, then inspect the execution plan if speed matters.

Common mistakes

Most beginner SQL bugs are reasoning bugs before they are syntax bugs.

Aggregate inside WHERE

WHERE SUM(amount_bdt) > 20000

WHERE runs before GROUP BY. Use HAVING after aggregation.

Compare NULL with =

promo_code = NULL

Use IS NULL / IS NOT NULL because NULL means unknown.

Hide duplication with DISTINCT

SELECT DISTINCT ...

Fix the join key or grain first. DISTINCT can hide a broken join.

Mix grouped and ungrouped columns

SELECT division, customer_name, SUM(amount_bdt) GROUP BY division

Every selected non-aggregate column must belong to the result grain.

Forget AND / OR precedence

status = 'Paid' OR status = 'Pending' AND amount_bdt > 5000

Use parentheses when the intended business rule is not obvious.

Use SELECT * everywhere

SELECT * FROM orders

Explore with it briefly, then select the columns the analysis actually needs.

Filter a timestamp with a fragile end date

created_at BETWEEN '2026-10-01' AND '2026-10-31'

For timestamps, inclusive start + exclusive next-period end is usually safer.

Use a SELECT alias in WHERE

SELECT amount_bdt * .1 AS fee ... WHERE fee > 500

SELECT is logically later than WHERE. Repeat the expression or move it into a subquery/CTE.

Before you call a query “optimized”

Check correctness first, then reduce waste.

Define the grain. Know what one source row and one output row represent.
Filter intentionally. Remove rows that are outside the business question before expensive aggregation when logically possible.
Select useful columns. Avoid dragging unused data through the analysis.
Avoid unnecessary DISTINCT and ORDER BY. Both can add work and can hide deeper logic problems.
Use range predicates on dates/timestamps. They are usually clearer and can be more index-friendly than wrapping the column in a function.
Validate joins. Compare row counts and totals before/after a join so duplication does not silently inflate metrics.
Use EXPLAIN when speed matters. The optimizer's actual plan is stronger evidence than intuition.

Checkpoints

Can you write the query before revealing it?

01Return only delivered Dhaka orders above 5,000 BDT.+

SELECT order_id, customer_name, amount_bdt
FROM orders
WHERE division = 'Dhaka'
  AND status = 'Delivered'
  AND amount_bdt > 5000
ORDER BY amount_bdt DESC;

02Count orders and total sales by division.+

SELECT division, COUNT(*) AS orders, SUM(amount_bdt) AS sales_bdt
FROM orders
GROUP BY division
ORDER BY sales_bdt DESC;

03Create a high-value flag for orders of at least 8,000 BDT.+

SELECT order_id, amount_bdt,
  CASE WHEN amount_bdt >= 8000 THEN 'High value' ELSE 'Standard' END AS order_band
FROM orders;

04Show only divisions with at least 2 delivered orders.+

SELECT division, COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'Delivered'
GROUP BY division
HAVING COUNT(*) >= 2
ORDER BY delivered_orders DESC;

Handoff

Now you are ready for multi-table SQL.

Next mission

You can now reason through execution order, select, filter, pattern-match, sort, aggregate, map, group and classify rows—and you know why WHERE, HAVING, JOINs and subqueries belong in different jobs. The next mission adds the harder analyst problems: join cardinality, CTE pipelines, deduplication, window functions and validation control queries.