Test Your MySQL HAVING Clause Knowledge with This Quiz

Progress

True / False

In MySQL, you can select values from a JSON column using the ->> operator.

True False

The ->> operator in MySQL is used to extract values from JSON columns.

True / False

In MySQL, the HAVING clause can only be used with aggregate functions such as COUNT, SUM, and AVG.

True False

The HAVING clause is used to filter results based on aggregate functions.

True / False

The HAVING clause can be used in combination with the WHERE clause in a MySQL query.

True False

The WHERE clause filters rows before grouping, and the HAVING clause filters groups after aggregation.

True / False

In MySQL, you can use HAVING with conditions on columns that are not aggregated.

True False

While the HAVING clause is usually used with aggregate functions, it can also filter based on non-aggregated columns if those columns are included in the GROUP BY clause.

True / False

You can use multiple conditions in the HAVING clause in MySQL, similar to the WHERE clause.

True False

The HAVING clause supports multiple conditions, allowing for complex filtering of aggregated data.

True / False

In MySQL, the HAVING clause can be used to filter rows that do not meet a condition specified in the GROUP BY clause.

True False

The HAVING clause filters the results of aggregated data and does not directly interact with the GROUP BY clause, which determines how data is grouped.

True / False

The HAVING clause can be used to filter results based on the results of subqueries in MySQL.

True False

In MySQL, you can use HAVING to filter results based on the results of subqueries as long as the subquery is part of the aggregate calculations.

True / False

In MySQL, the HAVING clause can be used with complex expressions, including subqueries and joins.

True False

The HAVING clause supports complex expressions, including those involving subqueries and joins, to filter aggregated results.

True / False

In a MySQL query, the HAVING clause is evaluated before the GROUP BY clause.

True False

The HAVING clause is evaluated after the GROUP BY clause. The GROUP BY clause groups the rows first, and then HAVING filters these groups.

Q10

True / False

In MySQL, the HAVING clause is used to filter rows before grouping them.

True False

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

Q11

Single Choice

What is the primary purpose of the HAVING clause in MySQL?

To filter rows after WHERE clause To filter rows after GROUP BY clause To create table aliasesTo order the result set

The HAVING clause is used to filter groups after they have been formed by the GROUP BY clause. It works similarly to the WHERE clause but applies to aggregated data.

Q12

Single Choice

Which of the following SQL statements correctly uses the HAVING clause to filter out groups with a sum less than 100?

SELECT SUM(salary) FROM employees GROUP BY department SELECT department_id, SUM(salary) FROM employees HAVING SUM(salary) < 100; SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) < 100;SELECT department_id, salary FROM employees GROUP BY department_id HAVING salary < 100;

The correct syntax for using the HAVING clause is after the GROUP BY clause. Here, the HAVING clause filters out departments where the total salary is less than 100.

Q13

Single Choice

Which of the following keywords is necessary for using the HAVING clause in MySQL?

ORDER BY JOIN GROUP BYLIMIT

The HAVING clause is used in conjunction with the GROUP BY clause to filter groups of rows based on a condition.

Q14

Single Choice

Given the following query, what will be the output?

SQL Code

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id
HAVING total_employees > 5;

Departments with more than 5 employees Departments with exactly 5 employees Departments with less than 5 employeesAll departments

The HAVING clause filters out the groups where the total number of employees is less than or equal to 5, showing only departments with more than 5 employees.

Q15

Single Choice

Which of the following queries will display departments with an average salary greater than 5000?

SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) > 5000; SELECT department_id FROM employees HAVING AVG(salary) > 5000; SELECT department_id FROM employees WHERE AVG(salary) > 5000;SELECT department_id FROM employees GROUP BY salary HAVING AVG(salary) > 5000;

The HAVING clause correctly filters out groups where the average salary is less than or equal to 5000.

Q16

Single Choice

Which of the following SQL queries will filter out the cities where the total number of orders is exactly 10?

SELECT city, COUNT(order_id) FROM orders GROUP BY city HAVING COUNT(order_id) = 10; SELECT city FROM orders GROUP BY city HAVING order_id = 10; SELECT city FROM orders WHERE COUNT(order_id) = 10;SELECT city, order_id FROM orders GROUP BY city HAVING COUNT(order_id) = 10;

The HAVING clause is used to filter cities where the total number of orders is exactly 10.

Q17

Single Choice

Consider the following SQL query. What does it return?

SQL Code

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > ALL (SELECT MAX(salary) FROM employees WHERE department_id != 1);

Departments where the maximum salary is greater than in any other department except department 1 All departments with the highest salary Only department 1Departments where the maximum salary is less than in department 1

