Answer-first summary for fast verification
Answer: SELECT Department, MAX(Salary) AS HighestSalary, MIN(Salary) AS LowestSalary FROM EmployeeSalaries GROUP BY Department ORDER BY Department;
Option D is the correct answer because it not only groups the data by department and calculates the highest and lowest salary amounts for each department using the MAX() and MIN() functions, respectively, but it also orders the results by department name in ascending order, as required. This approach ensures minimal execution time and resource usage by efficiently aggregating and sorting the data in a single query pass. Options A and B do not include the ORDER BY clause, and Option C fails to group the data by department, resulting in incorrect aggregate values for each department.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
You are working as a Fabric Analytics Engineer and are tasked with optimizing a SQL query for a report that analyzes employee salaries across different departments. The dataset includes columns for Department, Position, and Salary. The report requires the highest and lowest salary amounts for each department, along with the department name, to be displayed in ascending order by department name. Additionally, the solution must ensure minimal execution time and resource usage due to the large volume of data. Which SQL query best meets these requirements? (Choose one correct answer)
A
SELECT Department, MAX(Salary) AS HighestSalary, MIN(Salary) AS LowestSalary FROM EmployeeSalaries GROUP BY Department;
B
SELECT Department, MAX(Salary) AS HighestSalary FROM EmployeeSalaries GROUP BY Department UNION SELECT Department, MIN(Salary) AS LowestSalary FROM EmployeeSalaries GROUP BY Department;
C
SELECT Department, MAX(Salary) AS HighestSalary, MIN(Salary) AS LowestSalary FROM EmployeeSalaries;
D
SELECT Department, MAX(Salary) AS HighestSalary, MIN(Salary) AS LowestSalary FROM EmployeeSalaries GROUP BY Department ORDER BY Department;