66.
Review the following code snippet. Which line has an error?
1 UPDATE EMPLOYEES 2 WHERE EMPLOYEE_ID = 127 3 SET SALARY = SALARY * 1.25, 4 COMMISSION_PCT = 0
- [A]1
- [B]2
- [C]4
- [D]There is no error
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| In an UPDATE statement, the WHERE clause should come after the SET clause. |
67.
Jim executes the following SQL statement. What will be the result?
DELETE salary, commission_pct FROM employees WHERE department_id = 30;
- [A]The salary and commission_pct columns for all records with department_id 30 are deleted (changed to NULL).
- [B]All the rows belonging to department_id 30 are deleted from the table.
- [C]The salary and commission_pct columns are deleted from the employees table.
- [D]The statement will produce an error.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| When deleting a row from a table, do not use column names. To change column values to NULL, use the UPDATE statement. |
68.
Consider the following three SQL statements. Choose the most appropriate option.
1. DELETE FROM CITY WHERE CNT_CODE = 1; 2. DELETE CITY WHERE CNT_CODE = 1; 3. DELETE (SELECT * FROM CITY WHERE CNT_CODE = 1);
- [A]Statements 1 and 2 will produce the same result, statement 3 will error out.
- [B]Statements 1 and 2 will produce the same result; statement 3 will produce a different result.
- [C]Statements 1, 2, and 3 will produce the same result.
- [D]Statements 1, 2, and 3 will produce different results.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The FROM keyword in the DELETE statement is optional. Statement 3 is first building a subquery with the necessary condition and deleting the rows from the subquery. |
69.
Consider the following code segment. How many rows will be in the CARS table after all these statements are executed?
SELECT COUNT(*) FROM CARS;
COUNT(*)
--------
30
DELETE FROM CARS WHERE MAKE = 'TOYOTA';
2 rows deleted.
SAVEPOINT A;
Savepoint creted.
INSERT INTO CARS VALUES ('TOYOTA','CAMRY',4,220);
1 row created.
SAVEPOINT A;
INSERT INTO CARS VALUES ('TOYOTA','COROLLA',4,180);
1 row created.
ROLLBACK TO SAVEPOINT A;
Rollback complete.
- [A]30
- [B]29
- [C]28
- [D]32
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| When two savepoints are created with the same name, Oracle erases the older savepoint. In the code segment, the DELETE and the first INSERT are not rolled back. |
70.
Jim noticed that the HIRE_DATE and START_DATE columns in the EMPLOYEES table had date
and time values, and hence when he is trying to find employees hired on a certain date, he is
not getting the desired result. Which SQL statement will update all the rows in the EMPLOYEES
table with no time portion in the HIRE_DATE and START_DATE columns (00:00:00).
- [A]UPDATE EMPLOYEES SET HIRE_DATE = TRUNC(HIRE_DATE) AND START_DATE = TRUNC(START_DATE);
- [B]UPDATE TABLE EMPLOYEES SET TRUNC(HIRE_DATE) AND TRUNC(START_DATE);
- [C]UPDATE EMPLOYEES SET HIRE_DATE = TRUNC(HIRE_DATE), START_DATE = TRUNC(START_DATE);
- [D]FUPDATE HIRE_DATE = TRUNC(HIRE_DATE), START_DATE = TRUNC(START_DATE) IN EMPLOYEES;
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| When updating more than one column in a single UPDATE statement, separate the columns by a comma; do not use the AND operator. |
|
|
||||||||||||||||||||||||||||
