Hour 9, Summarizing Data Results from a Query

Team-Fly

---

 

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents
	Appendix C.  Answers to Quizzes and Exercises

---

Hour 9, "Summarizing Data Results from a Query"

Quiz Answers

1:	True or false: The AVG function returns an average of all rows from a select column including any NULL values.
A1:	False. The NULLs are not considered .
2:	True or false: The SUM function is used to add column totals.
A2:	False. The SUM function is used to return a total for a group of rows.
3:	True or false: The COUNT(*) function counts all rows in a table.
A3:	True.
4:	Will the following SELECT statements work? If not, what will fix the statements? SELECT COUNT * FROM EMPLOYEE_PAY_TBL; SELECT COUNT(EMPLOYEE_ID), SALARY FROM EMPLOYEE_PAY_TBL; SELECT MIN(BONUS), MAX(SALARY) FROM EMPLOYEE_PAY_TBL WHERE SALARY > 20000;
A4:	SELECT COUNT * FROM EMPLOYEE_PAY_TBL; This statement will not work because the left and right parentheses are missing around the asterisk. The correct syntax is SELECT COUNT(*) FROM EMPLOYEE_PAY_TBL; SELECT COUNT(EMPLOYEE_ID), SALARY FROM EMPLOYEE_PAY_TBL; Yes, this statement will work. SELECT MIN(BONUS), MAX(SALARY) FROM EMPLOYEE_PAY_TBL WHERE SALARY > 20000; Yes, this statement will work.

Exercise Answers

1:	Use EMPLOYEE_PAY_TBL to construct SQL statements to solve the following exercises: What is the average salary? What is the maximum bonus? What are the total salaries? What is the minimum pay rate? How many rows are in the table?
A1:	What is the average salary? The average salary is $30,000.00. The SQL statement to return the data is SELECT AVG(SALARY) FROM EMPLOYEE_PAY_TBL; What is the maximum bonus? The maximum bonus is $3000.00. The SQL statement to return the data is SELECT MAX(BONUS) FROM EMPLOYEE_PAY_TBL; What is the total of all the salaries? The sum of all the salaries is $60,000.00. The SQL statement to return the data is SELECT SUM(SALARY) FROM EMPLOYEE_PAY_TBL; What is the minimum pay rate? The minimum pay rate is $11.00 an hour. The SQL statement to return the data is SELECT MIN(PAY_RATE) FROM EMPLOYEE_PAY_TBL; What is the total rows in the table? The total row count of the table is six. The SQL statement to return the data is SELECT COUNT(*) FROM EMPLOYEE_PAY_TBL;
2:	How many employees do we have whose last names begin with a G?
A2:	SELECT COUNT(*) FROM EMPLOYEE_TBL WHERE LAST_NAME LIKE 'G%';
3:	If every product cost $10.00, what would be the total dollar amount for all orders?
A3:	SELECT SUM(QTY) * 10 FROM ORDERS_TBL;

---

Team-Fly

Top