- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
76.
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);
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.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| 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 num- ber of rows equal to the unique number of first characters in the first_name column. |
77.
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;
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.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| 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, state- ment 2 will not count them. |
78.
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;
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.
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. It cannot use column aliasing. |
79.
What will the following SQL statement return?
select max(prod_pack_size)
from sh.products
where min(prod_weight_class) = 5;
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. |
80.
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;
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. |
|
|
||||||||||||||||||||||||||||
