Test Your MySQL GROUP BY Knowledge with This Quiz

Progress

True / False

In MySQL, the GROUP BY clause is used to group rows that have the same values in specified columns.

True False

The GROUP BY clause groups rows based on the values of specified columns, which allows aggregate functions to be performed on these groups.

True / False

In MySQL, you must use the GROUP BY clause with an aggregate function like SUM or COUNT.

True False

While GROUP BY is often used with aggregate functions, it is not mandatory. You can use GROUP BY without aggregate functions to simply group rows.

True / False

MySQL allows you to use the GROUP BY clause with multiple columns.

True False

You can specify multiple columns in the GROUP BY clause, which groups rows based on the unique combinations of the values in these columns.

True / False

In MySQL, the HAVING clause can only be used with the GROUP BY clause.

True False

The HAVING clause can be used without GROUP BY, although it is typically used to filter groups of rows formed by GROUP BY.

True / False

The HAVING clause can be used without GROUP BY, although it is typically used to filter groups of rows formed by GROUP BY.

True False

You can use column aliases in the GROUP BY clause, which can help make queries more readable.

True / False

When using GROUP BY in MySQL, the order of the grouped columns does not affect the result set

True False

The order of columns in the GROUP BY clause affects the grouping and the resulting order of the groups.

True / False

In MySQL, the GROUP BY clause always returns results in the order specified by the GROUP BY columns.

True False

The GROUP BY clause does not guarantee the order of the results. If a specific order is required, the ORDER BY clause should be used.

True / False

MySQL’s GROUP BY allows the use of expressions and functions in the list of columns to group by.

True False

You can use expressions and functions in the GROUP BY clause to group by the results of these expressions or functions.

True / False

In MySQL, using GROUP BY with large datasets can impact performance significantly, and indexing the grouped columns can help mitigate this.

True False

Grouping large datasets can be resource-intensive, but indexing the columns used in the GROUP BY clause can improve performance.

Q10

True / False

In MySQL, it is a best practice to include only columns in the SELECT clause that are either part of the GROUP BY clause or used with an aggregate function when using GROUP BY.

True False

To ensure the query results are deterministic and to avoid errors, it is best practice to only select columns that are either included in the GROUP BY clause or used with aggregate functions.

Q11

Single Choice

Which of the following clauses is used in MySQL to group rows that have the same values in specified columns?

ORDER BY GROUP BY DISTINCTHAVING

The GROUP BY clause is used to group rows that have the same values in specified columns.

Q12

Single Choice

What will be the result of the following SQL query?

SQL Code

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

It will count the number of employees in each department It will list all employees and their departments. It will count the total number of departments.It will return an error because of the GROUP BY clause.

The GROUP BY clause groups the results by department, and the COUNT(*) function counts the number of rows (employees) in each group

Q13

Single Choice

In MySQL, can you use the GROUP BY clause without any aggregate functions?

Yes, but it won't be meaningful No, it's not allowed. Yes, and it is necessaryNo, it will return an error.

The GROUP BY clause can be used without aggregate functions, but it typically does not produce meaningful results unless combined with aggregates.

Q14

Single Choice

Given the following query, what will be the output?

SQL Code

SELECT city, SUM(sales)
FROM store_sales
GROUP BY city;

The total sales for each city. The total sales of all cities combined. A list of cities with no sales data.An error message due to the GROUP BY clause.

The SUM(sales) function sums up the sales for each group of cities, and the GROUP BY clause groups the results by city.

Q15

Single Choice

Which of the following is true about using GROUP BY with the HAVING clause in MySQL?

HAVING is used to filter groups after the GROUP BY clause. HAVING is used before the GROUP BY clause. HAVING can replace the GROUP BY clauseHAVING cannot be used with GROUP BY.

The HAVING clause is used to filter groups of rows after they have been grouped by the GROUP BY clause.

Q16

Single Choice

What will the following query return?

SQL Code

SELECT product, COUNT(*)
FROM orders
WHERE order_date > '2024-01-01'
GROUP BY product
HAVING COUNT(*) > 10;

Products ordered more than 10 times after January 1, 2024. All products ordered after January 1, 2024. Products ordered less than 10 times after January 1, 2024.The total count of all orders placed after January 1, 2024.

The HAVING clause filters the groups formed by GROUP BY to include only those products with a count greater than 10.

Q17

Single Choice

