WIXLIB

ContentsSQL Set Operations239Classic Set Operations versus SQL239Finding Common Values: INTERSECT240Finding Missing Values: EXCEPT (DIFFERENCE)Combining Sets: UNION245Summary248CHAPTER 8 INNER JOINsWhat Is a JOIN?The INNER JOIN249249250What’s “Legal” to JOIN?250Column References251Syntax252Check Those Relationships!267Uses for INNER JOINs268Find Related Rows268Find Matching Values269Sample Statements269Two Tables270More Than Two Tables276Looking for Matching ValuesSummary294Problems for You to Solve283295CHAPTER 9 OUTER JOINs299What Is an OUTER JOIN?299The LEFT/RIGHT OUTER JOIN301Syntax302The FULL OUTER JOIN320Syntax320FULL OUTER JOIN on Non-Key ValuesUNION JOIN323Uses for OUTER JOINs323324Find Missing Values324Find Partially Matched InformationSample Statements325Summary341Problems for You to Solve341325243ix

xContentsCHAPTER 10 UNIONs345What Is a UNION?345Writing Requests with UNION348Using Simple SELECT Statements348Combining Complex SELECT StatementsUsing UNION More Than Once355Sorting a UNION357Uses for UNION358Sample Statements359Summary371Problems for You to Solve372CHAPTER 11 SubqueriesWhat Is a Subquery?Row SubqueriesTable SubqueriesScalar Subqueries351375376376377378Subqueries as Column Expressions378Syntax378An Introduction to Aggregate Functions: COUNT and MAXSubqueries as Filters384Syntax384Special Predicate Keywords for SubqueriesUses for Subqueries386397Build Subqueries as Column ExpressionsUse Subqueries as Filters398Sample Statements381397399Subqueries in Expressions399Subqueries in Filters405Summary413Problems for You to Solve414Part IV Summarizing and Grouping DataCHAPTER 12 Simple TotalsAggregate Functions419420Counting Rows and Values with COUNTComputing a Total with SUM425422417

ContentsCalculating a Mean Value with AVG427Finding the Largest Value with MAX428Finding the Smallest Value with MIN430Using More Than One Function431Using Aggregate Functions in FiltersSample Statements435Summary442Problems for You to Solve443CHAPTER 13 Grouping Data432445Why Group Data?446The GROUP BY Clause448Syntax449Mixing Columns and Expressions454Using GROUP BY in a Subquery in a WHERE ClauseSimulating a SELECT DISTINCT Statement457“Some Restrictions Apply”458Column Restrictions459Grouping on Expressions461Uses for GROUP BY462Sample Statements463Summary474Problems for You to Solve475CHAPTER 14 Filtering Grouped DataA New Meaning of “Focus Groups”Where You Filter Makes a Difference478482Should You Filter in WHERE or in HAVING?Avoiding the HAVING COUNT Trap485Uses for HAVING490Sample Statements491Summary499Problems for You to Solve500477482456xi

xiiContentsPart V Modifying Sets of Data503CHAPTER 15 Updating Sets of Data505What Is an UPDATE?505The UPDATE Statement506Using a Simple UPDATE Expression507A Brief Aside: Transactions510Updating Multiple Columns511Using a Subquery to Filter Rows512Using a Subquery UPDATE Expression518Uses for UPDATE520Sample Statements521Summary538Problems for You to Solve538CHAPTER 16 Inserting Sets of Data541What Is an INSERT?541The INSERT Statement543Inserting Values543Generating the Next Primary Key ValueInserting Data by Using SELECT548Uses for INSERT555Sample Statements556Summary568Problems for You to Solve568CHAPTER 17 Deleting Sets of DataWhat Is a DELETE?571The DELETE Statement572Deleting All Rows573Deleting Some Rows575Uses for DELETE579Sample Statements580Summary588Problems for You to Solve547589571

