Learning SQL, 2nd Edition

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 15 chapters and 3 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.

Chapter 14, Views, shows how to build an interface to shield users from data complexities.

Chapter 15, Metadata, demonstrates the utility of the data dictionary.

Appendix A, shows the database schema used for all examples in the book.

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

Appendix C, shows solutions to the chapter exercises.

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.

Note

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

800-998-9938 (in the United States or Canada)

707-829-0515 (international or local)

707-829-0104 (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/9780596520830

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 website 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, Second Edition, by Alan Beaulieu. Copyright 2009 O’Reilly Media, Inc., 978-0-596-52083-0.”

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® Books Online

Note

When you see a Safari® Books Online 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://my.safaribooksonline.com.

Acknowledgments

I would like to thank my editor, Mary Treseler, for helping to make this second edition a reality, and many thanks to Kevin Kline, Roy Owens, Richard Sonen, and Matthew Russell, who were kind enough to review the book for me over the Christmas/New Year holidays. I would also like to thank the many readers of my first edition who were kind enough to send questions, comments, and corrections. Lastly, I thank my wife, Nancy, and my daughters, Michelle and Nicole, for their encouragement and inspiration.