SQL Joins u0026 Aggregations Basics Quiz Quiz

Test your knowledge of SQL joins, GROUP BY, HAVING clauses, and basic window functions with this easy-level quiz. Perfect for beginners looking to reinforce their understanding of data relationships and aggregation techniques in SQL.

1. Identifying an INNER JOIN

   Which SQL clause returns only the rows that have matching values in both tables when joining them?

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

   Explanation: INNER JOIN returns rows where there is a match in both tables, ensuring only matching data is selected. LEFT JOIN returns all records from the left table and the matched records from the right table, so unmatched rows from the right are not included. OUTER JOIN is a general term, but it is not a valid SQL keyword by itself. CROSS JOIN combines all records from both tables, regardless of matches.

2. Purpose of LEFT JOIN

   When using LEFT JOIN in SQL, what happens to the rows in the left table that do not have a matching row in the right table?

   1. They are included with NULLs for the right table columns
   2. They are excluded entirely
   3. They are duplicated
   4. They are included twice

   Explanation: LEFT JOIN keeps all rows from the left table, even if there is no match in the right table, and fills the right table's columns with NULL values where matches do not exist. Rows are not excluded; that's the behavior of INNER JOIN. Rows are not duplicated or included twice; each matches at most one in the right table.

3. Function of GROUP BY

   What is the primary use of the GROUP BY clause in an SQL SELECT statement?

   1. To sort the results in descending order
   2. To limit the number of returned rows
   3. To organize rows into summary groups based on one or more columns
   4. To combine columns from several tables

   Explanation: GROUP BY groups rows sharing the same value of specified columns into summary rows, which enables aggregating data such as sums or counts. LIMIT is used to restrict the row count, not GROUP BY. ORDER BY sorts results, and JOIN clauses are for combining columns from multiple tables.

4. Understanding the HAVING Clause

   Why would you use the HAVING clause instead of WHERE after a GROUP BY in SQL?

   1. To filter rows before grouping
   2. To filter groups based on aggregate functions
   3. To force JOIN conditions
   4. To sort grouped results

   Explanation: HAVING is used to filter groups after aggregation, making it possible to filter results based on aggregate values like SUM or COUNT. WHERE filters rows before they are grouped, not after aggregation. ORDER BY sorts results, and JOIN is unrelated to HAVING.

5. Result of COUNT(*) with GROUP BY

   What does the COUNT(*) function return when used with GROUP BY on the 'department' column in an 'employees' table?

   1. The number of employees in each department
   2. The sum of all employees' salaries
   3. The total number of departments
   4. The first employee's record in each department

   Explanation: COUNT(*) with GROUP BY 'department' counts the rows (employees) in each department, giving per-group totals. It does not count the number of departments, which could be found with COUNT(DISTINCT department). It does not sum salaries or retrieve the first record from each group.

6. Choosing the Correct Join for All Customers

   If you want a list of all customers, including those who have not placed any orders, which SQL join should you use between 'customers' and 'orders'?

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

   Explanation: LEFT JOIN includes all records from the left table (customers), even if there are no matching rows in the right table (orders). INNER JOIN would exclude customers without orders. RIGHT JOIN would keep all orders, not all customers. FULL JOIN includes all records from both tables but may add unnecessary unmatched records from both sides.

7. SUM() Aggregation with GROUP BY

   If you use 'SELECT SUM(sales) FROM store_sales GROUP BY region', what does each row in the result represent?

   1. All sales transactions for one store
   2. Total sales for all regions combined
   3. The average sales for each region
   4. Total sales for each region

   Explanation: SUM(sales) with GROUP BY region calculates the total sales within each region, outputting one row per region. It does not group by store, average sales, or give the overall sum unless GROUP BY is removed or modified.

8. ON Keyword in Join Syntax

   Which keyword specifies the condition used to match rows from two tables in an SQL JOIN?

   1. INTO
   2. WITH
   3. ON
   4. BY

   Explanation: The ON keyword is used in JOIN clauses to set the joining condition between columns. BY is used in GROUP BY and ORDER BY. WITH is for common table expressions, and INTO is for inserting or creating tables.

