Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Basics in MS SQL Server

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

Instruction

Very good! You're pretty much done with this part of the course. You've learned quite a lot! Let's take a look:

  • You can sort rows using ORDER BY. You can specify the order using ASC (ascending) and DESC (descending). By default, it's ASC.
  • In order to limit the number of rows in the result set, you can use TOP n. This returns the first n rows.
  • Sometimes, you don't want to see duplicates in the result set. Use the DISTINCT keyword to eliminate duplicates.
  • There are five aggregate functions: COUNT(), MIN(), MAX(), SUM(), and AVG().
  • Remember that AVG() will perform integer operations on integer columns, and the result may vary from what you expect (e.g., AVG() used on a column containing 1, 2, 2, and 2 will return 1, even though it's 1.75 using floating-point division).
  • Note that by default, aggregate columns don't have names. It's a good practice to give an alias to such a column.
  • To group columns, use the GROUP BY clause. It groups all rows with the same value in the chosen columns.
  • When using GROUP BY, all columns you select must either be in the GROUP BY clause or passed in to an aggregate function.
  • Filtering aggregate functions can be done using HAVING. It works just like WHERE, except you can use aggregate functions with it.

Let's check how much you remember. This exercise will check your knowledge of the entirety of Part 4. Good luck!

Exercise

Show the last name and first name of each employee together with each employee's average salary and the number of years they have worked at the company.

Use the following aliases: AverageSalary for each employee's average salary and YearsWorked for the number of years they have worked at the company. Show only those employees who have spent more than two years at the company. Order the results by average salary in descending order.

Stuck? Here's a hint!

Type:

SELECT
  LastName,
  FirstName,
  AVG(Salary) AS AverageSalary,
  COUNT(*) AS YearsWorked
FROM Employee
GROUP BY LastName,
  FirstName
HAVING COUNT(*) > 2
ORDER BY AVG(Salary) DESC;