Section 8 Quiz Oracle Database Programming with SQL

Section 8 Quiz

            (Answer all questions in this section)

                                                           

1.         The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following?    Mark for Review

(1) Points

            All except numeric

            Only numeric data types (*)

            Integers only

            Any data type

2.         Which aggregate function can be used on a column of the DATE data type?  Mark for Review

(1) Points

            MAX (*)

            STDDEV

            AVG

            SUM

3.         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?

 Mark for Review

(1) Points

                                    (Choose all correct answers)   

            SELECT SUM(order_dt)

            FROM MAX(order_dt)

            SELECT SUM(order_amount) (*)

            WHERE MAX(order_dt) = order_dt

            SELECT MIN(AVG(order_amount)) (*)

4.         Group functions return a value for ________________ and ________________ null values in their computations.  Mark for Review

(1) Points

            a row set, ignore (*)

            each row, include

            each row, ignore

            a row set, include

5.         Which group function would you use to display the lowest value in the SALES_AMOUNT column?            Mark for Review

(1) Points

            COUNT

            MAX

            AVG

            MIN (*)

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);

 Mark for Review

(1) Points

            SUM = 1.85 and COUNT =4

            SUM = 1.85 and COUNT = 6

            SUM = .85 and COUNT = 4

            SUM = .85 and COUNT = 6 (*)

7.         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?

 Mark for Review

(1) Points

            SELECT AVG(price), model

FROM trucks

WHERE model IS '4x4';

            SELECT AVG(price)

FROM trucks

WHERE model IS 4x4;

            SELECT AVG(price)

FROM trucks

WHERE model IS '4x4';

            SELECT AVG(price)

FROM trucks

WHERE model = '4x4';

(*)

8.         You need to compute the total salary amount for all employees in department 10. Which group function will you use?   Mark for Review

(1) Points

            COUNT

            SUM (*)

            VARIANCE

            MAX

9.         Evaluate this SELECT statement:

SELECT COUNT(*)

FROM products;

Which statement is true?

 Mark for Review

(1) Points

            The number of rows in the table is displayed. (*)

            An error occurs due to an error in the SELECT clause.

            An error occurs because no WHERE clause is included in the SELECT statement.

            The number of unique PRODUCT_IDs in the table is displayed.

10.       The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(9)

LAST_NAME VARCHAR2(20)

FIRST_NAME VARCHAR2(20)

SALARY NUMBER(7,2)

DEPARTMENT_ID NUMBER(9)

You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?

 Mark for Review

(1) Points

            SELECT COUNT(*)

FROM employees

WHERE salary < 50000;

            SELECT * FROM employees

WHERE salary < 50000;

            SELECT COUNT(*)

FROM employees

WHERE salary > 50000

GROUP BY employee_id, last_name, first_name, salary, department_id;

            SELECT COUNT(*)

FROM employees

WHERE salary > 50000;

(*)

            SELECT * FROM employees

WHERE salary > 50000;

11.       Evaluate this SQL statement:

SELECT COUNT (amount)

FROM inventory;

What will occur when the statement is issued?

 Mark for Review

(1) Points

            The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)

            The statement will return the total number of rows in the AMOUNT column.

            The statement will replace all NULL values that exist in the AMOUNT column.

            The statement will return the greatest value in the INVENTORY table.

12.       To include null values in the calculations of a group function, you must:       Mark for Review

(1) Points

            Group functions can never use null values

            Convert the null to a value using the NVL( ) function (*)

            Count the number of null values in that column using COUNT

            Precede the group function name with NULL

13.       Which statement about the COUNT function is true?  Mark for Review

(1) Points

            The COUNT function always ignores null values by default. (*)

            The COUNT function can be used to determine the number of unique, non-null values in a column.

            The COUNT function can be used to find the maximum value in each column.

            The COUNT function ignores duplicates by default.

14.       Which SELECT statement will calculate the number of rows in the PRODUCTS table?          Mark for Review

(1) Points

            SELECT ROWCOUNT FROM products;

            SELECT COUNT(products);

            SELECT COUNT (*) FROM products; (*)

            SELECT COUNT FROM products;

15.       Using your existing knowledge of the employees table, would the following two statements produce the same result?

SELECT COUNT(*)

FROM employees;

SELECT COUNT(commission_pct)

FROM employees;

 Mark for Review

(1) Points

            The second statement is invalid

            The first statement is invalid

            Yes

            No (*)

1.         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?

 Mark for Review

(1) Points

            SELECT AVG(price), model

FROM trucks

WHERE model IS '4x4';

            SELECT AVG(price)

FROM trucks

