D427 Data Management Applications Practice Exam 2026/2027 New Written Solution Questions and Answers

1



D427 DATA MANAGEMENT – APPLICATIONS
COMPREHENSIVE PRACTICE EXAM 2026/2027.
DOMAIN 1: ADVANCED SQL QUERIES .

Question 1 (Multiple-Choice)

A retail company has three tables: Customers (CustomerID, CustomerName, Email), Orders
(OrderID, CustomerID, OrderDate, TotalAmount), and OrderDetails (OrderDetailID, OrderID,
ProductID, Quantity, UnitPrice). A data analyst needs to generate a report showing all
customers and their total order amounts, including customers who have never placed an
order. Which JOIN combination should be used?

A) INNER JOIN Customers ON Orders, then INNER JOIN OrderDetails

B) LEFT JOIN Orders ON Customers, then INNER JOIN OrderDetails

C) RIGHT JOIN Orders ON Customers, then LEFT JOIN OrderDetails

D) LEFT JOIN Orders ON Customers, then LEFT JOIN OrderDetails

Answer: D) LEFT JOIN Orders ON Customers, then LEFT JOIN OrderDetails [CORRECT]

Rationale: A LEFT JOIN from Customers to Orders ensures all customers appear in the result,
including those with no orders (NULL values for order fields). The second LEFT JOIN to
OrderDetails preserves all orders (and customers with no orders) while including order line
details where they exist. INNER JOIN would eliminate customers without orders, and RIGHT
JOIN would incorrectly prioritize the Orders table.

Question 2 (Write the SQL Query)

Using the following schema, write a SQL query to retrieve the employee name, department
name, and project name for all employees who are assigned to projects. Include employees
even if they are not in a department.

Tables:

Employees (EmployeeID, EmployeeName, DepartmentID, HireDate)

Departments (DepartmentID, DepartmentName, Location)

, 2



EmployeeProjects (EmployeeID, ProjectID, HoursAllocated)

Projects (ProjectID, ProjectName, StartDate, EndDate)

Answer:

sql

Copy

SELECT

E.EmployeeName,

D.DepartmentName,

P.ProjectName

FROM Employees E

LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID

INNER JOIN EmployeeProjects EP ON E.EmployeeID = EP.EmployeeID

INNER JOIN Projects P ON EP.ProjectID = P.ProjectID;

[CORRECT]

Rationale: The query uses a LEFT JOIN between Employees and Departments to include all
employees regardless of department assignment (preserving employees with NULL
DepartmentID). INNER JOINs are used for EmployeeProjects and Projects because we only
want employees who are actually assigned to projects. The join order ensures referential
integrity is maintained while including all relevant employees.

Question 3 (Multiple-Choice)

Consider the query: SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.ID =
TableB.ID;. Which statement accurately describes the result set?

A) Returns only rows where TableA.ID matches TableB.ID

B) Returns all rows from TableA and only matching rows from TableB

C) Returns all rows from both TableA and TableB, with NULLs where no match exists

, 3



D) Returns all rows from TableB and only matching rows from TableA

Answer: C) Returns all rows from both TableA and TableB, with NULLs where no match
exists [CORRECT]

Rationale: A FULL OUTER JOIN returns all records when there is a match in either TableA or
TableB. For rows where there is no match, the result set contains NULL values for every
column of the table that lacks a match. This is distinct from INNER JOIN (only matches),
LEFT JOIN (all left table rows), and RIGHT JOIN (all right table rows).

Question 4 (Multiple-Choice)

A university database has Students (StudentID, StudentName, MajorID) and Majors
(MajorID, MajorName, DepartmentID). A report needs to show all majors and the number of
students in each major, including majors with zero enrolled students. Which query correctly
accomplishes this?

A) SELECT MajorName, COUNT(*) FROM Majors M INNER JOIN Students S ON M.MajorID =
S.MajorID GROUP BY MajorName;

B) SELECT MajorName, COUNT(S.StudentID) FROM Majors M LEFT JOIN Students S ON
M.MajorID = S.MajorID GROUP BY MajorName;

C) SELECT MajorName, COUNT(*) FROM Majors M RIGHT JOIN Students S ON M.MajorID =
S.MajorID GROUP BY MajorName;

D) SELECT MajorName, COUNT(S.StudentID) FROM Students S LEFT JOIN Majors M ON
S.MajorID = M.MajorID GROUP BY MajorName;

Answer: B) SELECT MajorName, COUNT(S.StudentID) FROM Majors M LEFT JOIN Students S
ON M.MajorID = S.MajorID GROUP BY MajorName; [CORRECT]

Rationale: The LEFT JOIN from Majors to Students ensures all majors appear in the result,
including those with no students (COUNT would return 0 for these). Using
COUNT(S.StudentID) instead of COUNT(*) is critical because COUNT(*) counts rows,
including the row with NULL student data for majors with no students, resulting in 1 instead
of 0. The query correctly starts from Majors (the table we want to preserve entirely).

Question 5 (Select-All-That-Apply)

, 4



Which of the following statements about SQL JOINs are TRUE? (Select all that apply)

A) An INNER JOIN returns only rows where there is a match in both tables

B) A LEFT JOIN returns all rows from the left table and matching rows from the right table

C) A RIGHT JOIN returns all rows from both tables, filling NULLs where there is no match

D) A FULL OUTER JOIN returns all rows from both tables, with NULLs where no match exists

E) A CROSS JOIN returns the Cartesian product of both tables

Answers: A, B, D, E [CORRECT]

Rationale:

A is correct: INNER JOIN returns only rows where the join condition is satisfied in both
tables.

B is correct: LEFT JOIN preserves all rows from the left table, with NULLs for non-matching
right table rows.

C is incorrect: A RIGHT JOIN returns all rows from the right table and matching rows from
the left table, not both tables. FULL OUTER JOIN returns all rows from both.

D is correct: FULL OUTER JOIN returns all rows from both tables, with NULLs filling in where
there is no match on either side.

E is correct: CROSS JOIN produces the Cartesian product, combining every row from the first
table with every row from the second table.

Sub-Topic 1.2: Subqueries (4 Questions)

Question 6 (Multiple-Choice)

Which of the following queries correctly uses a correlated subquery to find employees
whose salary is above the average salary of their own department?

A) SELECT EmployeeName, Salary FROM Employees E WHERE Salary > (SELECT AVG(Salary)
FROM Employees WHERE DepartmentID = E.DepartmentID);

B) SELECT EmployeeName, Salary FROM Employees WHERE Salary > (SELECT AVG(Salary)
FROM Employees GROUP BY DepartmentID);