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

5.8. Review Questions
1. What are functions?
Functions are preprogrammed mini-programs that perform a certain task.

2. What are aggregate functions? Give examples of aggregate functions. What is another

term for an aggregate function?
An aggregate function (or group function) is a function that returns a result (one number)

after calculations based on multiple rows.
COUNT, SUM, AVG, MAX, and MIN

3. What are row-level functions? Give examples of row-level functions.
row-level functions operate on values in single rows, one row at a time.

4. Is COUNT an aggregate function or a row-level function? Explain why. Give at least one

example of when the COUNT function may come in handy. Does the COUNT function take nulls into

account?
Aggregate function.
SELECT COUNT(*) FROM TABLENAME
NO.

5. Is AVG an aggregate function or a row-level function?
Aggregate function.

6. What is the NULLIF function? Explain.
NULLIF function returns a NULL if expression1 = expression2. If the expressions are not equal,

then expression1 is returned. The NULLIF function has the following form: NULLIF(expression1,

expression2)

7. How are ties handled in SQL Server?
WITH TIES
Specifies that additional rows be returned from the base result set with the same value in the

ORDER BY columns appearing as the last of the TOP n (PERCENT) rows.

8. How does the DISTINCT function work?
The DISTINCT function omits rows in the result set that contain duplicate data in the selected

columns. DISTINCT can also be used as an option with aggregate functions like COUNT, SUM and

AVG.

9. Are string functions (for example, SUBSTRING, RIGHT, LTRIM) aggregate functions or

row-level functions?
Row-level functions.

10. What is the SUBSTRING function used for?
The SUBSTRING function returns part of a string. Following is the format for the SUBSTRING

function:
    SUBSTRING(stringexpression, startposition, length)
stringexpression is the column that we will be using, startposition tells SQL Server where in

the stringexpression to start retrieving characters from, and length tells SQL Server how many

characters to extract.

11. What is the CHARINDEX function used for?
The CHARINDEX function returns the starting position of a specified pattern.

12. What function would you use to find the leftmost characters in a string?
LEFT

13. What are the LTRIM/RTRIM functions used for?
LTRIM removes blanks from the beginning (left) of a string.
RTRIM removes blanks from the end (right) of a string.

14. What function would produce the output in all lowercase?
LOWER

15. What function would you use to find the length of a string?
LEN

16. What characters or symbols are most commonly used as wildcard characters in SQL Server

2005?
%

17. What is the concatenation operator in Server SQL 2005?
+

18. What does the YEAR function do?
The YEAR(column) function will extract the year from a value stored as a SMALLDATETIME data

type.

19. What does the MONTH function do?
The MONTH function will extract the month from a date.

20. What does the GEtdATE function do?
The GEtdATE function returns the current system date and time.

21. What will the following query produce in SQL Server 2005?
     SELECT ('.....'+ names) AS [names]
     FROM Employee
Add '....' to each returned row

22. Does Server SQL allow an expression like COUNT(DISTINCT column_name)?
Yes

23. How is the ISNULL function different from the NULLIF function?
ISNULL ( check_expression , replacement_value )
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is

returned after it is implicitly converted to the type of check_expression, if the types are

different.

NULLIF ( expression , expression )
NULLIF returns the first expression if the two expressions are not equal. If the expressions

are equal, NULLIF returns a null value of the type of the first expression.

24. What function would you use to round a value to three decimal places?
ROUND(numeric_expression, 3)

25. Which functions can the WITH TIES option be used with?
TOP

26. What clause does the WITH TIES option require?
ORDER BY

27. What is the default date format in SQL Server 2005?
yyyy/mm/dd

28. How do dates have to be entered in Server SQL 2005?
character strings to dates, which are rounded by single quotes

29. What function is used to convert between data types?
CONVERT CAST STR

30. What function is useful for formatting numbers?
STR

31. What function is useful for formatting dates?
CONVERT
 
 

5.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 the COUNT of tuples (rows) in each of the tables Grade_report, Student, and

Section. How many rows would you expect in the Cartesian product of all three tables? Display

the COUNT (not the resulting rows) of the Cartesian product of all three and verify your

result (use SELECT COUNT(*) ...).
SELECT COUNT(*) FROM Grade_report

209

SELECT COUNT(*) FROM Student

48

SELECT COUNT(*) FROM Section

32

SELECT COUNT(*)
FROM Grade_report, Student, Section

321024

 

2. Display the COUNT of section-ids from the Section table. Display the COUNT of DISTINCT

section-ids from the Grade_report table. What does this information tell you? (Hint:

section_id is the primary key of the Section table.)

SELECT COUNT(SECTION_ID)
FROM Section
32

