Applied Class 7: DQL Multiple Relation SQL Queries

exercises
tutorial
databases
sql
joins
set-operations

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 and week7-tutorial-applied-class-6-aggregation-and-grouping:

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 distinct role ID, name and descriptions of all roles which permit access to commercial websites (URI ends with .com).

SELECT DISTINCT R.RoleID, R.Name, R.Description
FROM   Role R
JOIN   Permission P ON P.RoleID = R.RoleID
WHERE  P.WebsiteURI LIKE '%.com';

Question 2

Return the First Name, Last Name, Level, Type and Salary of all Administrative Employees.

SELECT FirstName, LastName, Level, Type, Salary
FROM   AdministrativeEmployee AE
JOIN   Employee E ON E.EmployeeID = AE.EmployeeID;

Question 3

For every role, find the number of websites it gives access to. Two columns: RoleId, number of website URIs.

SELECT     R.RoleID, COUNT(DISTINCT WebsiteURI)
FROM       Role R
LEFT JOIN  Permission P ON P.RoleId = R.RoleID
GROUP BY   R.RoleID;

A LEFT JOIN (not an inner join) is needed here so that roles with zero permissions still appear in the result, with a count of 0.

Question 4

For Employee Sofia Gonzalez, return the distinct name and description of the role(s) she was granted, along with the description of the associated permissions.

SELECT DISTINCT R.Name, R.Description, P.Description
FROM   Employee E
JOIN   RoleGranting RG ON E.EmployeeID = RG.EmployeeID
JOIN   Role R ON RG.RoleID = R.RoleID
LEFT JOIN Permission P ON P.RoleID = RG.RoleID
WHERE  E.FirstName = 'Sofia' AND E.LastName = 'Gonzalez';

Question 5

The co-founders of BestTechLtd can be identified as either: (A) Administrative Employees with a 'LegacyEngineer' type; (B) Employees granted the FinancialPerformanceOverview role; or (C) Employees with permission to view* (but not edit) https://grafana.besttechltd.com. Return the EmployeeID, first and last name of all co-founders.*

Restriction: use at least one set operation.

SELECT E.EmployeeID, E.FirstName, E.LastName
FROM   Employee E
JOIN   AdministrativeEmployee AE ON AE.EmployeeID = E.EmployeeID
WHERE  AE.Type = 'LegacyEngineer'
UNION
SELECT E.EmployeeID, E.FirstName, E.LastName
FROM   Employee E
JOIN   RoleGranting RG ON RG.EmployeeID = E.EmployeeID
JOIN   Role R ON R.RoleID = RG.RoleID
WHERE  R.Name = 'FinancialPerformanceOverview'
UNION
SELECT E.EmployeeID, E.FirstName, E.LastName
FROM   Employee E
JOIN   RoleGranting RG ON RG.EmployeeID = E.EmployeeID
JOIN   Permission P ON P.RoleID = RG.RoleID
WHERE  P.WebsiteURI = 'https://grafana.besttechltd.com' AND P.GrantType = 'View';

Three independent conditions, each a join producing the same (EmployeeID, FirstName, LastName) shape, combined with UNION (which also conveniently de-duplicates employees matching more than one condition).

Question 6

Return all employees who have been granted at least two roles, and can edit at least four distinct website URIs.

SELECT   EmployeeID
FROM     RoleGranting RG
GROUP BY EmployeeID
HAVING   COUNT(RoleID) >= 2
INTERSECT
SELECT   EmployeeID
FROM     RoleGranting RG
JOIN     Permission P ON P.RoleID = RG.RoleID
WHERE    P.GrantType = 'Edit'
GROUP BY EmployeeID
HAVING   COUNT(DISTINCT WebsiteURI) >= 4;

INTERSECT combines two independently-grouped-and-filtered employee lists — “granted ≥2 roles” and “can edit ≥4 distinct websites” — rather than trying to express both conditions in one GROUP BY/HAVING (which would conflate the two different counts). Note INTERSECT isn’t implemented in MySQL — see 2026-04-06-aggregation-grouping-and-multiple-relation-queries for how to rewrite it as a self-join.