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. (*)

Database programming section 8

1. What two group functions can be used with any datatype?
MIN, MAX (*)

2. The VENDORS table contains these columns:
VENDOR_ID NUMBER Primary Key
NAME VARCHAR2(30)
LOCATION_ID NUMBER
ORDER_DT DATE
ORDER_AMOUNT NUMBER(8,2)
Which two clauses represent valid uses of aggregate functions for this table?

(Choose all correct answers)
SELECT SUM(order_amount) (*)
SELECT MIN(AVG(order_amount)) (*)

3. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA:     (143, 2600, null
    144, 2500, null
    149, 10500, .2
    174, 11000, .3
    176, 8600, .2
    178, 7000, .15)
What is the result of the following statement:
SELECT AVG(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
0.2125(*)

4. The TRUCKS table contains these columns:
TRUCKS:
TYPE VARCHAR2(30)
YEAR DATE
MODEL VARCHAR2(20)
PRICE NUMBER(10)
Which SELECT statement will return the average price for the 4x4 model?
SELECT AVG(price)
FROM trucks
WHERE model = '4x4'; (*)

5. The CUSTOMER table contains these columns:
CUSTOMER_ID NUMBER(9)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(30)
CREDIT_LIMIT NUMBER (7,2)
CATEGORY VARCHAR2(20)
You need to calculate the average credit limit for all the customers in each category. The average should be calculated based on all the rows in the table excluding any customers who have not yet been assigned a credit limit value.
Which group function should you use to calculate this value?
AVG (*)

6. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(salary)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
SUM = .85 and COUNT = 6 (*)

7. You need to calculate the average salary of employees in each department. Which group function will you use?
AVG(*)

8. The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(30)
PROD_CAT VARCHAR2(30)
PROD_PRICE NUMBER(3)
PROD_QTY NUMBER(4)
The following statement is issued:
SELECT AVG(prod_price, prod_qty)
FROM products;
What happens when this statement is issued?
An error occurs. (*)

9. Evaluate this SQL statement:
SELECT COUNT (amount)
FROM inventory;
What will occur when the statement is issued?
The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)

10. Examine the data from the LINE_ITEM table:
LINE_ITEM_ID ORDER_ID PRODUCT_ID PRICE DISCOUNT
890898 847589 848399 8.99 0.10
768385 862459 849869 5.60 0.05
867950 985490 945809 5.60
954039 439203 438925 5.25 0.15
543949 349302 453235 4.50
You query the LINE_ITEM table and a value of 5 is returned. Which SQL statement did you execut
SELECT COUNT(*)
FROM line_item;
(*)

11. What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;
The number of unique salaries in the employees table (*)

12. Group functions can avoid computations involving duplicate values by including which keyword?
DISTINCT(*)

13. What would the following SQL statement return?
SELECT COUNT(first_name)
FROM employees;
The total number of non-null first names in the employees table (*)

14. Which SELECT statement will calculate the number of rows in the PRODUCTS table?
SELECT COUNT (*) FROM products; (*)

15. Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;
Which statement is true?
The number of rows in the table is displayed. (*)

Database programming section 5

1. Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2 (*)

2. If quantity is a number datatype, what is the result of this statement?
SELECT NVL(200/quantity, 'zero') FROM inventory;
The statement fails (*)

3. The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;
False(*)

4. Which of the following General Functions will return the first non-null expression in the expression list?
COALESCE(*)

5. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)

6. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts; (*)

7. Which of the following is a conditional expression used in SQL?
CASE (*)

8. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees

King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan

9. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?
True(*)

10.  The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SQL statement could you use?

SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM employees; (*)

11. Which functions allow you to perform explicit data type conversions?
TO_CHAR, TO_DATE, TO_NUMBER (*)

12. A table has the following definition: EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))

and contains the following rows:

(1001, 'Bob Bevan', '200')
(200, 'Natacha Hansen', null)

Will the folloiwng query work?

SELECT *
FROM employees
WHERE employee_id = manager_id;

Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)

13. All Human Resources data is stored in a table named EMPLOYEES. You have been asked to create a report that displays each employee's name and salary. Each employee's salary must be displayed in the following format: $000,000.00. Which function should you include in a SELECT statement to achieve the desired result?
TO_CHAR (*)

14. You have been asked to create a report that lists all customers who have placed orders of at least $2,500. The report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004 ).
Which statement should you issue?
SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500; (*)

15. Sysdate is 12-May-2004.
You need to store the following date: 7-Dec-89
Which statement about the date format for this value is true?
The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)