Applied Class 6: DQL Aggregation and Grouping

exercises
tutorial
databases
sql
aggregation
grouping

Practice for 2026-04-06-aggregation-grouping-and-multiple-relation-queries. Same BestTechLtd schema as week6-tutorial-applied-class-5-basic-sql-ddl-and-dml.

Employee              [EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt]
AdministrativeEmployee [EmployeeID, Level, Type]
Role                   [RoleID, Name, Description]
RoleGranting           [EmployeeID, RoleID, AdministrationID, Timestamp]
Permission             [WebsiteURI, RoleID, GrantType, Description]

AdministrativeEmployee.EmployeeID references Employee.EmployeeID
RoleGranting.EmployeeID references Employee.EmployeeID
RoleGranting.RoleID references Role.RoleID
RoleGranting.AdministrationID references AdministrativeEmployee.EmployeeID
Permission.RoleID references Role.RoleID

Question 1

Return the youngest Employee’s Date of Birth.

SELECT MAX(DOB)
FROM   Employee;

The youngest employee has the largest (most recent) DOB — a common trap is reaching for MIN by reflex.

Question 2

For each website URI, find the number of roles that can access it. Two columns: the website URI, and the number of roles.

SELECT   WebsiteURI, COUNT(DISTINCT RoleID)
FROM     Permission
GROUP BY WebsiteURI;

Question 3

Return the EmployeeId of all employees who have been granted more than one role.

SELECT   EmployeeID
FROM     RoleGranting
GROUP BY EmployeeID
HAVING   COUNT(RoleID) > 1;

Question 4

Return the highest AdministrativeEmployee level, the lowest level, and the difference between both, aliased DifferenceInAdministrativeEmployeeLevels.

SELECT MAX(Level), MIN(Level),
       (MAX(Level) - MIN(Level)) AS DifferenceInAdministrativeEmployeeLevels
FROM   AdministrativeEmployee;

Question 5 — Discuss

(1) What do you observe using an aggregation function without GROUP BY? Why? (2) What do you observe aggregating (GROUP BY) over a primary key or unique field? Why?

  1. Observation: the aggregate operates over the entire table and returns a single row/value. Reason: with no GROUP BY, the whole result set is treated as one big group.
  2. Observation: the number of rows returned equals the number of rows in the table, and most aggregate functions return values identical to the ungrouped row. Reason: a primary key/unique field is unique and non-null per row, so every “group” contains exactly one tuple.

Question 6

Return the average salary of Employees born after 1 January 1990.

SELECT AVG(Salary)
FROM   Employee
WHERE  DOB > '1990-01-01';

Question 7

Return the roleID of all roles which have at least two associated website URIs with a grantType of 'Edit'.

SELECT   RoleId
FROM     Permission
WHERE    GrantType = 'Edit'
GROUP BY RoleId
HAVING   COUNT(WebsiteURI) >= 2;

Question 8 (Challenge)

Return all last names that are shared by multiple employees.

SELECT   DISTINCT LastName
FROM     Employee
WHERE    LastName IS NOT NULL
GROUP BY LastName
HAVING   COUNT(FirstName) > 1;