36.
Consider the following SQL, 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 modification.
- 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. |
37.
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
- [E]A and C
- Answer & Explanation
- Report
Answer : [E]
Explanation :
Explanation :
| All the queries will return the same result. Since ORDER_NO is the primary key, there cannot be NULL values in the column. Hence, ALL and DISTINCT will give the same result. |
38.
Admin wants to find the highest salary within each department of the EMPLOYEES table.
Which query will help him get what he 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. |
39.
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.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| The first column in the first query is counting the distinct MGR values in the table. The first column in the second query is counting 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 are selecting the maximum salary value in the table. |
40.
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. |
|
|
||||||||||||||||||||||||||||
