PROGRAMMING: Section 4

GROUP FUNCTIONS
You can use GROUP functions in all clauses of a SELECT statement. True or False?
False  

What two group functions can be used with any datatype?
MIN, MAX
          
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
          
The following statement will work because it uses the same column with different GROUP functions:
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
True or False?
True
          
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


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

To include null values in the calculations of a group function, you must:
Convert the null to a value using the NVL( ) function
          
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    

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

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 = 4