sqlbeginner10 minutes

Refactor a Repetitive SQL Query Using Aliases and Simplified WHERE Clause

Optimize a beginner-level SQL query by refactoring repetitive conditions and using table aliases for clarity. The query fetches all employees from the 'employees' table who belong to certain departments and have a salary above a threshold.

Challenge prompt

Below is a SQL query that returns employees from the 'employees' table who work in either the 'Sales' or 'Marketing' department and earn more than 50000. The query is correct but overly repetitive and hard to read. Your task is to refactor this query to improve readability and maintain the same output without changing its behavior.

Guidance

• Use table aliases to shorten table names and improve readability.
• Combine the department conditions using the IN clause instead of multiple OR statements.
• Keep the same condition on salary to filter employees earning above 50000.

Hints

• Consider replacing OR conditions on the same column with the IN operator.
• Assign a short alias like `e` to the 'employees' table to reduce repetition.

Starter code

SELECT employees.id, employees.name, employees.department, employees.salary
FROM employees
WHERE employees.department = 'Sales'
   OR employees.department = 'Marketing'
  AND employees.salary > 50000;

Expected output

A result set listing employees working in 'Sales' or 'Marketing' departments earning more than 50000, identical to the original query.

Core concepts

SQL SELECT statementWHERE clause conditionsTable aliasesIN operator

Challenge a Friend

Send this duel to someone else and see if they can solve it.