Test Your Oracle UPDATE Statement Knowledge

Progress

True / False

The UPDATE statement in Oracle is used to modify existing rows in a table.

True False

The UPDATE statement is used to change the values of existing rows in a table

True / False

In Oracle, you must specify a WHERE clause in the UPDATE statement to modify rows.

True False

While it is recommended to use a WHERE clause to specify which rows to update, it is not mandatory. Without a WHERE clause, all rows in the table will be updated.

True / False

The SET clause in the UPDATE statement is used to specify the new values for the columns being updated.

True False

The SET clause is used to define the new values for one or more columns in the rows that meet the criteria of the WHERE clause.

True / False

In Oracle, you can use subqueries in the SET clause of an UPDATE statement.

True False

Subqueries can be used in the SET clause to assign values from another table or a derived result.

True / False

In Oracle, the UPDATE statement can modify multiple columns in a single operation.

True False

The UPDATE statement can modify multiple columns by specifying each column and its new value in the SET clause.

True / False

The UPDATE statement in Oracle cannot be used to update rows in a view.

True False

The UPDATE statement can be used to update rows in a view, provided the view is updatable.

True / False

In Oracle, you can use the RETURNING clause in an UPDATE statement to retrieve values from the updated rows.

True False

The RETURNING clause allows you to return values from the updated rows, which can be useful for retrieving the new values or other information.

True / False

In Oracle, an UPDATE statement can be part of a PL/SQL block and can include procedural logic.

True False

The UPDATE statement can be included in a PL/SQL block, allowing for procedural logic to be applied before, during, or after the update.

True / False

In Oracle, updating a primary key column is allowed without any restrictions.

True False

Updating a primary key column can have restrictions, especially if the primary key is referenced by foreign key constraints in other tables. Referential integrity must be maintained.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on optimizing the UPDATE statement for performance and understanding its interaction with indexes and triggers.

True False

The OCP certification covers advanced topics, including how to optimize the UPDATE statement for performance and understanding how it interacts with indexes and triggers, reflecting the candidate's comprehensive knowledge of Oracle database management.

Q11

Single Choice

What is the primary purpose of the UPDATE statement in Oracle?

To insert new records into a table. To delete records from a table. To modify existing records in a table.To create a new table.

The UPDATE statement in Oracle is used to modify existing records in a table.

Q12

Single Choice

Which of the following is the correct syntax for updating the SALARY of an employee with ID = 1 in the EMPLOYEES table to 5000?

UPDATE EMPLOYEES SET SALARY = 5000 WHERE ID = 1; MODIFY EMPLOYEES SET SALARY = 5000 WHERE ID = 1; CHANGE EMPLOYEES SET SALARY = 5000 WHERE ID = 1;ALTER EMPLOYEES SET SALARY = 5000 WHERE ID = 1;

The correct syntax for updating a record in Oracle is UPDATE table_name SET column_name = value WHERE condition.

Q13

Single Choice

In Oracle, what will happen if you execute an UPDATE statement without a WHERE clause?

Only the first matching record will be updated. An error will occur. All records in the table will be updated.No records will be updated.

If an UPDATE statement is executed without a WHERE clause, all records in the table will be updated.

Q14

Single Choice

How can you update multiple columns in a single UPDATE statement in Oracle?

UPDATE EMPLOYEES SET SALARY = 5000, DEPARTMENT_ID = 10 WHERE ID = 1; UPDATE EMPLOYEES SET SALARY AND DEPARTMENT_ID = 5000, 10 WHERE ID = 1; UPDATE EMPLOYEES SET SALARY = 5000 SET DEPARTMENT_ID = 10 WHERE ID = 1;UPDATE EMPLOYEES MODIFY SALARY = 5000, DEPARTMENT_ID = 10 WHERE ID = 1;

To update multiple columns in a single UPDATE statement, use SET column1 = value1, column2 = value2 with the correct syntax as shown in option A.

Q15

Single Choice

What will happen if the UPDATE statement in Oracle tries to modify a record to violate a UNIQUE constraint?

SQL Code

CREATE TABLE EMPLOYEES (
 ID NUMBER PRIMARY KEY,
 EMAIL VARCHAR2(100) UNIQUE,
 SALARY NUMBER
);

