Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 10. Sorting and Grouping Data
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
1:
Will the following SQL statements work?
SELECT SUM(SALARY), EMP_ID FROM EMPLOYEE_PAY_TBL GROUP BY 1 and 2;
SELECT EMP_ID, MAX(SALARY) FROM EMPLOYEE_PAY_TBL GROUP BY SALARY, EMP_ID;
SELECT EMP_ID, COUNT(SALARY) FROM EMPLOYEE_PAY_TBL ORDER BY EMP_ID GROUP BY SALARY;
2:
True or false: You must also use the GROUP BY clause when using the HAVING clause.
3:
True or false: The following SQL statement returns a total of the salaries by groups:
SELECT SUM(SALARY) FROM EMPLOYEE_PAY_TBL;
4:
True or false: The columns selected must appear in the GROUP BY clause in the same order.
5:
True or false: The HAVING clause tells the GROUP BY which groups to include.
Exercises
1:
Invoke mysql.exe on your computer, and then type use learnsql; at the mysql> prompt.
2:
Enter the following query at the mysql> prompt to show all cities in EMPLOYEE_TBL:
SELECT CITY FROM EMPLOYEE_TBL;
3:
Now, enter the following query and compare the results to the query in exercise 2:
SELECT CITY, COUNT(*) FROM EMPLOYEE_TBL GROUP BY CITY;
4:
The HAVING clause works like the WHERE clause in that is allows the user to specify conditions on data returned. The WHERE clause is the main filter on the query and the HAVING clause is the filter used after groups of data have been established using the GROUP BY clause. Enter the following query to see how the HAVING clause works:
SELECT CITY, COUNT(*) FROM EMPLOYEE_TBL GROUP BY CITY HAVING COUNT(*) > 1;
5:
Modify the query in exercise 3 to order the results in descending order, from highest count to lowest .
6:
Write a query to list the average pay rate by position from the EMPLOYEE_PAY_TBL table.
7:
Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL table.
