Database programming section 12

1. To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?
False (*)

2. If you are performing an UPDATE statement with a subquery, it MUST be a correlated subquery? (True or False)
False (*)

3. DELETE statements can use correlated subqueries? (True or False)
True (*)

4. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
You need to increase the salary for all employees in department 10 by 10 percent. You also need to increase the bonus for all employees in department 10 by 15 percent. Which statement should you use?
UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10; (*)

5. Assuming there are no Foreign Keys on the EMPLOYEES table, if the following subquery returns one row, how many rows will be deleted from the EMPLOYEES table?
DELETE FROM employees
WHERE department_id =
     (SELECT department_id
     FROM departments
     WHERE department_name LIKE '%Public%');

All the rows in the EMPLOYEES table with department_ids matching the department_id returned by the subquery. (*)

6. If a default value was set for a null column, Oracle sets the column to the default value. However, if no default value was set when the column was created, Oracle inserts an empty space. True or False?

False (*)

7. Multi-table inserts are used when the same source data should be inserted into _____________ target table.
More than one (*)

8. In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________.
A bad idea. The default value must match the DATE datatype of the column. (*)

9. Using MERGE accomplishes an __________ and __________ simultaneously.
INSERT; UPDATE (*)

10. Aliases can be used with MERGE statements. True or False?
True (*)

11. The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL
Which INSERT statement will execute successfully?
INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)

12. You need to add a row to an existing table. Which DML statement should you use?
INSERT (*)

13. Insert statements can be combined with subqueries to create more than one row per statement. True or False?
True (*)

14. DML is an acronym that stands for:
Data Manipulation Language (*)

15. If the employees table has 7 rows, how many rows are inserted into the copy_emps table with the following statement:
INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
7 rows, as no WHERE-clause restricts the rows returned on the subquery. (*)