SELECT COUNT(SECTION_ID)
FROM Grade_report
2909

SELECT COUNT(DISTINCT(SECTION_ID))
FROM Grade_report
30

有两个Section没有任何Grade report

3. Write, execute, and print a query to list student names and grades (just two

attributes) using the table alias feature. Restrict the list to students that have either As

or Bs in courses with ACCT prefixes only.
Here's how to complete this problem:
a. Get the statement to work as a COUNT of a join of the three tables, Student,

Grade_report, Section. Use table aliases in the join condition. Note that a join of n tables

requires (n - 1) join conditions, so here you have to have two join conditions: one to join

the Student and Grade_report tables, and one to join the Grade_report and Section tables. Note

the number of rows that you get (expect no more rows than is in the Grade_report table). Why

do you get this result?
SELECT COUNT(*)
FROM Grade_report g INNER JOIN Student st
ON g.STUDENT_NUMBER = st.STNO
INNER JOIN Section se
ON g.SECTION_ID = se.SECTION_ID

b. Modify the query and put the Accounting condition in the WHERE clause. Note the number

of rows in the resultit should be a good bit less than in question 3a.
SELECT COUNT(*)
FROM Grade_report g INNER JOIN Student st
ON g.STUDENT_NUMBER = st.STNO
INNER JOIN Section se
ON g.SECTION_ID = se.SECTION_ID
WHERE se.COURSE_NUM LIKE 'ACCT%'

c. Again, modify the query and add the grade constraints. The number of rows should

decrease again. Note that if you have WHERE x and y or z, parentheses are optional, but then

the criteria will be interpreted according to precedence rules.
The reason that we want you to "start small" and add conditions is that it gives you a check

on what you ought to get and it allows you to output less nonsense. Your minimal starting

point should be a count of the join with appropriate join conditions.

SELECT st.STNO, st.STNO, g.GRADE, se.COURSE_NUM
FROM Grade_report g INNER JOIN Student st
ON g.STUDENT_NUMBER = st.STNO
INNER JOIN Section se
ON g.SECTION_ID = se.SECTION_ID
WHERE g.GRADE in ('A', 'B')
AND se.COURSE_NUM LIKE 'ACCT%'

4. Using the Student table, answer the following questions:
a. How many students have names like Smith?
SELECT COUNT(*)
FROM Student s
WHERE s.SNAME LIKE '%Smith%'
3

b. How many have names that contain the letter sequence Smith?
SELECT COUNT(s.SNAME)
FROM Student s
WHERE  CHARINDEX('smith', s.SNAME) > 0

c. How many student names end in LD?
SELECT COUNT(s.SNAME)
FROM Student s
WHERE RIGHT(s.SNAME, 2) = 'LD'

d. How many student names start with S?
SELECT COUNT(*)
FROM Student s
WHERE LEFT(s.SNAME, 1) = 'S'

e. How many student names do not have "i" as the second letter?
SELECT COUNT(*)
FROM Student s
WHERE SUBSTRING(s.SNAME, 2, 1) <> 'i'

f. Would SELECT * FROM Student WHERE sname LIKE 'Smith%' find someone whose name is:
i. LA SMITH
ii. SMITH-JONES
iii. SMITH JR.
iv. SMITH, JR

Except the item i - LA SMITH

5. Using the Course table, answer the following questions:
a. List the junior-level COSC courses (LIKE COSC3xxx) and the name of the courses.
SELECT COURSE_NAME, COURSE_NUMBER
FROM Course
WHERE COURSE_NUMBER LIKE 'COSC3%'

b. List all the courses except the junior-level COSC courses (use NOT LIKE).
SELECT COURSE_NAME, COURSE_NUMBER
FROM Course
WHERE COURSE_NUMBER NOT LIKE 'COSC3%'

6. Using the COUNT feature, determine whether there are duplicate names or student

numbers in the Student table.
SELECT COUNT(STNO) as "all no", COUNT(SNAME) as "all name",
 COUNT(DISTINCT(STNO)) as "unique no", COUNT(DISTINCT(SNAME)) as "Unique name"
FROM Student
all no      all name    unique no   Unique name
----------- ----------- ----------- -----------
48          48          48          47

Therefore, there are duplicate student names, and the student nos are unique.

7. Assume that all math courses start with MATH. How many math courses are there in the

Section table? From the count of courses, does it appear that there any math courses in the

Section table that are not in the Course table?  Again, using COUNTs, are there any math

courses in the Course table that are not in the Section table? Does it appear that there are

any courses at all that are in the Grade_report, Section, or Course tables that are not in the

others? (We will study how to ask these questions in SQL in a later chapter.) Note that a

query like the following would not work:
    SELECT g.section_id
    FROM Grade_report g, Section t
    WHERE g.section_id <> t.section_id

