Ordering
Limiting the output
Eliminating duplicate results
Aggregation
Grouping
HAVING: filtering and ordering groups
Let's practice
Instruction
Very good! You've pretty much done with this part of the course. You've learned how to group rows, count statistics, and sort them.
Let's find out how much you remember. This exercise will check your knowledge of this part.
Exercise
Show the columns last_name and first_name from the employees table together with each person's average salary and the number of years they (have) worked in the company.
Use the following aliases: average_salary for each person's average salary and years_worked for the number of years worked in the company. Show only such employees who (have) spent more than 2 years in the company. Order the results according to the average salary in the descending order.
Stuck? Here's a hint!
Type:
SELECT last_name, first_name, AVG(salary) AS average_salary, COUNT(DISTINCT year) AS years_worked FROM employees GROUP BY last_name, first_name HAVING COUNT(DISTINCT year) > 2 ORDER BY AVG(salary) DESC;