UPDATE EMPLOYEES
SET EMAIL = 'duplicate@example.com'
WHERE ID = 2;

The update will succeed, and the duplicate value will be allowed. The update will fail, and an error will be thrown. The update will proceed, but the EMAIL will be set to NULL.Oracle will ignore the constraint and complete the update.

If an UPDATE statement tries to modify a record in a way that violates a UNIQUE constraint, Oracle will raise an error, and the update will fail.

Q16

Single Choice

How can you update a record in Oracle to set the LAST_MODIFIED column to the current date and time?

UPDATE EMPLOYEES SET LAST_MODIFIED = SYSDATE WHERE ID = 1; UPDATE EMPLOYEES SET LAST_MODIFIED = CURRENT_TIMESTAMP WHERE ID = 1; UPDATE EMPLOYEES SET LAST_MODIFIED = NOW() WHERE ID = 1;UPDATE EMPLOYEES SET LAST_MODIFIED = DATE() WHERE ID = 1;

In Oracle, the SYSDATE function is used to get the current date and time, which can be set in an UPDATE statement.

Q17

Single Choice

Consider the following Oracle SQL code. What will be the result of executing the UPDATE statement?

SQL Code

CREATE TABLE ORDERS (
 ORDER_ID NUMBER PRIMARY KEY,
 STATUS VARCHAR2(20),
 SHIP_DATE DATE
);

UPDATE ORDERS
SET SHIP_DATE = SYSDATE
WHERE STATUS = 'Shipped';

The STATUS column will be set to 'Shipped' for all records. The SHIP_DATE column will be set to the current date for all records with STATUS = 'Shipped'. An error will occur because SYSDATE cannot be used in an UPDATE statement.No records will be updated.

The SHIP_DATE column will be updated to the current date (SYSDATE) for all records where the STATUS is 'Shipped'.

Q18

Single Choice

In Oracle, how can you use a subquery in an UPDATE statement to set the SALARY of an employee to the average salary of their department?

UPDATE EMPLOYEES SET SALARY = (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) UPDATE EMPLOYEES SET SALARY = (SELECT AVG(SALARY) FROM EMPLOYEES) WHERE DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID AND ID = 1; UPDATE EMPLOYEES SET SALARY = AVG(SALARY) WHERE DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID AND ID = 1;UPDATE EMPLOYEES SET SALARY = (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) WHERE ID = 1;

The subquery calculates the average salary for the department, and the UPDATE statement sets the SALARY of the specified employee to this value.

Q19

Single Choice

What is the effect of using the RETURNING clause in an Oracle UPDATE statement?

SQL Code

UPDATE EMPLOYEES
SET SALARY = 6000
WHERE ID = 1
RETURNING SALARY INTO :new_salary;

It returns the old value of the SALARY before the update. It updates the SALARY and returns the new value into a variable. It rolls back the update if the condition is not met.It prevents the UPDATE from being committed.

The RETURNING clause in Oracle allows the UPDATE statement to return the new value of the column after the update into a variable.

Q20

Single Choice

In Oracle, which of the following is true about updating records in a table that has triggers defined?

Triggers are automatically disabled during an UPDATE. Triggers can be fired before or after the UPDATE statement is executed. Triggers prevent the UPDATE statement from executing.Triggers can only be used with INSERT statements.

Triggers in Oracle can be set to fire before or after an UPDATE statement, allowing custom logic to be executed as part of the update operation.

Q21

Multiple Choice

Identify the SQL scripts that correctly perform a basic UPDATE operation on a single row in Oracle.

SQL Code

You need to update the salary of an employee with employee_id 101 to 60000 in the 'employees' table.

Which of the following SQL scripts correctly implement this scenario?

UPDATE employees SET salary = 60000 WHERE employee_id = 101; UPDATE employees SET salary = 60000 WHERE employee_id = '101'; UPDATE employees SET salary = 60000 WHERE employee_id = 101 AND salary > 50000;UPDATE employees SET salary = 60000 WHERE employee_id = 101 OR salary > 50000;

The UPDATE statement in Oracle is used to modify existing records in a table. The correct scripts will demonstrate how to update the salary of a specific employee.

