Difficulty: Beginner | Prerequisites: None. This is the starting point for the course.
Relational databases store data in structured tables, and SQL (Structured Query Language) is how you talk to them. This set of notes covers the foundational skill of pulling data out of a single table: selecting columns, filtering rows, sorting results, and searching for patterns. If you can write a solid SELECT statement with WHERE, ORDER BY, and LIKE, you have the core toolkit for every query you will ever build. Everything else in this course layers on top of these basics.
SQL queries retrieve data from database tables using three core clauses: SELECT (which columns), FROM (which table), and WHERE (which rows). You can sort results with ORDER BY, search text patterns with LIKE and wildcards, filter with comparison operators, combine conditions with AND/OR, match lists with IN, and check ranges with BETWEEN. Dates follow a yyyy-mm-dd format in PostgreSQL and can be filtered and formatted like any other value.
Database
An organised collection of data, stored in one or more tables, each identified by a table name.
SQL (Structured Query Language)
The standard programming language used to communicate with relational databases. Keywords are not case-sensitive, but uppercase is best practice.
SELECT
An SQL clause that specifies which columns to retrieve. Use * as a wildcard for all columns, or list specific column names separated by commas.
FROM
An SQL clause that identifies which table(s) to pull data from.
WHERE
An SQL clause that filters rows based on conditions. Only rows meeting the condition appear in the result set.
Result set
The data returned by a SELECT statement. Think of it as a temporary table of results.
Schema browser
A list of table names, column names, and data types contained within a database. It is the map of your database structure.
ORDER BY
An SQL clause that sorts the result set by one or more columns. Ascending (ASC) is the default; use DESC for descending.
Cascading order sequence
Sorting by multiple columns in priority order, separated by commas after ORDER BY. The first column is the primary sort; ties are broken by the second column, and so on.
LIKE
An operator used in WHERE clauses to match text patterns using wildcards. Without wildcards, LIKE behaves the same as =.
Wildcard (%)
Represents zero, one, or multiple characters in a LIKE pattern. 'L%' matches any string starting with L.
Wildcard (_)
Represents exactly one character in a LIKE pattern. 'U__' matches any three-character string starting with U.
Comparison operators
Symbols used in WHERE clauses to compare values: =, <, <=, >, >=, <> (not equal to).
AND
A logical operator that requires all conditions to be true. Adding more AND conditions narrows the result set.
OR
A logical operator that requires at least one condition to be true. Adding more OR conditions widens the result set.
IN
An operator that checks whether a value matches any value in a specified list. Replaces multiple OR conditions on the same column.
NOT IN
Returns rows where the value does not match any value in the list. Useful when the exclusion list is shorter than the inclusion list.
BETWEEN
An operator that checks whether a value falls within an inclusive range. Always specify the smaller value first.
Timestamp
A data type that stores both date and time values.
The simplest query has two clauses, always in this order:
SELECT *
FROM customer;
* returns all columns in the order they appear in the table
To return specific columns, list them by name, separated by commas:
SELECT first_name, last_name, email
FROM customer;
Column order in the result set matches the order you list them in SELECT
You can even list the same column twice (useful later for calculations)
Without ORDER BY, rows return in insertion order (typically by primary key)
Add ORDER BY after FROM to sort by any column:
SELECT *
FROM invoice
ORDER BY billing_country;
Ascending order is the default. Add DESC for descending:
SELECT invoice_id, customer_id, total
FROM invoice
ORDER BY total DESC;
Sort by multiple columns using commas (cascading order sequence):
SELECT customer_id, last_name, first_name, company
FROM customer
ORDER BY last_name, first_name, company;
The first column listed is the primary sort key; ties are broken by subsequent columns
WHERE goes after FROM and before ORDER BY
Use comparison operators to set conditions:
SELECT *
FROM customer
WHERE customer_id = 5;
Text values must be wrapped in single quotes. Numeric values must not:
-- Correct:
WHERE first_name = 'Helena';
-- Wrong (will error or give wrong results):
WHERE first_name = Helena;
Comparison operators: =, <, <=, >, >=, <> (not equal)
Be careful with >= vs > when decimal values are involved. WHERE total >= 15 and WHERE total > 14 are not equivalent if totals like 14.5 exist
LIKE is used in WHERE to match text patterns using wildcards
The % wildcard matches zero or more characters:
-- Names starting with L:
WHERE first_name LIKE 'L%';
-- Gmail addresses:
WHERE email LIKE '%@gmail.com';
The _ wildcard matches exactly one character:
-- States starting with C, exactly 2 characters:
WHERE state LIKE 'C_';
-- Countries starting with U, exactly 3 characters:
WHERE country LIKE 'U__';
LIKE is case-sensitive for non-wildcard characters
Combine wildcards for complex patterns:
-- Last names: starts with S, at least 4 characters:
WHERE last_name LIKE 'S___%';
-- Email starts with m, domain starts with a:
WHERE email LIKE 'm%@a%.%';
Without any wildcard, LIKE behaves identically to =
PostgreSQL stores dates in yyyy-mm-dd format
Dates must be enclosed in single quotes, like strings:
WHERE invoice_date = '2009-01-01';
Use comparison operators for date ranges:
WHERE invoice_date < '2009-01-31';
Useful date/time functions:
SELECT now(); returns current date and time
SELECT now()::date; returns current date only
SELECT now()::time; returns current time only
Format dates with TO_CHAR:
SELECT TO_CHAR(now()::date, 'mm/dd/yyyy');
Common format tokens: yyyy (4-digit year), MM (month number), dd (day), hh24 (24-hour), mi (minute), ss (second), Month (full month name), Day (full day name)
AND requires all conditions to be true (intersection):
SELECT *
FROM customer
WHERE country = 'USA'
AND support_rep_id = 3
AND city LIKE 'C%';
OR requires at least one condition to be true (union):
SELECT *
FROM employee
WHERE title = 'IT Manager'
OR title = 'IT Staff';
AND is evaluated before OR. Use parentheses to override:
-- OR first, then AND:
WHERE (title = 'IT Staff' OR reports_to = 6)
AND phone LIKE '%1';
More AND conditions = smaller or equal result set
More OR conditions = larger or equal result set
IN checks if a value matches any item in a list:
WHERE country IN ('Brazil', 'Belgium', 'Norway', 'Austria');
Equivalent to multiple OR conditions on the same column, but cleaner
Works with numbers too:
WHERE support_rep_id IN (1, 2, 3, 4);
NOT IN returns rows that do not match the list:
WHERE country NOT IN ('Brazil', 'Belgium', 'Norway', 'Austria');
The order of values in the list does not matter
Especially useful with subqueries (covered later in the course)
BETWEEN checks if a value is within an inclusive range:
WHERE support_rep_id BETWEEN 1 AND 4;
-- Equivalent to: WHERE support_rep_id >= 1 AND support_rep_id <= 4;
The smaller value must come first. Reversing them returns zero rows
Works with dates:
WHERE invoice_date BETWEEN '2009-03-01' AND '2009-03-31';
NOT BETWEEN returns values outside the range (exclusive of the boundary values due to the NOT):
WHERE genre_id NOT BETWEEN 10 AND 20;
Every web application that shows you a list of products, a filtered search, or sorted results is running SELECT statements with WHERE and ORDER BY behind the scenes. When you search for flights by date range on a travel site, that is BETWEEN on a date column. When an e-commerce site lets you filter by price range and brand, that is WHERE with AND. Pattern matching with LIKE powers the autocomplete in search bars.
Students often forget single quotes around text values in WHERE clauses. Without them, the database interprets the text as a column name, not a value.
Students assume WHERE total > 14 and WHERE total >= 15 are the same. They are not when decimal values exist (14.5 would pass the first but not the second).
Students think LIKE is always case-insensitive. In PostgreSQL, LIKE is case-sensitive. Use ILIKE for case-insensitive matching.
Students confuse AND and OR precedence. AND is always evaluated first unless you use parentheses to force a different order.
⚠️ Know the required clause order: SELECT, FROM, WHERE, ORDER BY. Out-of-order clauses produce errors.
⚠️ Remember that BETWEEN is inclusive on both ends. BETWEEN 1 AND 4 includes 1, 2, 3, and 4.
⚠️ Understand the difference between % (zero or more characters) and _ (exactly one character) in LIKE patterns.
⚠️ Be able to rewrite IN as multiple OR conditions, and BETWEEN as two comparison conditions with AND.
⚠️ Know that AND narrows results and OR widens them. This distinction is commonly tested.
True or False: SELECT * FROM customer WHERE country = Canada; is valid SQL.
Fill in the blank: The ___ wildcard in a LIKE clause matches exactly one character.
True or False: BETWEEN 5 AND 1 returns the same results as BETWEEN 1 AND 5.
Fill in the blank: To sort results from highest to lowest, add the keyword ___ after the column name in ORDER BY.
True or False: WHERE country IN ('USA', 'Canada') is equivalent to WHERE country = 'USA' OR country = 'Canada'.
Answers: 1. False (Canada needs single quotes). 2. Underscore (_). 3. False (returns zero rows). 4. DESC. 5. True.
Q: Write a query that returns the first name, last name, and email of all customers, sorted alphabetically by last name.
A: SELECT first_name, last_name, email FROM customer ORDER BY last_name;
Q: Write a query that finds all invoices with a total between 5 and 15, inclusive.
A: SELECT * FROM invoice WHERE total BETWEEN 5 AND 15;
Q: Write a query that finds customers whose email ends with '.com' and who live in either the USA or Canada.
A: SELECT * FROM customer WHERE email LIKE '%.com' AND country IN ('USA', 'Canada');
Q: What is the difference between WHERE and HAVING?
A: WHERE filters individual rows before any grouping. HAVING filters groups of rows after a GROUP BY clause has been applied. You cannot use aggregate functions in WHERE.
Q: Write a query that returns all employees whose last name starts with 'S' and is exactly five characters long.
A: SELECT * FROM employee WHERE last_name LIKE 'S____';
Q: Explain why WHERE total BETWEEN 10 AND 5 returns no rows.
A: BETWEEN expands to total >= 10 AND total <= 5. No value can be simultaneously greater than or equal to 10 and less than or equal to 5, so the condition is always false.
This connects directly to aggregate functions (Part 3 of these notes), where WHERE filters rows before grouping and HAVING filters after. It also connects to multi-table queries later in the course, where the same SELECT/FROM/WHERE structure expands to include JOIN operations across related tables. The IN operator becomes especially powerful when combined with subqueries.
SQL SELECT, SQL FROM, SQL WHERE, ORDER BY, ASC, DESC, cascading order, LIKE operator, SQL wildcards, percent wildcard, underscore wildcard, pattern matching, comparison operators, AND OR operators, logical operators, IN operator, NOT IN, BETWEEN operator, NOT BETWEEN, date filtering, TO_CHAR, timestamp, PostgreSQL date format, result set, SQL query basics, filtering data, sorting data, RDB 101, relational database fundamentals, Sophia Pathways