Lesson 8 of 16 · Level 2: Intermediate

Introduction to SQL: SELECT, WHERE and ORDER BY

  • ⏱ 13 min read
  • ❓ 4 review questions
  • ✍️ Exercise with solution
  • 📥 Downloadable template
In this lesson
  1. Databases, tables and SQL dialects
  2. Set up: load the practice data into SQLite
  3. Your first query: SELECT … FROM
  4. Filter rows with WHERE, sort with ORDER BY
  5. Operators for WHERE
  6. AND, OR and parentheses
  7. Calculated columns, aliases and DISTINCT
  8. The query clauses at a glance
  9. Beginner SQL mistakes
  10. Summary
What you'll learn
  • What SQL and relational databases are, and why analysts use them every day.
  • How to set up a free SQLite database and load the practice data into it.
  • How to write queries with SELECT, FROM, WHERE, ORDER BY and LIMIT.
  • How to filter with comparison operators, AND/OR, BETWEEN, IN, LIKE and IS NULL.
  • How to create calculated columns with aliases and avoid the most common beginner errors.
📥 Practice this lesson

Load these two CSV files into a database as the tables sales and products. Every query result shown below was produced from them.

SQL (Structured Query Language, pronounced "sequel" or "S-Q-L") is the language you use to ask questions of a database. Company data rarely lives in spreadsheets; it lives in databases with millions of rows, and SQL is how analysts pull exactly the rows and columns they need. The good news: the basics read almost like English. In this lesson you'll load the course dataset into a free database and write queries that filter, sort and calculate, with every result shown so you can check your work.

Databases, tables and SQL dialects

A relational database stores data in tables (like sheets) with fixed columns and data types. Tables relate to each other through keys: our sales table has a product column that matches the product column of the products table. You'll join them in the next lesson.

Many database systems use SQL: PostgreSQL, MySQL, Microsoft SQL Server, Oracle, Google BigQuery, Snowflake and SQLite. Each has its own dialect with small differences (how dates work, how to limit rows), but SELECT, WHERE, GROUP BY and JOIN work almost the same everywhere. Learn the core once and you can move between them. We'll use SQLite because it's free, needs no server and stores the whole database in one file.

Set up: load the practice data into SQLite

  1. Install DB Browser for SQLite
  2. Create a new database
  3. Create the tables
  4. Import the CSV files
  5. Write changes
  1. Download DB Browser for SQLite (free, open source, for Windows, Mac and Linux) from its official website, sqlitebrowser.org.
  2. Click New Database and save it as sales.db. If a "table definition" window opens, close it.
  3. Open the Execute SQL tab, paste the code below and run it (the ▶ button or Ctrl+Enter). Creating the tables first guarantees that numbers are stored as numbers.
CREATE TABLE sales (
  order_id   TEXT PRIMARY KEY,
  order_date TEXT,      -- ISO format: YYYY-MM-DD
  region     TEXT,
  state      TEXT,
  product    TEXT,
  category   TEXT,
  channel    TEXT,
  units      INTEGER,
  unit_price INTEGER,
  revenue    INTEGER
);

CREATE TABLE products (
  product    TEXT PRIMARY KEY,
  category   TEXT,
  unit_price INTEGER,
  unit_cost  INTEGER
);
  1. Go to File → Import → Table from CSV file, choose sales-practice-data-clean.csv, tick Column names in first line, type sales as the table name and confirm that you want to import into the existing table. Repeat with sales-practice-products.csv into products.
  2. Click Write Changes (Ctrl+S) to save.
Why create the tables first?

If you let an import tool create the table, it may store every column as text. Then ORDER BY revenue DESC sorts alphabetically, so "998" comes before "5229". Defining INTEGER columns avoids that silent bug. Prefer to work in the browser? Google BigQuery's free sandbox and many online SQLite editors accept the same CSV files and the same queries.

Your first query: SELECT … FROM

Every query starts by saying which columns you want (SELECT) and from which table (FROM). LIMIT keeps the output short while you explore:

SELECT order_id, order_date, product, revenue
FROM sales
LIMIT 5;
order_idorder_dateproductrevenue
SO-100012025-01-01Office Chair498
SO-100022025-01-07Monitor Stand1080
SO-100032025-01-09Wireless Earbuds158
SO-100042025-01-09Smart Watch199
SO-100052025-01-15Air Fryer119

SELECT * returns all columns. It's handy for a first peek, but in real work name the columns you need: queries run faster on big tables and are easier to read. SQL keywords aren't case-sensitive (select works too), but writing them in uppercase is a common convention that makes queries easier to scan.