The HAVING clause filters departments where the maximum salary is greater than the maximum salary of any department other than department 1.

Q18

Single Choice

How does the following query behave with the HAVING clause?

SQL Code

SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 1 AND AVG(price) > 100;

Filters categories with more than 1 product and an average price greater than 100 Filters categories with exactly 1 product and an average price greater than 100 Filters products with an average price greater than 100Filters categories with less than 1 product

The HAVING clause applies both conditions to the grouped categories, showing only those that meet both criteria.

Q19

Single Choice

What does the following SQL query return?

SQL Code

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) BETWEEN 20000 AND 50000
ORDER BY SUM(salary) DESC;

Departments where the total salary is between 20000 and 50000, ordered by total salary in descending order Departments with a salary between 20000 and 50000, ordered by department_id Departments with more than 50000 total salaryAll departments

The HAVING clause filters departments where the total salary is within the specified range, and the result is ordered by the total salary in descending order.

Q20

Single Choice

Which of the following queries correctly filters out departments where the number of employees is less than the average number of employees across all departments?

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) < (SELECT AVG(total) FROM (SELECT COUNT(*) as total FROM employees GROUP BY department_id) as subquery); SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) < AVG(total); SELECT department_id FROM employees GROUP BY department_id HAVING AVG(COUNT(*)) < (SELECT AVG(COUNT(*)) FROM employees);SELECT department_id, COUNT(*) FROM employees HAVING COUNT(*) < (SELECT AVG(COUNT(*)) FROM employees);

This query uses a subquery to calculate the average number of employees across all departments and filters out departments with fewer employees using the HAVING clause.

Q21

Multiple Choice

Which query groups the records in the 'employees' table by 'department_id' and filters the groups to include only those with an average salary greater than 50000?

SQL Code

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

SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 50000; SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING salary > 50000; SELECT department_id, AVG(salary) AS avg_salary FROM employees HAVING AVG(salary) > 50000;SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id WHERE AVG(salary) > 50000;

The HAVING clause filters the groups created by the GROUP BY clause to include only those where the average salary is greater than 50000.

Q22

Multiple Choice

Identify the correct SQL query that groups records in the 'orders' table by 'order_date' and filters the groups to include only those with more than 10 orders.

SQL Code

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

SELECT order_date, COUNT(order_id) AS order_count FROM orders GROUP BY order_date HAVING COUNT(order_id) > 10; SELECT order_date, COUNT(order_id) FROM orders GROUP BY order_date HAVING order_count > 10; SELECT order_date, COUNT(order_id) AS order_count FROM orders HAVING COUNT(order_id) > 10;SELECT order_date, COUNT(order_id) AS order_count FROM orders GROUP BY order_date WHERE COUNT(order_id) > 10;

The HAVING clause filters the groups to include only those where the count of orders is greater than 10.

Q23

Multiple Choice

Which query groups records in the 'products' table by 'category_id' and filters the groups to include only those with a total price greater than 1000?

SQL Code

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

SELECT category_id, SUM(price) AS total_price FROM products GROUP BY category_id HAVING SUM(price) > 1000; SELECT category_id, SUM(price) FROM products GROUP BY category_id HAVING price > 1000; SELECT category_id, SUM(price) AS total_price FROM products HAVING SUM(price) > 1000;SELECT category_id, SUM(price) AS total_price FROM products GROUP BY category_id WHERE SUM(price) > 1000;

This query groups the records by 'category_id' and uses the HAVING clause to filter the groups where the total price is greater than 1000.

Q24

Multiple Choice

Determine the correct SQL query that groups records in the 'customers' table by 'country' and filters the groups to include only those with maximum revenue greater than 100000.

SQL Code

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

SELECT country, MAX(revenue) AS max_revenue FROM customers GROUP BY country HAVING MAX(revenue) > 100000; SELECT country, MAX(revenue) FROM customers GROUP BY country HAVING revenue > 100000; SELECT country, MAX(revenue) AS max_revenue FROM customers HAVING MAX(revenue) > 100000;SELECT country, MAX(revenue) AS max_revenue FROM customers GROUP BY country WHERE MAX(revenue) > 100000;

The query groups the records by 'country' and filters the groups where the maximum revenue exceeds 100000.

Q25

Multiple Choice

Which SQL query groups records in the 'inventory' table by 'item_code' and filters the groups to include only those with more than 5 distinct locations?

SQL Code

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

