Challenge: Employees With More Than Average Salary
When a subquery is written in the WHERE section, we can use the IN operator and comparison operators:
SELECT column1, ...
FROM table
WHERE column1 > (SELECT AGG(column2) FROM table)
Swipe to start coding
Find employees whose salary is above the average salary of all employees using a subquery in the WHERE section.
The resulting table should have 3 columns: first_name, last_name, and salary. Then, sort the result by salary from highest to lowest using ORDER BY.
Note
This syntax can be used as a great alternative to the
HAVINGclause.
Brief Instructions
- Retrieve the
first_name,last_name, andsalarycolumns from theemployeestable. - In the
WHEREclause, use an inner query with the syntaxsalary > [inner query]. - In the inner query, get the average value of the
salarycolumn from theemployeestable. - Sort the results by
salaryin descending order.
Solution
Thanks for your feedback!
single
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
Can you explain how the IN operator works with subqueries?
What are some examples of comparison operators I can use in subqueries?
Can you show a practical example using both IN and a comparison operator in a WHERE clause?
Awesome!
Completion rate improved to 4Challenge: Employees With More Than Average Salary
Swipe to show menu
When a subquery is written in the WHERE section, we can use the IN operator and comparison operators:
SELECT column1, ...
FROM table
WHERE column1 > (SELECT AGG(column2) FROM table)
Swipe to start coding
Find employees whose salary is above the average salary of all employees using a subquery in the WHERE section.
The resulting table should have 3 columns: first_name, last_name, and salary. Then, sort the result by salary from highest to lowest using ORDER BY.
Note
This syntax can be used as a great alternative to the
HAVINGclause.
Brief Instructions
- Retrieve the
first_name,last_name, andsalarycolumns from theemployeestable. - In the
WHEREclause, use an inner query with the syntaxsalary > [inner query]. - In the inner query, get the average value of the
salarycolumn from theemployeestable. - Sort the results by
salaryin descending order.
Solution
Switch to desktop for real-world practiceContinue from where you are using one of the options belowThanks for your feedback!
single