Filter rows with WHERE, sort with ORDER BY

WHERE keeps only the rows that meet a condition. ORDER BY sorts the result (ASC ascending is the default; DESC is descending). Here are the five largest wholesale orders in the West:

SELECT order_id, state, product, units, revenue
FROM sales
WHERE region = 'West'
  AND channel = 'Wholesale'
ORDER BY revenue DESC
LIMIT 5;
order_idstateproductunitsrevenue
SO-10186ArizonaOffice Chair215229
SO-10046WashingtonSmart Watch244776
SO-10048CaliforniaSmart Watch224378
SO-10182CaliforniaAdjustable Dumbbells133887
SO-10090CaliforniaWireless Earbuds241896

Text values go in single quotes ('West'). Numbers don't need quotes. To count the matching rows instead of listing them, replace the column list with COUNT(*): SELECT COUNT(*) FROM sales WHERE region = 'West' AND channel = 'Wholesale'; returns 14.

1. Which query lists Texas orders from newest to oldest?

  • SELECT * FROM sales ORDER BY order_date WHERE state = 'Texas';
  • SELECT * FROM sales WHERE state = 'Texas' ORDER BY order_date DESC;
  • SELECT * FROM sales WHERE state = Texas ORDER BY order_date ASC;

Clauses must appear in order (SELECT, FROM, WHERE, ORDER BY), text needs single quotes, and newest-first means descending dates. The first option has WHERE after ORDER BY; the third forgets the quotes and sorts oldest first. The query returns 25 rows.

Operators for WHERE

OperatorExampleMeaning
=, <>channel <> 'Online'Equal, not equal (!= also works in SQLite)
>, >=, <, <=revenue >= 1000Comparisons
BETWEEN … AND …units BETWEEN 5 AND 10Inclusive range
IN (…)state IN ('California','Texas','Florida')Matches any value in the list
LIKEproduct LIKE '%Watch%'Pattern: % = any characters, _ = one character
IS NULL, IS NOT NULLrevenue IS NULLMissing values

The IN example counts 74 orders from the three biggest states. Dates in our table are text in ISO format (YYYY-MM-DD), so they compare correctly as text. All orders of $1,000 or more in December:

SELECT order_id, order_date, state, revenue
FROM sales
WHERE order_date BETWEEN '2025-12-01' AND '2025-12-31'
  AND revenue >= 1000
ORDER BY order_date;
order_idorder_datestaterevenue
SO-101762025-12-01New Jersey2587
SO-101802025-12-05Colorado1104
SO-101822025-12-09California3887
SO-101862025-12-13Arizona5229
SO-101902025-12-18Arizona1264
Dates in other databases

SQLite stores these dates as text, which works because of the ISO format. PostgreSQL, SQL Server and BigQuery have real DATE types and functions like EXTRACT(MONTH FROM order_date) or DATE_TRUNC. And if a timestamp column includes times, BETWEEN '2025-12-01' AND '2025-12-31' can miss orders placed during the day on December 31; order_date < '2026-01-01' is safer.

AND, OR and parentheses

When you mix AND and OR, SQL evaluates AND first, just like multiplication before addition. That can silently change your result. Compare these two queries, which look almost the same:

-- Returns 73 rows: ALL West orders, plus South wholesale orders
SELECT COUNT(*) FROM sales
WHERE region = 'West' OR region = 'South' AND channel = 'Wholesale';

-- Returns 23 rows: wholesale orders from the West or the South
SELECT COUNT(*) FROM sales
WHERE (region = 'West' OR region = 'South') AND channel = 'Wholesale';

The first query reads as "West, or (South and Wholesale)": 64 West orders plus 9 South wholesale orders = 73. The second gives the intended 14 + 9 = 23. Rule: whenever AND and OR appear together, add parentheses. Even better, use region IN ('West','South').

2. How many rows does WHERE region = 'South' OR channel = 'Online' return, if the South has 59 orders, there are 92 online orders and 26 orders are both South and Online?

  • 151
  • 125
  • 26

OR keeps rows that match either condition, and rows matching both are counted once: 59 + 92 − 26 = 125. AND would return only the 26 rows matching both.

Calculated columns, aliases and DISTINCT

You can calculate new columns in the SELECT list and name them with AS. This query finds smart watch orders of 10 units or more and double-checks that revenue equals units × price:

SELECT order_id, product, units, unit_price,
       units * unit_price AS check_revenue
FROM sales
WHERE product LIKE '%Watch%'
  AND units >= 10
