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

2.11. Review Questions

1. What is usually the first word in a SQL query?
   SELECT
2. Does a SQL Server 2005 SELECT statement require a FROM?
   No. For example Select GETDATE()
3. Can a SELECT statement in SQL Server 2005 be used to make an assignment? Explain with examples.
   Yes，SELECT col1=100, col2=200
4. What is the ORDER BY used for?
   A relational database contains sets of rows of data and sets are not ordered. If you wish to display the contents of a table in a predictable manner, you may use the ORDER BY clause in the SELECT statement.
5. Does ORDER BY actually change the order of the data in the tables or does it just change the output?
   Only change the output.
6. What is the default order of an ORDER BY clause?
   ascending
7. What kind of comparison operators can be used in a WHERE clause?
   > < >= <= = <>
8. What are four major operators that can be used to combine conditions on a WHERE clause? Explain the operators with examples.
   AND, OR, BETWEEN, NOT BETWEEN
9. What are the logical operators?
   The logical operators are AND, OR, and NOT. AND and OR are used to connect search conditions in WHERE clauses. NOT reverses the result of a search condition. AND joins two conditions and returns TRUE only when both conditions are true. OR also connects two conditions, but it returns TRUE when either of the conditions is true.
10. In a WHERE clause, do you need to enclose a text column in quotes? Do you need to enclose a numeric column in quotes?
    Character or text value should be in single quotes, but the numeric value should not be in single quotes. Column name could be in Double quotes or brackets.
11. Is a null value equal to anything? Can a space in a column be considered a null value? Why or why not?
    Null values are used to designate missing data in columns, which is not equal to anthing, even another null. A space or empty string is a real string, which is not null.
12. Will COUNT(column) include columns with null values in its count?
    No
13. What are column aliases? Why would you want to use column aliases? How can you embed blanks in column aliases?
    An alternative name to replace the column name in the query results set. Aliases are used also to specify names for the results of expressions.
    Column aliases are used to improve the readability of a query and its output. It is shown in query result.
    Column aliases with embed blanks should be placed in square brackets or double quotes.
14. What are table aliases?
    A table alias, usually used in multi-table queries, allows us to use a shorter name for a table when we reference the table in the query
15. What are table qualifiers? When should table qualifiers be used?
    Table qualifiers are needed when the same column name has been used in more than one table. Table qualifiers before the column names determine which table the column is from.
16. Are semicolons required at the end of SQL statements in SQL Server 2005?
    It is could be used, but not required.
17. Do comments need to go in a special place in SQL Server 2005?
    No.
18. When would you use the ROWCOUNT function versus using the WHERE clause?
    The WHERE clause assumes that you have knowledge of the actual data values present in a data set. ROWCOUNT returns only a sample of a result set, and you have no idea which range of values are present in the table.
19. Is SQL case-sensitive? Is SQL Server 2005 case-sensitive?
    No.
20. What is a synonym? Why would you want to create a synonym?
21. Can a synonym name of a table be used instead of a table name in a SELECT statement?
    Yes.
22. Can a synonym of a table be used when you are trying to alter the definition of a table?
    No.
23. Can you type more than one query in the query editor screen at the same time?
    Yes.

2.12. Exercises

 

1. The Student_course database used in this book has the following tables: Student, Dependent, Course, Section, Prereq (for prerequisite), Grade_report, Department_to_major, and Room.
   1. Display the data from each of these tables by using the simple form of the SELECT * statement.

SELECT * FROM Student

SELECT * FROM Dependent

SELECT * FROM Course

SELECT * FROM Section

SELECT * FROM Prereq

SELECT * FROM Grade_report

SELECT * FROM Department_to_major

SELECT * FROM Room

b. Display the first five rows from each of these tables.

SET ROWCOUNT 5

SELECT * FROM Student

SELECT * FROM Dependent

SELECT * FROM Course

SELECT * FROM Section

SELECT * FROM Prereq

SELECT * FROM Grade_report

SELECT * FROM Department_to_major

SELECT * FROM Room

SET ROWCOUNT 0 

c.Display the student name and student number of all students who are juniors (hint: class = 3).

SELECT sname as [Student name], stno as [Student number]

FROM Student

WHERE class = 3

d.Display the student names and numbers (from question 2) in descending order by name.

SELECT sname as [Student name], stno as [Student number]

FROM Student

WHERE class = 3

ORDER BY sname DESC

e. Display the course name and number of all courses that are three credit hours.

SELECT course_name, course_number

FROM Course

WHERE credit_hours = 3

f. Display all the course names and course numbers (from question 3) in ascending order by course name.

SELECT course_name, course_number

FROM Course

WHERE credit_hours = 3

ORDER BY course_name

2. Display the building number, room number, and room capacity of all rooms in descending order by room capacity. Use appropriate column aliases to make your output more readable.

SELECT BLDG as [Building No], room as [Room No], Capacity

FROM Room

ORDER BY capacity DESC

3. Display the course number, instructor, and building number of all courses that were offered in the Fall semester of 1998. Use appropriate column aliases to make your output more readable.

SELECT course_num as [Course number], instructor as [Instructor], [BLDG] as [Building number]

FROM Section

WHERE semester = 'FALL' AND YEAR = '98'

4.List the student number of all students who have grades of C or D.

SELECT DISTINCT student_number

FROM Grade_report

WHERE GRADE = 'C' OR GRADE = 'D'

5.List the offering_dept of all courses that are more than three credit hours.

SELECT course_name, course_number, offering_dept, credit_hours

FROM Course

WHERE credit_hours > 3

ORDER BY offering_dept

6.Display the student name of all students who have a major of COSC.

SELECT sname as [Student Name]

FROM student

WHERE major = 'COSC'

7.Find the capacity of room 120 in Bldg 36.

SELECT BLDG as [Building No], room as [Room No], Capacity

FROM Room

WHERE BLDG = 36 AND room = 120

8. Display a list of all student names ordered by major.

SELECT sname as [Student Name], major as [Major]

FROM student

ORDER BY  major

9.Display a list of all student names ordered by major, and by class within major. Use appropriate table and column aliases.

SELECT sname as [Student Name], major as [Major], Class

FROM student

ORDER BY  major, class

10. Count the number of departments in the Department_to_major table.

SELECT COUNT(*)

FROM Department_to_major

11. Count the number of buildings in the Room table.

SELECT COUNT(DISTINCT  BLDG)

FROM Room

12. What output will the following query produce?

SELECT COUNT(class)

FROM Student

WHERE class IS NULL

Why do you get this output?

Output is 0. COUNT(*) returns number including null. COUNT(expression) returns number only including nonnull values.

13. Use the BETWEEN operator to list all the sophomores, juniors, and seniors from the Student table.

SELECT *

FROM Student

WHERE class BETWEEN 2 AND 4

ORDER BY class

Sophomores 2, juniors 3, and seniors 4

14 Use the NOT BETWEEN operator to list all the sophomores and juniors from the Student table.

SELECT *

FROM Student

WHERE class NOT BETWEEN 1 AND 1

AND class NOT BETWEEN 4 AND 4

ORDER BY class

15. Create synonyms for each of the tables available in the Student_course database. View your synonyms in the Object Explorer.

CREATE SYNONYM s1 FOR Student