08 Introduction to Functions

1.  For each task, choose whether a single-row or multiple row function would be most appropriate:

a. Showing all of the email addresses in upper case letters- single row

b. Determining the average salary for the employees in the sales department- multiple row

c. Showing hire dates with the month spelled out (September 1, 2004)- single row

d. Finding out the employees in each department that had the most seniority (the earliest hire date)- multiple row

e. Displaying the employees’ salaries rounded to the hundreds place- single row

f. Substituting zeros for null values when displaying employee commissions. - Single row

2.  The most common multiple-row functions are: AVG, COUNT, MAX, MIN, and SUM. Give your own definition for each of these functions.

AVG- finds the average value of in group of rows.

COUNT- counts the number of rows input

MAX- finds highest value in the group of rows

MIN- finds lowest value in the group of rows.

SUM- Adds values in group of rows. 

3.  Test your definitions by substituting each of the multiple-row functions into this query.

SELECT FUNCTION(salary)

FROM employees

Write out each query and its results.

SELECT AVG(salary)

FROM employees;

--8775

SELECT COUNT(salary)

FROM employees;

--20

SELECT MAX(salary)

FROM employees;

--24000

SELECT MIN(salary)

FROM employees;

--2500

SELECT SUM(salary)

FROM employees;

--175500