Question

1. Examine the structures of the DEPARTMENT and ASSET tables:

   DEPARTMENT
   -------------------------
   DEPT_ID NUMBER(9) NOT NULL
   DEPT_ABBR VARCHAR2(4)
   DEPT_NAME VARCHAR2(25) NOT NULL
   MGR_ID NUMBER
   
   ASSET
   -----------
   ASSET_ID NUMBER(9) NOT NULL
   ASSET_VALUE FLOAT
   ASSET_DESCRIPTION VARCHAR2(25)
   DEPT_ID NUMBER(9)
   
   The DEPT_ID column of the ASSET table has a FOREIGN KEY constraint referencing the DEPARTMENT table. You attempt to update the ASSET table using 
   this statement:

   UPDATE asset

   SET dept_id =(SELECT dept_id
   FROM department
   WHERE dept_name =(SELECT dept_name
   FROM department
   WHERE dept_abbr = 'FINC')),
   asset_value = 10000
   WHERE asset_id = 2;
   
   Which two statements must be true for this UPDATE statement to execute without generating an error? (Choose two.)

   	A.	An asset with an ASSET_ID value of 2 must exist in the ASSET table.
   	B.	Only one row in the DEPARTMENT table can have a DEPT_ABBR value of FINC.
   	C.	One of the subqueries should be removed because subqueries cannot be nested.
   	D.	Both of the subqueries used in the UPDATE statement must return one and only one non-null value.
   	E.	Only one row in the DEPARTMENT table can have the same DEPT_NAME value as the department with DEPT_ABBR of FINC.

Answer 1

Disambiguating the SQL command first.

UPDATE asset

SET dept_id =(SELECT dept_id
FROM department
WHERE dept_name =(SELECT dept_name
FROM department
WHERE dept_abbr = 'FINC')),
asset_value = 10000
WHERE asset_id = 2;

1. Asset table which have asset_id as 2.
2. Values to set for condition (1) is to put department_id as id which has department_abbr as 'FINC', asset_value as 10000.

Understanding statements

1. An asset with an ASSET_ID value of 2 must exist in the ASSET table.
This is condition on the update statement and not necessarily true. The update command would not do anything if the condition is not met.

2. Only one row in the DEPARTMENT table can have a DEPT_ABBR value of FINC.
This is true. In case it results in multiple rows, the upper subquery would result in multiple DEPT_IDs and would result in error.

3. One of the subqueries should be removed because subqueries cannot be nested.
There is no limit on the number of nested subqueries.

4. Both of the subqueries used in the UPDATE statement must return one and only one non-null value
This is not completely true. The second sub query can return empty result since DEPT_NAME can be null, so it will return single DEPT_ID to be updated with.

5. Only one row in the DEPARTMENT table can have the same DEPT_NAME value as the department with DEPT_ABBR of FINC.
This is correct. If there are multiple such entries, it will result in multiple DEPT_ID and the set command would fail.