Hour 10, Sorting and Grouping Data

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 10, "Sorting and Grouping Data"

Quiz Answers

1:

Will the following SQL statements work?

  1.  SELECT SUM(SALARY), EMP_ID  FROM EMPLOYEE_PAY_TBL GROUP BY 1 and 2; 
  2.  SELECT EMP_ID, MAX(SALARY)  FROM EMPLOYEE_PAY_TBL GROUP BY SALARY, EMP_ID; 
  3.  SELECT EMP_ID, COUNT(SALARY)  FROM EMPLOYEE_PAY_TBL ORDER BY EMP_ID GROUP BY SALARY; 
A1:
  1.  SELECT SUM(SALARY), EMP_ID  FROM EMPLOYEE_PAY_TBL GROUP BY 1 and 2; 

    No, this statement will not work. The and in the GROUP BY clause does not belong there, and you cannot use an integer in the GROUP BY clause. The correct syntax is

     SELECT SUM(SALARY), EMP_ID  FROM EMPLOYEE_PAY_TBL GROUP BY SALARY, EMP_ID; 
  2.  SELECT EMP_ID, MAX(SALARY)  FROM EMPLOYEE_PAY_TBL GROUP BY SALARY, EMP_ID; 

    Yes, this statement will work.

  3.  SELECT EMP_ID, COUNT(SALARY)  FROM EMPLOYEE_PAY_TBL ORDER BY EMP_ID GROUP BY SALARY; 

    No, this statement will not work. The ORDER BY clause and the GROUP BY clause are not in the correct sequence. Also, the EMP_ID column is required in the GROUP BY clause The correct syntax is

     SELECT EMP_ID, COUNT(SALARY)  FROM EMPLOYEE_PAY_TBL GROUP BY EMP_ID ORDER BY EMP_ID; 
2:

True or false: You must also use the GROUP BY clause when using the HAVING clause.

A2:

False. The HAVING clause can be used without a GROUP BY clause.

3:

True or false: The following SQL statement returns a total of the salaries by groups:

 SELECT SUM(SALARY)  FROM EMPLOYEE_PAY_TBL; 
A3:

False. The statement cannot return a total of the salaries by groups because there is no GROUP BY clause.

4:

True or false: The columns selected must appear in the GROUP BY clause in the same order.

A4:

False. The order of the columns in the SELECT clause can be in a different order in the GROUP BY clause.

5:

True or false: The HAVING clause tells the GROUP BY which groups to include.

A5:

True.

Exercise Answers

1:

Invoke mysql.exe on your computer, and then type use learnsql; at the mysql> prompt.

A1:

N/A

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 .

A5:
 SELECT CITY, COUNT(*)  FROM EMPLOYEE_TBL GROUP BY CITY ORDER BY 2 DESC; 
6:

Write a query to list the average pay rate by position from the EMPLOYEE_PAY_TBL table.

A6:
 SELECT POSITION, AVG(PAY_RATE)  FROM EMPLOYEE_PAY_TBL GROUP BY POSITION; 
7:

Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL table.

A7:
 SELECT POSITION, AVG(SALARY)  FROM EMPLOYEE_PAY_TBL GROUP BY POSITION; 

Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net