SELECT item_code, COUNT(DISTINCT location) AS location_count FROM inventory GROUP BY item_code HAVING COUNT(DISTINCT location) > 5; SELECT item_code, COUNT(location) AS location_count FROM inventory GROUP BY item_code HAVING location_count > 5; SELECT item_code, COUNT(DISTINCT location) AS location_count FROM inventory HAVING COUNT(DISTINCT location) > 5;SELECT item_code, COUNT(DISTINCT location) AS location_count FROM inventory GROUP BY item_code WHERE COUNT(DISTINCT location) > 5;

This query groups the records by 'item_code' and uses the HAVING clause to filter groups where the count of distinct locations is greater than 5.

Q26

Multiple Choice

Which query correctly groups records in the 'sales' table by 'sales_rep' and filters the groups to include only those with total sales greater than 200000?

SQL Code

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

SELECT sales_rep, SUM(sales_amount) AS total_sales FROM sales GROUP BY sales_rep HAVING SUM(sales_amount) > 200000; SELECT sales_rep, SUM(sales_amount) FROM sales GROUP BY sales_rep HAVING sales_amount > 200000; SELECT sales_rep, total_sales = SUM(sales_amount) FROM sales GROUP BY sales_rep HAVING SUM(sales_amount) > 200000;SELECT sales_rep, SUM(sales_amount) AS total_sales FROM sales GROUP BY sales_rep WHERE SUM(sales_amount) > 200000;

In this query, the HAVING clause is used to filter the groups where the total sales amount exceeds 200000.

Q27

Multiple Choice

Determine the correct SQL query that groups records in the 'transactions' table by 'transaction_date' and filters the groups to include only those with average transaction amount greater than 1000.

SQL Code

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

SELECT transaction_date, AVG(transaction_amount) AS avg_amount FROM transactions GROUP BY transaction_date HAVING AVG(transaction_amount) > 1000; SELECT transaction_date, AVG(transaction_amount) FROM transactions GROUP BY transaction_date HAVING transaction_amount > 1000; SELECT transaction_date, AVG(transaction_amount) AS avg_amount FROM transactions HAVING AVG(transaction_amount) > 1000;SELECT transaction_date, AVG(transaction_amount) AS avg_amount FROM transactions GROUP BY transaction_date WHERE AVG(transaction_amount) > 1000;

The HAVING clause filters the groups to include only those where the average transaction amount exceeds 1000.

Q28

Multiple Choice

Which SQL query groups records in the 'projects' table by 'project_manager' and filters the groups to include only those with minimum budget less than 50000?

SQL Code

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

SELECT project_manager, MIN(budget) AS min_budget FROM projects GROUP BY project_manager HAVING MIN(budget) < 50000; SELECT project_manager, MIN(budget) FROM projects GROUP BY project_manager HAVING budget < 50000; SELECT project_manager, MIN(budget) AS min_budget FROM projects HAVING MIN(budget) < 50000;SELECT project_manager, MIN(budget) AS min_budget FROM projects GROUP BY project_manager WHERE MIN(budget) < 50000;

This query uses the HAVING clause to filter groups where the minimum budget is less than 50000.

Q29

Multiple Choice

Identify the correct query that groups records in the 'clients' table by 'region' and filters the groups to include only those with distinct client count greater than 50.

SQL Code

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

SELECT region, COUNT(DISTINCT client_id) AS client_count FROM clients GROUP BY region HAVING COUNT(DISTINCT client_id) > 50; SELECT region, COUNT(DISTINCT client_id) FROM clients GROUP BY region HAVING client_count > 50; SELECT region, COUNT(DISTINCT client_id) AS client_count FROM clients HAVING COUNT(DISTINCT client_id) > 50;SELECT region, COUNT(DISTINCT client_id) AS client_count FROM clients GROUP BY region WHERE COUNT(DISTINCT client_id) > 50;

The HAVING clause is used to filter the groups where the distinct client count exceeds 50.

Q30

Multiple Choice

Which SQL query correctly groups records in the 'students' table by 'grade' and filters the groups to include only those with more than 100 students?

SQL Code

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

SELECT grade, COUNT(student_id) AS total_students FROM students GROUP BY grade HAVING COUNT(student_id) > 100; SELECT grade, COUNT(student_id) FROM students GROUP BY grade HAVING total_students > 100; SELECT grade, COUNT(student_id) AS total_students FROM students HAVING COUNT(student_id) > 100;SELECT grade, COUNT(student_id) AS total_students FROM students GROUP BY grade WHERE COUNT(student_id) > 100;

In this query, the HAVING clause filters the groups where the student count exceeds 100.

MySQL Database Quiz Questions