Question No. 13 Which SQL clause filters groups of rows? (A) GROUP BY (B) HAVING (C) ORDER BY (D) WHERE
Answer (1)
The SQL clause that filters groups of rows is the HAVING clause. Therefore, the correct option is (B) HAVING.
To understand why the HAVING clause is used to filter groups of rows, it's important to distinguish between the HAVING and WHERE clauses in SQL:
WHERE Clause : This clause is used to filter rows before any groupings are made with the GROUP BY clause. It applies conditions to individual records.
GROUP BY Clause : This clause is used to divide rows into smaller groups based on the values of specified columns. It's often used with aggregate functions like SUM(), COUNT(), AVG(), etc., to perform calculations on each group of rows.
HAVING Clause : After the rows have been grouped by the GROUP BY clause, the HAVING clause is used to filter these groups based on certain conditions. This is useful when you want to apply conditions to the result of aggregate functions, which cannot be done with the WHERE clause.
For example, consider a table called Sales with columns Product, SalesAmount, and Region. To find products with total sales greater than $1,000, you would write the SQL query as:
SELECT Product, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY Product HAVING SUM(SalesAmount) > 1000;
In this query:
SUM(SalesAmount) AS TotalSales calculates the total sales for each product.
GROUP BY Product groups the rows by products.
HAVING SUM(SalesAmount) > 1000 filters these groups to include only those where the total sales exceed $1,000.
Through this filtering process, the HAVING clause plays a crucial role in SQL queries that involve grouped data.
