section 4

-----------------------------------------------------------------------------------------
Group Functions

1. What would the following SQL statement return?
SELECT MAX(hire_date)
FROM employees;

The hire date of the longest serving employee
The hire date of the newest employee (*)
The hire dates of all employees in ascending order
The hire dates of all employees

2. What two group functions can be used with any datatype?
STDDEV, VARIANCE
SUM, AVG
COUNT, SUM
MIN, MAX (*)

3. You can use GROUP functions in all clauses of a SELECT statement. True or False?
True
False (*)

4. 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)

1.2125
This statement is invalid
0.2125 (*)
0.0425

5. The following statement will work even though it uses the same column with different GROUP functions:
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
True or False?

True (*)
False

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(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)

SUM = .85 and COUNT = 6
SUM = 1.85 and COUNT = 6
SUM = .85 and COUNT = 4 (*)
SUM = 1.85 and COUNT = 4

-----------------------------------------------------------------------------------------
Test: Quiz: Count, Distinct, NVL
Count, Distinct, NVL

1. To include null values in the calculations of a group function, you must:
Precede the group function name with NULL
Count the number of null values in that column using COUNT
Convert the null to a value using the NVL( ) function (*)
Group functions can never use null values

2. What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;

A listing of all unique salaries in the employees table
The total number of rows in the employees table
The total amount of salaries in the employees table
The number of unique salaries in the employees table (*)

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

The first statement is invalid
Yes
No (*)
The second statement is invalid

4. What would the following SQL statement return?
SELECT COUNT(first_name)
FROM employees;

A listing of all non-null first names in the employees table
The total number of non-null first names in the employees table (*)
The total number of rows in the employees table
A listing of all unique first names in the employees table