xiiiContentsPart VI Introduction to Solving Tough ProblemsCHAPTER 18 “NOT” and “AND” ProblemsA Short Review of Sets593593Sets with Multiple AND Criteria594Sets with Multiple NOT Criteria595Sets Including Some Criteria but Excluding OthersFinding Out the “Not” Case604Finding Multiple Matches in the Same TableUsing INNER JOIN608Using IN610Using EXISTS612Using GROUP BY/HAVINGSample Statements618Summary636Problems for You to Solve596597Using OUTER JOIN598Using NOT IN601Using NOT EXISTS603Using GROUP BY/HAVING607614637CHAPTER 19 Condition TestingConditional Expressions (CASE)Why Use CASE?Syntax642591641641642Solving Problems with CASE647Solving Problems with Simple CASE647Solving Problems with Searched CASE652Using CASE in a WHERE Clause655Sample Statements655Summary669Problems for You to Solve669CHAPTER 20 Using Unlinked Data and “Driver” TablesWhat Is Unlinked Data?672Deciding When to Use a CROSS JOIN675Solving Problems with Unlinked Data676671

xivContentsSolving Problems Using “Driver” Tables679Setting Up a Driver Table679Using a Driver Table682Sample Statements686Examples Using Unlinked Tables687Examples Using Driver Tables697Summary705Problems for You to SolveIn ClosingAppendices705709711A SQL Standard Diagrams713B Schema for the Sample Databases723Sales Orders Example Database724Sales Orders Modify Database725Entertainment Agency Example Database726Entertainment Agency Modify Database727School Scheduling Example Database728School Scheduling Modify Database729Bowling League Example Database730Bowling League Modify Database731Recipes Database732C Date and Time Types, Operations, and FunctionsIBM DB2733Microsoft Office Access736Microsoft SQL Server738MySQL740Oracle743D Suggested ReadingDatabase BooksBooks on SQLIndex747745745745733

ForewordIn the 25 years since the database language SQL was adopted as an international standard, and the 30 years since SQL database products appeared onthe market, SQL has become the predominant language for storing, modifying, retrieving, and deleting data. Today, a significant portion of the world’sdata—and the world’s economy—is tracked using SQL databases.SQL is everywhere because it is a very powerful tool for manipulating data. Itis in high-performance transaction processing systems. It is behind Web interfaces. I’ve even found SQL in network monitoring tools and spam firewalls.Today, SQL can be executed directly, embedded in programming languages,and accessed through call interfaces. It is hidden inside GUI developmenttools, code generators, and report writers. However visible or hidden, theunderlying queries are SQL. Therefore, to understand existing applicationsand to create new ones, you need to understand SQL.SQL Queries for Mere Mortals, Third Edition, provides a step-by-step, easy-toread introduction to writing SQL queries. It includes hundreds of exampleswith detailed explanations. This book provides the tools you need to understand, modify, and create SQL queries.As a database consultant and a participant in both the U.S. and internationalSQL standards committees, I spend a lot of time working with SQL. So, it iswith a certain amount of authority that I state,“The authors of this book notonly understand SQL, they also understand how to explain it.” Both qualitiesmake this book a valuable resource.—Keith W. Hare, Senior Consultant,JCC Consulting, Inc.Vice Chair, INCITS DM32.2—the USA SQL Standards Committee; Convenor, ISO/IEC JTC1 SC32 WG3—the International SQL Standards Committeexv

Preface“Language is by its very nature a communal thing; that is, it expressesnever the exact thing but a compromise—that which is common toyou, me, and everybody.”—Thomas Ernest Hulme, SpeculationsLearning how to retrieve information from or manipulate information in adatabase is commonly a perplexing exercise. However, it can be a relativelyeasy task as long as you understand the question you’re asking or the changeyou’re trying to make to the database. After you understand the problem, youcan translate it into the language used by any database system, which in mostcases is Structured Query Language (SQL). You have to translate your requestinto an SQL statement so that your database system knows what informationyou want to retrieve or change. SQL provides the means for you and yourdatabase system to communicate.Throughout our many years as database consultants, we’ve found that thenumber of people who merely need to retrieve information from a databaseor perform simple data modifications in a database far outnumber those whoare charged with the task of creating programs and applications for a database. Unfortunately, no books focus solely on this subject, particularly from a“mere mortals” viewpoint. There are numerous good books on SQL, to besure, but most are targeted to database programming and development.With this in mind, we decided it was time to write a book that would helppeople learn how to query a database properly and effectively. We producedthe first edition of this book in 2000. We created a second edition in 2008 thatintroduced basic ways to change data in your database using SQL. With thisnew edition, we stepped lightly into the realm of tougher problems—thesorts of problems that make the heads of even experienced users spin aroundthree times. The result of our efforts is in your hands. This book is uniquexvi

