Learning SQL, 2nd edition

1 SECOND EDITIONL earning SQLAlan BeaulieuBeijing Cambridge Farnham K ln Sebastopol Taipei TokyoDl d t WB k CLearning SQL, Second Editionby Alan BeaulieuCopyright 2009 O Reilly Media, Inc. All rights in the United States of by O Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA Reilly books may be purchased for educational, business, or sales promotional use. Online editionsare also available for most titles ( ). For more information, contact our corporate/institutional sales department: (800) 998-9938 or E. TreselerProduction Editor:Loranah DimantCopyeditor:Audrey DoyleProofreader:Nancy ReinhardtIndexer:Ellen Troutman ZaigCover Designer:Karen MontgomeryInterior Designer:David FutatoIllustrator:Robert RomanoPrinting History:August 2005:First edition . April 2009:Second edition . Nutshell Handbook, the Nutshell Handbook logo, and the O Reilly logo are registered trademarks ofO Reilly Media, Inc.

2 Learning SQL, the image of an Andean marsupial tree frog, and related trade dressare trademarks of O Reilly Media, of the designations used by manufacturers and sellers to distinguish their products are claimed astrademarks. Where those designations appear in this book, and O Reilly Media, Inc. was aware of atrademark claim, the designations have been printed in caps or initial every precaution has been taken in the preparation of this book, the publisher and author assumeno responsibility for errors or omissions, or for damages resulting from the use of the information con-tained book uses RepKover , a durable and flexible lay-flat : 978-0-596-52083-0[M]1239115419Dl d t WB k CTable of ContentsPreface .. Little Background .. 1 Introduction to Databases1 Nonrelational Database Systems2 The Relational Model4 Some Terminology6 What Is SQL?7 SQL Statement Classes7 SQL: A Nonprocedural Language9 SQL Examples10 What Is MySQL?

3 12 What s in and Populating a Database .. 15 Creating a MySQL Database15 Using the mysql Command-Line Tool17 MySQL Data Types18 Character Data18 Numeric Data21 Temporal Data23 Table Creation25 Step 1: Design25 Step 2: Refinement26 Step 3: Building SQL Schema Statements27 Populating and Modifying Tables30 Inserting Data31 Updating Data35 Deleting Data35 When Good Statements Go Bad36 Nonunique Primary Key36 Nonexistent Foreign Key36iiiDl d t WB k CColumn Value Violations37 Invalid Date Conversions37 The Bank Primer .. 41 Query Mechanics41 Query Clauses43 The select Clause43 Column Aliases46 Removing Duplicates47 The from Clause48 Tables49 Table Links51 Defining Table Aliases52 The where Clause52 The group by and having Clauses54 The order by Clause55 Ascending Versus Descending Sort Order57 Sorting via Expressions58 Sorting via Numeric Placeholders59 Test Your .. 63 Condition Evaluation63 Using Parentheses64 Using the not Operator65 Building a Condition66 Condition Types66 Equality Conditions66 Range Conditions68 Membership Conditions71 Matching Conditions73 Null: That Four-Letter Word76 Test Your Multiple Tables.

4 81 What Is a Join?81 Cartesian Product82 Inner Joins83 The ANSI Join Syntax86 Joining Three or More Tables88 Using subqueries As Tables90 Using the Same Table Twice92iv|Table of ContentsDl d t WB k CSelf-Joins93 Equi-Joins Versus Non-Equi-Joins94 Join Conditions Versus Filter Conditions96 Test Your with Sets .. 99 Set Theory Primer99 Set Theory in Practice101 Set Operators103 The union Operator103 The intersect Operator106 The except Operator107 Set Operation Rules108 Sorting Compound Query Results108 Set Operation Precedence109 Test Your Generation, Conversion, and Manipulation .. 113 Working with String Data113 String Generation114 String Manipulation119 Working with Numeric Data126 Performing Arithmetic Functions126 Controlling Number Precision128 Handling Signed Data130 Working with Temporal Data130 Dealing with Time Zones131 Generating Temporal Data132 Manipulating Temporal Data137 Conversion Functions141 Test Your and Aggregates.

5 143 Grouping Concepts143 Aggregate Functions145 Implicit Versus Explicit Groups146 Counting Distinct Values147 Using Expressions149 How Nulls Are Handled149 Generating Groups150 Single-Column Grouping151 Multicolumn Grouping151 Grouping via Expressions152 Table of Contents|vDl d t WB k CGenerating Rollups152 Group Filter Conditions155 Test Your .. 157 What Is a Subquery?157 Subquery Types158 Noncorrelated Subqueries159 Multiple-Row, Single-Column Subqueries160 Multicolumn Subqueries165 Correlated Subqueries167 The exists Operator169 Data Manipulation Using Correlated Subqueries170 When to Use Subqueries171 subqueries As Data Sources172 subqueries in Filter Conditions177 subqueries As Expression Generators177 Subquery Wrap-up181 Test Your Revisited .. 183 Outer Joins183 Left Versus Right Outer Joins187 Three-Way Outer Joins188 Self Outer Joins190 Cross Joins192 Natural Joins198 Test Your Logic.

