Essential SQL Joins and Aggregations Quiz Quiz

Test your understanding of SQL joins, GROUP BY, and HAVING clauses with these beginner-friendly questions. Learn to identify correct join types and aggregation usage in common SQL scenarios.

1. Basic INNER JOIN Usage

   Which SQL keyword pair combines rows from two tables only when there is a match in both tables, such as linking 'students' and 'enrollments' by 'student_id'?

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

   Explanation: INNER JOIN returns rows that have matching values in both tables, making it perfect for combining students and enrollments that share a student_id. LEFT JOIN and RIGHT JOIN also combine rows but may include non-matching rows from one side. OUTER JOIN is not a specific SQL keyword; rather, it's a broad term sometimes informally used for left and right joins.

2. LEFT JOIN Concept

   If you want to select all customers and their orders, showing customers even if they have no orders, which join type should you use?

   1. FULL JOIN
   2. INNER JOIN
   3. RIGHT JOIN
   4. LEFT JOIN

   Explanation: LEFT JOIN includes all rows from the left table (customers) and the matching rows from the right table (orders), filling NULLs where there’s no match. INNER JOIN would exclude customers without orders. RIGHT JOIN would include all orders, which is not the focus. FULL JOIN is not standard in all databases and returns all rows from both tables.

3. GROUP BY Purpose

   What is the primary purpose of the GROUP BY clause in a SQL SELECT statement using a 'sales' table?

   1. To update all rows in a group
   2. To collect rows into groups for aggregation
   3. To order rows alphabetically
   4. To filter rows before joining

   Explanation: GROUP BY is used to collect rows that have the same values in specified columns into summary rows, such as sums or counts. It doesn't filter rows before joins; that's done with WHERE. Ordering rows is done with ORDER BY. UPDATE is a separate SQL command not related to aggregation.

4. COUNT with GROUP BY

   Which query counts the number of orders placed by each customer in an 'orders' table?

   1. SELECT COUNT(customer_id) FROM orders;
   2. SELECT customer_id, SUM(*) FROM orders;
   3. SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;
   4. SELECT customer_id, COUNT(*) FROM orders;

   Explanation: The correct query groups orders by customer_id and counts them, showing how many each customer made. SELECT COUNT(customer_id) would return only a total count, not broken by customer. SUM is not suitable for counting rows. The last option lacks GROUP BY, so it wouldn't aggregate per customer.

5. HAVING vs WHERE

   When filtering aggregate results such as totals after a GROUP BY, which clause is appropriate to use in SQL?

   1. WHERE
   2. ORDER BY
   3. JOIN
   4. HAVING

   Explanation: HAVING filters groups created by GROUP BY and is evaluated after aggregation, making it suitable for aggregate filters. WHERE filters rows before grouping but cannot reference aggregate functions. ORDER BY is for sorting results, and JOIN is for combining tables.

6. Handling NULL Values

   If you JOIN two tables and some rows have no matches, what value does SQL show for columns from the non-matching table?

   1. ZERO
   2. BLANK
   3. NULL
   4. DEFAULT

   Explanation: SQL displays NULL for columns from the non-matching table in JOINs like LEFT or RIGHT JOIN when there's no corresponding row to join. ZERO is not automatically used unless specified. BLANK is not a data type; it’s a visual description. DEFAULT values are only used if defined for missing values, but not in JOINs by default.

7. Simple Aggregation Function

   To find the highest salary in an 'employees' table, which aggregation function should you use?

   1. SUM
   2. MAX
   3. COUNT
   4. AVG

   Explanation: The MAX function returns the highest value in a column, such as the highest salary. SUM adds up all values, AVG calculates the mean, and COUNT tallies the number of rows, so these do not identify the single highest value.

8. Column Reference in GROUP BY

   If you GROUP BY 'department' in a 'staff' table, which other column(s) can you select directly without aggregation?

   1. 'salary' without aggregation
   2. 'department' and columns with aggregate functions
   3. Any column
   4. Only 'department'

   Explanation: In SQL, after GROUP BY, only grouped columns and columns with aggregation (like SUM or AVG) can be selected directly. Selecting any column, or 'salary' without aggregation, would cause an error. Only 'department' is too restrictive because aggregates are also allowed.

9. Selecting All with JOIN

   If you write SELECT * FROM tableA INNER JOIN tableB ON tableA.id = tableB.id, what do you get in the result?

   1. Only columns from tableA
   2. All columns from both tables for matching rows
   3. Only columns from tableB
   4. All rows from both tables, even non-matching

   Explanation: SELECT * with INNER JOIN returns all columns from both tables but only for rows where the join condition matches. You do not get non-matching rows; that's the job of OUTER or FULL joins. Restricting to one table’s columns is not what SELECT * achieves.

10. Using GROUP BY with Multiple Columns

    If you use GROUP BY on two columns, like 'city' and 'store_type', what is the effect in a 'stores' table?

    1. Results are not grouped and show duplicates
    2. Results are grouped for each unique city and store_type combination
    3. Results show only unique cities
    4. Results show only unique store_types

    Explanation: GROUP BY with multiple columns groups data for every unique combination of the listed columns, like each city and each type of store. Grouping by only one column would not combine both. Not grouping would show all duplicates. Selecting only unique cities or store_types is more restrictive and misses the paired combinations.