Soroush Esnaashari

01 — Tables, Keys & Basic SQL

Understand how relational data is organized and learn the basic SQL statements used to work with it.

Let's use one simple example throughout:

Students
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1  | Ali    | 24  |
| 2  | Sara   | 25  |
| 3  | Reza   | 23  |
+----+--------+-----+

1. Database

A database is an organized collection of data. A database can contain many tables.

2. Table

A table stores data about one type of thing, such as students, employees, products, or orders.

3. Row

A row represents one record.

1 | Ali | 24

4. Column

A column represents one attribute or property.

id
name
age

5. Primary Key

A primary key uniquely identifies each row in a table.

id | name
---+-----
1  | Ali
2  | Sara
3  | Reza

A primary key is unique and cannot be NULL.

6. Foreign Key

A foreign key is a column that references a key in another table. It is used to connect related data.

Students
id | name
---+-----
1  | Ali
2  | Sara

Courses
id | course | student_id
---+--------+-----------
1  | SQL    | 1
2  | Python | 2

Here, Courses.student_id references Students.id.

7. NULL

NULL means that a value is missing or unknown. It is not the same as 0 or an empty string.

8. Data Types

Columns have data types that define what kind of values they store.

INT          -- integer
VARCHAR      -- text
DECIMAL      -- decimal number
DATE         -- date
BOOLEAN      -- true/false

9. CREATE TABLE

Creates a new table.

CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

10. INSERT

Adds new rows.

INSERT INTO Students (id, name, age)
VALUES (1, 'Ali', 24);

You do not have to provide every column. If a column is omitted and has no default value, it will usually become NULL if NULL is allowed.

INSERT INTO Students (id, name)
VALUES (1, 'Ali');

11. SELECT

SELECT retrieves data from a table.

SELECT *
FROM Students;

* means all columns. You can also choose specific columns:

SELECT name, age
FROM Students;

12. UPDATE

Changes existing rows.

UPDATE Students
SET age = 25
WHERE id = 1;

Without WHERE, every row is updated:

UPDATE Students
SET age = 25;

13. DELETE

Deletes rows.

DELETE FROM Students
WHERE id = 1;

Without WHERE, all rows are deleted.

Key idea

At this stage, think of SQL as:

02 — Filtering, Sorting & SELECT

Learn how to choose the columns and rows you want, then sort and limit the result.

Use this table for the examples:

Students
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1  | Ali    | 24  |
| 2  | Sara   | 25  |
| 3  | Reza   | 23  |
| 4  | Mina   | 25  |
+----+--------+-----+

1. SELECT — choose columns

SELECT chooses which columns you want in the result.

SELECT name, age
FROM Students;

You can select all columns with *.

2. WHERE — choose rows

WHERE filters which rows you want.

SELECT *
FROM Students
WHERE age > 24;

This returns Sara and Mina.

So:

To find Ali's row:

SELECT *
FROM Students
WHERE name = 'Ali';

To get only Ali's age:

SELECT age
FROM Students
WHERE name = 'Ali';

3. AND

AND requires both conditions to be true.

SELECT *
FROM Students
WHERE age > 23 AND age < 26;

4. OR

OR requires at least one condition to be true.

SELECT *
FROM Students
WHERE age = 23 OR age = 25;

5. NOT

NOT reverses a condition.

SELECT *
FROM Students
WHERE NOT age = 25;

6. IN

IN checks whether a value is one of several values.

SELECT *
FROM Students
WHERE age IN (23, 25);

It is a shorter version of multiple OR conditions.

7. BETWEEN

BETWEEN checks whether a value is inside a range. The two endpoints are included.

SELECT *
FROM Students
WHERE age BETWEEN 23 AND 25;

8. LIKE

LIKE is used for text patterns. % means any number of characters.

SELECT *
FROM Students
WHERE name LIKE 'A%';

This finds names that start with A.

9. IS NULL

Use IS NULL or IS NOT NULL to check for missing values.

SELECT *
FROM Students
WHERE age IS NULL;

WHERE age = NULL is not the correct way to test for NULL.

10. ORDER BY

ORDER BY sorts the result.

