SQL Group By Clause Quiz

1. What is the primary purpose of the GROUP BY clause in SQL?

To sort query results in ascending order

To divide rows into groups and apply aggregate functions to each group

To filter individual rows before aggregation

To join multiple tables together

The GROUP BY clause in SQL is used to organize rows that share a common value into groups. This allows for the application of aggregate functions, such as COUNT, SUM, or AVG, to each group, facilitating summary data analysis and reporting based on the grouped criteria.

Explanation

The GROUP BY clause in SQL is used to organize rows that share a common value into groups. This allows for the application of aggregate functions, such as COUNT, SUM, or AVG, to each group, facilitating summary data analysis and reporting based on the grouped criteria.

2. Which aggregate function returns the number of non-null values in a column?

SUM()

AVG()

COUNT()

MAX()

COUNT() is an aggregate function that specifically counts the number of non-null values in a specified column. Unlike SUM(), AVG(), and MAX(), which perform mathematical operations, COUNT() focuses solely on the presence of data, making it essential for determining how many entries exist in a dataset.

Explanation

COUNT() is an aggregate function that specifically counts the number of non-null values in a specified column. Unlike SUM(), AVG(), and MAX(), which perform mathematical operations, COUNT() focuses solely on the presence of data, making it essential for determining how many entries exist in a dataset.

3. In a GROUP BY query, non-aggregated columns in the SELECT list must appear in the ______ clause.

In SQL, when using a GROUP BY query, any column in the SELECT statement that is not part of an aggregate function must be included in the GROUP BY clause. This ensures that the results are grouped correctly based on the specified columns, allowing for accurate aggregation of data.

Explanation

In SQL, when using a GROUP BY query, any column in the SELECT statement that is not part of an aggregate function must be included in the GROUP BY clause. This ensures that the results are grouped correctly based on the specified columns, allowing for accurate aggregation of data.

Submit

4. What is the difference between WHERE and HAVING in a GROUP BY query?

WHERE filters groups; HAVING filters rows

WHERE filters rows before grouping; HAVING filters groups after aggregation

They are identical and interchangeable

HAVING is used only with COUNT() functions

WHERE is used to filter individual rows from the dataset before any grouping occurs, allowing for more precise selection of data. In contrast, HAVING is applied after the data has been grouped and aggregated, enabling conditions to be set on the aggregated results, such as sums or averages.

Explanation

WHERE is used to filter individual rows from the dataset before any grouping occurs, allowing for more precise selection of data. In contrast, HAVING is applied after the data has been grouped and aggregated, enabling conditions to be set on the aggregated results, such as sums or averages.

5. Which SQL statement correctly groups employees by department and counts them?

SELECT department, COUNT(*) FROM employees GROUP BY salary

SELECT department, COUNT(*) FROM employees GROUP BY department

SELECT COUNT(*) FROM employees WHERE department GROUP BY department

SELECT department FROM employees GROUP BY COUNT(*)

This SQL statement correctly groups the employees by their department and counts the number of employees in each department. The `GROUP BY department` clause ensures that the results are aggregated based on the department, while `COUNT(*)` calculates the total number of employees within each group, providing a clear count per department.

Explanation

This SQL statement correctly groups the employees by their department and counts the number of employees in each department. The `GROUP BY department` clause ensures that the results are aggregated based on the department, while `COUNT(*)` calculates the total number of employees within each group, providing a clear count per department.

6. The HAVING clause is used to filter ______ after aggregation has occurred.

The HAVING clause is utilized in SQL to filter records after an aggregation function, such as COUNT or SUM, has been applied to groups of data. Unlike the WHERE clause, which filters rows before aggregation, HAVING allows for conditions to be set on the aggregated results, ensuring only relevant groups are returned.

Explanation

The HAVING clause is utilized in SQL to filter records after an aggregation function, such as COUNT or SUM, has been applied to groups of data. Unlike the WHERE clause, which filters rows before aggregation, HAVING allows for conditions to be set on the aggregated results, ensuring only relevant groups are returned.

