SQL Joins u0026 Aggregations: Advanced Concepts Quiz Quiz

Challenge your understanding of complex SQL join techniques and aggregation scenarios. This quiz is designed to test deep knowledge of joins, groupings, subqueries, and aggregate functions.

1. Understanding FULL OUTER JOIN

   If Table1 has 10 rows and Table2 has 8 rows with 2 matching IDs, how many rows will the result of a FULL OUTER JOIN on ID contain?

   1. 10
   2. 16
   3. 12
   4. 18
   5. 8

2. Aggregating with GROUP BY and HAVING

   Given a table Sales with columns: id, product, quantity, and price, which query correctly returns products with a total quantity sold greater than 100?

   1. SELECT product, SUM(quantity) FROM Sales HAVING SUM(quantity) u003E 100;
   2. SELECT product FROM Sales GROUP BY product WHERE SUM(quantity) u003E 100;
   3. SELECT product, SUM(quantity) FROM Sales WHERE quantity u003E 100 GROUP BY product;
   4. SELECT product, SUM(quantity) FROM Sales GROUP BY product HAVING SUM(quantity) u003E 100;
   5. SELECT product FROM Sales WHERE SUM(quantity) u003E 100 GROUP BY product;

3. Identifying Correct JOIN Types

   Which join should you use to return all records from Table A and only the matching records from Table B, filling with NULLs when no match is found?

   1. OUTER JOIN
   2. LEFT JOIN
   3. CROSS JOIN
   4. RIGHT JOIN
   5. INNER JOIN

4. Ambiguous Columns in Joins

   When joining Table Employee and Table Department, both containing a column named 'id', how should you refer to 'id' to avoid ambiguity?

   1. Employee-id
   2. id
   3. Empid
   4. Employee.id
   5. Department-id

5. Aggregating After JOINs

   After joining Orders and Customers, how do you count the number of orders for each customer using SQL aggregation functions?

   1. SELECT Customers.id, COUNT(Orders.id) FROM Customers LEFT JOIN Orders ON Customers.id = Orders.customer_id GROUP BY Customers.id;
   2. SELECT Customers.id, SUM(Orders.id) FROM Orders JOIN Customers ON Orders.customer_id = Customers.id;
   3. SELECT COUNT(*) FROM Customers GROUP BY Orders.id;
   4. SELECT Customers.id, COUNT(*) FROM Orders INNER JOIN Customers GROUP BY Orders.id;
   5. SELECT Customers.id, COUNT(Orders.id) FROM Customers GROUP BY Customers.id;

6. Joins vs. Subqueries in Aggregation

   Which approach is generally more efficient for finding the maximum salary per department in a large Employee table?

   1. Nesting subqueries for each department
   2. Self CROSS JOIN with WHERE filters
   3. Joins with UNION ALL
   4. Multiple correlated subqueries in the SELECT clause
   5. Using GROUP BY with aggregation in a single scan

7. Set Operations vs. Joins

   What distinguishes a UNION from an INNER JOIN in SQL?

   1. INNER JOIN produces more rows than UNION always
   2. UNION only works with numeric data, INNER JOIN is data type agnostic
   3. UNION always requires an ON clause
   4. UNION combines rows from result sets, INNER JOIN merges columns from matching rows
   5. UNION removes duplicate rows, INNER JOIN keeps all duplicates

8. JOINs and NULL Values

   If a LEFT JOIN is performed between Students and Scores on student_id, what will be returned for students with no scores?

   1. Only student names are returned
   2. Only students with at least one score
   3. All student details with NULLs in the Scores columns
   4. No students are shown at all
   5. All scores with NULLs in the Students columns

9. GROUP BY with Multiple Columns

   Given a table Orders with columns (order_id, customer_id, status), which SQL snippet correctly counts the number of orders per customer and status combination?

   1. SELECT order_id, COUNT(*) FROM Orders GROUP BY order_id, status;
   2. SELECT customer_id, status, COUNT(*) FROM Orders GROUP BY customer_id, status;
   3. SELECT customer_id, status, SUM(order_id) FROM Orders GROUP BY status;
   4. SELECT customer_id, COUNT(status) FROM Orders GROUP BY customer_id;
   5. SELECT COUNT(*), status FROM Orders GROUP BY customer_id;

10. Self-JOIN Scenario

    To find employees who have the same manager in an Employee table (employee_id, manager_id), which approach is correct?

    1. JOIN on employee_id = manager_id without condition
    2. LEFT JOIN Employee to itself on employee_id = manager_id
    3. Use an INNER JOIN with a UNION on manager_id
    4. Aggregate by employee_id and count manager_id
    5. Perform a self-join on Employee.manager_id, filtering for different employee_ids