SQL Query Pipeline
Inspect and replay every relation—without sending arbitrary SQL anywhere.
Active department payroll
Find the two departments with at least two active employees and the highest average pay.
SQL query
SELECT d.department_name AS department,
COUNT(e.employee_id) AS active_employees,
SUM(e.salary) AS total_salary,
AVG(e.salary) AS average_salary
FROM employees e
INNER JOIN departments d
ON d.department_id = e.department_id
WHERE e.status = 'Active'
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.employee_id) >= 2
ORDER BY average_salary DESC
LIMIT 2; ReplayCS evaluates a fixed in-memory dataset. This lab does not execute user-provided SQL.
Logical order ≠ physical plan
The clause-by-clause result defined by SQL semantics.
An engine may push filters earlier or select different algorithms.
- Filtered employee scan≈ 6 rows
- Department hash build≈ 4 rows
- Hash inner join≈ 5 rows
- Hash aggregate + group filter≈ 2 rows
- Top-N sort≈ 2 rows
This is an illustrative possible plan, not output captured from a database optimizer. Real plans vary with indexes, statistics, data size, engine, and configuration.
Consistent datasetInspect source tables 13 rows
departments d
Four departments, including People with no matching employee rows.
| # | department id | department name | office | annual budget |
|---|---|---|---|---|
| 1 | 10 | Engineering | Dhaka | 350,000 |
| 2 | 20 | Sales | London | 250,000 |
| 3 | 30 | Support | Dhaka | 140,000 |
| 4 | 40 | People | Dhaka | 100,000 |
employees e
Nine employees; Iman is not assigned to a department.
| # | employee id | employee name | department id | salary | status |
|---|---|---|---|---|---|
| 1 | 101 | Aisha | 10 | 120,000 | Active |
| 2 | 102 | Farhan | 10 | 100,000 | Active |
| 3 | 103 | Mina | 10 | 80,000 | Leave |
| 4 | 201 | Omar | 20 | 90,000 | Active |
| 5 | 202 | Nila | 20 | 75,000 | Active |
| 6 | 203 | Theo | 20 | 70,000 | Leave |
| 7 | 301 | Rafi | 30 | 65,000 | Active |
| 8 | 302 | Lena | 30 | 60,000 | Leave |
| 9 | 901 | Iman | NULL | 72,000 | Active |
FROM · Start with employee rows
FROM establishes the first working relation: all nine employee rows.
Logical evaluation begins with row sources, even though SELECT is written first.
Rows entering FROM
No rows are entering this stage.
Rows leaving FROM
| # | employee id | employee name | department id | salary | status |
|---|---|---|---|---|---|
| 1 | 101 | Aisha | 10 | 120,000 | Active |
| 2 | 102 | Farhan | 10 | 100,000 | Active |
| 3 | 103 | Mina | 10 | 80,000 | Leave |
| 4 | 201 | Omar | 20 | 90,000 | Active |
| 5 | 202 | Nila | 20 | 75,000 | Active |
| 6 | 203 | Theo | 20 | 70,000 | Leave |
| 7 | 301 | Rafi | 30 | 65,000 | Active |
| 8 | 302 | Lena | 30 | 60,000 | Leave |
| 9 | 901 | Iman | NULL | 72,000 | Active |
Query result
| # | department | active employees | total salary | average salary |
|---|---|---|---|---|
| 1 | Engineering | 2 | 220,000 | 110,000 |
| 2 | Sales | 2 | 165,000 | 82,500 |
Reach LIMIT to complete this scenario and earn 25 XP.