Prefacexviiamong SQL books in that it focuses on SQL with little regard to any one specific database system implementation. This third edition includes hundreds ofnew examples, and we included versions of the sample databases usingMicrosoft Office Access, Microsoft SQL Server, and the popular open-sourceMySQL database system. When you finish reading this book, you’ll have theskills you need to retrieve or modify any information you require.AcknowledgmentsWriting a book such as this is always a cooperative effort. There are alwayseditors, colleagues, friends, and relatives willing to lend their support andprovide valuable advice when we need it the most. These people continuallyprovide us with encouragement, help us to remain focused, and motivate usto see this project through to the end.First and foremost, we want to thank our acquisitions editor, Joan Murray, forhelping us get signed up to produce this third edition. Thanks also to Developmental Editor Chris Cleveland for shepherding us along the way. And wecan’t forget Production Editor Tonya Simpson and the production staff—they’re a great team! Finally, thanks to Associate Publisher David Dusthimer,who put this team together and kept a watchful eye over the entire process.Next, we’d like to acknowledge our technical editor, Dale Wallentine. We alsohad help from some of our database friends—Jeff Boyce, Ben Clothier, HenryHabermacher, Leo theDBGuy, and Doug Steele. Thanks once again to all of youfor your time and input and for helping us to make this a solid treatise on SQLqueries.Finally, another very special thanks to Keith Hare for providing the Foreword.As the Convenor of the International SQL Standards Committee, Keith is anSQL expert par excellence. We have a lot of respect for Keith’s knowledgeand expertise on the subject, and we’re pleased to have his thoughts and comments at the beginning of our book.

About the AuthorsJohn L. Viescas is an independent database consultant withmore than 45 years of experience. He began his career as a systems analyst, designing large database applications for IBM mainframe systems. He spent 6 years at Applied Data Research inDallas, Texas, where he directed a staff of more than 30 peopleand was responsible for research, product development, andcustomer support of database products for IBM mainframe computers. While working at Applied Data Research, John completed a degree inbusiness finance at the University of Texas at Dallas, graduating cum laude.John joined Tandem Computers, Inc., in 1988, where he was responsible forthe development and implementation of database marketing programs inTandem’s U.S. Western Sales region. He developed and delivered technicalseminars on Tandem’s relational database management system, NonStop SQL.John wrote his first book, A Quick Reference Guide to SQL (Microsoft Press,1989), as a research project to document the similarities in the syntax amongthe ANSI-86 SQL standard, IBM’s DB2, Microsoft’s SQL Server, Oracle Corporation’s Oracle, and Tandem’s NonStop SQL. He wrote the first edition of Running Microsoft Access (Microsoft Press, 1992) while on sabbatical fromTandem. He has since written four editions of Running, three editions ofMicrosoft Office Access Inside Out (Microsoft Press, 2003, 2007, and 2010—the successor to the Running series), and Building Microsoft Access Applications (Microsoft Press, 2005).John formed his own company in 1993. He provides information systemsmanagement consulting for a variety of small to large businesses around theworld, with a specialty in the Microsoft Access and SQL Server database management products. He maintains offices in Nashua, New Hampshire, and Paris,France. He has been recognized as a “Most Valuable Professional” (MVP) since1993 by Microsoft Product Support Services for his assistance with technicalquestions on public support forums. He set a landmark 20 consecutive yearsas an MVP in 2013.You can visit John’s Web site at www.viescas.com or contact him by e-mail atjohn@viescas.com.xviii

About the AuthorsxixMichael J. Hernandez has been an independent relationaldatabase consultant specializing in relational database design.He has more than 20 years of experience in the technologyindustry, developing database applications for a wide variety ofclients. He’s been a contributing author to a wide variety ofmagazine columns, white papers, books, and periodicals, and iscoauthor of the best-selling SQL Queries for Mere Mortals.Mike has been a top-rated and noted technical trainer for the government, themilitary, the private sector, and companies throughout the United States. Hehas spoken at numerous national and international conferences, and has consistently been a top-rated speaker and presenter.Aside from his technical background, Mike has a diverse set of skills and interests that he also pursues, ranging from the artistic to the metaphysical. Hisgreatest interest is still the guitar, as he’s been a practicing guitarist for morethan 40 years and played professionally for 15 years. He’s also a working actor,a great cook, loves to teach (writing, public speaking, music), has a gift for badpuns, and even reads Tarot cards.He says he’s never going to retire, per se, but rather just change whatever it ishe’s doing whenever he finally gets tired of it and move on to something elsethat interests him.

