SQL Joins and Aggregations Fundamentals Quiz Quiz

Test your knowledge of SQL joins and aggregation techniques with beginner-friendly questions on INNER JOIN, LEFT JOIN, GROUP BY, and HAVING. This quiz is perfect for anyone seeking to review or strengthen their SQL query skills, especially with joins and group-based calculations.

1. Basic INNER JOIN usage

   Which SQL clause combines rows from two tables only when there is a matching value in both tables?

   1. OUTER MATCH
   2. FULL JOIN
   3. INNER JOIN
   4. LEFT JOIN

   Explanation: INNER JOIN returns only the rows where there is a match in both tables, making it the standard join for intersecting data. LEFT JOIN includes unmatched rows from the left table, FULL JOIN returns all rows when there is a match in one of the tables, and OUTER MATCH is not a valid SQL clause.

2. LEFT JOIN result set

   When using a LEFT JOIN between tables 'Customers' and 'Orders', which describes the returned data?

   1. All customers, with order data if available
   2. Only orders placed on Mondays
   3. All orders, with customer data if available
   4. Only customers with orders

   Explanation: LEFT JOIN returns all records from the left table—in this case, Customers—even if there is no matching row in Orders. 'Only customers with orders' would describe INNER JOIN, 'All orders, with customer data if available' is a RIGHT JOIN scenario, and 'Only orders placed on Mondays' is irrelevant here.

3. GROUP BY purpose

   In a SQL query, what does the GROUP BY clause accomplish?

   1. Filters rows after aggregation
   2. Deletes duplicates from a result set
   3. Groups rows sharing a value for specified columns
   4. Joins tables based on a key

   Explanation: The GROUP BY clause organizes rows into groups based on the values of specified columns, allowing for aggregate calculations per group. Filtering after aggregation is handled by HAVING, not GROUP BY. Joining tables is done by JOIN clauses, and duplicates are removed with DISTINCT.

4. Using HAVING with aggregates

   Which SQL clause should you use to filter the results of a GROUP BY operation based on aggregated values?

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

   Explanation: The HAVING clause filters groups created by GROUP BY, typically using aggregate functions like COUNT or SUM. WHERE filters individual rows before aggregation, ORDER BY sorts the results, and WITH defines common table expressions.

5. INNER JOIN multiple tables

   How can you combine three tables with INNER JOIN so that only rows with matching keys in all tables are shown?

   1. Use only LEFT JOIN clauses
   2. List tables separated by commas
   3. Chain multiple INNER JOIN clauses
   4. Apply GROUP BY on all columns

   Explanation: Chaining INNER JOIN clauses connects all three tables, ensuring only rows present in all are included. LEFT JOIN would retain all rows from the first table, GROUP BY does not perform joining, and listing tables with commas is obsolete, error-prone syntax.

6. Aggregate function example

   Which SQL function would you use to count the number of rows in each group after a GROUP BY?

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

   Explanation: COUNT returns the number of rows in each group, making it ideal for quantifying group size. AVG computes averages, MAX returns the highest value, and RANK is not an aggregate function.

7. LEFT JOIN with missing matches

   What value is returned for the right table's columns in a LEFT JOIN when there is no matching row?

   1. N/A
   2. 0
   3. Empty string
   4. NULL

   Explanation: When there is no match in the right table, LEFT JOIN produces NULL for those columns. Zero and empty string are valid values but not defaults, and N/A is not a standard SQL value.

8. Filtering with WHERE vs HAVING

   If you want to filter sales rows before grouping by region, which SQL clause should you use?

   1. GROUP FILTER
   2. WHERE
   3. WHEN
   4. HAVING

   Explanation: WHERE filters records before the GROUP BY groups them. HAVING is for filtering after aggregation. WHEN and GROUP FILTER are not valid SQL filtering clauses in this context.

9. Correct GROUP BY syntax

   Which is a correct usage of GROUP BY if you want to aggregate data by 'department'?

   1. GROUP BY department
   2. GROUP BY EACH department
   3. GROUP BY SUM(department)
   4. GROUP department BY

   Explanation: GROUP BY department is the correct syntax. GROUP BY SUM(department) is invalid as SUM should only be in the SELECT or HAVING clauses, 'EACH' is not used in SQL for groups, and 'GROUP department BY' is reversed.

10. Aggregate column naming

    How would you display the average salary in a 'salary' column by each job title along with the job title?

    1. SELECT job_title, AVG(salary) FROM employees GROUP BY job_title
    2. SELECT job_title FROM employees GROUP BY job_title
    3. SELECT AVG(salary) GROUP BY job_title FROM employees
    4. SELECT AVG(job_title), salary FROM employees

    Explanation: The correct format is SELECT job_title, AVG(salary), grouping by job_title. The second option places GROUP BY incorrectly, the third option omits the aggregate column, and the fourth tries to average job_title, which is a string.

11. JOIN ON clause purpose

    Why is the ON clause important in JOIN operations?

    1. It specifies the conditions for joining the tables
    2. It adds new columns to a table
    3. It sorts the joined data
    4. It filters aggregated group results

    Explanation: The ON clause defines how the tables are linked by setting join conditions. It doesn't filter groups (that's HAVING), add columns directly, or perform sorting (which is handled by ORDER BY).

12. Finding unmatched records

    How can you find customers who have placed no orders using 'Customers' and 'Orders' tables?

    1. LEFT JOIN Orders and filter where Orders.id IS NULL
    2. RIGHT JOIN Orders filter Orders.id IS NOT NULL
    3. JOIN Orders with ON TRUE
    4. INNER JOIN Orders and count orders u003E 0

    Explanation: LEFT JOIN returns all customers; filtering where Orders.id IS NULL shows those without a matching order. INNER JOIN would not include unmatched customers, RIGHT JOIN is the reverse, and ON TRUE joins everything indiscriminately.

13. Simple HAVING clause usage

    Which query filters to show only products with more than 50 sales, grouped by product?

    1. SELECT product FROM sales WHERE COUNT(*) u003E 50 GROUP BY product
    2. SELECT COUNT(*) WHERE product u003E 50
    3. SELECT product, COUNT(*) FROM sales HAVING COUNT(*) u003E 50 GROUP BY product
    4. SELECT product, COUNT(*) FROM sales GROUP BY product HAVING COUNT(*) u003E 50

    Explanation: HAVING must follow GROUP BY to filter on aggregates; the first option uses correct order and logic. The second has HAVING before GROUP BY, which is invalid. The others misuse WHERE or do not group properly.

14. Type of join for keeping all left-side data

    Which type of JOIN would you use to include all rows from the left table even if there are no matching rows in the right table?

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

    Explanation: LEFT JOIN ensures all rows appear from the left table, filling with NULLs where needed. INNER JOIN would omit unmatched rows. OUTER JOIN typically refers to FULL OUTER JOIN, which includes all rows from both tables. CROSS JOIN creates combinations, not matched results.

15. Counting grouped results

    After grouping by 'category', which aggregate function tells you how many groups there are?

    1. DIVIDE
    2. SUM
    3. COUNT
    4. GROUPS

    Explanation: COUNT is the standard function to determine the number of resulting groups or rows. SUM accumulates values, GROUPS is not a standard function, and DIVIDE is not a SQL aggregate.

16. JOINs and NULL handling

    If a column contains NULL values, which join type will NOT return rows where the join key is NULL?

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

    Explanation: INNER JOIN requires matching values in both tables, so NULLs in the join column will prevent a match. LEFT JOIN, FULL JOIN, and RIGHT JOIN can include rows with NULLs if present in either side, depending on which table has the NULL.