← DBMS Flagship logical execution lab

SQL Query Pipeline

Inspect and replay every relation—without sending arbitrary SQL anywhere.

⚡ 0 XP
Curated HR scenario

Active department payroll

Find the two departments with at least two active employees and the highest average pay.

Read-only preset

SQL query

Deterministic
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.

Two different ideas

Logical order ≠ physical plan

Logical teaching order

The clause-by-clause result defined by SQL semantics.

FROMJOINWHEREGROUP BYHAVINGSELECTORDER BYLIMIT
Illustrative possible physical plan

An engine may push filters earlier or select different algorithms.

  1. Filtered employee scan≈ 6 rows
  2. Department hash build≈ 4 rows
  3. Hash inner join≈ 5 rows
  4. Hash aggregate + group filter≈ 2 rows
  5. 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.

4 rows
departments source table
#department iddepartment nameofficeannual budget
110EngineeringDhaka350,000
220SalesLondon250,000
330SupportDhaka140,000
440PeopleDhaka100,000

employees e

Nine employees; Iman is not assigned to a department.

9 rows
employees source table
#employee idemployee namedepartment idsalarystatus
1101Aisha10120,000Active
2102Farhan10100,000Active
3103Mina1080,000Leave
4201Omar2090,000Active
5202Nila2075,000Active
6203Theo2070,000Leave
7301Rafi3065,000Active
8302Lena3060,000Leave
9901ImanNULL72,000Active
Logical stage 1 of 8

FROM · Start with employee rows

9rows after +9 from input

FROM establishes the first working relation: all nine employee rows.

Trace insight

Logical evaluation begins with row sources, even though SELECT is written first.

Before

Rows entering FROM

0
∅

No rows are entering this stage.

After

Rows leaving FROM

9
Rows after FROM
#employee idemployee namedepartment idsalarystatus
1101Aisha10120,000Active
2102Farhan10100,000Active
3103Mina1080,000Leave
4201Omar2090,000Active
5202Nila2075,000Active
6203Theo2070,000Leave
7301Rafi3065,000Active
8302Lena3060,000Leave
9901ImanNULL72,000Active
1 / 8
Final deterministic relation

Query result

2 rows
Final result for Active department payroll
#departmentactive employeestotal salaryaverage salary
1Engineering2220,000110,000
2Sales2165,00082,500

Reach LIMIT to complete this scenario and earn 25 XP.