WHERE model IS 4x4;

            SELECT AVG(price)

FROM trucks

WHERE model IS '4x4';

            SELECT AVG(price)

FROM trucks

WHERE model = '4x4';

(*)

2.         Which group function would you use to display the highest salary value in the EMPLOYEES table? Mark for Review

(1) Points

            MIN

            MAX (*)

            AVG

            COUNT

3.         You need to compute the total salary amount for all employees in department 10. Which group function will you use?   Mark for Review

(1) Points

            SUM (*)

            MAX

            VARIANCE

            COUNT

4.         You need to calculate the average salary of employees in each department. Which group function will you use?          Mark for Review

(1) Points

            AVG (*)

            AVERAGE

            MEDIAN

            MEAN

5.         Examine the data in the PAYMENT table:

PAYMENT_ID            CUSTOMER_ID          PAYMENT_DATE      PAYMENT_TYPE            PAYMENT_AMOUNT

86590586        8908090          10-Jun-2003    BASIC 859.00

89453485        8549038          15-Feb-2003    INTEREST       596.00

85490345        5489304          20-Mar-2003   BASIC 568.00

You need to determine the average payment amount made by each customer in January, February, and March of 2003.

Which SELECT statement should you use?

 Mark for Review

(1) Points

            SELECT AVG(payment_amount)

FROM payment

WHERE payment_date

BETWEEN '01-Jan-2003' AND '31-Mar-2003';

(*)

            SELECT AVG(payment_amount)

FROM payment;

            SELECT AVG(payment_amount)

FROM payment

WHERE TO_CHAR(payment_date) IN (Jan, Feb, Mar);

            SELECT SUM(payment_amount)

FROM payment

WHERE payment_date BETWEEN '01-Jan-2003' and '31-Mar-2003';

6.         Which group function would you use to display the lowest value in the SALES_AMOUNT column?            Mark for Review

(1) Points

            MIN (*)

            MAX

            COUNT

            AVG

7.         The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following?    Mark for Review

(1) Points

            Integers only

            Only numeric data types (*)

            Any data type

            All except numeric

8.         Which group function would you use to display the total of all salary values in the EMPLOYEES table?            Mark for Review

(1) Points

            COUNT

            MAX

            AVG

            SUM (*)

9.         Evaluate this SELECT statement:

SELECT COUNT(*)

FROM products;

Which statement is true?

 Mark for Review

(1) Points

            The number of unique PRODUCT_IDs in the table is displayed.

            An error occurs due to an error in the SELECT clause.

            The number of rows in the table is displayed. (*)

            An error occurs because no WHERE clause is included in the SELECT statement.

10.       Evaluate this SQL statement:

SELECT COUNT (amount)

FROM inventory;

What will occur when the statement is issued?

 Mark for Review

(1) Points

            The statement will return the greatest value in the INVENTORY table.

            The statement will replace all NULL values that exist in the AMOUNT column.

            The statement will return the total number of rows in the AMOUNT column.

            The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)

11.       Group functions can avoid computations involving duplicate values by including which keyword?   Mark for Review

(1) Points

            DISTINCT (*)

            SELECT

            UNLIKE

            NULL

12.       The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(9)

LAST_NAME VARCHAR2(20)

FIRST_NAME VARCHAR2(20)

SALARY NUMBER(7,2)

DEPARTMENT_ID NUMBER(9)

You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?

 Mark for Review

(1) Points

            SELECT * FROM employees

WHERE salary > 50000;

            SELECT COUNT(*)

FROM employees

WHERE salary > 50000;

(*)

            SELECT * FROM employees

WHERE salary < 50000;

            SELECT COUNT(*)

FROM employees

WHERE salary > 50000

GROUP BY employee_id, last_name, first_name, salary, department_id;

            SELECT COUNT(*)

FROM employees

WHERE salary < 50000;

13.       Which statement about the COUNT function is true?  Mark for Review

(1) Points

            The COUNT function always ignores null values by default. (*)

            The COUNT function can be used to find the maximum value in each column.

            The COUNT function ignores duplicates by default.

            The COUNT function can be used to determine the number of unique, non-null values in a column.

14.       Which SELECT statement will calculate the number of rows in the PRODUCTS table?          Mark for Review

(1) Points

            SELECT COUNT FROM products;

            SELECT ROWCOUNT FROM products;

            SELECT COUNT (*) FROM products; (*)

            SELECT COUNT(products);

15.       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 

You issue this SELECT statement:

SELECT COUNT(category)

FROM styles;

Which value is displayed?

 Mark for Review

(1) Points

            7 (*)

            6

            The statement will NOT execute successfully.

            0