Refactor a SQL Query to Improve Readability and Efficiency
You are given a simple SQL query written with redundant expressions and unnecessary complexity. Refactor the query to improve its readability and optimize performance while keeping the same output.
Challenge prompt
The following SQL query retrieves all records from the 'employees' table where the employee's department is 'Sales' and the salary is greater than 50000. It also calculates a 10% bonus for each employee. Refactor this query to eliminate redundant conditions and improve clarity without changing the result. Original query: SELECT * FROM employees WHERE department = 'Sales' AND department = 'Sales' AND salary > 50000 AND salary > 50000; SELECT employee_id, name, salary, salary * 0.10 AS bonus FROM employees WHERE department = 'Sales' AND salary > 50000;
Guidance
- • Remove duplicate conditions in the WHERE clause to simplify the query.
- • Separate the selection of all fields and the calculation of bonus into a single clear SELECT statement.
- • Use meaningful column aliases to clarify the data you are retrieving.
Hints
- • Check for repeated conditions in the WHERE clause and remove duplicates.
- • Combine the two SELECT statements into one that retrieves only relevant columns along with the calculated bonus.
- • Use aliasing to clearly label the calculated bonus column.
Starter code
SELECT * FROM employees WHERE department = 'Sales' AND department = 'Sales' AND salary > 50000 AND salary > 50000;
SELECT employee_id, name, salary, salary * 0.10 AS bonus FROM employees WHERE department = 'Sales' AND salary > 50000;Expected output
A query returning employee_id, name, salary, and a bonus column for all employees in 'Sales' with salary > 50000, with no redundant conditions.
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.