Answer to Chapter 9 of O'Reilly Learning Sql on SQL Server 2005

 

9.8. Review Questions
1. What do aggregate functions do?
An aggregate function is one that extracts information such as a COUNT of rows or an average,

minimum, or maximumby operating on multiple rows.

2. How does the GROUP BY clause work?
GROUP BY is used in conjunction with aggregate functions to group data on the basis of the

same values in a column. GROUP BY returns one row for each value of the column(s) that is

grouped.

3. What is the difference between a GROUP BY and ORDER BY?
GROUP BY is used group data on the basis of the same values in a column.
ORDER BY is used to change the sequence of result rows.

4. What is the HAVING clause used for?
The HAVING clause is used as a final filter (rather than as a conditional filter) on the

aggregate column values in the result set of a SELECT statement.

5. Can the WHERE clause always be considered a substitute for the HAVING clause? Why or

why not?
No. WHERE clause could not filter the aggregation result.

6. Do functions of functions have to be handled in a special way in Server SQL 2005?

 

7. Will nulls in grouped columns be included in a result set?
COUNT (*) counts all the rows. But most aggregate or SET operation ignore the null.

8. How do aggregate functions treat nulls?
aggregate functions ignore the null values except count().

9. Does the sequence of the columns in a GROUP BY clause have an effect on the end

result?
Only the row sequence will be changed.

10. When would it not make sense to use the GROUP BY and DISTINCT functions together?
GROUP BY and DISTINCT generate the same result if there is not aggregate functions.

11. Is GROUP BY affected by nulls?
If the grouping column contains a null value, that row becomes a group in the results. If the

grouping column contains more than one null value, the null values are put into a single

group.

12. Which comes first in a SELECT statement, an ORDER BY or GROUP BY? Why?
Group by comes first

13. The GROUP BY and ________________ clauses are used together.
HAVING

9.9. Exercises
Unless specified otherwise, use the Student_course database to answer the following questions.

Also, use appropriate column headings when displaying your output.
1. Display a list of courses (course names) that have prerequisites and the number of

prerequisites for each course. Order the list by the number of prerequisites.
SELECT c.COURSE_NAME, pc.COURSE_NUMBER, pc.Prcount
FROM Course as c,
(SELECT COURSE_NUMBER, COUNT(PREREQ) as Prcount
From Prereq
GROUP BY COURSE_NUMBER
) as pc
WHERE pc.COURSE_NUMBER = c.COURSE_NUMBER
ORDER BY pc.Prcount

2. How many juniors (class = 3) are there in the Student table?
SELECT COUNT(*)
FROM Student
WHERE CLASS = 3

3. Group and count all MATH majors by class and display the count if there are two or

more in a class. (Remember that class here refers to freshman, sophomore, and so on and is

recorded as 1, 2, and so on.)
SELECT  CLASS, COUNT(*)
FROM Student
WHERE MAJOR = 'MATH'
GROUP BY CLASS
HAVING COUNT(*) >= 2

4. Print the counts of As, Bs, and so on from the Grade_report table.
SELECT  GRADE, COUNT(*)
FROM Grade_report
GROUP BY GRADE

a. Using temporary tables (local or global), print the minimum counts of the grades (that

is, if there were 20 As, 25 Bs, and 18 Cs, you should print the minimum count of grades as C)

from the Grade_report table.
SELECT  GRADE, COUNT(*) as gcnt INTO #Temp11
FROM Grade_report
GROUP BY GRADE

