Question 36

Explanation

Let's evaluate each of the given statements about the WHERE and HAVING clauses, and identify the three true ones.

True Statements:

1. The HAVING clause conditions can have aggregating functions.
   True – The HAVING clause is specifically designed to filter on aggregated values, like:

   sql
   HAVING COUNT(*) > 5

2. The WHERE clause is used to exclude rows before grouping data.
   True – WHERE filters individual rows before GROUP BY is applied.

3. The HAVING clause is used to exclude one or more aggregated results after grouping data.
   True – HAVING is used after aggregation, for example:

   sql
   GROUP BY department
   HAVING AVG(salary) > 50000

False Statements:

1. The HAVING clause conditions can use aliases for the columns.
   False – You typically cannot use an alias defined in the SELECT clause inside HAVING. You should repeat the full expression or use the aggregate directly.

2. The WHERE and HAVING clauses cannot be used together in a SQL statement.
   False – You can absolutely use both together:

   sql
   SELECT department, COUNT(*) AS total
   FROM employees
   WHERE active = 'Y'
   GROUP BY department
   HAVING COUNT(*) > 10

Where and Having

• WHERE clause executes just after FROM clause.

• WHERE comes before the group by clause

• WHERE cannot use group functions

• WHERE clause restricts few rows from taking part in any other operation like joins, functions etc...

• HAVING comes after the group by clause

• HAVING can use the aggregate / group functions like MAX , MIN , AVG, SUM, etc.

• HAVING clause just hides few rows from your eyes from the result set produced by the query.

• HAVING is the last clause to be executed.

Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.