Apply One Salary Rule to Two Teams
Find IT or Sales candidates who earn strictly more than $70,000.
- Filtering
- Sorting
Exercise brief
Understand the request
Compensation operations analyst A review covers IT and Sales candidates but applies one strict salary rule to both teams.
Return IT or Sales candidates who earn strictly more than $70,000.
Return
- Return candidate_id, team_code, salary in this exact left-to-right order.
Constraints
- Group the two team alternatives before applying the salary condition.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
cohort_candidates
candidate_idINTEGERteam_codeVARCHAR(20)salaryDECIMAL(10,2)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
The salary rule must apply to both team alternatives, not only the second team.
Hint 2
Group IT and Sales inside parentheses, then connect that group to the strict salary predicate with AND.
Hint 3
SELECT candidate_id, team_code, salary FROM cohort_candidates WHERE (team_code = /* team one */ OR team_code = /* team two */) AND salary /* strict comparison */ 70000 ORDER BY candidate_id;
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT candidate_id, team_code, salary FROM cohort_candidates WHERE (team_code = 'IT' OR team_code = 'SALES') AND salary > 70000 ORDER BY candidate_id;Why this works
Parentheses turn the two team alternatives into one logical unit before the shared salary rule is applied. The adversarial fixture includes an IT candidate earning exactly $70,000, which must be excluded by the strict boundary, plus lower-paid Sales and unrelated-team rows. Without the grouping, SQL evaluates AND before OR and admits the IT boundary row. Boolean precedence is consistent across the supported engines, but explicit parentheses make the business rule portable, reviewable, and resistant to later edits.
Success check
Every returned candidate belongs to IT or Sales and has salary above the exclusive boundary.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| candidate_id | team_code | salary |
|---|---|---|
| 104 | SALES | 70001 |
| 105 | IT | 72000 |
| 107 | SALES | 75001 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.