Databricks-Spark-Assoc Using Spark SQL Practice Question
A data engineer must produce a report that shows each department's total salary, but only for departments where the total salary exceeds 500,000. The source DataFrame is created from a Delta table with columns department and salary. Which Spark SQL query correctly returns the desired result?
⚠ Common exam trap
The trap here is assuming that WHERE can filter aggregated results or that a SELECT alias can be used in WHERE, when in Spark SQL only HAVING can filter on aggregates after grouping.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) > 500000
Filtering on an aggregate result requires the HAVING clause, which executes after GROUP BY. The query that groups by department, sums salary, and then applies HAVING with the aggregate condition returns only departments whose total salary exceeds 500,000, exactly matching the requirement.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department WHERE total_salary > 500000
Why it's wrong here
Referencing the alias total_salary in WHERE is not allowed because WHERE is evaluated before the SELECT list aliases are defined. Even if the alias were recognized, WHERE cannot filter on an aggregate. Spark will raise an unresolved column error or an invalid aggregate usage error.
- ✗
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department WHERE SUM(salary) > 500000
Why it's wrong here
This query fails because the WHERE clause is evaluated before aggregation, so aggregate functions like SUM are not allowed there. Spark will throw an AnalysisException about a missing aggregate function or invalid use of an aggregate in WHERE. The correct clause for filtering aggregated results is HAVING, which is evaluated after GROUP BY.
- ✓
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) > 500000
Why this is correct
This query correctly groups rows by department, computes the total salary per group, and then applies HAVING to filter groups whose aggregate exceeds 500,000. In Spark SQL, HAVING is the proper clause for aggregate-based filtering, and it is evaluated after GROUP BY, so it works as required.
- ✗
SELECT department, SUM(salary) AS total_salary FROM employees WHERE SUM(salary) > 500000 GROUP BY department
Why it's wrong here
Placing the aggregate condition in WHERE is invalid because WHERE filters individual rows before grouping. Spark SQL will reject SUM(salary) in the WHERE clause with an analysis error. The condition must be applied after aggregation, which is what HAVING does, not WHERE.
About these practice questions
This Databricks-Spark-Assoc question is part of Courseiva's 295-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-Spark-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-Spark-Assoc exam.