SELECT *
FROM Students
ORDER BY age ASC;

ASC means smallest to largest. DESC means largest to smallest.

11. LIMIT

LIMIT controls how many rows are returned. Support and syntax can vary by database system.

SELECT *
FROM Students
LIMIT 2;

12. DISTINCT

DISTINCT removes duplicate results.

SELECT DISTINCT age
FROM Students;

Putting it together

SELECT name, age
FROM Students
WHERE age >= 24
ORDER BY age DESC
LIMIT 2;

Read it as: choose name and age → keep rows with age at least 24 → sort by age from high to low → return the first 2 rows.

Query building blocks

03 — Aggregation & GROUP BY

Learn how to summarize many rows with COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING.

Aggregation turns many rows into useful summary values.

1. COUNT()

COUNT(*) counts rows.

SELECT COUNT(*)
FROM Students;

COUNT(column) counts non-NULL values in that column.

2. SUM()

SUM() adds numeric values.

SELECT SUM(age)
FROM Students;

In real datasets, this is useful for values such as revenue, quantity, cost, or score.

3. AVG()

AVG() calculates the average.

SELECT AVG(age)
FROM Students;

4. MIN() and MAX()

MIN() finds the smallest value and MAX() finds the largest.

SELECT MIN(age), MAX(age)
FROM Students;

5. GROUP BY — make groups

GROUP BY puts rows with the same value into groups so you can calculate something for each group.

For example, count students for each age:

SELECT age, COUNT(*)
FROM Students
GROUP BY age;

The result is:

23 | 1
24 | 1
25 | 2

6. WHERE vs HAVING

WHERE filters individual rows before grouping.

HAVING filters groups after grouping.

SELECT age, COUNT(*)
FROM Students
WHERE age > 23
GROUP BY age
HAVING COUNT(*) > 1;

The order is:

You cannot put WHERE after GROUP BY in standard SQL.

7. Aliases with AS

AS gives a column or calculation a clearer name.

SELECT AVG(age) AS average_age
FROM Students;

Common query order

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

This is the order you normally write these clauses in a SQL query.

04 — Combining Tables with JOINs

Understand how JOINs connect related tables and when to use INNER, LEFT, RIGHT, and FULL OUTER JOIN.

Suppose we have two tables:

Students
id | name
---+-----
1  | Ali
2  | Sara
3  | Reza

Courses
id | course | student_id
---+--------+-----------
1  | SQL    | 1
2  | Python | 2
3  | ML     | 4

Courses.student_id points to Students.id.

1. What is a JOIN?

A JOIN combines rows from two tables when a related value matches.

The ON condition tells SQL how the tables are related.

SELECT Students.name, Courses.course
FROM Students
JOIN Courses
    ON Students.id = Courses.student_id;

Question — What is a JOIN?

Write a JOIN that returns each student's name together with their course.

2. INNER JOIN — only matching rows

INNER JOIN returns only rows that have a match in both tables.

SELECT Students.name, Courses.course
FROM Students
INNER JOIN Courses
    ON Students.id = Courses.student_id;

Here, Ali and Sara are returned because their IDs match. The course with student_id = 4 is not returned because student 4 does not exist in Students.

Question — INNER JOIN

Write a query that returns only students who have a matching course.

3. LEFT JOIN — keep all rows from the left table

LEFT JOIN keeps every row from the left table. If there is no match in the right table, the right-side columns become NULL.

SELECT Students.name, Courses.course
FROM Students
LEFT JOIN Courses
    ON Students.id = Courses.student_id;

Reza is included even though Reza has no matching course, so the course value is NULL.

Question — LEFT JOIN

Write a query that returns every student, including students who have not taken any course.

4. RIGHT JOIN — keep all rows from the right table

RIGHT JOIN is the opposite of LEFT JOIN: it keeps every row from the right table, even when there is no match in the left table.

SELECT Students.name, Courses.course
FROM Students
RIGHT JOIN Courses
    ON Students.id = Courses.student_id;

Here, the course with student_id = 4 is kept, but the student's name is NULL because student 4 is missing.

Some database systems do not support RIGHT JOIN. In those systems, the same result can usually be written with the tables reversed and a LEFT JOIN.

