Mastering MySQL: HAVING and GROUP BY

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

• To filter rows before grouping them

• To filter groups after aggregation

• To sort the results

• To join tables

Which of the following is an example of an aggregate function in MySQL?

• `SELECT`

• `WHERE`

• `COUNT()`

• `JOIN`

Consider the following SQL query: `SELECT department, COUNT(*) FROM employees GROUP BY department;`. What does this query do?

• It counts the total number of employees

• It counts the number of employees in each department

• It lists all departments

• It lists all employees

Which clause would you use to filter the results of a `GROUP BY` query based on an aggregate function?

• `WHERE`

• `ORDER BY`

• `HAVING`

• `LIMIT`

What is the difference between `WHERE` and `HAVING` clauses in MySQL?

• `WHERE` filters rows before aggregation, `HAVING` filters after

• `WHERE` filters after aggregation, `HAVING` filters before

• Both filter after aggregation

• Both filter before aggregation

Which of the following queries correctly uses `HAVING` with `GROUP BY` to find departments with more than 10 employees?

• `SELECT department FROM employees WHERE COUNT(*) > 10 GROUP BY department;`

• `SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 10;`

• `SELECT department FROM employees GROUP BY department WHERE COUNT(*) > 10;`

• `SELECT department FROM employees HAVING COUNT(*) > 10 GROUP BY department;`

In the query `SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;`, what does `AVG(salary) > 50000` do?

• It filters departments with an average salary less than $50,000

• It filters departments with an average salary greater than $50,000

• It calculates the average salary for all employees

• It lists all departments

Which of the following is NOT an aggregate function in MySQL?

• `SUM()`

• `MAX()`

• `MIN()`

• `JOIN()`

How can you combine `GROUP BY` and `HAVING` to find the total sales for each product that exceed $1000?

• `SELECT product, SUM(sales) FROM sales_data WHERE SUM(sales) > 1000 GROUP BY product;`

• `SELECT product, SUM(sales) FROM sales_data GROUP BY product HAVING SUM(sales) > 1000;`

• `SELECT product, SUM(sales) FROM sales_data GROUP BY product WHERE SUM(sales) > 1000;`

• `SELECT product, SUM(sales) FROM sales_data HAVING SUM(sales) > 1000 GROUP BY product;`

What does the `GROUP BY` clause do in a SQL query?

• It sorts the results

• It groups rows that have the same values in specified columns into summary rows

• It filters rows based on a condition

• It limits the number of rows returned