- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
31.
Choose the SQL statement that has no syntax error and is valid.
- A.SELECT department_id, SUM(salary)
FROM employees
WHERE department_id <> 50
GROUP BY department_id
HAVING COUNT(*) > 30; - B.SELECT department_id, SUM(salary) sum_sal
FROM employees
WHERE department_id <> 50
GROUP BY department_id
HAVING sum_sal > 3000; - C.SELECT department_id, SUM(salary) sum_sal
FROM employees
WHERE department_id <> 50
AND sum_sal > 3000
GROUP BY department_id; - D.SELECT department_id, SUM(salary)
FROM employees
WHERE department_id <> 50
AND SUM(salary) > 3000
GROUP BY department_id;
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| It is perfectly all right to have one function in the SELECT clause and another function in the HAVING clause of the query. Options B and C are trying to use the alias name, which is not allowed. Option D has a group function in the WHERE clause, which is not allowed. |
32.
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. |
33.
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 (+);
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. |
34.
The CUSTOMERS and ORDERS tables have the following data:
When the following query is executed, what will be the value of PROD_ID and ORD_DATE
for the customer Abraham Taylor, Jr.?
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 (+);
for the customer Abraham Taylor, Jr.?
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.NULL, 01-JAN-01
- B.NULL, NULL
- C.1001, 02-FEB-00
- D.The query will not return customer Abraham Taylor, Jr.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| When an outer join returns values from a table that does not have corresponding records, a NULL is returned. |
35.
When using ANSI join syntax, which clause is used to specify a join condition?
- A.JOIN
- B.USING
- C.ON
- D.WHERE
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The join condition is specified in the ON clause. The JOIN clause specifies the table to be joined. The USING clause specifies the column names that should be used in the join. The WHERE clause is used to specify additional search criteria to restrict the rows returned. |
|
|
||||||||||||||||||||||||||||
