- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
71.
Consider the following SQL code, and choose the most appropriate option.
SELECT COUNT(DISTINCT SUBSTR(first_name, 1,1))
FROM employees;
SELECT COUNT(DISTINCT SUBSTR(first_name, 1,1))
FROM employees;
- A.A single-row function nested inside a group function is not allowed.
- B.The GROUP BY clause is required to successfully run this query.
- C.Removing the DISTINCT qualifier will fix the error in the query.
- D.The query will execute successfully without any modifications.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The query will return how many distinct alphabets are used to begin names in the EMPLOYEES table. You can nest a group function inside a single-row function, and vice versa. |
72.
The sales order number ( ORDER_NO ) is the primary key in the table SALES_ORDERS .
Which query will return the total number of orders in the SALES_ORDERS table?
- A.SELECT COUNT(ALL order_no) FROM sales_orders;
- B.SELECT COUNT(DISTINCT order_no) FROM sales_orders;
- C.SELECT COUNT(order_no) FROM sales_orders;
- D.SELECT COUNT(NVL(order_no,0) FROM sales_orders;
- E.All of the above
- F.A and C
- Answer & Explanation
- Report
Answer : [E]
Explanation :
Explanation :
| All the queries will return the same result. Because ORDER_NO is the primary key, NULL values cannot be in the column. Hence, ALL and DISTINCT will give the same result. |
73.
Sheila wants to find the highest salary within each department of the EMPLOYEES table.
Which query will help her get what she wants?
- A.SELECT MAX(salary) FROM employees;
- B.SELECT MAX(salary BY department_id) FROM employees;
- C.SELECT department_id, MAX(salary) max_sal FROM employees;
- D.SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;
- E.SELECT department_id, MAX(salary) FROM employees USING department_id
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| Option A will display the highest salary of all the employees. Options B and E use invalid syntax keywords. Option C does not have a GROUP BY clause. |
74.
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;
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.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| 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. |
75.
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
- Answer & Explanation
- Report
Answer : [A, B]
Explanation :
Explanation :
| A group function is not allowed in GROUP BY or WHERE clauses, whether you use it as nested or not. |
|
|
||||||||||||||||||||||||||||
