- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
26.
Using the SALES table described here, you need to report the following:
-> Gross, net, and earned revenue for the second and third quarters of 1999
-> Gross, net, and earned revenue for sales in the states of Illinois, California, and Texas (codes IL, CA, and TX)
Will all the requirements be met with the following SQL statement?
SELECT state_code, SUM(ALL gross), SUM(net), SUM(earned)
FROM sales_detail
WHERE TRUNC(sales_date,'Q') BETWEEN
TO_DATE('01-Apr-1999','DD-Mon-YYYY')
AND TO_DATE('01-Sep-1999','DD-Mon-YYYY')
AND state_code IN ('IL','CA','TX')
GROUP BY state_code;
-> Gross, net, and earned revenue for the second and third quarters of 1999
-> Gross, net, and earned revenue for sales in the states of Illinois, California, and Texas (codes IL, CA, and TX)
SELECT state_code, SUM(ALL gross), SUM(net), SUM(earned)
FROM sales_detail
WHERE TRUNC(sales_date,'Q') BETWEEN
TO_DATE('01-Apr-1999','DD-Mon-YYYY')
AND TO_DATE('01-Sep-1999','DD-Mon-YYYY')
AND state_code IN ('IL','CA','TX')
GROUP BY state_code;
- A.The statement meets all three requirements.
- B.The statement meets two of the three requirements.
- C.The statement meets one of the three requirements.
- D.The statement meets none of the three requirements.
- E.The statement will raise an exception.
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| All requirements are met. The gross-, net-, and earned-revenue requirements are satisfied with the SELECT clause. The second- and third-quarter sales requirement is satisfied with the first predicate of the WHERE clause—the sales date will be truncated to the first day of a quarter; thus, 01-Apr-1999 or 01-Jul-1999 for the required quarters (which are both between 01-Apr-1999 and 01-Sep-1999). The state codes requirement is satisfied by the second predicate in the WHERE clause. This question is intentionally misleading, but so are some exam questions (and, unfortunately, some of the code in some shops is too). |
27.
Which line in the following SQL has an error?
1 SELECT department_id, SUM(salary) 2 FROM employees 3 WHERE department_id <> 40 4 ORDER BY department_id;
1 SELECT department_id, SUM(salary) 2 FROM employees 3 WHERE department_id <> 40 4 ORDER BY department_id;
- A.1
- B.3
- C.4
- D.No errors in SQL
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| Because the department_id column does not have any aggregate function applied to it, it must appear in the GROUP BY clause. To make the query work, the ORDER BY clause in the SQL instructions must be replaced with a GROUP BY clause. |
28.
John is trying to determine the average salary of employees in each department. He
noticed that the SALARY column can have NULL values, and he does not want the NULLs
included when the average is calculated. Identify the correct SQL statements that will
produce the desired results.
- A.SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id; - B.SELECT department_id, AVG(NVL(salary,0))
FROM employees
GROUP BY department_id; - C.SELECT department_id, NVL(AVG(salary), 0)
FROM employees
GROUP BY department_id; - D.SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING salary IS NOT NULL;
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| Because group functions do not include NULL values in their calculations, you do not have to do anything special to exclude the NULL values. Only COUNT(*) includes NULL values. |
29.
Review the following two SQL statements, and choose the appropriate option.
1. SELECT department_id, COUNT(*)
FROM employees
HAVING COUNT(*) > 10
GROUP BY department_id;
2. SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 10
GROUP BY department_id;
- A.Statement 1 and statement 2 will produce the same results.
- B.Statement 1 will succeed, and statement 2 will fail.
- C.Statement 2 will succeed, and statement 1 will fail.
- D.Both statements fail.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| An aggregate function is not allowed in the WHERE clause. You can have the GROUP BY and HAVING clauses in any order, but they must appear after the WHERE clause. |
30.
Carefully read the following SQL instructions, and choose the appropriate option. The
JOB_ID column shows the various jobs.
SELECT MAX(COUNT(*))
FROM employees
GROUP BY job_id, department_id;
SELECT MAX(COUNT(*))
FROM employees
GROUP BY job_id, department_id;
- A.Aggregate functions cannot be nested.
- B.The columns in the GROUP BY clause must appear in the SELECT clause for the query to work.
- C.The GROUP BY clause is not required in this query.
- D.The SQL code will produce the highest number of jobs within a department.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The SQL code will work fine and produce the desired result. Because group functions are nested, a GROUP BY clause is required. |
|
|
||||||||||||||||||||||||||||
