Which two statements are true regarding constraints? (Choose two.)

• A. A constraint is enforced only for an INSERT operation on a table.
• B. A foreign key cannot contain NULL values.
• C. You can have more than one column in a table as part of a primary key.
• D. The column with a UNIQUE constraint can store NULLS .

View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.

Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit
FROM orders o JOIN customers c
USING (customer_id)
WHERE o.order_total > c.credit_limit
FOR UPDATE
ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)

• A. The locks are released only when a COMMIT or ROLLBACK is issued.
• B. It locks only the columns that satisfy the condition in both the tables.
• C. It locks all the rows that satisfy the condition in the statement.
• D. The locks are released after a DML statement is executed on the locked rows.

View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?

• A. SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• B. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);
• C. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• D. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update the EMPLOYEES table as follows:4 ? 4;
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London
(location_id 2100).
-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in location_id 2100 to 1.5 times the average commission of their
department.
You issue the following command:
SQL>UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = 2100),
(salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900,2700,2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700)
What is the outcome?

• A. It generates an error because a subquery cannot have a join condition in an UPDATE statement.
• B. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.
• C. It executes successfully and gives the correct result.
• D. It executes successfully but does not give the correct result.