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
- 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.
Load these two CSV files into a database as the tables sales and products. Every query result shown below was produced from them.
- sales-practice-data-clean.csv · 200 clean orders (table
sales) - sales-practice-products.csv · 11 products with unit cost (table
products)
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
- Install DB Browser for SQLite
- Create a new database
- Create the tables
- Import the CSV files
- Write changes
- Download DB Browser for SQLite (free, open source, for Windows, Mac and Linux) from its official website, sqlitebrowser.org.
- Click New Database and save it as
sales.db. If a "table definition" window opens, close it. - 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
);
- Go to File → Import → Table from CSV file, choose
sales-practice-data-clean.csv, tick Column names in first line, typesalesas the table name and confirm that you want to import into the existing table. Repeat withsales-practice-products.csvintoproducts. - Click Write Changes (
Ctrl+S) to save.
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_id | order_date | product | revenue |
|---|---|---|---|
| SO-10001 | 2025-01-01 | Office Chair | 498 |
| SO-10002 | 2025-01-07 | Monitor Stand | 1080 |
| SO-10003 | 2025-01-09 | Wireless Earbuds | 158 |
| SO-10004 | 2025-01-09 | Smart Watch | 199 |
| SO-10005 | 2025-01-15 | Air Fryer | 119 |
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_id | state | product | units | revenue |
|---|---|---|---|---|
| SO-10186 | Arizona | Office Chair | 21 | 5229 |
| SO-10046 | Washington | Smart Watch | 24 | 4776 |
| SO-10048 | California | Smart Watch | 22 | 4378 |
| SO-10182 | California | Adjustable Dumbbells | 13 | 3887 |
| SO-10090 | California | Wireless Earbuds | 24 | 1896 |
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?
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
| Operator | Example | Meaning |
|---|---|---|
=, <> | channel <> 'Online' | Equal, not equal (!= also works in SQLite) |
>, >=, <, <= | revenue >= 1000 | Comparisons |
BETWEEN … AND … | units BETWEEN 5 AND 10 | Inclusive range |
IN (…) | state IN ('California','Texas','Florida') | Matches any value in the list |
LIKE | product LIKE '%Watch%' | Pattern: % = any characters, _ = one character |
IS NULL, IS NOT NULL | revenue IS NULL | Missing 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_id | order_date | state | revenue |
|---|---|---|---|
| SO-10176 | 2025-12-01 | New Jersey | 2587 |
| SO-10180 | 2025-12-05 | Colorado | 1104 |
| SO-10182 | 2025-12-09 | California | 3887 |
| SO-10186 | 2025-12-13 | Arizona | 5229 |
| SO-10190 | 2025-12-18 | Arizona | 1264 |
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?
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_id | product | units | unit_price | check_revenue |
|---|---|---|---|---|
| SO-10046 | Smart Watch | 24 | 199 | 4776 |
| SO-10048 | Smart Watch | 22 | 199 | 4378 |
| SO-10143 | Smart Watch | 16 | 199 | 3184 |
| SO-10176 | Smart Watch | 13 | 199 | 2587 |
| SO-10061 | Smart Watch | 10 | 199 | 1990 |
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;:
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"?
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?
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_id | state | product | revenue |
|---|---|---|---|
| SO-10182 | California | Adjustable Dumbbells | 3887 |
| SO-10096 | Pennsylvania | Adjustable Dumbbells | 2392 |
| SO-10093 | Texas | Adjustable Dumbbells | 1196 |
| SO-10064 | Arizona | Yoga Mat | 700 |
| SO-10063 | New Jersey | Adjustable Dumbbells | 598 |
| SO-10094 | California | Adjustable Dumbbells | 598 |
| SO-10070 | North Carolina | Yoga Mat | 525 |
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.
Create your account in 30 seconds, mark your lessons and download your certificate at the end.