26.
Read the following two statements, and choose the best option. 1. HAVING clause should always appear after the GROUP BY clause. 2. GROUP BY clause should always appear after the WHERE clause.
- [A]Statement 1 and 2 are false.
- [B]Statement 1 is true, and statement 2 is false.
- [C]Statement 1 is false, and statement 2 is true.
- [D]Statements 1 and 2 are true.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The GROUP BY and HAVING clauses can appear in any order in the SELECT clause. If a WHERE clause is present, it must be before the GROUP BY clause. |
27.
Which statements are true? (Choose all that apply.)
- [A]A group function can be used only if the GROUP BY clause is present
- [B]Group functions along with nonaggregated columns can appear in the SELECT clause as long as a GROUP BY clause and a HAVING clause are present.
- [C]The HAVING clause is optional when the GROUP BY clause is used.
- [D]The HAVING clause and the GROUP BY clause are mutually exclusive; you can use only one clause in a SELECT statement.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The HAVING clause can be used in a SELECT statement only if the GROUP BY clause is present. The optional HAVING clause filters data after the rows are summarized. |
28.
Which clause will generate an error when the following query is executed?
SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id HAVING TRUNC(department_id) > 50;
- [A]The GROUP BY clause, because it is missing the group function.
- [B]The HAVING clause, because single-row functions cannot be used.
- [C]The HAVING clause, because the AVG function used in the SELECT clause is not used in the HAVING clause.
- [D]None of the above. The SQL statement will not return an error.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The HAVING clause filters data after the group function is applied. If an aggregate function is not used in the HAVING clause, the column used must be part of the SELECT clause. |
29.
Why will the following query raise an exception?
select dept_no, avg(distinct salary), count(job) job_count from emp where mgr like 'J%' or abs(salary) > 10 having count(job) > 5 order by 2 desc;
- [A]The HAVING clause cannot contain a group function.
- [B]The GROUP BY clause is missing.
- [C]ABS() is not an Oracle function.
- [D]The query will not raise an exception.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| There is at least one column in the SELECT list that is not a constant or group function, so a GROUP BY clause is mandatory. |
30.
What will the following SQL statement return?
select max(prod_pack_size)
from sh.products
where min(prod_weight_class) = 5;
- [A]An exception will be raised.
- [B]The largest PROD_PACK_SIZE for rows containing PROD_WEIGHT_CLASS of 5 or higher
- [C]The largest PROD_PACK_SIZE for rows containing PROD_WEIGHT_CLASS of 5
- [D]The largest PROD_PACK_SIZE in the SH.PRODUCTS table
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| You cannot place a group function in the WHERE clause. Instead, you should use a HAVING clause. |
|
|
||||||||||||||||||||||||||||
