MockRounds Logo

SQL Joins u0026 Aggregations: Concepts and Scenarios Quiz

Test your understanding of SQL JOIN types, GROUP BY, and HAVING clauses with this quiz. Assess your ability to distinguish between INNER and LEFT JOIN operations and apply aggregation and filtering using GROUP BY and HAVING.

  1. INNER JOIN basics

    Which SQL JOIN type returns only the records that have matching values in both related tables?

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

    Explanation: INNER JOIN returns rows when there are matching values in both tables, ensuring only related data is included. LEFT JOIN returns all rows from the left table and matches from the right, while unmatched rows will show as NULL. RIGHT JOIN behaves similarly but for the right table. CROSS JOIN produces the Cartesian product and is unrelated to matching values.

  2. LEFT JOIN outcome

    Given two tables, Employees and Departments, which JOIN returns all employees, including those without a department?

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

    Explanation: LEFT JOIN retrieves all records from the left table (Employees) and matched rows from the right table (Departments), showing NULLs for employees with no department. INNER JOIN would only show employees with a department. RIGHT JOIN focuses on all departments, and FULL JOIN includes unmatched rows from both tables, not specifically all employees.

  3. Result of unmatched LEFT JOIN

    When a LEFT JOIN finds no match in the right table, what value is returned for the right table's columns?

    1. Empty string
    2. Error
    3. 0
    4. NULL

    Explanation: When there is no corresponding row in the right table during a LEFT JOIN, the resulting columns from that table are filled with NULL. '0' or an empty string would require explicit default values or functions. It does not produce an error; it simply outputs NULL.

  4. GROUP BY clause use

    Which clause groups rows with the same values in specified columns, often used with aggregate functions like COUNT or SUM?

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

    Explanation: GROUP BY arranges rows sharing the same values into summary rows, often paired with aggregation. ORDER BY sorts results, WHERE filters rows before grouping, and HAVING filters groups after aggregation.

  5. Purpose of HAVING clause

    After aggregating sales data by region with GROUP BY, which clause would you use to show only regions with total sales above 10,000?

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

    Explanation: HAVING filters groups by aggregate conditions, like total sales. WHERE cannot filter aggregated results; it's used before grouping. ORDER BY sorts the output, and SELECT chooses columns to display, but neither filter groups by aggregates.

  6. Accuracy of GROUP BY syntax

    Which of these queries correctly groups product sales by category in SQL?

    1. SELECT category SUM(sales) products GROUP BY;
    2. SELECT category, SUM(sales) FROM products GROUP BY category;
    3. SELECT category, sales FROM products GROUP BY SUM(sales);
    4. SELECT SUM(sales) GROUPED BY category FROM products;

    Explanation: The correct syntax is 'SELECT category, SUM(sales) FROM products GROUP BY category'. The other options contain syntax errors or incorrect SQL keywords, such as missing commas, wrong keyword order, or non-existent phrases like 'GROUPED BY.'

  7. Difference between INNER and LEFT JOIN

    If you want customers who have placed at least one order, which JOIN should you use between Customers and Orders?

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

    Explanation: INNER JOIN only returns customers who appear in both tables, i.e., those who have placed orders. LEFT JOIN would include all customers, even those without orders. FULL JOIN includes all from both tables, and SELF JOIN isn't needed here.

  8. Filtering groups with HAVING

    Which statement correctly retrieves product categories with a total sales count greater than 100?

    1. SELECT category, COUNT(*) FROM sales GROUP BY category HAVING COUNT(*) u003E 100;
    2. SELECT category, COUNT(*) FROM sales GROUP BY category WHERE COUNT(*) u003E 100;
    3. SELECT category, COUNT(*) FROM sales WHERE COUNT(*) u003E 100 GROUP BY category;
    4. SELECT category, COUNT(*) WHERE COUNT(*) u003E 100 FROM sales GROUP BY category;

    Explanation: HAVING allows filtering on aggregated data like COUNT. WHERE doesn't allow aggregates after GROUP BY, and other options show incorrect SQL syntax or keyword order.

  9. Aggregating with multiple columns

    To find the total sales per month for each store, which SQL clause combination should you use?

    1. WHERE store AND month
    2. GROUP BY store, month
    3. ORDER BY month, store
    4. HAVING store, month

    Explanation: GROUP BY multiple columns, such as store and month, groups results accordingly for aggregation. ORDER BY simply sorts, HAVING is for filtering aggregated results, and WHERE filters rows before aggregation, not for grouping.

  10. Handling typos in JOIN types

    Identify the correct JOIN type out of the following options used to include all rows from the left table.

    1. LEFTED JOIN
    2. LEFT JOIN
    3. LFT JOIN
    4. LEF JOIN

    Explanation: LEFT JOIN is the correct and standard SQL keyword. 'LEF JOIN', 'LFT JOIN', and 'LEFTED JOIN' are not valid SQL JOIN types and likely result from typographical errors.

  11. JOIN output row count

    What effect does an INNER JOIN have on the number of rows in the query result compared to a LEFT JOIN?

    1. INNER JOIN always returns exactly one row
    2. INNER JOIN returns the same or fewer rows than LEFT JOIN
    3. INNER JOIN and LEFT JOIN always return the same number of rows
    4. INNER JOIN always returns more rows

    Explanation: An INNER JOIN only includes matching rows, so the result set is always less than or equal to that of a LEFT JOIN, which can include non-matching rows from the left table. INNER JOIN never guarantees more rows, and they rarely return exactly the same count unless all left-table rows have matches.

  12. Aggregating unique values

    How would you count the number of unique customer IDs in an Orders table?

    1. SELECT COUNT(DIFFERENT customer_id) FROM Orders;
    2. SELECT SUM(customer_id) FROM Orders;
    3. SELECT COUNT(DISTINCT customer_id) FROM Orders;
    4. SELECT COUNT(ALL customer_id) FROM Orders;

    Explanation: COUNT(DISTINCT ...) tallies the unique values in the selected column. SUM returns the total of values, not counts. COUNT(ALL) is redundant since COUNT counts all by default, and COUNT(DIFFERENT) is not valid SQL syntax.

  13. Identifying missing aggregation

    What happens if you include a column in the SELECT list that is not in GROUP BY or used in an aggregation function?

    1. The query returns duplicate rows
    2. The column is ignored
    3. The query runs, but results are unpredictable
    4. It results in a SQL error

    Explanation: Unless the column is part of an aggregate or listed in GROUP BY, most SQL systems return an error for ambiguous selection. The column is not simply ignored nor causes duplicates; its ambiguous calculation causes the error.

  14. Recognizing correct HAVING usage

    Which clause must be used to filter results after aggregation, such as showing only products with sum of sales above 500?

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

    Explanation: HAVING operates on aggregated data after GROUP BY. WHERE filters before aggregation, ORDER BY sorts the results, and DISTINCT removes duplicates without filtering by aggregation.

  15. Simple aggregation calculation

    What will SELECT SUM(amount) FROM Payments; return?

    1. The average amount in each row
    2. The total of all values in the amount column
    3. The count of payment records
    4. The maximum amount value

    Explanation: SUM returns the total of numeric values in the column. AVERAGE and MAX refer to other functions, and COUNT tallies records, not their summed values.

  16. Selecting correct JOIN for missing values

    Which JOIN should you use if you want to see a complete list of students, including those not enrolled in any courses, when joining Students and Enrollments tables?

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

    Explanation: LEFT JOIN keeps all records from Students, marking students with no enrollments with NULL in joined columns. INNER JOIN would exclude students not enrolled. RIGHT and CROSS JOIN do not specifically address this requirement.

mockrounds.com Learn • Practice • Evaluate