9. Purpose of the ROW_NUMBER() Window Function

   What does the ROW_NUMBER() window function do in an SQL query?

   1. Calculates the running sum of a column
   2. Indicates the primary key of a table
   3. Counts the number of rows in each group
   4. Assigns a unique sequential number to each row in a window partition

   Explanation: ROW_NUMBER() assigns a sequential integer to rows within each partition of result set, often used for ranking. COUNT counts rows, not row numbers. SUM computes running totals with SUM() OVER, while primary keys are table-level constraints, not functions.

10. Basic SQL Window Function Syntax

    In basic window functions like 'ROW_NUMBER() OVER (PARTITION BY category)', what does PARTITION BY do?

    1. Filters rows before aggregation
    2. Restricts the number of rows returned
    3. Divides rows into groups where the window function is applied independently
    4. Sorts the rows alphabetically

    Explanation: PARTITION BY splits the data into subsets (partitions), so the window function like ROW_NUMBER() starts over in each group. It does not restrict row count, sort order, or perform filtering before aggregation.

11. Using ORDER BY with Window Functions

    When used with ROW_NUMBER(), what does the ORDER BY clause inside the OVER() window specify?

    1. The column used for grouping
    2. The ordering of rows for row numbering
    3. Which rows are filtered
    4. The columns joined from another table

    Explanation: ORDER BY inside the OVER() clause determines the sequence in which ROW_NUMBER() (or similar functions) assigns numbers to rows. It does not group, filter, or join rows. Grouping is set by PARTITION BY, and joins happen earlier in the query.

12. Matching Records with NULL Values

    If two tables have NULL values in the join columns, what is the result of an INNER JOIN on those columns?

    1. The rows are always matched together
    2. NULL rows generate multiple matches
    3. The query fails with an error
    4. Rows with NULL values in the join columns are not matched

    Explanation: In INNER JOIN, rows with NULL in the join columns are not considered equal and do not match, so those rows are excluded. Matching NULLs together does not happened, and NULLs don’t generate extra matches. The query does not fail because of NULL, but the rows are simply not joined.

13. Syntax of an Aggregate Function

    What is the correct syntax to calculate the average salary in an 'employees' table?

    1. SELECT MEAN(salary) FROM employees;
    2. SELECT AVG(salary) FROM employees;
    3. SELECT SUM(salary)/COUNT(salary) FROM employees;
    4. SELECT AVERAGE(salary) FROM employees;

    Explanation: AVG() is the standard SQL function for calculating the average. AVERAGE and MEAN are not valid SQL aggregate functions. While SUM divided by COUNT may provide the same result, it is less direct and could give misleading results if NULLs are present.

14. Eliminating Duplicate Groups in Aggregations

    Which keyword would you add to a GROUP BY query to eliminate duplicate results in the output?

    1. REMOVE
    2. UNIQUE
    3. EXCLUDE
    4. DISTINCT

    Explanation: DISTINCT removes duplicate rows from the final result set of a query, and can be combined with GROUP BY. UNIQUE and REMOVE are not valid SQL keywords in this context, while EXCLUDE does not remove duplicates in SQL.

15. Aggregating Data with COUNT(DISTINCT ...)

    How can you count the number of unique customers who placed an order in an 'orders' table?

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

    Explanation: COUNT(DISTINCT column) counts the number of unique non-NULL values in a column, ideal for unique customers. COUNT with GROUP BY order_id would not count unique customers. SUM is for adding numeric values, not counting. DISTINCT by itself with SELECT returns unique customer IDs, not a count.

16. Aggregations Without GROUP BY

    If you write 'SELECT SUM(price) FROM sales;' without GROUP BY, what value is returned?

    1. The total of all 'price' values in the entire table
    2. The number of sales records
    3. One sum for each sales region
    4. The total for each different price

    Explanation: Without GROUP BY, SUM aggregates over the entire table and gives one value: the total sum. It does not give grouped totals, nor does it count records. Multiple sums or sums by region require GROUP BY.