This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter is from the book
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
- True or false: The AVG function returns an average of all rows from a SELECT column, including any NULL values.
- True or false: The SUM function adds column totals.
- True or false: The COUNT(*) function counts all rows in a table.
- True or false: The COUNT([column name]) function counts NULL values.
Will the following SELECT statements work? If not, what fixes the statements?
SELECT COUNT * FROM EMPLOYEES;
SELECT COUNT(EMPLOYEEID), SALARY FROM EMPLOYEES;
SELECT MIN(PAYRATE), MAX(SALARY) FROM EMPLOYEES WHERE SALARY > 50000;
SELECT COUNT(DISTINCT EMPLOYEEID) FROM EMPLOYEES;
SELECT AVG(LASTNAME) FROM EMPLOYEES;
SELECT AVG(CAST(ZIP AS INT)) FROM EMPLOYEES;
Exercises
Use the EMPLOYEES table to construct SQL statements to solve the following exercises:
- What is the average salary?
- What is the maximum pay rate for hourly employees?
- What are the total salaries?
- What is the minimum pay rate?
- How many rows are in the table?
- Write a query to determine how many employees are in the company whose last names begin with a G.
- Write a query to determine the minimum and maximum salary and pay rates per city for employees.
- Write two sets of queries to find the first employee name and last employee name when they are listed in alphabetical order.
- Write a query to perform an AVG function on the employee names. Does the statement work? Determine why it is that you got that result.
- Write a query to display the average value of employees’ salaries that takes NULL values into account. Hint: You won’t be using the AVG function.
< Back
Page 4 of 4