ORDER BY units DESC;
order_idproductunitsunit_pricecheck_revenue
SO-10046Smart Watch241994776
SO-10048Smart Watch221994378
SO-10143Smart Watch161993184
SO-10176Smart Watch131992587
SO-10061Smart Watch101991990

SELECT DISTINCT channel FROM sales ORDER BY channel; returns each value once: Online, Retail, Wholesale. It's the SQL version of the UNIQUE function, and a quick way to spot inconsistent spellings in a column.

Finally, the five largest orders of the year, from SELECT order_id, region, product, revenue FROM sales ORDER BY revenue DESC LIMIT 5;:

Top 5 orders of 2025 by revenue

SO-10186 · Office Chair (West)$5,229
SO-10046 · Smart Watch (West)$4,776
SO-10048 · Smart Watch (West)$4,378
SO-10182 · Dumbbells (West)$3,887
SO-10143 · Smart Watch (Northeast)$3,184

These five orders add up to $21,454, almost a quarter of the year's revenue. One query, one important finding.

3. Which pattern matches every product whose name starts with "Smart"?

  • product LIKE 'Smart%'
  • product LIKE '%Smart'
  • product = 'Smart%'

The % wildcard stands for any characters, so 'Smart%' means "Smart followed by anything." '%Smart' would match names ending in "Smart," and the = operator treats % as a literal character. Note that LIKE is case-insensitive for plain English letters in SQLite but case-sensitive in PostgreSQL.

The query clauses at a glance

SELECT

Which columns or calculations to return, with optional aliases (AS).

Tip: name the columns instead of using * in final queries.

FROM

Which table the rows come from.

Tip: next lesson, FROM grows to include JOINs.

WHERE

Which rows to keep, before any sorting or grouping.

Tip: parentheses whenever you mix AND and OR.

ORDER BY + LIMIT

How to sort the result and how many rows to return.

Tip: LIMIT without ORDER BY returns rows in no guaranteed order.

Beginner SQL mistakes

Using double quotes for text: WHERE region = "West".

Use single quotes for text values. In standard SQL, double quotes are for column names.

Writing WHERE revenue = NULL.

NULL means "unknown," so nothing equals it. Use IS NULL.

Forgetting a comma between columns: SELECT order_id product.

Without the comma, SQL treats "product" as an alias for order_id. Check commas first when output looks strange.

Mixing AND and OR without parentheses.

Group conditions explicitly; the result can differ by dozens of rows.

4. In the raw data (before cleaning), you want the orders whose revenue is missing. Which condition is correct?

  • WHERE revenue = NULL
  • WHERE revenue = 0
  • WHERE revenue IS NULL

Any comparison with NULL using = returns unknown, so it never matches. IS NULL is the correct test. On the raw CSV it would return the 4 orders with a blank revenue that you fixed in Lesson 5.

Practice exercise

Write a query that returns the order ID, state, product and revenue of every Fitness order worth more than $500, from largest to smallest. How many rows do you get, and which product dominates the list?

See solution
SELECT order_id, state, product, revenue
FROM sales
WHERE category = 'Fitness'
  AND revenue > 500
ORDER BY revenue DESC;
order_idstateproductrevenue
SO-10182CaliforniaAdjustable Dumbbells3887
SO-10096PennsylvaniaAdjustable Dumbbells2392
SO-10093TexasAdjustable Dumbbells1196
SO-10064ArizonaYoga Mat700
SO-10063New JerseyAdjustable Dumbbells598
SO-10094CaliforniaAdjustable Dumbbells598
SO-10070North CarolinaYoga Mat525

You get 7 rows. Adjustable dumbbells ($299 each) account for 5 of them; yoga mats ($35) only reach $500 in bulk orders of 15 or 20 mats. Note the condition is > 500, not >= 500; read requirements carefully, because "more than" and "at least" give different results when a value sits exactly on the boundary.

Summary

  • SQL is the standard language for querying relational databases; the core works across PostgreSQL, MySQL, SQL Server, BigQuery and SQLite.
  • Create tables with the right types before importing CSVs, so numbers sort and sum as numbers.
  • A basic query is SELECT columns FROM table WHERE conditions ORDER BY column LIMIT n.
  • Use single quotes for text, IS NULL for missing values and parentheses when mixing AND and OR.
  • BETWEEN, IN and LIKE make filters shorter; AS names calculated columns.
  • The 5 largest orders bring in $21,454, almost a quarter of 2025 revenue.

Next lesson: Intermediate SQL: GROUP BY, HAVING and JOINs. You'll summarize revenue by group and combine sales with product costs to calculate gross profit.

🎓 Save your progress and get a free certificate

Create your account in 30 seconds, mark your lessons and download your certificate at the end.

Enroll for free