Module 13intermediate
CTEs - WITH Clause
Lessons
What is a CTE?
1 / 1A CTE (Common Table Expression) is a temporary result set that you can reference within a query. It makes complex queries easier to read.
Syntax
text-blue-600 dark:text-blue-400 font-semibold">WITH cte_name text-blue-600 dark:text-blue-400 font-semibold">AS (text-blue-600 dark:text-blue-400 font-semibold">SELECT ... ) text-blue-600 dark:text-blue-400 font-semibold">SELECT ... text-blue-600 dark:text-blue-400 font-semibold">FROM cte_name;Sample Tables Used in This Course
employeesEmployee records
| Column | Type | Key |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | |
| department | TEXT | |
| salary | INTEGER | |
| country | TEXT | |
| manager_id | INTEGER | FK → employees.id |
| TEXT | ||
| hire_date | TEXT |
Sample Data (first 5 rows)
| id | name | department | salary | country | manager_id | hire_date | |
|---|---|---|---|---|---|---|---|
| 1 | Ahmed Al-Thani | IT | 25000 | Qatar | NULL | ahmed@qatarco.qa | 2018-03-15 |
| 2 | Fatima Al-Kuwari | HR | 18000 | Qatar | 1 | fatima@qatarco.qa | 2019-06-01 |
| 3 | Mohammed Al-Sulaiti | Finance | 22000 | Qatar | 1 | mohammed@qatarco.qa | 2017-09-20 |
| 4 | Sara Al-Mansouri | IT | 20000 | Qatar | 1 | sara@qatarco.qa | 2020-01-10 |
| 5 | Khalid Hassan | IT | 17000 | Egypt | 4 | khalid@qatarco.qa | 2021-04-05 |
ordersOrder transactions
| Column | Type | Key |
|---|---|---|
| id | INTEGER | PK |
| customer_id | INTEGER | FK → customers.id |
| amount | DECIMAL | |
| order_date | TEXT | |
| region | TEXT | |
| status | TEXT |
Sample Data (first 5 rows)
| id | customer_id | amount | order_date | region | status |
|---|---|---|---|---|---|
| 1 | 1 | 15000 | 2024-01-15 | Doha | completed |
| 2 | 2 | 8500 | 2024-01-20 | Doha | completed |
| 3 | 3 | 22000 | 2024-02-01 | Doha | pending |
| 4 | 1 | 5000 | 2024-02-10 | Al Wakrah | completed |
| 5 | 4 | 12000 | 2024-02-15 | Lusail | shipped |
Relationships: Primary Keys (PK) uniquely identify rows. Foreign Keys (FK) reference other tables.
Practice with these tables in the SQL Console. Click "Open Console" in the sidebar.
Example
text-blue-600 dark:text-blue-400 font-semibold">WITH high_earners text-blue-600 dark:text-blue-400 font-semibold">AS (
text-blue-600 dark:text-blue-400 font-semibold">SELECT * text-blue-600 dark:text-blue-400 font-semibold">FROM employees text-blue-600 dark:text-blue-400 font-semibold">WHERE salary > text-amber-600 dark:text-amber-400">15000
)
text-blue-600 dark:text-blue-400 font-semibold">SELECT department, text-blue-600 dark:text-blue-400 font-semibold">COUNT(*) text-blue-600 dark:text-blue-400 font-semibold">FROM high_earners text-blue-600 dark:text-blue-400 font-semibold">GROUP BY department;Creates a temporary table of high earners, then counts by department
Learning Objectives
- Understand what CTEs are
- Write queries using WITH clause