Preface

SQL Server is one of the most powerful database engines used today. Microsoft’s latest release of SQL Server, SQL Server 2005, is a comprehensive database platform that provides secure and reliable storage for both relational and structured data, enabling one to build and manage high-performance data applications. SQL Server 2005’s close integration with Microsoft Visual Studio, the Microsoft Office System, and a suite of new development tools set SQL Server 2005 apart from previous versions and from other database engines. This system allows developers to build, debug, and operate applications faster then ever before.

SQL Server 2005 can be installed on small machines using Microsoft Windows as well as on large servers. In recent years, the computer industry has seen a dramatic increase in the popularity of relational databases and multiuser databases, and the computer industry needs application developers and people who can write SQL code efficiently and correctly for relational and multiuser databases.

Why This Book?

This book is mainly intended to be a systematic guide to learning SQL using SQL Server 2005--a relational and multiuser database. The book is aimed at students who wish to learn SQL using Microsoft’s SQL Server 2005. The book is expected to be used by schools and SQL training organizations as well as by database and IT professionals who are actively working with SQL Server 2005.

The book starts with very simple SQL concepts, and slowly builds into more complex query development. The purpose of this book is to present every topic, concept, and idea with examples of code and output. Exercises have also been included to gain SQL proficiency using SQL Server. The best approach to using this book efficiently is to read through the book with SQL Server open and active. As the book is read, it will be advantageous for you to work with and understand the examples.

If the book is used for a beginning database course, the exercises are presented to be done by the students over the course of one semester at a pace of one chapter per week. The exercises are found at the end of each chapter.

Due to the dramatic increase in the popularity of relational and multiuser databases, many schools and training organizations are using SQL Server in their database courses to teach database principles and concepts. This development has generated a need for a concise book on SQL Server programming, tied in with database principles and concepts—hence this book.

SQL and SQL Server

SQL (Structured Query Language) is a standard language used for querying, updating, and managing relational databases, and lately SQL has become the de facto standard “language” for accessing relational databases. SQL is not really as much of a language as it is a database query tool. In this book, we concentrate on learning SQL using SQL Server 2005.

SQL allows us to define a relational database—create and modify tables (in this sense, SQL is a data definition language, or DDL). SQL also allows us to tell SQL Server which information we want to select (retrieve), insert, update, or delete. That is, SQL also allows us to query the relational database in a most flexible way, as well as to change the stored data (and in this sense, SQL is a data manipulation language, or DML).

The book is targeted at SQL Server users on the Windows operating system, but is easily adaptable to other platforms.

Audience and Coverage

A book like this can be used in an “Introduction to Databases” course or a second database course along with textbooks like Fundamentals of Database Systems, 4th Edition, Addison Wesley, 2003 (Elmasri and Navathe), and Database Processing, Fundamentals, Design & Implementation, 9th Edition, Prentice Hall, 2003 (David Kroenke). Students could learn the database theory from the texts, and apply the theory using this book (using SQL Server) as they learn SQL.

This book can also be used as a standalone text in a course on learning SQL using SQL Server 2005. This book does not assume any prior computer knowledge.

This book consists of 11 chapters. Chapter 1 introduces the user to SQL Server 2005. In Chapter 1, you will learn how to open SQL Server 2005 using SQL Server Management Studio, load the database, and view and perform simple table manipulations. Chapter 1 also introduces the user to the query editor; shows you how to view, save, and print queries and output; and how to customize SQL Server 2005’s settings. Chapter 2 introduces the user/learner to some basic SQL commands in SQL Server. Chapter 3 discusses creating, populating, altering, and deleting tables; an example relational database is built on the idea of tabular data. Chapter 4 introduces and covers different types of joins—a common database mechanism for combining tables. Chapter 5 covers SQL Server 2005’s functions. Chapter 6 discusses query development as well as the use of views and other derived structures. Chapter 7 covers simple set operations. Chapters 8, 9, and 10 cover subqueries, aggregate functions, and correlated subqueries; and Chapter 11 presents indexes and constraints that can be added to tables in SQL Server 2005.

Appendix A describes the Student_course database and other databases that have been used throughout the book. Appendix B provides the actual script used to create the Student_course database. Glossaries defining terms and important functions are provided, as well as indexes of terms and functions in the book.

