List employees grouped by department who receive the second half of their salary.

To list employees by department who receive the second half of their salary, you can first determine the threshold that separates the lower half from the upper half of the salary distribution. One common approach is to use the median salary as the threshold. In many SQL dialects you can compute the median with a window function or a percentile function. For example, in PostgreSQL:

WITH median_salary AS (

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS med

FROM employee

)

SELECT d.dept_name,

   e.emp_name,
   e.salary

FROM employee e

JOIN department d ON e.dept_id = d.dept_id

CROSS JOIN median_salary m

WHERE e.salary > m.med

ORDER BY d.dept_name, e.salary DESC;

This query joins the employee and department tables, calculates the median salary, and then filters employees whose salary is greater than the median (i.e., in the second half). The result is grouped by department name and lists the employees in each department who meet the criterion.

If your database does not support PERCENTILE_CONT, you can approximate the median by ordering salaries and picking the middle row using ROW_NUMBER() or by using a subquery that selects the salary at the 50th percentile. The key idea is to compute a threshold that splits the salary distribution in half and then filter on that threshold.