MockRounds Logo

SQL Joins and Aggregations Fundamentals Quiz Quiz

Test your understanding of SQL joins and aggregation techniques with this quiz. Questions focus on concepts, syntax, and usage of different join types and aggregation functions to help you master key SQL skills.

  1. Identifying an INNER JOIN

    Which type of JOIN returns only the rows where there is a match in both tables for a given condition?

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

    Explanation: An INNER JOIN returns records that have matching values in both tables according to the specified condition, so only overlapping data is included. LEFT JOIN includes all records from the left table regardless of matches in the right, and FULL JOIN returns all records when there is a match in either table. CROSS JOIN simply combines each row from one table with every row from another, which does not require any matching.

  2. Purpose of LEFT JOIN

    In SQL, which JOIN should you use if you want to include all records from the first (left) table and only the matching rows from the second (right) table?

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

    Explanation: LEFT JOIN returns all rows from the left table, and the matched rows from the right table; rows from the right with no match result in NULLs. RIGHT JOIN works in the reverse order, while SELF JOIN joins a table with itself, and OUTER JOIN is not a standard SQL keyword (FULL OUTER JOIN is different).

  3. Recognizing a right join result

    Given two tables, if you want all records from the second (right) table regardless of matching, which SQL JOIN should you use?

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

    Explanation: RIGHT JOIN includes all rows from the right table along with matching rows from the left table; unmatched left rows result in NULLs. INNER JOIN only includes matched rows, LEFT JOIN prioritizes the left table, and UNION JOIN is not a valid SQL operation.

  4. Syntax for joining with ON

    Which keyword is typically used in SQL to specify the columns used to JOIN two tables together?

    1. ORDER
    2. WHERE
    3. USING
    4. ON

    Explanation: ON is the standard keyword required to define the condition on which two tables are joined, such as ON table1.id = table2.id. USING is only valid for joining on columns with the same name, WHERE is used for filtering data, and ORDER is used for sorting, not joining.

  5. Understanding the COUNT aggregate function

    Which SQL aggregate function should you use to determine the number of rows returned by a query?

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

    Explanation: COUNT calculates the number of rows that meet the query conditions. SUM adds up values, AVG calculates the arithmetic mean, and MIN finds the minimum value. Only COUNT is intended for counting rows.

  6. Describing the GROUP BY clause

    When writing a query to show the total sales per customer, which SQL clause groups the results by customer?

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

    Explanation: GROUP BY organizes rows into groups sharing equivalent values, allowing aggregate functions across those groups. ORDER BY sorts results, WHERE filters rows before grouping, and HAVING is used to filter groups after aggregation.

  7. Purpose of the SUM() function

    If you want to calculate the total revenue from a 'sales_amount' column, which aggregate function should you use?

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

    Explanation: SUM returns the total value of a numeric column, ideal for calculating total revenue. COUNT counts rows, MAX finds the highest value, and FIRST, while it might sound logical, is not a standard aggregate function in SQL.

  8. Minimizing duplicated rows using DISTINCT

    In which scenario would you use the DISTINCT keyword with an aggregation?

    1. To join two tables
    2. To order results alphabetically
    3. To show every row in a table
    4. To count unique values in a column

    Explanation: DISTINCT is used in combination with aggregate functions like COUNT to count only unique values in a column. It does not display every row, perform joins, or order results (ORDER BY is for sorting).

  9. Joining on multiple columns

    Which option correctly describes joining two tables using more than one column from each table?

    1. Using ORDER BY on columns
    2. Using HAVING clause
    3. Multiple conditions in ON clause
    4. Using WHERE to filter columns

    Explanation: You can join tables on multiple columns by specifying several conditions within the ON clause separated by AND. HAVING filters after aggregation, ORDER BY only sorts, and WHERE filters rows but is not directly for joining tables.

  10. Role of FULL JOIN

    What does a FULL JOIN (also called FULL OUTER JOIN) do in SQL?

    1. Returns only matching rows between two tables
    2. Returns only rows present in the left table
    3. Returns only rows present in the right table
    4. Returns all matching and non-matching rows from both tables

    Explanation: FULL JOIN includes every row from both tables, placing NULL where there is no match. Left JOIN limits to left table rows, Inter JOIN shows only matches, and Right JOIN focuses on right table entries.

  11. Purpose of the HAVING clause

    Which SQL clause is used to filter the results of a GROUP BY after aggregation?

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

    Explanation: HAVING filters the results after aggregation, allowing conditions on aggregate values (such as COUNT u003E 10). WHERE filters rows before aggregation, ORDER BY sorts the output, and JOIN is used to combine tables, not filter aggregate groups.

  12. Understanding AVG()

    Which aggregate function should you use to calculate the average value in the 'score' column?

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

    Explanation: AVG finds the mean, which is the total of the values divided by the number of items. SUM adds up the values, COUNT counts them, and TOP is not an aggregate function in SQL.

  13. Describing a CROSS JOIN

    Given two tables, which type of JOIN returns every possible combination of rows from both tables?

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

    Explanation: A CROSS JOIN produces the Cartesian product of the tables, giving all possible row combinations. INNER JOIN combines only matching rows, LEFT JOIN only left table rows with matches, and NATURAL JOIN automatically joins columns with the same name (not every combination).

  14. Identifying the MAX aggregate function

    Which function will return the largest value from a column named 'salary'?

    1. MAX
    2. MEDIAN
    3. COUNT
    4. MIN

    Explanation: MAX picks the highest value in a set. MIN does the opposite, COUNT tallies rows, and MEDIAN—which sounds plausible—is not a standard SQL aggregate function.

  15. SELF JOIN concept

    What does SELF JOIN mean in the context of SQL tables?

    1. Creating circular references in columns
    2. Joining a table to itself using aliases
    3. Joining tables only by primary key
    4. Joining two unrelated tables

    Explanation: A SELF JOIN references the same table twice in a query using aliases, usually to compare rows within that table. Joining unrelated tables is not specific, primary key joins are a technique but not unique to SELF JOIN, and circular references describe a data design issue rather than a join type.

  16. Handling NULLs in aggregates

    How does the SUM() aggregate function handle NULL values?

    1. It returns an error
    2. It counts NULLs as values
    3. It ignores NULLs completely
    4. It treats NULLs as zeros

    Explanation: SUM ignores NULL values and only adds up non-null entries, not counting them as zeros or causing errors. If all values are NULL, the result is NULL. COUNT can exclude or include NULLs based on usage, but SUM does not.

mockrounds.com Learn • Practice • Evaluate