Question — RIGHT JOIN

Write a query that keeps every course, even when a course has no matching student.

5. FULL OUTER JOIN — keep everything

FULL OUTER JOIN keeps all rows from both tables. Matching rows are combined; unmatched rows appear with NULL on the missing side.

SELECT Students.name, Courses.course
FROM Students
FULL OUTER JOIN Courses
    ON Students.id = Courses.student_id;

This keeps Reza even without a course and also keeps the course whose student does not exist.

Support for FULL OUTER JOIN depends on the database system.

Question — FULL OUTER JOIN

Write a query that keeps every student and every course, whether or not they match.

6. JOIN with WHERE

You can join tables and then filter the joined rows with WHERE.

SELECT Students.name, Courses.course
FROM Students
INNER JOIN Courses
    ON Students.id = Courses.student_id
WHERE Students.name = 'Ali';

The JOIN decides which rows can be connected. WHERE then filters those joined rows.

Question — JOIN with WHERE

Write a query that joins students with their courses and returns only Sara's courses.

7. Table aliases

A table alias gives a table a short temporary name inside the query. This makes JOINs easier to read.

SELECT s.name, c.course
FROM Students AS s
JOIN Courses AS c
    ON s.id = c.student_id;

Here, s means Students and c means Courses.

AS is optional, so Students s is also valid.

Question — Table aliases

Rewrite an INNER JOIN between Students and Courses using the aliases s and c.

8. Joining more than two tables

You can keep adding JOINs when your data is spread across several related tables.

SELECT s.name, c.course, p.name AS professor
FROM Students AS s
JOIN Courses AS c
    ON s.id = c.student_id
JOIN Professors AS p
    ON c.professor_id = p.id;

Question — Joining more than two tables

Write a query that returns the student's name, course name, and professor name from three related tables.

Core JOIN idea

05 — Advanced Queries: CTEs & Windows

Learn a small set of advanced SQL features that make real-world queries easier to read and more powerful.

1. CASE — add logic inside a query

CASE works like an if/else expression.

SELECT name, age,
    CASE
        WHEN age >= 25 THEN '25 or older'
        ELSE 'Under 25'
    END AS age_group
FROM Students;

2. COALESCE — use a fallback value

COALESCE returns the first non-NULL value.

SELECT name,
    COALESCE(phone, 'No phone') AS phone
FROM Students;

This is useful when you want to replace missing values in a result.

3. Subqueries — query inside a query

A subquery is a query nested inside another query.

SELECT name, age
FROM Students
WHERE age > (
    SELECT AVG(age)
    FROM Students
);

The inner query calculates the average age. The outer query returns students older than that average.

4. CTEs with WITH — make complex queries clearer

A CTE (Common Table Expression) gives a temporary result a name so the main query is easier to read.

WITH average_age AS (
    SELECT AVG(age) AS avg_age
    FROM Students
)
SELECT name, age
FROM Students, average_age
WHERE age > avg_age;

A CTE is especially useful when a query has multiple steps.

5. Window Functions — calculate without collapsing rows

A window function performs a calculation across related rows while keeping the original rows in the result.

This is the key difference from GROUP BY: GROUP BY combines rows into groups, while a window function keeps each row.

6. ROW_NUMBER()

Assigns a unique row number to each row in the result.

SELECT name, age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num
FROM Students;

7. RANK()

Ranks rows based on a value. Ties receive the same rank.

SELECT name, age,
    RANK() OVER (ORDER BY age DESC) AS age_rank
FROM Students;

8. LAG() and LEAD()

LAG() looks at a previous row. LEAD() looks at a following row.

SELECT name, age,
    LAG(age) OVER (ORDER BY id) AS previous_age,
    LEAD(age) OVER (ORDER BY id) AS next_age
FROM Students;

9. A practical multi-step query

These features can be combined. For example, first create a result with a CTE, then rank the rows.

WITH student_scores AS (
    SELECT name, age
    FROM Students
)
SELECT name, age,
    RANK() OVER (ORDER BY age DESC) AS age_rank
FROM student_scores;

Core idea

What to remember from the whole course