SQL (II): Joins, Subqueries & Relational Queries
Practice intermediate and advanced relational queries. Each exercise validates your query against dual datasets to verify correctness and sorting.
2. Practice Questions
Question 1: Aggregation & Sorting
Inner JoinFor each project that has assignments, list the project code (proj_code), project name (proj_name), the total hours logged across all its assignments, and the number of times it has been assigned. You must order the result by total hours logged in descending order.
💡 Hint (No solution)
Perform an INNER JOIN between Projects and ProjectAssignments using proj_id. Group by the project key and attributes. Apply SUM() to aggregate hours_logged and COUNT(*) for total assignment records. Don't forget ORDER BY ... DESC.
Question 2: Finding Unmatched Records
Outer Join / Null CheckList all projects (all columns of Projects) that have never been assigned to any engineer.
💡 Hint (No solution)
You can use a LEFT JOIN from Projects to ProjectAssignments and check where the child table's foreign key IS NULL, or use a subquery with WHERE proj_id NOT IN (...).
Question 3: Multi-Table Filter
Distinct JoinList distinct project codes and project names that had active assignments during the year 2026.
💡 Hint (No solution)
Join Projects with ProjectAssignments on proj_id. Filter with WHERE assignment_year = 2026. Since multiple engineers may work on the same project in 2026, eliminate duplicate output rows with DISTINCT.
Question 4: Subquery Criteria
Subquery with INList all columns of projects that had any single assignment where hours_logged strictly exceeded 60 hours. Use a subquery. (Note: Projects with high cumulative hours whose individual assignments are all 60 or less must NOT be included).
💡 Hint (No solution)
Construct a subquery that selects proj_id from ProjectAssignments where hours_logged > 60. Do not aggregate with SUM(); the question requires a single assignment exceeding 60 hours.
Question 5: Filtered Subquery with HAVING
Subquery & HAVINGList the project ID, project code, and project name of all projects that have been assigned 2 or more times. Implement this using an IN subquery with a HAVING clause.
💡 Hint (No solution)
In your inner subquery, group ProjectAssignments by proj_id and filter groups using HAVING COUNT(*) >= 2. The outer query then selects details from Projects matching those IDs.
Question 6: Preserving Missing Rows with Outer Joins
Left Join & Group ByList all engineers (showing eng_id and name) and their total number of project assignments. Engineers with zero assignments must appear with a count of 0. (Row ordering is not graded).
💡 Hint (No solution)
Use a LEFT JOIN starting from Engineers. Remember: COUNT(*) on an unmatched left-joined row evaluates to 1 because the row exists; use COUNT(pa.proj_id) to count foreign keys, which properly yields 0 for NULL.
Question 7: Distinct Counting across Relations
HAVING & Count DistinctList the engineer ID and name of all engineers who have worked on 2 or more different projects. (Note: An engineer assigned to the same project across multiple years has only worked on 1 distinct project).
💡 Hint (No solution)
Join Engineers with ProjectAssignments, group by the engineer, and evaluate COUNT(DISTINCT pa.proj_id) >= 2 in the HAVING clause. Using plain COUNT(*) will incorrectly count repeat assignments to the same project.
Question 8: Three-Table Join with Numeric Average
3-Table Join & AVGFor each department that has active project assignments, list the department name (dept_name) and the average hours_logged per assignment for all engineers in that department.
💡 Hint (No solution)
Join Departments to Engineers on dept_id, and then to ProjectAssignments on eng_id. Group by d.dept_id, d.dept_name and apply AVG(pa.hours_logged). In SQLite, AVG() automatically computes floating-point averages.
Question 9: Self-Join for Intra-Table Comparisons
Self JoinFind all pairs of engineers where the approximate age difference by birth year is strictly greater than 20 years (birth_year difference > 20). Display the older engineer's name, the younger engineer's name, and their birth year difference. You must order the result by the birth year difference in descending order. (Note: Pairs exactly 20 years apart must NOT appear).
💡 Hint (No solution)
Join Engineers to itself using aliases (e.g. o for older and y for younger). An older engineer has a smaller birth year. The join predicate should ensure (y.birth_year - o.birth_year) > 20. Remember ORDER BY ... DESC.
Question 10: Multi-Table Column Comparison
Cross-Column FilterList distinct engineer names and project names for all assignments where the assigned project requires strictly more site_hours than office_hours.
💡 Hint (No solution)
Join Engineers, ProjectAssignments, and Projects. In your WHERE clause, directly compare the project columns: p.site_hours > p.office_hours. Use DISTINCT to eliminate duplicate pairs.
3. Certificate of Completion
🔒 Complete all 10 questions above to unlock your completion certificate.