61.
What will the salary of employee Arsinoe be at the completion of the following SQL
statements?
UPDATE emp SET salary = 1000 WHERE name = 'Arsinoe'; SAVEPOINT Point_A; UPDATE emp SET salary = salary * 1.1 WHERE name = 'Arsinoe'; SAVEPOINT Point_B; UPDATE emp SET salary = salary * 1.1 WHERE name = 'Berenike'; SAVEPOINT point_C; ROLLBACK TO SAVEPOINT point_b; COMMIT; UPDATE emp SET salary = 1500 WHERE name = 'Arsinoe'; SAVEPOINT point_d; ROLLBACK TO point_d; COMMIT;
- [A]1000
- [B]1100
- [C]1111
- [D]1500
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The final rollback (to point_d) will roll the changes back to just after setting the salary to 1500. |
62.
Which of the following INSERT statements will raise an exception?
- [A]INSERT INTO EMP SELECT * FROM NEW_EMP;
- [B]INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO EMP SELECT * FROM NEW_EMP;
- [C]INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP;
- [D]INSERT ALL WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP;
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| TThe keywords INSERT INTO are required in single-table INSERT statements but are not valid in multiple-table INSERT statements. |
63.
The table ORDERS has 35 rows. The following UPDATE statement updates all 35 rows. Which
is the best option?
UPDATE orders SET ship_date = TRUNC(ship_date) WHERE ship_date != TRUNC(ship_date)
- [A]When all rows in a table are updated, the LOCK TABLE orders IN EXCLUSIVE MODE statement must be executed before the UPDATE statement.
- [B]No other session can query from the table until the transaction ends.
- [C]Since all rows are updated, there is no need for any locking, and hence Oracle does not lock the records.
- [D]The statement locks all the rows until the transaction ends.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| When DML operations are performed, Oracle automatically locks the rows. You can query (read) the rows, but no other DML operation is allowed on those rows. When you read the rows, Oracle constitutes a read-consistent view using the undo segments. |
64.
Which is the best option that describes the following SQL statement?
1. UPDATE countries 2. CNT_NAME = UPPER(CNT_NAME) 3. WHERE country_code BETWEEN 1 and 99;
- [A]The statement is missing the keyword SET, but the statement will work just fine because SET is an optional keyword.
- [B]The BETWEEN operator cannot be used in the WHERE clause used in an UPDATE statement.
- [C]The function UPPER(CNT_NAME) should be changed to UPPER('CNT_NAME').
- [D]The statement is missing keyword SET; hence, the statement will fail.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| You must have the SET keyword in an UPDATE statement. The BETWEEN operator and any other valid operators are allowed in the WHERE clause. |
65.
How many rows will be counted in the last SQL statement that follows?
SELECT COUNT(*) FROM emp; 120 returned INSERT INTO emp (emp_id) VALUES (140); SAVEPOINT emp140; INSERT INTO emp (emp_id) VALUES (141); INSERT INTO emp (emp_id) VALUES (142); INSERT INTO emp (emp_id) VALUES (143); TRUNCATE TABLE employees; INSERT INTO emp (emp_id) VALUES (144); ROLLBACK; SELECT COUNT(*) FROM emp;
- [A]121
- [B]0
- [C]124
- [D]143
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The TRUNCATE statement is DDL and performs an implicit commit. After the TRUNCATE statement on the employees table, there are 124 rows in the emp table. The one row that got inserted was removed when the ROLLBACK statement was executed. |
|
|
||||||||||||||||||||||||||||