Q22

Multiple Choice

Select the SQL scripts that correctly perform an UPDATE operation with a JOIN in Oracle.

SQL Code

You need to update the 'employees' table by setting the 'department_name' based on the 'departments' table using a JOIN on 'department_id'.

Which of the following SQL scripts correctly implement this scenario?

UPDATE employees e SET e.department_name = (SELECT d.department_name FROM departments d WHERE e.department_id = d.department_id); UPDATE employees SET department_name = (SELECT department_name FROM departments WHERE departments.department_id = employees.department_id); UPDATE employees e SET e.department_name = d.department_name FROM departments d WHERE e.department_id = d.department_id;MERGE INTO employees e USING departments d ON (e.department_id = d.department_id) WHEN MATCHED THEN UPDATE SET e.department_name = d.department_name;

Updating a table with values from another table can be done using a JOIN in Oracle. The correct scripts will demonstrate how to perform an UPDATE operation using a JOIN clause.

Q23

Multiple Choice

Determine the SQL scripts that correctly handle conditional UPDATE operations in Oracle.

SQL Code

You need to update the 'projects' table to increase the budget by 10% for projects with a start date earlier than 2023.

Which of the following SQL scripts correctly implement this scenario?

UPDATE projects SET budget = budget * 1.10 WHERE start_date < TO_DATE('01-JAN-2023', 'DD-MON-YYYY'); UPDATE projects SET budget = budget * 1.10 WHERE start_date < '2023-01-01'; UPDATE projects SET budget = budget + (budget * 0.10) WHERE start_date < '01-JAN-2023';UPDATE projects SET budget = budget * 1.10 WHERE start_date < DATE '2023-01-01';

Conditional UPDATE operations in Oracle involve specifying a WHERE clause to filter the rows to be updated. The correct scripts will demonstrate how to conditionally update rows based on a date comparison.

Q24

Multiple Choice

Identify the SQL scripts that correctly perform an UPDATE operation with a subquery in Oracle.

SQL Code

You need to update the 'employees' table by setting the 'manager_id' based on the highest salary within the same department.

Which of the following SQL scripts correctly implement this scenario?

UPDATE employees e SET manager_id = (SELECT manager_id FROM employees WHERE department_id = e.department_id AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id)); UPDATE employees SET manager_id = (SELECT manager_id FROM employees WHERE department_id = employees.department_id AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = employees.department_id)); UPDATE employees e SET e.manager_id = (SELECT manager_id FROM employees WHERE department_id = e.department_id AND salary = MAX(salary));UPDATE employees SET manager_id = (SELECT manager_id FROM employees WHERE department_id = employees.department_id AND salary = MAX(salary)) WHERE employee_id IN (SELECT employee_id FROM employees);

Using subqueries within an UPDATE statement allows for complex updates based on aggregated or related data. The correct scripts will demonstrate how to use a subquery to update the 'manager_id' column.

Q25

Multiple Choice

Select the SQL scripts that correctly perform a bulk UPDATE operation in Oracle.

SQL Code

You need to update the 'products' table to set the 'discount' column to 5% for all products in the 'Electronics' category.

Which of the following SQL scripts correctly implement this scenario?

UPDATE products SET discount = 0.05 WHERE category = 'Electronics'; UPDATE products SET discount = 0.05 WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics'); UPDATE products SET discount = 0.05 WHERE category_name = 'Electronics';UPDATE products p SET p.discount = 0.05 WHERE p.category = 'Electronics';

Bulk UPDATE operations in Oracle are used to modify multiple rows at once based on certain criteria. The correct scripts will demonstrate how to perform a bulk update on the 'products' table.

Q26

Multiple Choice

Determine the SQL scripts that correctly handle an UPDATE operation with error handling in Oracle.

SQL Code

You need to update the 'orders' table to set the 'order_status' to 'Shipped' but handle any errors that occur during the operation.

Which of the following SQL scripts correctly implement this scenario?

