MockRounds Logo

SQL Joins u0026 Aggregation Essentials Quiz Quiz

Test your knowledge of SQL Joins, GROUP BY, HAVING, and writing practical reporting queries. This quiz covers core SQL concepts and basic skills needed to analyze and summarize data using joins and aggregations.

  1. Identifying an INNER JOIN

    Which SQL clause returns only rows with matching values in both joined tables?

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

    Explanation: An INNER JOIN only returns rows where there is a match in both tables, making it ideal for finding relationships shared by both. LEFT JOIN returns all records from the left table and matching rows from the right table, potentially including unmatched left-side rows. OUTER JOIN is not a standard SQL clause; it's usually specified as LEFT or RIGHT OUTER JOIN. FULL JOIN includes rows from both tables whether or not they match.

  2. Purpose of GROUP BY

    What is the primary function of the GROUP BY clause in SQL?

    1. To limit the number of returned records
    2. To sort results in ascending order
    3. To filter records based on a condition
    4. To group rows that have the same values in specified columns

    Explanation: GROUP BY groups together rows that have identical values in specified columns, allowing aggregate functions to summarize data within those groups. LIMIT (not listed) would restrict the number of records. Filtering is done with WHERE or HAVING, not GROUP BY. Sorting results is managed by ORDER BY, not GROUP BY.

  3. LEFT JOIN Behavior

    If you perform a LEFT JOIN between Table A and Table B, what happens to rows from Table A with no matching rows in Table B?

    1. They are excluded from the result set
    2. They are duplicated in the result set
    3. They appear with NULLs for columns from Table B
    4. They cause an error in the query

    Explanation: LEFT JOIN returns all records from Table A, and where there is no match in Table B, columns from Table B are filled with NULL. Excluding unmatched left rows is the behavior of INNER JOIN. Duplicating rows occurs only in special cases with multiple matches, not due to the lack of matches. This operation will not cause a query error.

  4. Selecting groups with HAVING

    Which clause would you use to filter aggregated group results, such as groups with a sum greater than 100?

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

    Explanation: HAVING filters groups after aggregation, making it ideal to restrict results based on total values like a sum or count. ORDER BY only sorts results. WHERE is used to filter individual rows before grouping, not groups themselves. IN tests whether a value matches a set, not group aggregation.

  5. Aggregating data using COUNT

    What does the SQL function COUNT(*) do in a SELECT query?

    1. Adds up all the values in a column
    2. Finds the minimum value in a result set
    3. Counts the number of rows for each result group
    4. Calculates the average of a column's values

    Explanation: COUNT(*) counts the number of rows in a group or entire result, making it useful for summarizing records. Adding values is done with SUM(), averaging with AVG(), and finding minimum with MIN(). Each function provides a different type of aggregation.

  6. Interpreting query output

    Given tables 'sales' and 'employees', which JOIN would show all sales, including those without a matching employee?

    1. INNER JOIN employees ON sales.emp_id = employees.id
    2. FULL JOIN sales ON employees.id = sales.emp_id
    3. LEFT JOIN employees ON sales.emp_id = employees.id
    4. LEFT JOIN sales ON employees.id = sales.emp_id

    Explanation: LEFT JOIN sales ON employees.id = sales.emp_id ensures all records from 'sales' are shown, with employee details where available. The INNER JOIN only displays rows with matches in both tables. LEFT JOIN employees ON sales.emp_id = employees.id prioritizes employees, not sales. FULL JOIN displays all matches from both sides, showing unmatched rows from both, not just from 'sales'.

  7. Understanding aggregate output

    If you run SELECT department, AVG(salary) FROM staff GROUP BY department, what does each row in the result represent?

    1. Departments sorted by salary
    2. A summary of all staff salaries
    3. A staff member's record
    4. A department with its average salary

    Explanation: This query groups data by department, then computes the average salary for each group. Each row represents a unique department and its associated average. Individual staff records are not shown. Sorting is not performed here, as there's no ORDER BY. A summary of all salaries would use no grouping.

  8. Report query design

    Which SQL construct allows you to join multiple tables to present a combined report view?

    1. SELECT DISTINCT
    2. JOIN clause
    3. CREATE DATABASE
    4. DROP TABLE

    Explanation: The JOIN clause is used to combine rows from two or more tables based on related columns, enabling comprehensive reports. SELECT DISTINCT removes duplicates but does not join tables. CREATE DATABASE defines a new database and does not relate to querying. DROP TABLE deletes a table and is unrelated to combining data.

  9. Recognizing mismatches in joins

    What is the result if you join Table X and Table Y using INNER JOIN and there are no matching rows in either table?

    1. Rows with NULL values are shown
    2. No rows are returned
    3. All rows from Table Y are shown
    4. All rows from Table X are shown

    Explanation: An INNER JOIN returns only matches between both tables. If no matches exist, the output is empty. LEFT JOIN or RIGHT JOIN would include unmatched rows from one table. Including rows filled with NULL values is typical for OUTER or LEFT/RIGHT JOINs, not INNER JOINs. All rows from one table with unmatched rows is not the behavior of INNER JOIN.

  10. SUM aggregation in reports

    When using SELECT department, SUM(amount) FROM payments GROUP BY department, what does SUM(amount) represent?

    1. The number of payments made
    2. The average payment per department
    3. The total payments in each department
    4. The highest payment in the table

    Explanation: SUM(amount) calculates the total of the 'amount' column for each department group. The highest payment would require MAX(), counting payments uses COUNT(), and the average payment uses AVG(). Each aggregation has a specific function for summarizing data.

  11. Using ON with JOINs

    In a SQL query joining customers and orders, which keyword specifies the matching columns?

    1. LIMIT
    2. AS
    3. ON
    4. WHERE

    Explanation: The ON keyword defines the joining condition, specifying which columns to match from each table. WHERE can be used for additional filtering but isn't meant specifically for join conditions. AS is used for aliases. LIMIT restricts the number of rows displayed and is unrelated to join conditions.

  12. Removing duplicate results

    If you want each group to appear only once when aggregating data, which SQL keyword would you generally use with GROUP BY?

    1. ORDER BY
    2. GROUP BY
    3. DROP
    4. UNION

    Explanation: GROUP BY collects rows into unique groups based on specified columns, ensuring one row per group in the result. UNION combines results of two queries but does not aggregate. ORDER BY sorts data, and DROP is used to delete objects like tables, not organize query output.

  13. Difference between WHERE and HAVING

    In which situation would you use WHERE instead of HAVING in a query with GROUP BY?

    1. To filter rows before grouping occurs
    2. To order the final output
    3. To rename output columns
    4. To filter groups after aggregation

    Explanation: WHERE filters rows before they are grouped, ensuring only matching rows participate in the grouping and aggregation. HAVING is used after data has been grouped, for filtering groups. ORDER BY is for sorting, and renaming columns is done with AS, not WHERE or HAVING.

  14. Tracking unmatched right-side records

    What happens to records in the right table with no matching left-table row in a LEFT JOIN?

    1. They are excluded from the result
    2. They duplicate the left table's first row
    3. They appear with NULLs for left-table columns
    4. They cause an error

    Explanation: LEFT JOIN includes all records from the left table, matching those from the right table where possible, and excludes any right table records without a left table match. The unmatched right-side rows are not included at all. Including NULLs for left-table columns is specific to RIGHT JOIN. Duplication and errors do not occur as distractions suggest.

  15. Choosing the correct JOIN for unmatched rows

    Which JOIN returns all rows from both tables, including unmatched rows as NULLs for missing side values?

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

    Explanation: FULL JOIN includes every row from both tables, adding NULLs for columns when there isn't a matching row on one side. INNER JOIN shows only matching records. LEFT JOIN returns all left-table rows but not all from the right. OUTER JOIN is a more generic term, and FULL JOIN is its specific implementation.

  16. Reporting on grouped aggregates

    Which SQL query best reports the number of orders for each customer?

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

    Explanation: This query groups orders by customer and counts them, reporting how many orders each customer made. Selecting only COUNT(*) from orders gives a total count, not per customer. Counting by order_id groups incorrectly. SELECT * with WHERE customer_id only filters, not groups or aggregates.

mockrounds.com Learn • Practice • Evaluate