Consider the table sales with columns product_id, sale_date, and amount. What does the following query accomplish?

SQL Code

SELECT product_id, MAX(amount)
FROM sales
GROUP BY product_id
ORDER BY MAX(amount) DESC;

It finds the maximum sales amount for each product and orders them in descending order It lists all sales amounts for each product. It calculates the average sales amount for each productIt generates an error due to the GROUP BY and ORDER BY clauses.

The MAX(amount) function finds the maximum sales amount per product, and the ORDER BY clause sorts these results in descending order.

Q18

Single Choice

What does the following query return in a table with columns department, employee, and salary?

SQL Code

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING total_salary > 100000;

Departments with total salaries greater than 100,000. Employees with salaries greater than 100,000. Departments where at least one employee earns more than 100,000.An error due to the HAVING clause.

The HAVING clause filters the grouped results to include only those departments where the sum of salaries exceeds 100,000.

Q19

Single Choice

How does MySQL handle NULL values when using GROUP BY?

MySQL groups all NULL values together. MySQL ignores rows with NULL values in the grouping column. MySQL throws an error when NULL values are encountered.MySQL replaces NULL with a default value in GROUP BY.

In MySQL, NULL values are treated as a distinct group when using GROUP BY.

Q20

Single Choice

Given the following table orders with columns order_id, customer_id, and order_total, what does the query below accomplish

SQL Code

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING total_orders = (
 SELECT MAX(total_orders)
 FROM (
 SELECT COUNT(order_id) AS total_orders
 FROM orders
 GROUP BY customer_id
 ) AS order_counts
);

It finds the customer(s) with the highest number of orders. It finds customers who have placed exactly one order. It lists all customers with their total order countIt throws an error due to nested queries.

The nested query calculates the maximum number of orders placed by any customer, and the outer query finds the customer(s) who match this maximum order count.

Q21

Multiple Choice

Which query groups the records in the 'employees' table by 'department_id' and calculates the average salary for each department?

SQL Code

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id; SELECT department_id, AVG(salary) FROM employees GROUP BY department_id; SELECT department_id, AVG(salary) AS avg_salary FROM employees;SELECT AVG(salary) AS avg_salary FROM employees GROUP BY department_id;

The query groups the records by 'department_id' and calculates the average salary for each group using the GROUP BY clause.

Q22

Multiple Choice

Identify the correct SQL query that groups records in the 'orders' table by 'order_date' and counts the number of orders for each date.

SQL Code

SELECT order_date, COUNT(order_id) AS order_count
FROM orders
GROUP BY order_date;

SELECT order_date, COUNT(order_id) AS order_count FROM orders GROUP BY order_date; SELECT order_date, COUNT(order_id) FROM orders GROUP BY order_date; SELECT COUNT(order_id) AS order_count FROM orders GROUP BY order_date;SELECT order_date, COUNT(order_id) AS order_count FROM orders;

The GROUP BY clause groups the records by 'order_date', and the COUNT function counts the number of orders for each group.

Q23

Multiple Choice

Which query groups records in the 'products' table by 'category_id' and sums the 'price' for each category?

SQL Code

SELECT category_id, SUM(price) AS total_price
FROM products
GROUP BY category_id;

SELECT category_id, SUM(price) AS total_price FROM products GROUP BY category_id; SELECT category_id, SUM(price) FROM products GROUP BY category_id; SELECT category_id, price AS total_price FROM products GROUP BY category_id;SELECT SUM(price) AS total_price FROM products GROUP BY category_id;

This query groups the records by 'category_id' and calculates the sum of 'price' for each group using the GROUP BY clause.

Q24

Multiple Choice

Determine the correct SQL query that groups records in the 'customers' table by 'country' and finds the maximum 'revenue' for each country.

SQL Code

SELECT country, MAX(revenue) AS max_revenue
FROM customers
GROUP BY country;

SELECT country, MAX(revenue) AS max_revenue FROM customers GROUP BY country; SELECT country, MAX(revenue) FROM customers GROUP BY country; SELECT country, revenue AS max_revenue FROM customers GROUP BY country;SELECT MAX(revenue) AS max_revenue FROM customers GROUP BY country;

The query groups the records by 'country' and calculates the maximum 'revenue' for each group using the GROUP BY clause.

Q25

Multiple Choice

Which SQL query groups records in the 'inventory' table by 'item_code' and counts the distinct 'locations' for each item?