The book is sufficient for beginning SQL users to get an overview of what SQL Server entails and how to use SQL. Many SQL programmers have based their employment on this material. The book gives a very good feel for what SQL is, and how SQL is used in SQL Server.

A Few Notes About SQL Server 2005 Installation

For best results, one should install SQL Server 2005 on a computer that does not have a prerelease version of SQL Server 2005, Visual Studio 2005, or the .NET Framework 2.0 installed on it. If your computer has any of the prerelease versions on it, they must be removed in the correct order before you can successfully manually install the actual version of SQL Server 2005 software. For the correct order of these required uninstallations before you can install SQL Server 2005, visit:

http://msdn.microsoft.com/vstudio/express/support/uninstall/#Uninstall

We strongly recommend that you instead run the autoinstall tool (found at the same site), rather than attempting a manual install.

Once the uninstall has been correctly done, you may successfully load SQL Server 2005 and begin learning SQL.

Conventions Used in This Book

The following conventions are used in this book:

Italic

Used for URLs and for emphasis when introducing a new term.

Constant width

Used for MySQL and SQL keywords and for code examples.

Constant width bold

In some code examples, highlights the statements being discussed.

Constant width italic

In some code examples, indicates an element (e.g., a filename) that you supply.

UPPERCASE

In code examples, generally indicates MySQL keywords.

lowercase

In code examples, generally indicates user-defined items such as variables, parameters, etc.

punctuation

In code examples, enter exactly as shown.

indentation

In code examples, helps to show structure but is not required.

--

In code examples, begins a single-line comment that extends to the end of a line.

/* and */

In code examples, delimit a multiline comment that can extend from one line to another.

.

In code examples and related discussions, qualifies a reference by separating an object name from a component name.

[ ]

In syntax descriptions, enclose optional items.

{ }

In syntax descriptions, enclose a set of items from which you must choose only one.

|

In syntax descriptions, separates the items enclosed in curly brackets, as in {TRUE | FALSE}.

...

In syntax descriptions, indicates repeating elements. An ellipsis also shows that statements or clauses irrelevant to the discussion were left out.

Tip

Indicates a tip, suggestion, or general note. For example, we’ll tell you if a certain setting is version-specific.

Warning

Indicates a warning or caution. For example, we’ll tell you if a certain setting has some kind of negative impact on the system.

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: "Learning SQL on SQL Server 2005 by Sikha Saha Bagui and Richard Walsh Earp. Copyright 2006 O’Reilly Media, Inc., 0-596-10215-1.”

If you feel that your use of code examples falls outside fair use or the permission given here, feel free to contact us at .

How to Contact Us

We have tested and verified the information in this book and in the source code to the best of our ability, but given the amount of text and the rapid evolution of technology, you may find that features have changed or that we have made mistakes. If so, please notify us by writing to:

O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, CA 95472
800-998-9938 (in the United States or Canada)
707-829-0515 (international or local)
707-829-0104 (fax)

You can also send messages electronically. To be put on the mailing list or request a catalog, send email to:

To ask technical questions or comment on the book, send email to:

As mentioned in the earlier section, we have a web site for this book where you can find code, errata (previously reported errors and corrections available for public view), and other book information. You can access this web site at:

http://www.oreilly.com/catalog/learnsqlsvr05

For more information about this book and others, see the O’Reilly web site:

http://www.oreilly.com

Acknowledgments

Our special thanks are due to our editor, Jeff Pepper, and the production crew at O’Reilly for putting up with all the changes.

We would also like to thank President John Cavanaugh, Dean Jane Halonen, and Provost Sandra Flake of the University of West Florida for their inspiration, encouragement, support, and true leadership quality. We would also like to express our gratitude to Dr. Wes Little on the same endeavor.

Our sincere thanks also go to Dr. Ed Rodgers for his continuing support and encouragement throughout past years. We also appreciate Dr. Leo Terhaar, chair, Computer Science Department, for his advice, guidance, and support, and encouraging us to complete this book. Last, but not least, we would like to thank our fellow faculty members, Dr. Jim Bezdek and Dr. Norman Wilde for their continuous support and encouragement.

Get Learning SQL on SQL Server 2005 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.