Database Tables and Median | Dataford Interview Questions

Given three relation database tables of employee, salary, and departments, ask to calculate median salary history for each department with SQL.

Use every available salary history record and include departments without salary records.

Output

One row per department with department_id, department_name, salary_history_count, and median_salary
Include departments with no salary history, using NULL for their median and a count of zero
Order by department_id ascending

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

employee

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	Department assigned to the employee

salary

Column	Type	Description
salary_idPK	INT	Unique salary history record identifier
employee_id	INT	Employee receiving the salary
salary_amount	NUMERIC(12,2)	Historical annual salary amount
effective_date	DATE	Date the salary became effective

Your question is Database Tables and Median. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.