Introduction“I presume you’re mortal, and may err.”—James Shirley, The Lady of PleasureIf you’ve used a computer more than casually, you have probably used Structured Query Language or SQL—perhaps without even knowing it. SQL is thestandard language for communicating with most database systems. Any timeyou import data into a spreadsheet or perform a merge into a word processing program, you’re most likely using SQL in some form or another. Every timeyou go online to an e-commerce site on the Web and place an order for abook, a recording, a movie, or any of the dozens of other products you canorder, there’s a very high probability that the code behind the web pageyou’re using is accessing its databases with SQL. If you need to get information from a database system that uses SQL, you can enhance your understanding of the language by reading this book.Are You a Mere Mortal?You might ask,“Who is a mere mortal? Me?” The answer is not simple. Whenwe started to write this book, we thought we were experts in the databaselanguage called SQL. Along the way, we discovered we were mere mortals,too, in several areas. We understood a few specific implementations of SQLvery well, but we unraveled many of the complex intricacies of the languageas we studied how it is used in many commercial products. So, if you fit any ofthe following descriptions, you’re a mere mortal too! If you use computer applications that let you access information from adatabase system, you’re probably a mere mortal. The first time youdon’t get the information you expected using the query tools built in toyour application, you’ll need to explore the underlying SQL statementsto find out why.xx

Introductionxxi If you have recently discovered one of the many available desktop database applications but are struggling with defining and querying thedata you need, you’re a mere mortal. If you’re a database programmer who needs to “think outside of thebox” to solve some complex problems, you’re a mere mortal. If you’re a database guru in one product but are now faced with integrating the data from your existing system into another system that supports SQL, you’re a mere mortal.In short, anyone who has to use a database system that supports SQL can usethis book. As a beginning database user who has just discovered that the datayou need can be fetched using SQL, you will find that this book teaches youall the basics and more. For an expert user who is suddenly faced with solvingcomplex problems or integrating multiple systems that support SQL, thisbook will provide insights into leveraging the complex abilities of the SQLdatabase language.About This BookEverything you read in this book is based on the current International Organization for Standardization (ISO) Standard for the SQL database language –SQL/Foundation (document ISO/IEC 9075-2:2011), as currently implementedin most of the popular commercial database systems. The ISO document wasalso adopted by the American National Standards Institute (ANSI), so this istruly an international standard. The SQL you’ll learn here is not specific to anyparticular software product.As you’ll learn in more detail in Chapter 3,“A Concise History of SQL,” the SQLStandard defines both more and less than you’ll find implemented in mostcommercial database products. Most database vendors have yet to implementmany of the more advanced features, but most do support the core of thestandard.We researched a wide range of popular products to make sure that you canuse what we’re teaching in this book. Where we found parts of the core of thelanguage not supported by some major products, we warn you in the text andshow you alternate ways to state your database requests in standard SQL.When we found significant parts of the SQL Standard supported by only a fewvendors, we introduced you to the syntax and then suggested alternatives.

xxiiIntroductionWe have organized this book into six major sections: Part I,“Relational Databases and SQL,” explains how modern databasesystems are based on a rigorous mathematical model and provides abrief history of the database query language that has evolved into whatwe know as SQL. We also discuss some simple rules that you can use tomake sure your database design is sound. Part II,“SQL Basics,” introduces you to using the SELECT statement, creating expressions, and sorting information with an ORDER BY clause.You’ll also learn how to filter data by using a WHERE clause. Part III,“Working with Multiple Tables,” shows you how to form

SQL Queries for Mere Mortals,Third Edition,provides a step-by-step,easy-to-read intro-duction to writing SQL queries. It includes hundreds of examples with detailed expla-nations. This book provides the tools you need to understand,modify,and create SQ