SQL Code

SELECT item_code, COUNT(DISTINCT location) AS location_count
FROM inventory
GROUP BY item_code;

SELECT item_code, COUNT(DISTINCT location) AS location_count FROM inventory GROUP BY item_code; SELECT item_code, COUNT(location) AS location_count FROM inventory GROUP BY item_code; SELECT item_code, DISTINCT location AS location_count FROM inventory GROUP BY item_code;SELECT COUNT(DISTINCT location) AS location_count FROM inventory GROUP BY item_code;

This query groups the records by 'item_code' and counts the distinct 'location' for each group using the GROUP BY clause.

Q26

Multiple Choice

Which query correctly groups records in the 'sales' table by 'sales_rep' and calculates the total 'sales_amount' for each representative?

SQL Code

SELECT sales_rep, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY sales_rep;

SELECT sales_rep, SUM(sales_amount) AS total_sales FROM sales GROUP BY sales_rep; SELECT sales_rep, SUM(sales_amount) FROM sales GROUP BY sales_rep; SELECT sales_rep, total_sales = SUM(sales_amount) FROM sales GROUP BY sales_rep;SELECT sales_rep, SUM(sales_amount) AS total_sales FROM sales;

In this query, the GROUP BY clause is used to group the records by 'sales_rep', and the SUM function calculates the total 'sales_amount' for each representative.

Q27

Multiple Choice

Determine the correct SQL query that groups records in the 'transactions' table by 'transaction_date' and calculates the average 'transaction_amount' for each date.

SQL Code

SELECT transaction_date, AVG(transaction_amount) AS avg_amount
FROM transactions
GROUP BY transaction_date;

SELECT transaction_date, AVG(transaction_amount) AS avg_amount FROM transactions GROUP BY transaction_date; SELECT transaction_date, AVG(transaction_amount) FROM transactions GROUP BY transaction_date; SELECT AVG(transaction_amount) AS avg_amount FROM transactions GROUP BY transaction_date;SELECT transaction_date, AVG(transaction_amount) AS avg_amount FROM transactions;

The GROUP BY clause groups the records by 'transaction_date', and the AVG function calculates the average 'transaction_amount' for each group.

Q28

Multiple Choice

Which SQL query groups records in the 'projects' table by 'project_manager' and finds the minimum 'budget' for each manager?

SQL Code

SELECT project_manager, MIN(budget) AS min_budget
FROM projects
GROUP BY project_manager;

SELECT project_manager, MIN(budget) AS min_budget FROM projects GROUP BY project_manager; SELECT project_manager, MIN(budget) FROM projects GROUP BY project_manager; SELECT project_manager, budget AS min_budget FROM projects GROUP BY project_manager;SELECT MIN(budget) AS min_budget FROM projects GROUP BY project_manager;

This query groups the records by 'project_manager' and calculates the minimum 'budget' for each group using the GROUP BY clause.

Q29

Multiple Choice

Identify the correct query that groups records in the 'clients' table by 'region' and counts the number of distinct 'client_id' for each region.

SQL Code

SELECT region, COUNT(DISTINCT client_id) AS client_count
FROM clients
GROUP BY region;

SELECT region, COUNT(DISTINCT client_id) AS client_count FROM clients GROUP BY region; SELECT region, COUNT(client_id) AS client_count FROM clients GROUP BY region; SELECT COUNT(DISTINCT client_id) AS client_count FROM clients GROUP BY region;SELECT region, DISTINCT COUNT(client_id) AS client_count FROM clients GROUP BY region;

The GROUP BY clause groups the records by 'region', and the COUNT(DISTINCT client_id) function counts the number of unique clients in each region.

Q30

Multiple Choice

Which SQL query correctly groups records in the 'students' table by 'grade' and calculates the total number of students in each grade?

SQL Code

SELECT grade, COUNT(student_id) AS total_students
FROM students
GROUP BY grade;

SELECT grade, COUNT(student_id) AS total_students FROM students GROUP BY grade; SELECT grade, COUNT(student_id) FROM students GROUP BY grade; SELECT grade, total_students = COUNT(student_id) FROM students GROUP BY grade;SELECT grade, COUNT(student_id) AS total_students FROM students;

In this query, the GROUP BY clause groups the records by 'grade', and the COUNT function calculates the total number of students in each grade.

MySQL Database Quiz Questions