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.

Initializing SQLite WASM Database...

2. Practice Questions

Question 1: Aggregation & Sorting

Inner Join

For 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 Check

List 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 Join

List 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 IN

List 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 & HAVING

List 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 By

List 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 Distinct

List 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 & AVG

For 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 Join

Find 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 Filter

List 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.