41.
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.
- 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 number of rows equal to the unique number of first characters in the first_name column. |
42.
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 may 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, statement 2 will not count them. |
43.
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.
- 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. |
44.
Which line of code has an error?
- [A]SELECT dname, ename
- [B]FROM emp e, dept d
- [C]WHERE emp.deptno = dept.deptno
- [D]ORDER BY 1, 2;
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| When table aliases are defined, you should qualify the column names with the table alias only. In this case, the table name cannot be used to qualify column names. The line in option C should read WHERE e.deptno = d.deptno. |
45.
What will be the result of the following query?
SELECT c.cust_id, c.cust_name, o.ord_date, o.prod_id FROM customers c, orders o WHERE c.cust_id = o.cust_id (+);
- [A]List all the customer names in the CUSTOMERS table and the orders they made from the ORDERS table, even if the customer has not placed an order.
- [B]List only the names of customers from the CUSTOMERS table who have placed an order in the ORDERS table.
- [C]List all orders from the ORDERS table, even if there is no valid customer record in the CUSTOMERS table.
- [D]For each record in the CUSTOMERS table, list the information from the ORDERS table.
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| An outer join operator (+) indicates an outer join and is used to display the records, even if there are no corresponding records in the table mentioned on the other side of the operator. Here, the outer join operator is next to the ORDERS table, so even if there are no corresponding orders from a customer, the result set will have the customer ID and name. |
|
|
||||||||||||||||||||||||||||
