Learning SQL

Preface

Programming languages come and go constantly, and very few languages in use today have roots going back more than a decade or so. Some examples are Cobol, which is still used quite heavily in mainframe environments, and C, which is still quite popular for operating system and server development and for embedded systems. In the database arena, we have SQL, whose roots go all the way back to the 1970s.

SQL is the language for generating, manipulating, and retrieving data from a relational database. One of the reasons for the popularity of relational databases is that properly designed relational databases can handle huge amounts of data. When working with large data sets, SQL is akin to one of those snazzy digital cameras with the high-power zoom lens in that you can use SQL to look at large sets of data, or you can zoom in on individual rows (or anywhere in between). Other database management systems tend to break down under heavy loads because their focus is too narrow (the zoom lens is stuck on maximum), which is why attempts to dethrone relational databases and SQL have largely failed. Therefore, even though SQL is an old language, it is going to be around for a lot longer and has a bright future in store.

Why Learn SQL?

If you are going to work with a relational database, whether you are writing applications, performing administrative tasks, or generating reports, you will need to know how to interact with the data in your database. Even if you are using a tool that generates SQL for you, such as a reporting tool, there may be times when you need to bypass the automatic generation feature and write your own SQL statements.

Learning SQL has the added benefit of forcing you to confront and understand the data structures used to store information about your organization. As you become comfortable with the tables in your database, you may find yourself proposing modifications or additions to your database schema.

Why Use This Book to Do It?

The SQL language is broken into several categories. Statements used to create database objects (tables, indexes, constraints, etc.) are collectively known as SQL schema statements . The statements used to create, manipulate, and retrieve the data stored in a database are known as the SQL data statements . If you are an administrator, you will be using both SQL schema and SQL data statements. If you are a programmer or report writer, you may only need to use (or be allowed to use) SQL data statements. While this book demonstrates many of the SQL schema statements, the main focus of this book is on programming features.

With only a handful of commands, the SQL data statements look deceptively simple. In my opinion, many of the available SQL books help to foster this notion by only skimming the surface of what is possible with the language. However, if you are going to work with SQL, it behooves you to understand fully the capabilities of the language and how different features can be combined to produce powerful results. I feel that this is the only book that provides detailed coverage of the SQL language without the added benefit of doubling as a “door stop” (you know, those 1,250-page “complete references” that tend to gather dust on people’s cubicle shelves).

While the examples in this book run on MySQL, Oracle Database, and SQL Server, I had to pick one of those products to host my sample database and to format the result sets returned by the example queries. Of the three, I chose MySQL because it is freely obtainable, easy to install, and simple to administer. For those readers using a different server, I ask that you download and install MySQL and load the sample database so that you can run the examples and experiment with the data.

Structure of This Book

This book is divided into 13 chapters and 4 appendixes:

• Chapter 1, A Little Background, explores the history of computerized databases, including the rise of the relational model and the SQL language.

• Chapter 2, Creating and Populating a Database, demonstrates how to create a MySQL database, create the tables used for the examples in this book, and populate the tables with data.

• Chapter 3, Query Primer, introduces the select statement and further demonstrates the most common clauses (select, from, where).

• Chapter 4, Filtering, demonstrates the different types of conditions that can be used in the where clause of a select, update, or delete statement.

• Chapter 5, Querying Multiple Tables, shows how queries can utilize multiple tables via table joins.

• Chapter 6, Working with Sets, is all about data sets and how they can interact within queries.

• Chapter 7, Data Generation, Conversion, and Manipulation, demonstrates several built-in functions used for manipulating or converting data.

• Chapter 8, Grouping and Aggregates, shows how data can be aggregated.

• Chapter 9, Subqueries, introduces the subquery (a personal favorite) and shows how and where they can be utilized.

• Chapter 10, Joins Revisited, further explores the various types of table joins.

• Chapter 11, Conditional Logic, explores how conditional logic (i.e., if-then-else) can be utilized in select, insert, update, and delete statements.

• Chapter 12, Transactions, introduces transactions and shows how to use them.

• Chapter 13, Indexes and Constraints, explores indexes and constraints.

• Appendix A, ER Diagram for Example Database, shows the database schema used for all examples in the book.

• Appendix B, MySQL Extensions to the SQL Language, demonstrates some of the interesting non-ANSI features of MySQL’s SQL implementation.

• Appendix C, Solutions to Exercises, shows solutions to the chapter exercises.

• Appendix D, Further Resources, suggests where to turn for more advanced training.

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Used for filenames, directory names, and URLs. Also used for emphasis and to indicate the first use of a technical term.

Constant width

Used for code examples and to indicate SQL keywords within text.

Constant width italic

Used to indicate user-defined terms.

UPPERCASE

Used to indicate SQL keywords within example code.

Constant width bold

Indicates user input in examples showing an interaction. Also indicates emphasized code elements to which you should pay particular attention.

Tip

Indicates a tip, suggestion, or general note. For example, I use notes to point you to useful new features in Oracle9i.

Warning

Indicates a warning or caution. For example, I’ll tell you if a certain SQL clause might have unintended consequences if not used carefully.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

[phone] (in the United States or Canada)

[phone] (international or local)

[phone] (fax)

O’Reilly maintains a web page for this book, which lists errata, examples, and any additional information. You can access this page at:

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

To comment or ask technical questions about this book, send email to:

[e-mail]

For more information about O’Reilly books, conferences, Resource Centers, and the O’Reilly Network, see the web site at:

http://www.oreilly.com

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 by Alan Beaulieu. Copyright 2005 O’Reilly Media, Inc., 0-596-00727-2.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [e-mail].

Safari Enabled

When you see a Safari® Enabled icon on the cover of your favorite technology book, that means the book is available online through the O’Reilly Network Safari Bookshelf.

Safari offers a solution that’s better than e-books. It’s a virtual library that lets you easily search thousands of top tech books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.

Acknowledgments

A book is a living thing, and what you now hold in your hands is a far cry from my initial ramblings. The person most responsible for this metamorphosis is my editor, Jonathan Gennick; thank you for your assistance in every step of this project, both for your editorial prowess and your expertise with the SQL language. Next, I would like to acknowledge my three technical reviewers, Peter Gulutzan, Joseph Molinaro, and Jeff Cox, who challenged me to make this book both technically sound and appropriate for readers new to SQL. Also, many thanks to the multitude of people at O’Reilly Media who have helped make this book a reality, including my production editor, Matt Hutchinson; the cover designer, Ellie Volckhausen; and the illustrator, Rob Romano.