DA0-002 Data Acquisition and Preparation Practice Question
A data analyst uses a CTE to find employees who earn more than the average salary in their department. Which SQL clause is used to define the CTE?
⚠ Common exam trap
Candidates often confuse the conceptual name 'CTE' with the actual SQL keyword — candidates who know the term but not the syntax may select 'CTE' or 'DEFINE' instead of the correct WITH clause.
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
✓
WITH
The WITH clause is the standard SQL syntax that introduces a Common Table Expression (CTE), allowing the analyst to define a named temporary result set (e.g., WITH dept_avg AS (SELECT dept, AVG(salary) ...)) that can then be referenced in the main query. This is defined in the SQL standard and supported by PostgreSQL, SQL Server, Oracle, and MySQL 8+. The CTE exists only for the duration of the single statement that follows it.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
DECLARE
Why it's wrong here
DECLARE defines variables or cursors in procedural SQL, not a named result set reusable within a query. A CTE requires the WITH clause preceding the SELECT. DECLARE would be tempting when writing T-SQL stored procedures that need local variables or server-side cursors, but it cannot define the temporary named result set this scenario demands.
- ✓
WITH
Why this is correct
The WITH clause introduces a named common table expression, letting the analyst define the per-department average salary subquery once and reference it in the main SELECT. This satisfies the stem's requirement for computing employees earning above their department's average without repeating the aggregate logic.
- ✗
DEFINE
Why it's wrong here
DEFINE is not a SQL keyword; a CTE is introduced with the WITH clause followed by the CTE name and an AS subquery. DEFINE belongs to other query languages or vendor extensions, so it cannot declare a common table expression in standard SQL.
- ✗
CTE
Why it's wrong here
A CTE is the construct being defined, not the clause that defines it; the question asks which clause introduces it. It is tempting because CTEs are genuinely used for exactly this per-department average comparison, and naming the construct feels like answering the question. WITH is the clause that precedes and defines the CTE.
Visual reference
About these practice questions
This DA0-002 question is part of Courseiva's 1,004-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 CompTIA exam blueprint
This DA0-002 practice question is part of Courseiva's free CompTIA 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 DA0-002 exam.