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

By | 11月08日
Advertisement

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

Similar Posts:

  • Answer to Chapter 11 of O&amp;amp;#39;Reilly Learning Sql on SQL Server 2005 .

    11.5. Review Questions 1. What is an index? An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. 2. Does an index slow down updates on indexed columns? YES 3. What is a constraint? con

  • MCITP SQL Server 2005 Database Developer All-in-One Exam Guide

    版权声明:原创作品,允许转载,转载时请务必以超链接形式标明文章原始出版.作者信息和本声明.否则将追究法律责任.http://blog.csdn.net/topmvp - topmvp All-in-One is All You Need Get complete coverage of all three Microsoft Certified IT Professional database developer exams for SQL Server 2005 in this compreh

  • 学习SQL Server 2005不得不看的一些图书(学习中)

    学习SQL Server 2005不得不看的一些图书(学习中) Microsoft SQL Server 2005 技术内幕 : <T-SQL Querying><T-SQL查询> Microsoft SQL Server 2005 技术内幕 : <T-SQL Programming><T-SQL程序设计> Microsoft SQL Server 2005 技术内幕 : <The Storage Engine><存储引擎> Micr

  • 电子书免费下载 The Microsoft Data Warehouse Toolkit : With SQL Server 2005 and the Micr

    This groundbreaking book is the first in the Kimball Toolkit series to be product-specific. Microsoft's BI toolset has undergone significant changes in the SQL Server 2005 development cycle. SQL Server 2005 is the first viable, full-functioned data w

  • SQL Server 2005: SQL Server Storage and Index Structures

    SQL Server 2005: SQL Server Storage and Index Structures The Extent An extent is the basic unit of storage used to allocate space for tables and indexes. It is made up of eight contiguous 64K data pages. The Page Much like an extent is a unit of allo

  • SQL Server 2005从入门到精通 前言

    对每一个需要进行数据存储.分析和报告的组织而言,数据库解决方案都是不可或缺的一部分.Microsoft SQL Server 2005为实现数据库解决方案提供了一个健壮的平台.Microsoft SQL Server 2005易于使用的特性使其成为一个完美的数据库环境.特别要指出的是,作为SQL Server的最新版本,Microsoft SQL Server 2005比以前任何版本更安全,可扩展性更强,更易使用和管理. 本书是为熟悉SQL Server并希望了解SQL Server 2005最

  • Document Sarbanes-Oxley compliance with SQL Server 2005

    http://blogs.techrepublic.com.com/datacenter/?p=323&tag=nl.e046 Author: Tim Chapman As the DBA for revenue-generating applications, I am frequently invited by my CIO to meetings with Sarbanes-Oxley auditors to discuss the security and integrity of co

  • Professional SQL Server 2005 Administration

    版权声明:原创作品,允许转载,转载时请务必以超链接形式标明文章原始出版.作者信息和本声明.否则将追究法律责任.http://blog.csdn.net/topmvp - topmvp SQL Server 2005 is the largest leap forward for SQL Server since its inception. With this update comes new features that will challenge even the most experien

  • Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl

    <script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <s

  • 回首经典的SQL Server 2005

    原创文章转载请注明出处:@协思, http://zeeman.cnblogs.com SQL Server是我使用时间最长的数据库,算起来已经有10年了.上世纪90年代,微软在软件开发的所有领域高歌猛进,形成了操作系统.办公软件.企业开发.游戏制作.浏览器各领域的全线垄断.那曾经是微软发展的黄金时期,至到今天,它仍然在享受着当年的红利. 在SQL Server 2000那个年代,由其功能强大,使用方便,"国人免费"等特点迅速流行起来,成为企业开发的不二选择.早期的用友.管家婆的ERP也

Tags: