SQL Basics

Your first queries: SELECT, WHERE, ORDER BY.

SQL: The Language of Databases

SQL (Structured Query Language) is how you communicate with relational databases. It's declarative, you describe what you want, not how to get it. The database figures out the most efficient way to retrieve your data. Mastering SQL means you can extract insights from millions of records with just a few lines of code.

Our Sample Database

Everything in this lesson runs against these tables. Paste this into psql first and you can reproduce every result below:

-- Run this once and every query in this lesson will work.
CREATE TABLE employees (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL,
    middle_name VARCHAR(50),              -- left NULL on purpose, for the IS NULL example
    department  VARCHAR(50)  NOT NULL,
    salary      INTEGER      NOT NULL,
    hire_date   DATE         NOT NULL
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
    ('Alice',   'Johnson',  'Engineering',  95000, '2022-01-15'),
    ('Bob',     'Smith',    'Marketing',    65000, '2021-03-22'),
    ('Charlie', 'Brown',    'Engineering', 105000, '2020-07-10'),
    ('Diana',   'Martinez', 'Sales',        72000, '2023-02-01'),
    ('Eve',     'Davis',    'Engineering',  88000, '2022-11-05'),
    ('Frank',   'Wilson',   'Marketing',    70000, '2021-09-14'),
    ('Grace',   'Lee',      'Sales',        78000, '2023-06-20'),
    ('Henry',   'Taylor',   'Engineering',  92000, '2022-04-18');

-- Three tiny tables used later, for the LIKE and escaping examples
CREATE TABLE cities   (city_name VARCHAR(50));
CREATE TABLE settings (key_name  VARCHAR(50));
CREATE TABLE products (discount  VARCHAR(10));

INSERT INTO cities   VALUES ('London'), ('Lisbon'), ('Lyon');
INSERT INTO settings VALUES ('User_ID'), ('UserXID');
INSERT INTO products VALUES ('10%'), ('105');

SELECT id, first_name, last_name, department, salary, hire_date
FROM employees ORDER BY id;

Expected Output:

┌────┬────────────┬───────────┬─────────────┬────────┬────────────┐
│ id │ first_name │ last_name │ department  │ salary │ hire_date  │
├────┼────────────┼───────────┼─────────────┼────────┼────────────┤
│  1 │ Alice      │ Johnson   │ Engineering │  95000 │ 2022-01-15 │
│  2 │ Bob        │ Smith     │ Marketing   │  65000 │ 2021-03-22 │
│  3 │ Charlie    │ Brown     │ Engineering │ 105000 │ 2020-07-10 │
│  4 │ Diana      │ Martinez  │ Sales       │  72000 │ 2023-02-01 │
│  5 │ Eve        │ Davis     │ Engineering │  88000 │ 2022-11-05 │
│  6 │ Frank      │ Wilson    │ Marketing   │  70000 │ 2021-09-14 │
│  7 │ Grace      │ Lee       │ Sales       │  78000 │ 2023-06-20 │
│  8 │ Henry      │ Taylor    │ Engineering │  92000 │ 2022-04-18 │
└────┴────────────┴───────────┴─────────────┴────────┴────────────┘

SELECT: Retrieving Data

Select All Columns

The asterisk (*) retrieves all columns from a table.

SELECT *
  FROM employees;

Returns all 8 rows with all 6 columns

Best Practice: Avoid SELECT * in production. Always specify the columns you need, it's faster and more maintainable.

Select Specific Columns

List only the columns you need, separated by commas.

SELECT first_name, last_name, department
  FROM employees;

Expected Output:

┌────────────┬───────────┬─────────────┐
│ first_name │ last_name │ department  │
├────────────┼───────────┼─────────────┤
│ Alice      │ Johnson   │ Engineering │
│ Bob        │ Smith     │ Marketing   │
│ Charlie    │ Brown     │ Engineering │
│ Diana      │ Martinez  │ Sales       │
│ Eve        │ Davis     │ Engineering │
│ Frank      │ Wilson    │ Marketing   │
│ Grace      │ Lee       │ Sales       │
│ Henry      │ Taylor    │ Engineering │
└────────────┴───────────┴─────────────┘

Renaming Columns (Aliases)

Use AS to give columns more readable names in the output.

SELECT first_name AS "First Name",
         salary     AS "Annual Salary"
  FROM employees;

Expected Output:

┌────────────┬───────────────┐
│ First Name │ Annual Salary │
├────────────┼───────────────┤
│ Alice      │         95000 │
│ Bob        │         65000 │
│ Charlie    │        105000 │
│ Diana      │         72000 │
│ Eve        │         88000 │
│ Frank      │         70000 │
│ Grace      │         78000 │
│ Henry      │         92000 │
└────────────┴───────────────┘

Calculations in SELECT

Perform math operations on columns directly in your query.

SELECT first_name,
         salary,
         salary * 0.1 AS bonus
  FROM employees;

Expected Output:

┌────────────┬────────┬─────────┐
│ first_name │ salary │  bonus  │
├────────────┼────────┼─────────┤
│ Alice      │  95000 │  9500.0 │
│ Bob        │  65000 │  6500.0 │
│ Charlie    │ 105000 │ 10500.0 │
│ Diana      │  72000 │  7200.0 │
│ Eve        │  88000 │  8800.0 │
│ Frank      │  70000 │  7000.0 │
│ Grace      │  78000 │  7800.0 │
│ Henry      │  92000 │  9200.0 │
└────────────┴────────┴─────────┘

Remove Duplicates (DISTINCT)

Get unique values only, removing duplicates.

SELECT DISTINCT department
  FROM employees;

Expected Output:

┌─────────────┐
│ department  │
├─────────────┤
│ Marketing   │
│ Engineering │
│ Sales       │
└─────────────┘

Not alphabetical: DISTINCT removes duplicates, it does not sort, and here PostgreSQL used a hash to do it, so the rows come back in whatever order the hash produced. Without ORDER BY, no query guarantees an order. Add ORDER BY department if you want them alphabetical.

WHERE: Filtering Data

WHERE clauses filter rows based on conditions. Only matching rows are returned.

Exact Match (=)

Find rows where a column equals a specific value.

SELECT first_name, department
  FROM employees
  WHERE department = 'Engineering';

Expected Output:

┌────────────┬─────────────┐
│ first_name │ department  │
├────────────┼─────────────┤
│ Alice      │ Engineering │
│ Charlie    │ Engineering │
│ Eve        │ Engineering │
│ Henry      │ Engineering │
└────────────┴─────────────┘

Greater Than (>)

Find rows where a value is greater than a threshold.

SELECT first_name, salary
  FROM employees
  WHERE salary > 90000;

Expected Output:

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Alice      │  95000 │
│ Charlie    │ 105000 │
│ Henry      │  92000 │
└────────────┴────────┘

All Comparison Operators

= Equal to
!= or <> Not equal to
> Greater than
>= Greater than or equal
< Less than
<= Less than or equal

Combining Conditions: AND

Both conditions must be true.

SELECT first_name, department, salary
  FROM employees
  WHERE department = 'Engineering'
    AND salary > 90000;

Expected Output:

┌────────────┬─────────────┬────────┐
│ first_name │ department  │ salary │
├────────────┼─────────────┼────────┤
│ Alice      │ Engineering │  95000 │
│ Charlie    │ Engineering │ 105000 │
│ Henry      │ Engineering │  92000 │
└────────────┴─────────────┴────────┘

Combining Conditions: OR

Either condition can be true.

SELECT first_name, department
  FROM employees
  WHERE department = 'Sales'
     OR department = 'Marketing';

Expected Output:

┌────────────┬────────────┐
│ first_name │ department │
├────────────┼────────────┤
│ Bob        │ Marketing  │
│ Diana      │ Sales      │
│ Frank      │ Marketing  │
│ Grace      │ Sales      │
└────────────┴────────────┘

Range: BETWEEN

Find values within a range (inclusive).

SELECT first_name, salary
  FROM employees
  WHERE salary BETWEEN 70000 AND 90000;

Expected Output:

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Diana      │  72000 │
│ Eve        │  88000 │
│ Frank      │  70000 │
│ Grace      │  78000 │
└────────────┴────────┘

Same as: WHERE salary >= 70000 AND salary <= 90000

Multiple Values: IN

Match any value in a list.

SELECT first_name, department
  FROM employees
  WHERE department IN ('Engineering', 'Sales');

Expected Output:

┌────────────┬─────────────┐
│ first_name │ department  │
├────────────┼─────────────┤
│ Alice      │ Engineering │
│ Charlie    │ Engineering │
│ Diana      │ Sales       │
│ Eve        │ Engineering │
│ Grace      │ Sales       │
│ Henry      │ Engineering │
└────────────┴─────────────┘

Cleaner than: WHERE department = 'Engineering' OR department = 'Sales'

Pattern Matching: LIKE

Search for patterns using wildcards: % (any characters) and _ (single character).

Starts with 'A':

SELECT first_name
  FROM employees
  WHERE first_name LIKE 'A%';

Expected Output:

┌────────────┐
│ first_name │
├────────────┤
│ Alice      │
└────────────┘

Ends with 'e':

SELECT first_name
  FROM employees
  WHERE first_name LIKE '%e';

Expected Output:

┌────────────┐
│ first_name │
├────────────┤
│ Alice      │
│ Charlie    │
│ Eve        │
│ Grace      │
└────────────┘

Contains 'ar':

SELECT first_name
  FROM employees
  WHERE first_name LIKE '%ar%';

Expected Output:

┌────────────┐
│ first_name │
├────────────┤
│ Charlie    │
└────────────┘

The Underscore (_) Wildcard

While % matches any number of characters, the underscore _ matches exactly one single character. This is useful when the length of the string is fixed.

-- cities holds: London, Lisbon, Lyon

-- Four characters: L, any two, then n.
SELECT * FROM cities WHERE city_name LIKE 'L__n';

-- Six characters: L, any three, then "on".
-- Careful: this is a shape, not a name. It matches London AND Lisbon.
SELECT * FROM cities WHERE city_name LIKE 'L___on';

Expected Output:

┌───────────┐
│ city_name │
├───────────┤
│ Lyon      │
└───────────┘

┌───────────┐
│ city_name │
├───────────┤
│ London    │
│ Lisbon    │
└───────────┘

Not a name match: Underscore counts characters, it does not understand words, the second pattern matched two different cities that happen to share a shape. Example: 'B_g' would match "Bag", "Beg", or "Big", but NOT "Brag".

Escaping Wildcards

If you need to search for the actual % or _ characters (for example, in a column containing "Discount_%"), you must "escape" them so SQL doesn't treat them as wildcards.

-- Use a backslash (\) to search for a literal underscore
SELECT * FROM settings WHERE key_name LIKE 'User\_ID';

-- In some systems, you explicitly define the escape character
SELECT * FROM products WHERE discount LIKE '10!%' ESCAPE '!';

Expected Output:

┌──────────┐
│ key_name │
├──────────┤
│ User_ID  │
└──────────┘

┌──────────┐
│ discount │
├──────────┤
│ 10%      │
└──────────┘

Note: The specific escape character can vary depending on your database system (PostgreSQL, MySQL, SQL Server).

Handling NULL Values

NULL represents missing or unknown data. Use IS NULL to check for it.

-- Find employees with no middle name
SELECT first_name
FROM employees
WHERE middle_name IS NULL;

Expected Output:

┌────────────┐
│ first_name │
├────────────┤
│ Alice      │
│ Bob        │
│ Charlie    │
│ Diana      │
│ Eve        │
│ Frank      │
│ Grace      │
│ Henry      │
└────────────┘

Every employee has no middle_name set, so all 8 rows match.

Common Mistake: Never use = NULL. Always use IS NULL or IS NOT NULL.

ORDER BY: Sorting Results

ORDER BY controls the order of results. Without it, order is unpredictable.

Ascending Order (Low to High)

ASC is the default sort order (optional to specify).

SELECT first_name, salary
  FROM employees
  ORDER BY salary;

Expected Output:

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Bob        │  65000 │
│ Frank      │  70000 │
│ Diana      │  72000 │
│ Grace      │  78000 │
│ Eve        │  88000 │
│ Henry      │  92000 │
│ Alice      │  95000 │
│ Charlie    │ 105000 │
└────────────┴────────┘

Descending Order (High to Low)

Use DESC for reverse order.

SELECT first_name, salary
  FROM employees
  ORDER BY salary DESC;

Expected Output:

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Charlie    │ 105000 │
│ Alice      │  95000 │
│ Henry      │  92000 │
│ Eve        │  88000 │
│ Grace      │  78000 │
│ Diana      │  72000 │
│ Frank      │  70000 │
│ Bob        │  65000 │
└────────────┴────────┘

Sort by Multiple Columns

First sort by department, then by salary within each department.

SELECT first_name, department, salary
  FROM employees
  ORDER BY department, salary DESC;

Expected Output:

┌────────────┬─────────────┬────────┐
│ first_name │ department  │ salary │
├────────────┼─────────────┼────────┤
│ Charlie    │ Engineering │ 105000 │
│ Alice      │ Engineering │  95000 │
│ Henry      │ Engineering │  92000 │
│ Eve        │ Engineering │  88000 │
│ Frank      │ Marketing   │  70000 │
│ Bob        │ Marketing   │  65000 │
│ Grace      │ Sales       │  78000 │
│ Diana      │ Sales       │  72000 │
└────────────┴─────────────┴────────┘

LIMIT: Controlling Result Size

LIMIT restricts how many rows are returned. Essential for large datasets and pagination.

Get Top N Results

Return only the first 3 results.

SELECT first_name, salary
  FROM employees
  ORDER BY salary DESC LIMIT 3;

Expected Output:

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Charlie    │ 105000 │
│ Alice      │  95000 │
│ Henry      │  92000 │
└────────────┴────────┘

Pagination with OFFSET

Skip the first N rows, then return the next M rows.

-- Get results 4-6 (skip first 3)
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

Expected Output:

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Eve        │  88000 │
│ Grace      │  78000 │
│ Diana      │  72000 │
└────────────┴────────┘

Reading the rows: These are the 4th, 5th and 6th highest salaries, OFFSET 3 skipped Charlie, Alice and Henry.

Pagination Formula

Page 1: LIMIT 10 OFFSET 0 (rows 1-10)
Page 2: LIMIT 10 OFFSET 10 (rows 11-20)
Page 3: LIMIT 10 OFFSET 20 (rows 21-30)
Page N: LIMIT 10 OFFSET ((N-1) * 10)

Putting It All Together

SQL Execution Order

FROM - Get data from table
WHERE - Filter rows
SELECT - Choose columns
ORDER BY - Sort results
LIMIT - Restrict count

Complex Query Example

Find high-earning engineers hired recently.

SELECT first_name, salary, hire_date
  FROM employees
  WHERE department = 'Engineering'
    AND salary >= 90000
    AND hire_date >= '2022-01-01'
  ORDER BY salary DESC LIMIT 3;

Expected Output:

┌────────────┬────────┬────────────┐
│ first_name │ salary │ hire_date  │
├────────────┼────────┼────────────┤
│ Alice      │  95000 │ 2022-01-15 │
│ Henry      │  92000 │ 2022-04-18 │
└────────────┴────────┴────────────┘

Common Mistakes

❌ Using = Instead of LIKE

-- WRONG
WHERE first_name = 'Ali%'  -- Looks for literal "Ali%"
-- CORRECT
WHERE first_name LIKE 'Ali%'  -- Pattern match

❌ Forgetting Quotes on Strings

-- WRONG
WHERE department = Engineering  -- ERROR
-- CORRECT
WHERE department = 'Engineering'  -- ✓

❌ Using = NULL

-- WRONG
WHERE middle_name = NULL  -- Returns nothing
-- CORRECT
WHERE middle_name IS NULL  -- ✓

❌ Missing WHERE with DELETE

-- DANGER: Deletes ALL employees!
DELETE FROM employees;
-- SAFE: Always use WHERE
DELETE FROM employees WHERE id = 5;
-- Pro tip: Test with SELECT first
SELECT * FROM employees WHERE id = 5;  -- ✓ Verify
DELETE FROM employees WHERE id = 5;    -- Then delete

Practice Exercises

Easy

Find all employees in Sales
Show the 3 lowest paid employees
Find names starting with 'D'

Medium

Engineers earning more than $90,000
Employees hired between 2021-2022
Last names containing 'son' or ending with 'z'

Key Takeaways

SELECT specifies which columns to retrieve
WHERE filters rows based on conditions
ORDER BY sorts results (ASC or DESC)
LIMIT restricts the number of rows returned
Execution order: FROM → WHERE → SELECT → ORDER BY → LIMIT
NULL is special - always use IS NULL, never = NULL
Test first: Use SELECT to verify before UPDATE or DELETE
These four clauses form the foundation of data retrieval, master them and you can query any relational database

Back to "Relational Concepts"

Database FundamentalsLesson 3

Next: "Data Manipulation"