SELECT *
FROM #Temp11 as t
WHERE t.gcnt = ( SELECT MIN(gcnt)
 FROM #Temp11)

b. Using inline views, print the maximum counts of the grades (that is, if there were 20

As, 25 Bs, and 18 Cs, you should print the maximum count of grades as B) from the Grade_report

table.
SELECT  GRADE, COUNT(*)
FROM Grade_report
GROUP BY GRADE
HAVING COUNT(*) = ( SELECT MAX(gcnt)
 FROM (SELECT  GRADE, COUNT(*) as gcnt
FROM Grade_report
GROUP BY GRADE) as in_view
)

c. Why would you not want to use views for this problem?
It is not nessary to create a view for a simple query.

the simplese solution:
SELECT  TOP 1 GRADE, COUNT(*)
FROM Grade_report
GROUP BY GRADE
ORDER BY COUNT(*) DESC

5. Print the counts of course numbers offered in descending order by count. Use the

Section table only.
SELECT YEAR, COUNT(*) YCNT
FROM Section
GROUP BY YEAR
ORDER BY COUNT(*) DESC

6. Create a table with names and number-of-children (NOC). Populate the table with five

or six rows. Use COUNT, SUM, AVG, MIN, and MAX on the NOC attribute in one query and confirm

that the numbers you get are what you expect.
Take care the null value

 

7. Create a table of names, salaries and job locations. Populate the table with at least

10 rows and no fewer than three job locations. (There will be several employees at each

location.) Find the average salary for each job location with one SELECT.

CREATE TABLE CH9_7
(
name VARCHAR(10),
Salary float,
Location VARCHAR(10)
)

INSERT CH9_7 VALUES ('aa',  12, 'la')
INSERT CH9_7 VALUES ('ab',  13, 'la')
INSERT CH9_7 VALUES ('ba',  12, 'lb')
INSERT CH9_7 VALUES ('bb',  13, 'lb')
INSERT CH9_7 VALUES ('bc',  12, 'lb')
INSERT CH9_7 VALUES ('cb',  11, 'lc')
INSERT CH9_7 VALUES ('ca',  11, 'lc')
INSERT CH9_7 VALUES ('cb',  11, 'lc')

SELECT AVG(Salary)
FROM CH9_7
GROUP BY Location

8. Print an ordered list of instructors and the number of As they assigned to students.

Order the output by number of As (lowest to greatest). You can (and probably will) ignore

instructors that assign no As.

SELECT s.INSTRUCTOR, COUNT(*)
FROM Grade_report g
 INNER JOIN Section s
 ON g.SECTION_ID = s.SECTION_ID
WHERE GRADE = 'A'
GROUP BY s.INSTRUCTOR
ORDER BY COUNT(*)

9. Create a table called Employees with a name, a salary and job title. Include exactly

six rows. Make the salary null in one row, the job title null in another, and both the salary

and the job title in another. Use this data:
Name Salary Title
Mary 1000 Programmer
Brenda 3000 
Stephanie   Artist
Alice   
Lindsay 2000 Artist
Christina 500 Programmer

Create Table Employees
(
name VARCHAR(10),
salary decimal(6,2),
job VARCHAR(10)
)

INSERT Employees VALUES('Mary', 1000, 'Programmer')
INSERT Employees VALUES('Brenda', 3000, null)
INSERT Employees VALUES('Stephanie', null, 'Artist')
INSERT Employees VALUES('Alice', null, null)
INSERT Employees VALUES('Lindsay', 2000, 'Artist')
INSERT Employees VALUES('Christina', 500, 'Programmer')

a. Display the table.
SELECT * FROM Employees

b. Display count, sum, maximum, minimum, and average salary.
SELECT COUNT(salary) as [cnt], SUM(salary) as [sum], MAX(salary) as [max],
 MIN(salary) as [min], AVG(salary) as [avg]
FROM Employees

c. Display count, sum, maximum, minimum, and average salary, counting salary as 0 if no

salary is listed.

SELECT COUNT(sa) as [cnt], SUM(sa) as [sum], MAX(sa) as [max],
 MIN(sa) as [min], AVG(sa) as [avg]
FROM ( SELECT ISNULL(salary, 0) as sa FROM Employees ) as t

d. Display the average salary grouped by job title on the table as is.
SELECT job, AVG(salary) as [avg]
FROM Employees
GROUP BY job

e. Display the average salary grouped by job title when null salary is counted as 0.
SELECT job, AVG(ISNULL(salary, 0)) as [avg]
FROM Employees
GROUP BY job

f. Display the average salary grouped by job title when salary is counted as 0 if it is

null and include a value for "no job title."

SELECT jb, AVG(sa)
FROM (
 SELECT ISNULL(job, 'no job title.') as jb,  ISNULL(salary, 0) as sa
 FROM Employees ) as t
GROUP BY jb

10. Find the instructor and the section where the maximum number of As were awarded.
SELECT TOP 1 s.INSTRUCTOR, s.SECTION_ID, COUNT(*)
FROM Grade_report g
 INNER JOIN Section s
 ON g.SECTION_ID = s.SECTION_ID
WHERE GRADE = 'A'
GROUP BY s.INSTRUCTOR, s.SECTION_ID
ORDER BY COUNT(*) DESC

11. Find the COUNT of the number of students by class who are taking classes offered by

the computer science (COSC) department. Perform the query in two ways: once using a condition

in the WHERE clause and once filtering with a HAVING clause. (Hint: These queries need a

five-table join.)
Tip:
 SELECT sc.SECTION_ID, COUNT(*)
  FROM [Grade_report] as gr
 INNER JOIN Section as sc
 ON gr.SECTION_ID = sc.SECTION_ID
 INNER JOIN Course as cr
 ON cr.COURSE_NUMBER = sc.COURSE_NUM
 WHERE cr.OFFERING_DEPT = 'COSC'
 GROUP BY sc.SECTION_ID

 

Delete (DROP) all of your "scratch" tables (the ones you created just for this exercise:

Employees, NOC, and any others you may have created).