Exercises

Exercise 1

Write a query to return the first name and the highest ShiftID value for each group of employees named Kevin, Linda, or Mary.

Answers

 SELECT FirstName, MAX(ShiftID) FROM Employee WHERE FirstName IN('Kevin', 'Linda', 'Mary')  GROUP BY FirstName 

The IN() function provides the most concise method to match a column to a list of values. The MAX() aggregate is used to return the largest value for a column based on the sort order of the column's data type.

This query returns one row for each name, which represent multiple employees sharing these names. The ShiftID values are 3, 2, and 2 respectively.

Exercise 2

Return a list of ProductSubCategoryID values from the Product table. Include only subcategories that occur more than 20 times. In addition to the ID value, also return the first product name in alphabetical order and the highest price for products in this subcategory.

Answers

 SELECT ProductSubCategoryID, MIN(Name) AS Name, MAX(ListPrice)  FROM Product GROUP BY ProductSubCategoryID HAVING COUNT(ProductSubCategoryID) > 20 ORDER BY Name 

Exercise 3

Produce a list of managers from the Employee table using the ManagerID. For each manager, include the average base pay for all employees of each gender. Also include a row for each manager that includes the average base pay for all employees of that manager. This should be done using only one SELECT expression.

Answers

 SELECT ManagerID, Gender, AVG(BaseRate)  FROM Employee GROUP by ManagerID, Gender WITH ROLLUP