1. Which assertion about the following queries is true?
SELECT COUNT(DISTINCT mgr), MAX(DISTINCT salary) FROM
emp;
SELECT COUNT(ALL mgr), MAX(ALL salary) FROM emp;
A. They will always return the same numbers in columns
1 and 2.
B. They may return different numbers in column 1 but
will always return the same number in column 2.
C. They may return different numbers in both columns
1 and 2.
D. They will always return the same number in column
1 but may return different numbers in column 2.
2. Which clauses in the SELECT statement can use single-row
functions nested in aggregate functions?
(Choose all that apply.)
A. SELECT
B. ORDER BY
C. WHERE
D. GROUP BY
3. Consider the following two SQL statements.
Choose the most appropriate option.
1. select substr(first_name, 1,1) fn, SUM(salary) FROM
employees GROUP BY first_name;
2. select substr(first_name, 1,1) fn, SUM(salary) FROM
employees GROUP BY substr(first_name, 1,1);
A. Statement 1 and 2 will produce the same result.
B. Statement 1 and 2 will produce different results.
C. Statement 1 will fail.
D. Statement 2 will fail, but statement 1 will succeed.
4. How will the results of the following two SQL statements differ?
Statement 1:
SELECT COUNT(*), SUM(salary) FROM hr.employees;
Statement 2:
SELECT COUNT(salary), SUM(salary) FROM hr.employees;
A. Statement 1 will return one row, and statement 2 may
return more than one row.
B. Both statements will fail because they are missing
a GROUP BY clause.
C. Both statements will return the same results.
D. Statement 2 might return a smaller COUNT value than
statement 1.
5. Why does the following SELECT statement fail?
SELECT colorname Colour, MAX(cost) FROM itemdetail
WHERE UPPER(colorname) LIKE '%WHITE%'
GROUP BY colour HAVING COUNT(*) > 20;
A. A GROUP BY clause cannot contain a column alias.
B. The condition COUNT(*) > 20 should be in the WHERE
clause.
C. The GROUP BY clause must contain the group functions
used in the SELECT list.
D. The HAVING clause can contain only the group functions
used in the SELECT list.
6. 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.
7. 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.
8. 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.
9. 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.
10. Read the following two statements, and choose the best option.
1. A HAVING clause should always appear after the GROUP
BY clause.
2. A 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.
---
Answers:
1. B.
The first column in the first query counts the distinct MGR values in the table. The first column in the second query counts all MGR values in the table. If a manager appears twice, the first query will count her one time, but the second will count her twice. Both the first query and the second query select the maximum salary value in the table.
2. A, B.
A group function is not allowed in GROUP BY or WHERE clauses, whether you use it as nested or not.
3. B.
Both statements are valid. The first statement will produce the number of rows equal to the number of unique first_name values. The second statement will produce the number of rows equal to the unique number of first characters in the first_name column.
4. D.
COUNT(*) will count all rows in the table. COUNT(salary) will count only the number of salary values that appear in the table. If there are any rows with a NULL salary, statement 2 will not count them.
5. A.
A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. It cannot use column aliasing.
6. A.
You cannot place a group function in the WHERE clause. Instead, you should use a HAVING clause.
7. B.
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.
8. D.
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.
9. C.
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.
10. C.
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. ORDER BY clause, if present, follows the GROUP BY clause – ORDER BY is recommended for consistent result order.
