Ordering
Eliminating duplicate results
Aggregation
Grouping
HAVING: filtering and ordering groups
Let's practice
23. Put your skills into practice

Instruction

Very good! You've pretty much done in this part of the course. You've learnt how to group rows, count statistics and sort them.

Let's find out how much you remember. This exercise will check your knowledge all of Part 3.

Exercise

Show the columns last_name and first_name from the table employees 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(*) AS years_worked 
FROM employees 
GROUP BY last_name, first_name 
HAVING count(*) > 2 
ORDER BY avg(salary) DESC;