1
D427 DATA MANAGEMENT – APPLICATIONS
PRACTICE EXAM | New Written Solution 2026/2027
Domain 1: Advanced SQL Queries (JOINs, Subqueries, Aggregates)
1. You are writing a query to retrieve customer names and the total amount of their
orders. You need to include customers who have placed zero orders. Which JOIN
clause should you use?
A) INNER JOIN
B) LEFT JOIN
C) RIGHT JOIN
D) FULL OUTER JOIN
[CORRECT] B
Rationale:
A LEFT JOIN returns all rows from the left table (Customers) and matched rows
from the right table (Orders). If there is no match (i.e., a customer has placed no
orders), the result is NULL on the right side. An INNER JOIN would exclude
customers with no orders. A RIGHT JOIN would prioritize the Orders table, and a
FULL OUTER JOIN would return everything, including customers with no orders and
orders with no valid customer (which shouldn't exist in a clean schema).
, 2
2. Write a SQL query to list the EmployeeID and EmployeeName from the
Employees table and the OrderDate from the Orders table. Include only employees
who have successfully processed at least one order.
(Free Response)
[CORRECT]
sql
SELECT e.EmployeeID, e.EmployeeName, o.OrderDate
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID;
Rationale:
The question asks for employees who have processed orders, which implies an
intersection of data present in both tables. The INNER JOIN keyword is required to
return rows where there is a match in both tables based on the EmployeeID. The
aliasing (e and o) is used for readability but not strictly required if fully qualified
names are used.
3. Consider the following query:
, 3
sql
SELECT P.ProductName, S.SupplierName
FROM Products P
RIGHT JOIN Suppliers S ON P.SupplierID = S.SupplierID;
Which scenario describes a row that will appear in the result set with a NULL value
in the ProductName column?
A) A supplier that supplies no products.
B) A product that has no supplier assigned.
C) A product supplied by multiple suppliers.
D) A supplier that has been deleted.
[CORRECT] A
Rationale:
In a RIGHT JOIN, all rows from the right table (Suppliers) are returned. If there is no
matching row in the left table (Products) based on the SupplierID, the columns from
the left table (like ProductName) will be NULL. This correctly identifies suppliers
who exist in the database but have not supplied any products yet.
, 4
4. Write a SQL query using a Self Join to find the names of all employees and the
names of their managers. The table name is Staff, and columns are StaffID,
StaffName, and ManagerID.
(Free Response)
[CORRECT]
sql
SELECT
E.StaffName AS Employee,
M.StaffName AS Manager
FROM Staff E
LEFT JOIN Staff M ON E.ManagerID = M.StaffID;
Rationale:
A Self Join is required to join a table to itself. We treat the table as two separate
instances: one for the Employee (E) and one for the Manager (M). A LEFT JOIN is
used instead of an INNER JOIN to ensure that top-level employees (who might have
a NULL ManagerID) are still included in the list, rather than filtered out.
D427 DATA MANAGEMENT – APPLICATIONS
PRACTICE EXAM | New Written Solution 2026/2027
Domain 1: Advanced SQL Queries (JOINs, Subqueries, Aggregates)
1. You are writing a query to retrieve customer names and the total amount of their
orders. You need to include customers who have placed zero orders. Which JOIN
clause should you use?
A) INNER JOIN
B) LEFT JOIN
C) RIGHT JOIN
D) FULL OUTER JOIN
[CORRECT] B
Rationale:
A LEFT JOIN returns all rows from the left table (Customers) and matched rows
from the right table (Orders). If there is no match (i.e., a customer has placed no
orders), the result is NULL on the right side. An INNER JOIN would exclude
customers with no orders. A RIGHT JOIN would prioritize the Orders table, and a
FULL OUTER JOIN would return everything, including customers with no orders and
orders with no valid customer (which shouldn't exist in a clean schema).
, 2
2. Write a SQL query to list the EmployeeID and EmployeeName from the
Employees table and the OrderDate from the Orders table. Include only employees
who have successfully processed at least one order.
(Free Response)
[CORRECT]
sql
SELECT e.EmployeeID, e.EmployeeName, o.OrderDate
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID;
Rationale:
The question asks for employees who have processed orders, which implies an
intersection of data present in both tables. The INNER JOIN keyword is required to
return rows where there is a match in both tables based on the EmployeeID. The
aliasing (e and o) is used for readability but not strictly required if fully qualified
names are used.
3. Consider the following query:
, 3
sql
SELECT P.ProductName, S.SupplierName
FROM Products P
RIGHT JOIN Suppliers S ON P.SupplierID = S.SupplierID;
Which scenario describes a row that will appear in the result set with a NULL value
in the ProductName column?
A) A supplier that supplies no products.
B) A product that has no supplier assigned.
C) A product supplied by multiple suppliers.
D) A supplier that has been deleted.
[CORRECT] A
Rationale:
In a RIGHT JOIN, all rows from the right table (Suppliers) are returned. If there is no
matching row in the left table (Products) based on the SupplierID, the columns from
the left table (like ProductName) will be NULL. This correctly identifies suppliers
who exist in the database but have not supplied any products yet.
, 4
4. Write a SQL query using a Self Join to find the names of all employees and the
names of their managers. The table name is Staff, and columns are StaffID,
StaffName, and ManagerID.
(Free Response)
[CORRECT]
sql
SELECT
E.StaffName AS Employee,
M.StaffName AS Manager
FROM Staff E
LEFT JOIN Staff M ON E.ManagerID = M.StaffID;
Rationale:
A Self Join is required to join a table to itself. We treat the table as two separate
instances: one for the Employee (E) and one for the Manager (M). A LEFT JOIN is
used instead of an INNER JOIN to ensure that top-level employees (who might have
a NULL ManagerID) are still included in the list, rather than filtered out.
