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