1
RPT121I SQL II PRACTICE EXAM Actual
Exam 2026/2027 Complete Questions and
Verified Answers Already Graded A+ Pass
Guaranteed - A+ Graded
Section 1: Advanced Joins & Set Operators
Q1: In Epic Clarity, you need to retrieve all patients who have either an inpatient admission OR
an emergency department visit, but you want to exclude patients who appear in both categories
to avoid duplicate counting in your report. Which set operator combination achieves this?
A. UNION ALL between inpatient and ED tables
B. UNION between inpatient and ED tables
C. INTERSECT between inpatient and ED tables
D. (SELECT PatientID FROM Inpatient) EXCEPT (SELECT PatientID FROM ED) combined
with (SELECT PatientID FROM ED) EXCEPT (SELECT PatientID FROM Inpatient) using
UNION [CORRECT]
Correct Answer: D
Rationale: The requirement describes a symmetric difference (XOR logic) - patients in exactly
one set but not both. Option A (UNION ALL) would include all duplicates. Option B (UNION)
removes duplicates but includes patients present in both. Option C (INTERSECT) returns only
patients in BOTH categories (opposite of requirement). Option D correctly implements
symmetric difference using two EXCEPT operations combined with UNION to get patients
exclusively in one table or the other. In Epic reporting, this pattern is essential when analyzing
mutually exclusive patient cohorts across encounter types.
Q2: You are writing a query in Caboodle to find all providers who have never placed a specific
order type. The Provider table contains 5,000 providers and OrderProcedure contains 2 million
rows. Which query structure is most efficient?
A. SELECT p.ProviderID FROM Provider p LEFT JOIN OrderProcedure op ON p.ProviderID =
op.OrderingProviderID WHERE op.OrderProcedureID IS NULL
B. SELECT p.ProviderID FROM Provider p WHERE p.ProviderID NOT IN (SELECT
OrderingProviderID FROM OrderProcedure WHERE OrderType = 'SpecificType')
,2
C. SELECT p.ProviderID FROM Provider p WHERE NOT EXISTS (SELECT 1 FROM
OrderProcedure op WHERE op.OrderingProviderID = p.ProviderID AND op.OrderType =
'SpecificType') [CORRECT]
D. SELECT p.ProviderID FROM Provider p WHERE p.ProviderID IN (SELECT
OrderingProviderID FROM OrderProcedure WHERE OrderType <> 'SpecificType')
Correct Answer: C
Rationale: Option C using NOT EXISTS is optimal for Epic's large datasets because it short-
circuits on first match and handles NULLs safely. Option A's LEFT JOIN with NULL check is
readable but less efficient with large fact tables like OrderProcedure. Option B's NOT IN fails if
subquery returns NULL (returns empty set) and performs poorly with large subqueries. Option D
is logically incorrect—it returns providers who placed ANY order except the specific type, not
those who never placed it. In Caboodle performance tuning, NOT EXISTS is preferred over
NOT IN for anti-join patterns.
Q3: Given the following tables in Clarity:
• PATIENT (PatientID, Name, DateOfBirth)
• PAT_ENCOUNTER (EncounterID, PatientID, EncounterType, EncounterDate)
Which query returns patients who have had BOTH an 'Inpatient' encounter AND an 'Outpatient'
encounter (at any time)?
A. SELECT DISTINCT p.PatientID FROM PATIENT p JOIN PAT_ENCOUNTER e ON
p.PatientID = e.PatientID WHERE e.EncounterType IN ('Inpatient', 'Outpatient')
B. SELECT p.PatientID FROM PATIENT p JOIN PAT_ENCOUNTER e1 ON p.PatientID =
e1.PatientID JOIN PAT_ENCOUNTER e2 ON p.PatientID = e2.PatientID WHERE
e1.EncounterType = 'Inpatient' AND e2.EncounterType = 'Outpatient' [CORRECT]
C. SELECT p.PatientID FROM PATIENT p WHERE p.PatientID IN (SELECT PatientID
FROM PAT_ENCOUNTER WHERE EncounterType = 'Inpatient' UNION SELECT PatientID
FROM PAT_ENCOUNTER WHERE EncounterType = 'Outpatient')
D. SELECT p.PatientID FROM PATIENT p JOIN PAT_ENCOUNTER e ON p.PatientID =
e.PatientID WHERE e.EncounterType = 'Inpatient' OR e.EncounterType = 'Outpatient' GROUP
BY p.PatientID HAVING COUNT(DISTINCT e.EncounterType) = 1
Correct Answer: B
Rationale: The requirement is for patients with BOTH encounter types (intersection logic), not
EITHER. Option B uses a self-join to ensure both conditions are met simultaneously—one alias
for inpatient, one for outpatient. Option A and D's OR logic/IN list returns patients with either
type. Option C's UNION also returns patients with either type (union is OR logic, not AND). The
, 3
self-join pattern in B is the standard Epic approach for "patient has both X and Y" scenarios in
Clarity reporting.
Q4: In Caboodle, you need to generate a report showing every possible combination of
Departments and Service Lines to ensure all combinations are represented (even those with zero
activity). Which join type is appropriate?
A. Inner Join between Department and ServiceLine tables
B. Left Outer Join from Department to ServiceLine
C. Right Outer Join from Department to ServiceLine
D. CROSS JOIN between Department and ServiceLine tables [CORRECT]
Correct Answer: D
Rationale: A CROSS JOIN produces the Cartesian product—all possible combinations of rows
from both tables. This is exactly what's needed for a "master grid" or "shell report" where you
need every Department-ServiceLine combination, then left join to activity data. Inner Join (A)
only returns matches. Left/Right joins (B, C) preserve one side but don't generate all
combinations. In Epic reporting, CROSS JOINs are commonly used for building reporting
hierarchies or validating data completeness across organizational dimensions.
Q5: You have two result sets from Clarity extracts that need to be combined:
• Set A: 10,000 rows with potential duplicates
• Set B: 5,000 rows with potential duplicates
• Requirement: All rows from both sets, keeping duplicates if they exist within each set but
removing exact duplicates between sets
Which operator should you use?
A. UNION
B. UNION ALL
C. UNION ALL with a subsequent DISTINCT on the final result
D. (SELECT * FROM SetA UNION ALL SELECT * FROM SetB) then filter duplicates
[CORRECT]
Correct Answer: D
Rationale: The requirement specifies keeping intra-set duplicates (duplicates within A or within
B) but removing inter-set duplicates (exact matches between A and B). Standard UNION (A)
removes ALL duplicates. UNION ALL (B) keeps everything including inter-set duplicates.
Option C is vague. Option D correctly describes using UNION ALL to preserve intra-set
RPT121I SQL II PRACTICE EXAM Actual
Exam 2026/2027 Complete Questions and
Verified Answers Already Graded A+ Pass
Guaranteed - A+ Graded
Section 1: Advanced Joins & Set Operators
Q1: In Epic Clarity, you need to retrieve all patients who have either an inpatient admission OR
an emergency department visit, but you want to exclude patients who appear in both categories
to avoid duplicate counting in your report. Which set operator combination achieves this?
A. UNION ALL between inpatient and ED tables
B. UNION between inpatient and ED tables
C. INTERSECT between inpatient and ED tables
D. (SELECT PatientID FROM Inpatient) EXCEPT (SELECT PatientID FROM ED) combined
with (SELECT PatientID FROM ED) EXCEPT (SELECT PatientID FROM Inpatient) using
UNION [CORRECT]
Correct Answer: D
Rationale: The requirement describes a symmetric difference (XOR logic) - patients in exactly
one set but not both. Option A (UNION ALL) would include all duplicates. Option B (UNION)
removes duplicates but includes patients present in both. Option C (INTERSECT) returns only
patients in BOTH categories (opposite of requirement). Option D correctly implements
symmetric difference using two EXCEPT operations combined with UNION to get patients
exclusively in one table or the other. In Epic reporting, this pattern is essential when analyzing
mutually exclusive patient cohorts across encounter types.
Q2: You are writing a query in Caboodle to find all providers who have never placed a specific
order type. The Provider table contains 5,000 providers and OrderProcedure contains 2 million
rows. Which query structure is most efficient?
A. SELECT p.ProviderID FROM Provider p LEFT JOIN OrderProcedure op ON p.ProviderID =
op.OrderingProviderID WHERE op.OrderProcedureID IS NULL
B. SELECT p.ProviderID FROM Provider p WHERE p.ProviderID NOT IN (SELECT
OrderingProviderID FROM OrderProcedure WHERE OrderType = 'SpecificType')
,2
C. SELECT p.ProviderID FROM Provider p WHERE NOT EXISTS (SELECT 1 FROM
OrderProcedure op WHERE op.OrderingProviderID = p.ProviderID AND op.OrderType =
'SpecificType') [CORRECT]
D. SELECT p.ProviderID FROM Provider p WHERE p.ProviderID IN (SELECT
OrderingProviderID FROM OrderProcedure WHERE OrderType <> 'SpecificType')
Correct Answer: C
Rationale: Option C using NOT EXISTS is optimal for Epic's large datasets because it short-
circuits on first match and handles NULLs safely. Option A's LEFT JOIN with NULL check is
readable but less efficient with large fact tables like OrderProcedure. Option B's NOT IN fails if
subquery returns NULL (returns empty set) and performs poorly with large subqueries. Option D
is logically incorrect—it returns providers who placed ANY order except the specific type, not
those who never placed it. In Caboodle performance tuning, NOT EXISTS is preferred over
NOT IN for anti-join patterns.
Q3: Given the following tables in Clarity:
• PATIENT (PatientID, Name, DateOfBirth)
• PAT_ENCOUNTER (EncounterID, PatientID, EncounterType, EncounterDate)
Which query returns patients who have had BOTH an 'Inpatient' encounter AND an 'Outpatient'
encounter (at any time)?
A. SELECT DISTINCT p.PatientID FROM PATIENT p JOIN PAT_ENCOUNTER e ON
p.PatientID = e.PatientID WHERE e.EncounterType IN ('Inpatient', 'Outpatient')
B. SELECT p.PatientID FROM PATIENT p JOIN PAT_ENCOUNTER e1 ON p.PatientID =
e1.PatientID JOIN PAT_ENCOUNTER e2 ON p.PatientID = e2.PatientID WHERE
e1.EncounterType = 'Inpatient' AND e2.EncounterType = 'Outpatient' [CORRECT]
C. SELECT p.PatientID FROM PATIENT p WHERE p.PatientID IN (SELECT PatientID
FROM PAT_ENCOUNTER WHERE EncounterType = 'Inpatient' UNION SELECT PatientID
FROM PAT_ENCOUNTER WHERE EncounterType = 'Outpatient')
D. SELECT p.PatientID FROM PATIENT p JOIN PAT_ENCOUNTER e ON p.PatientID =
e.PatientID WHERE e.EncounterType = 'Inpatient' OR e.EncounterType = 'Outpatient' GROUP
BY p.PatientID HAVING COUNT(DISTINCT e.EncounterType) = 1
Correct Answer: B
Rationale: The requirement is for patients with BOTH encounter types (intersection logic), not
EITHER. Option B uses a self-join to ensure both conditions are met simultaneously—one alias
for inpatient, one for outpatient. Option A and D's OR logic/IN list returns patients with either
type. Option C's UNION also returns patients with either type (union is OR logic, not AND). The
, 3
self-join pattern in B is the standard Epic approach for "patient has both X and Y" scenarios in
Clarity reporting.
Q4: In Caboodle, you need to generate a report showing every possible combination of
Departments and Service Lines to ensure all combinations are represented (even those with zero
activity). Which join type is appropriate?
A. Inner Join between Department and ServiceLine tables
B. Left Outer Join from Department to ServiceLine
C. Right Outer Join from Department to ServiceLine
D. CROSS JOIN between Department and ServiceLine tables [CORRECT]
Correct Answer: D
Rationale: A CROSS JOIN produces the Cartesian product—all possible combinations of rows
from both tables. This is exactly what's needed for a "master grid" or "shell report" where you
need every Department-ServiceLine combination, then left join to activity data. Inner Join (A)
only returns matches. Left/Right joins (B, C) preserve one side but don't generate all
combinations. In Epic reporting, CROSS JOINs are commonly used for building reporting
hierarchies or validating data completeness across organizational dimensions.
Q5: You have two result sets from Clarity extracts that need to be combined:
• Set A: 10,000 rows with potential duplicates
• Set B: 5,000 rows with potential duplicates
• Requirement: All rows from both sets, keeping duplicates if they exist within each set but
removing exact duplicates between sets
Which operator should you use?
A. UNION
B. UNION ALL
C. UNION ALL with a subsequent DISTINCT on the final result
D. (SELECT * FROM SetA UNION ALL SELECT * FROM SetB) then filter duplicates
[CORRECT]
Correct Answer: D
Rationale: The requirement specifies keeping intra-set duplicates (duplicates within A or within
B) but removing inter-set duplicates (exact matches between A and B). Standard UNION (A)
removes ALL duplicates. UNION ALL (B) keeps everything including inter-set duplicates.
Option C is vague. Option D correctly describes using UNION ALL to preserve intra-set
