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

We'll use a simple employees table throughout this lesson:

┌────┬────────────┬────────────┬─────────────┬────────┬────────────┐
│ 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;

┌────────────┬────────────┬─────────────┐
│ first_name │ last_name  │ department  │
├────────────┼────────────┼─────────────┤
│ Alice      │ Johnson    │ Engineering │
│ Bob        │ Smith      │ Marketing   │
│ ...        │ ...        │ ...         │
└────────────┴────────────┴─────────────┘

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;

┌────────────┬───────────────┐
│ First Name │ Annual Salary │
├────────────┼───────────────┤
│ Alice      │ 95000         │
│ Bob        │ 65000         │
└────────────┴───────────────┘

Calculations in SELECT

Perform math operations on columns directly in your query.

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

┌────────────┬────────┬────────┐
│ first_name │ salary │ bonus  │
├────────────┼────────┼────────┤
│ Alice      │ 95000  │ 9500   │
│ Bob        │ 65000  │ 6500   │
└────────────┴────────┴────────┘

Remove Duplicates (DISTINCT)

Get unique values only, removing duplicates.

SELECT DISTINCT department
  FROM employees;

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

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';

┌────────────┬─────────────┐
│ 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;

┌────────────┬────────┐
│ 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;

┌────────────┬─────────────┬────────┐
│ 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';

┌────────────┬───────────┐
│ 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;

┌────────────┬────────┐
│ 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');

┌────────────┬─────────────┐
│ 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%';

Result: Alice

Ends with 'e':

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

Results: Alice, Charlie, Eve, Grace

Contains 'ar':

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

Result: 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.

-- Find 3-letter names starting with 'L' and ending in 'n' (e.g., Lin, Len)
SELECT * FROM employees WHERE first_name LIKE 'L_n';

-- Find "London" by specifying the exact number of middle characters
SELECT * FROM cities WHERE city_name LIKE 'L___on';

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 '!';

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;

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;

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

Descending Order (High to Low)

Use DESC for reverse order.

SELECT first_name, salary
  FROM employees
  ORDER BY salary DESC;

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤
│ Charlie    │ 105000 │ ← Highest
│ Alice      │ 95000  │
│ Henry      │ 92000  │
│ ...        │ ...    │
│ Bob        │ 65000  │ ← Lowest
└────────────┴────────┘

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;

┌────────────┬─────────────┬────────┐
│ first_name │ department  │ salary │
├────────────┼─────────────┼────────┤
│ Charlie    │ Engineering │ 105000 │ ┐
│ Alice      │ Engineering │ 95000  │ │ Engineering
│ Henry      │ Engineering │ 92000  │ │ (sorted by
│ Eve        │ Engineering │ 88000  │ ┘  salary DESC)
│ Frank      │ Marketing   │ 70000  │ ┐ Marketing
│ Bob        │ Marketing   │ 65000  │ ┘
│ Grace      │ Sales       │ 78000  │ ┐ Sales
│ 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;

┌────────────┬────────┐
│ 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;

┌────────────┬────────┐
│ first_name │ salary │
├────────────┼────────┤ 
│ Eve        │ 88000  │ ← 4th 
│ Grace      │ 78000  │ ← 5th   
│ Diana      │ 72000  │ ← 6th 
└────────────┴────────┘

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;

┌────────────┬────────┬────────────┐ 
│ 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"