Explain why WHERE .. <> .. will not work to produce the desired output.

(1)

SELECT COUNT(*)
FROM Section
WHERE COURSE_NUM LIKE 'MATH%'

(2)

SELECT COUNT(DISTINCT(COURSE_NUM))
FROM Section
WHERE COURSE_NUM LIKE 'MATH%'
3
SELECT COUNT(DISTINCT(COURSE_NUMBER))
FROM Section s INNER JOIN Course c
ON s.COURSE_NUM = c.COURSE_NUMBER
WHERE COURSE_NUMBER LIKE 'MATH%'
3
So all the courses in Section table are in the Course table too.

(3)
SELECT COUNT(DISTINCT(COURSE_NUMBER))
FROM Course
WHERE COURSE_NUMBER LIKE 'MATH%'
6

SELECT COUNT(DISTINCT(COURSE_NUMBER))
FROM Section s INNER JOIN Course c
ON s.COURSE_NUM = c.COURSE_NUMBER
WHERE COURSE_NUMBER LIKE 'MATH%'
3

There are 3 courses in Course table only.

(4)
SELECT COUNT(DISTINCT(s.COURSE_NUM))
FROM Grade_report g INNER JOIN Section s
ON g.SECTION_ID = s.SECTION_ID
19
SELECT COUNT(DISTINCT(s.COURSE_NUM))
FROM Grade_report g INNER JOIN Section s
ON g.SECTION_ID = s.SECTION_ID
INNER JOIN Course c
ON c.COURSE_NUMBER = s.COURSE_NUM
19
SELECT COUNT(DISTINCT(s.COURSE_NUM))
FROM  Section s INNER JOIN Course c
ON c.COURSE_NUMBER = s.COURSE_NUM
20
SELECT COUNT(DISTINCT(COURSE_NUMBER))
FROM Course
32

All coursed in Grade_report table are in two others. There is 1 course that only in Section,

but not in Grade_report. There are more 12 courses in Course table than those in Section

table.

8. For every table in the Student_course database, we would like to compile the following

information: attributes, number of rows, number of distinct rows, and rows without nulls. Find

this information using different queries and compile the information in a table as shown here:
Table Attribute Rows Distinct Rows Rows without Nulls
Student Stno 48 48 48
  Sname 48 47 48
  Major 48 8 
  Class etc. etc. etc.
Section Section_id etc. etc. etc.

The other tables in the Student_course database are Grade_report, Dependent, Section, Room,

Course, Prereq, and Department_to_major.
Hint: You can use the following query:
    SELECT COUNT(*)
    FROM Student
    WHERE sname IS NULL

SELECT 'Stno', COUNT(*), COUNT(STNO), COUNT(DISTINCT(Stno))
FROM Student

SELECT 'Sname', COUNT(*), COUNT(SNAME), COUNT(DISTINCT(SNAME))
FROM Student

SELECT 'Major', COUNT(*), COUNT(MAJOR), COUNT(DISTINCT(MAJOR))
FROM Student

9. Find the count, sum, average, minimum, and maximum capacity of rooms in the database.

Format the output using the STR function.

SELECT 'All rows is ' + STR(COUNT(*), 3) + '. Count is ' + STR(COUNT(CAPACITY),3 )
 + '. Sum is ' + STR(SUM(CAPACITY),3) + '. Averge is ' + STR(AVG(CAPACITY),6,3)
 + '. Minimun is ' + STR(MIN(CAPACITY), 3) + '. Max is ' + STR(MAX(CAPACITY), 3)
FROM Room

a. Where there is a null value for the capacity, assume the capacity to be 40, and find

the average room size again.

SELECT AVG(ISNULL(CAPACITY, 40)) as [avergae]
FROM Room

SELECT STR(AVG(ISNULL(CAST(CAPACITY as FLOAT) , 40) ), 6, 3) as [avergae]
FROM Room

10. Using the Student table, display the first 10 rows with an appended initial. For the

appended initial, choose the halfway letter of the name, so that if a name is Evans, the

initial is A (half of the length +1). If the name is Conway, the initial is W (again, (half of

the length +1)). You do not need to round up or down, just use (LEN(Name)/2)+1 as the starting

place to create the initial. Use appropriate column aliases. Your result should look like this

(actual names may vary depending on the current database):
    PERSON#   NAMES
    --------- ------------------------
    1         Lineas, E.
    2         Mary, R.
    3         Brenda, N.
    4         Richard, H.
    5         Kelly, L.
    6         Lujack, A.
    7         Reva, V.
    8         Elainie, I.
    9         Harley, L.
    10        Donald, A.