Submit

7. True or False: You can use column aliases from the SELECT clause in the HAVING condition.

True

False

In SQL, column aliases defined in the SELECT clause cannot be used in the HAVING clause because the HAVING clause is evaluated before the SELECT clause. Instead, you must use the actual column names or aggregate functions in the HAVING condition to filter results based on aggregate calculations.

Explanation

In SQL, column aliases defined in the SELECT clause cannot be used in the HAVING clause because the HAVING clause is evaluated before the SELECT clause. Instead, you must use the actual column names or aggregate functions in the HAVING condition to filter results based on aggregate calculations.

8. Which query correctly finds departments with average salary greater than $50,000?

SELECT department, AVG(salary) FROM employees HAVING AVG(salary) > 50000

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000

SELECT department FROM employees WHERE AVG(salary) > 50000 GROUP BY department

SELECT department, AVG(salary) FROM employees WHERE AVG(salary) > 50000

This query first groups the employees by department, allowing the calculation of the average salary per department. The HAVING clause then filters these groups to include only those with an average salary greater than $50,000, ensuring accurate results based on the aggregated data.

Explanation

This query first groups the employees by department, allowing the calculation of the average salary per department. The HAVING clause then filters these groups to include only those with an average salary greater than $50,000, ensuring accurate results based on the aggregated data.

9. What does the DISTINCT keyword do in an aggregate function like COUNT(DISTINCT column)?

Removes NULL values from the result

Counts only unique non-null values in the column

Sorts the results in distinct order

Groups rows by distinct values

The DISTINCT keyword in an aggregate function like COUNT ensures that only unique, non-null values from the specified column are counted. This means that if the same value appears multiple times, it will only be counted once, providing a more accurate representation of the unique entries in that column.

Explanation

The DISTINCT keyword in an aggregate function like COUNT ensures that only unique, non-null values from the specified column are counted. This means that if the same value appears multiple times, it will only be counted once, providing a more accurate representation of the unique entries in that column.

10. In GROUP BY queries, the ______ function returns the smallest value in each group.

In SQL, the MIN function is used in GROUP BY queries to identify the smallest value within each group of data. It aggregates results based on specified columns, allowing users to analyze minimum values for distinct categories or groups, facilitating comparative insights in data analysis.

Explanation

In SQL, the MIN function is used in GROUP BY queries to identify the smallest value within each group of data. It aggregates results based on specified columns, allowing users to analyze minimum values for distinct categories or groups, facilitating comparative insights in data analysis.

Submit

11. True or False: You must use GROUP BY whenever you use an aggregate function in SELECT.

True

False

Using an aggregate function in a SELECT statement does not always require a GROUP BY clause. Aggregate functions can be applied to the entire result set without grouping, providing a single summary value. GROUP BY is only necessary when you want to calculate aggregates for specific subsets of data based on one or more columns.

Explanation

Using an aggregate function in a SELECT statement does not always require a GROUP BY clause. Aggregate functions can be applied to the entire result set without grouping, providing a single summary value. GROUP BY is only necessary when you want to calculate aggregates for specific subsets of data based on one or more columns.

12. Which statement correctly groups sales by product and sums total revenue?

SELECT product, SUM(revenue) FROM sales GROUP BY product ORDER BY SUM(revenue)

SELECT product, revenue FROM sales GROUP BY product SUM(revenue)

SELECT SUM(revenue) FROM sales GROUP BY product, revenue

SELECT product FROM sales SUM(revenue) GROUP BY product

This SQL query correctly aggregates sales data by product, using the `SUM(revenue)` function to calculate total revenue for each product. The `GROUP BY` clause organizes the results by product, while the `ORDER BY` clause sorts the output based on the summed revenue, ensuring the highest revenue products are listed first.

Explanation

This SQL query correctly aggregates sales data by product, using the `SUM(revenue)` function to calculate total revenue for each product. The `GROUP BY` clause organizes the results by product, while the `ORDER BY` clause sorts the output based on the summed revenue, ensuring the highest revenue products are listed first.