6 203 What Is Conditional Logic?203 The Case Expression204 Searched Case Expressions205 Simple Case Expressions206 Case Expression Examples207 Result Set Transformations208 Selective Aggregation209 Checking for Existence211 Division-by-Zero Errors212 Conditional Updates213 Handling Null Values214 Test Your Knowledge215vi|Table of ContentsDl d t WB k .. 217 Multiuser Databases217 Locking217 Lock Granularities218 What Is a Transaction?219 Starting a Transaction220 Ending a Transaction221 Transaction Savepoints223 Test Your and Constraints .. 227 Indexes227 Index Creation228 Types of Indexes231 How Indexes Are Used234 The Downside of Indexes237 Constraints238 Constraint Creation238 Constraints and Indexes239 Cascading Constraints240 Test Your .. 245 What Are Views?245 Why Use Views?248 Data Security248 Data Aggregation249 Hiding Complexity250 Joining Partitioned Data251 Updatable Views251 Updating Simple Views252 Updating Complex Views253 Test Your.

7 257 Data About Data257 Information_Schema258 Working with Metadata262 Schema Generation Scripts263 Deployment Verification265 Dynamic SQL Generation266 Test Your Knowledge270 Table of Contents|viiDl d t WB k Diagram for Example Database .. Extensions to the SQL Language .. to Exercises .. 287 Index .. 309viii|Table of ContentsDl d t WB k CPrefaceProgramming languages come and go constantly, and very few languages in use todayhave roots going back more than a decade or so. Some examples are Cobol, which isstill used quite heavily in mainframe environments, and C, which is still quite popularfor operating system and server development and for embedded systems. In the data-base arena, we have SQL, whose roots go all the way back to the is the language for generating, manipulating, and retrieving data from a relationaldatabase. One of the reasons for the popularity of relational databases is that properlydesigned relational databases can handle huge amounts of data.

8 When working withlarge data sets, SQL is akin to one of those snazzy digital cameras with the high-powerzoom lens in that you can use SQL to look at large sets of data, or you can zoom in onindividual rows (or anywhere in between). Other database management systems tendto break down under heavy loads because their focus is too narrow (the zoom lens isstuck on maximum), which is why attempts to dethrone relational databases and SQLhave largely failed. Therefore, even though SQL is an old language, it is going to bearound for a lot longer and has a bright future in Learn SQL?If you are going to work with a relational database, whether you are writing applica-tions, performing administrative tasks, or generating reports, you will need to knowhow to interact with the data in your database. Even if you are using a tool that generatesSQL for you, such as a reporting tool, there may be times when you need to bypass theautomatic generation feature and write your own SQL SQL has the added benefit of forcing you to confront and understand the datastructures used to store information about your organization.

9 As you become com-fortable with the tables in your database, you may find yourself proposing modifica-tions or additions to your database d t WB k CWhy Use This Book to Do It?The SQL language is broken into several categories. Statements used to create databaseobjects (tables, indexes, constraints, etc.) are collectively known as SQL schema state-ments. The statements used to create, manipulate, and retrieve the data stored in adatabase are known as the SQL data statements. If you are an administrator, you willbe using both SQL schema and SQL data statements. If you are a programmer or reportwriter, you may only need to use (or be allowed to use) SQL data statements. Whilethis book demonstrates many of the SQL schema statements, the main focus of thisbook is on programming only a handful of commands, the SQL data statements look deceptively my opinion, many of the available SQL books help to foster this notion by onlyskimming the surface of what is possible with the language.

10 However, if you are goingto work with SQL, it behooves you to understand fully the capabilities of the languageand how different features can be combined to produce powerful results. I feel that thisis the only book that provides detailed coverage of the SQL language without the addedbenefit of doubling as a door stop (you know, those 1,250-page complete referen-ces that tend to gather dust on people s cubicle shelves).While the examples in this book run on MySQL, Oracle Database, and SQL Server, Ihad to pick one of those products to host my sample database and to format the resultsets returned by the example queries. Of the three, I chose MySQL because it is freelyobtainable, easy to install, and simple to administer. For those readers using a differentserver, I ask that you download and install MySQL and load the sample database sothat you can run the examples and experiment with the of This BookThis 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 2, Creating and Populating a Database, demonstrates how to create aMySQL database, create the tables used for the examples in this book, and populatethe tables with 3, Query Primer, introduces the select statement and further demon-strates the most common clauses (select, from, where).