SELECT [STNO]
      ,[SNAME] + ', ' + UPPER(SUBSTRING([SNAME], LEN([SNAME])/2+1, 1)) + '.' AS [Names]
     
  FROM [Student_Course].[dbo].[Student]

a. Display the preceding output in all capital letters.
SELECT [STNO]
      ,UPPER([SNAME] + ', ' + SUBSTRING([SNAME], LEN([SNAME])/2+1, 1) + '.') AS [Names]
     
  FROM [Student_Course].[dbo].[Student]

11. Find the names of the bottom 50 percent of the students, ordered by grade.
SELECT TOP 50 PERCENT  *
FROM Student s INNER JOIN Grade_report g
ON s.STNO = g.STUDENT_NUMBER
ORDER BY g.GRADE DESC
a. Find the names of the top 25 percent of the seniors, ordered by grade.
SELECT TOP 25 PERCENT  *
FROM Student s INNER JOIN Grade_report g
ON s.STNO = g.STUDENT_NUMBER
WHERE CLASS = 3 and GRADE IS NOT NULL
ORDER BY g.GRADE ASC

b. Now use the WITH TIES option with part (b). Is there any difference in the output?
SELECT TOP 25 PERCENT WITH TIES  *
FROM Student s INNER JOIN Grade_report g
ON s.STNO = g.STUDENT_NUMBER
WHERE CLASS = 3 and GRADE IS NOT NULL
ORDER BY g.GRADE ASC

12. Count the number of courses taught by each instructor.
SELECT COUNT([COURSE_NUM])
      ,[INSTRUCTOR]
  FROM [Section]
  GROUP BY [INSTRUCTOR]

a. Count the number of distinct courses taught by each instructor.
SELECT COUNT(DISTINCT([COURSE_NUM]))
      ,[INSTRUCTOR]
  FROM [Section]
  GROUP BY [INSTRUCTOR]

13. Count the number of classes each student is taking.
  SELECT STUDENT_NUMBER, COUNT(SECTION_ID)
  FROM Grade_report
  GROUP BY STUDENT_NUMBER

14. Display all the names that are less than five characters long from the Student table.
SELECT sname
  FROM Student
  WHERE LEN(sname) < 5

15. List all the students with student numbers in the 140s range.
 SELECT sname, stno
  FROM Student
  WHERE stno <= 149 AND stno >= 140

16. Find all the students (the student names should be listed only once) who received As

and Bs.
  SELECT DISTINCT s.STNO, s.SNAME
  FROM Grade_report g INNER JOIN Student s
  ON s.STNO = g.STUDENT_NUMBER
  WHERE g.GRADE in ('A', 'B')

17. Would you call TOP an aggregate function? Why or why not?
No. Aggegate function only return one row.

18. Add an asterisk (*) to the names of all juniors and seniors who received at least one

A. (This question will take a few steps, and you will have to approach this problem in a

step-by-step manner.)
  SELECT s.STNO, '*' +  s.SNAME
  FROM Grade_report g INNER JOIN Student s
  ON s.STNO = g.STUDENT_NUMBER
  WHERE g.GRADE = 'A' AND (s.CLASS = 2 OR s.CLASS =3)

 

19. In this chapter, we used a table called Employee. Add a birthdate column and an

employment_date column to the Employee table. Insert values into both the columns.
     CREATE TABLE Employee      (birthdate        SMALLDATETIME,
                                employment_date       SMALLDATETIME,
                                names            VARCHAR(20))

a. Display the current ages of all the employees.
SELECT names, YEAR(GETDATE()) - YEAR(birthdate) AS Age
FROM Employee

b. Find the youngest employee.
SELECT TOP 1 *
FROM Employee
ORDER BY birthdate DESC

c. Find the oldest employee.
SELECT TOP 1 *
FROM Employee
ORDER BY birthdate ASC

d. Find the youngest employee at the time of employment.

SELECT TOP 1 *, YEAR(GETDATE()) - YEAR(birthdate)
FROM Employee
ORDER BY YEAR(GETDATE()) - YEAR(birthdate) ASC

e. Find the oldest employee at the time of employment.
SELECT TOP 1 *, YEAR(GETDATE()) - YEAR(birthdate)
FROM Employee
ORDER BY YEAR(GETDATE()) - YEAR(birthdate) DESC

f. Add five years to the current ages of all employees. Will any of the employees be over

65 in five years?
SELECT names, YEAR(GETDATE()) - YEAR(birthdate) + 5 as YrAfter5
FROM Employee

SELECT count(names )
FROM Employee
WHERE YEAR(GETDATE()) - YEAR(birthdate) + 5 > 65

g. List the birth months and names of all employees.
 
SELECT names, MONTH(birthdate)
FROM Employee