Higher DDD - Aggregate Functions, GROUP BY

• We are learning how to implement SELECT FROM, UPDATE, DELETE FROM and INSERT INTO queries.  

• We are learning how to use aggregate functions to return a value from a row in a database entity.  

• We are learning how the GROUP BY clause to group records together in a set of query results.  

• We are learning how to implement sub-queries   

Aims

• I can design a SQL query making use of the AVG, SUM, COUNT, MIN and MAX aggregate functions.  

• I can design a SQL query making use of the GROUP BY clause to combine records as a summary.  

Achievements (theory)

• I can implement SELECT FROM, UPDATE, DELETE FROM and INSERT INTO queries.  

• I can implement a SQL query making use of the AVG, SUM, COUNT, MIN and MAX aggregate functions.  

• I can implement a SQL query making use of the GROUP BY clause to combine records as a summary.  

• I can implement sub-queries in a database.  

Achievements (practical)

Multiple Choice

In the following database which aggregate function would you use to find the employee with the longest service?

• AVERAGE

• SUM

• COUNT

• MAX

• MIN

Multiple Choice

In the following database which aggregate function would you use to calculate the total number of years served by all employees?

• AVERAGE

• SUM

• COUNT

• MAX

• MIN

Multiple Choice

In the following database which aggregate function would you use to find the number of employees who work in building 1e?

• AVERAGE

• SUM

• COUNT

• MAX

• MIN

Multiple Choice

In the following database which aggregate function would you use to find the employee with the shortest service?

• AVERAGE

• SUM

• COUNT

• MAX

• MIN

Fill in the Blank

Perfect Eyes is an optician that has branches throughout Scotland. It uses a relational database consisting of three linked tables to store data about customers, opticians and specialist referrals. Extracts from the three tables are shown below. 

Average Points

38.4

(2 marks)

(

)

[

]

Multiple Select

Which of the fields in this database could be used in a GROUP BY clause. Select all that apply.

• employeeID

• jobTitle

• employeeName

• building

• yearsEmployed