BEGIN UPDATE orders SET order_status = 'Shipped' WHERE order_date < SYSDATE - 7; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the update operation.'); END; UPDATE orders SET order_status = 'Shipped' WHERE order_date < SYSDATE - 7; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the update operation.'); BEGIN UPDATE orders SET order_status = 'Shipped' WHERE order_date < SYSDATE - 7; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Duplicate value error occurred during the update operation.'); END;UPDATE orders SET order_status = 'Shipped' WHERE order_date < SYSDATE - 7; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the update operation.');

Error handling during an UPDATE operation can be managed using PL/SQL exception handling. The correct scripts will demonstrate how to catch and handle errors during an UPDATE operation.

Q27

Multiple Choice

Identify the SQL scripts that correctly perform an UPDATE operation using the RETURNING clause in Oracle.

SQL Code

You need to update the 'inventory' table to increase the 'stock_quantity' and return the updated values for each affected row.

Which of the following SQL scripts correctly implement this scenario?

UPDATE inventory SET stock_quantity = stock_quantity + 10 RETURNING stock_quantity INTO :new_stock_quantity; UPDATE inventory SET stock_quantity = stock_quantity + 10 RETURNING stock_quantity INTO :stock_quantity; UPDATE inventory SET stock_quantity = stock_quantity + 10 RETURNING stock_quantity;UPDATE inventory SET stock_quantity = stock_quantity + 10; SELECT stock_quantity INTO :new_stock_quantity FROM inventory;

The RETURNING clause in Oracle allows you to return values from the rows that are being updated. The correct scripts will demonstrate how to use the RETURNING clause with an UPDATE statement.

Q28

Multiple Choice

Select the SQL scripts that correctly perform an UPDATE operation on a table with a BLOB column in Oracle.

SQL Code

You need to update the 'documents' table to replace the contents of a BLOB column 'doc_content' with a new file for a specific document.

Which of the following SQL scripts correctly implement this scenario?

UPDATE documents SET doc_content = :new_file WHERE doc_id = 101; UPDATE documents SET doc_content = EMPTY_BLOB() WHERE doc_id = 101; UPDATE documents SET doc_content = :new_file WHERE doc_id = 101 RETURNING doc_content INTO :updated_blob;UPDATE documents SET doc_content = EMPTY_BLOB() WHERE doc_id = 101; RETURNING doc_content INTO :updated_blob;

Updating a BLOB column in Oracle often requires binding the binary data to the BLOB column. The correct scripts will demonstrate how to perform an update operation on a BLOB column.

Q29

Multiple Choice

Determine the SQL scripts that correctly perform a complex UPDATE operation with multiple columns in Oracle.

SQL Code

You need to update both the 'salary' and 'job_title' columns in the 'employees' table for employees in the 'Sales' department.

Which of the following SQL scripts correctly implement this scenario?

UPDATE employees SET salary = salary * 1.05, job_title = 'Senior Sales Rep' WHERE department = 'Sales'; UPDATE employees SET (salary, job_title) = (salary * 1.05, 'Senior Sales Rep') WHERE department = 'Sales'; UPDATE employees SET salary = salary * 1.05 AND job_title = 'Senior Sales Rep' WHERE department = 'Sales';UPDATE employees SET salary = salary * 1.05, job_title = 'Senior Sales Rep' WHERE department_name = 'Sales';

Updating multiple columns in a single UPDATE statement is common in Oracle. The correct scripts will demonstrate how to update both the 'salary' and 'job_title' columns simultaneously.

Q30

Multiple Choice

Select the SQL scripts that correctly perform an UPDATE operation with a sequence in Oracle.

SQL Code

You need to update the 'transactions' table to assign a new sequence-generated value to the 'transaction_id' column for each existing record.

Which of the following SQL scripts correctly implement this scenario?

UPDATE transactions SET transaction_id = transaction_seq.NEXTVAL; UPDATE transactions SET transaction_id = (SELECT transaction_seq.NEXTVAL FROM dual); UPDATE transactions SET transaction_id = (SELECT transaction_seq.CURRVAL FROM dual);UPDATE transactions SET transaction_id = transaction_seq.CURRVAL;

Using a sequence during an UPDATE operation allows for assigning unique values to a column. The correct scripts will demonstrate how to update the 'transaction_id' column with a sequence in Oracle.

Oracle Database Quiz Questions