SQL Queries For Mere Mortals : A Hands-On Guide To Data .
Praise for SQL Queries for Mere Mortals , Second EditionUnless you are working at a very advanced level, this is the only SQL book youwill ever need. The authors have taken the mystery out of complex queries andexplained principles and techniques with such clarity that a “Mere Mortal” willindeed be empowered to perform the superhuman. Do not walk past this book!— Graham Mandeno, Database ConsultantI learned SQL primarily from the first edition of this book, and I am pleased to seea second edition of this book so that others can continue to benefit from its organized presentation of the language. Starting from how to design your tables so thatSQL can be effective (a common problem for database beginners), and then continuing through the various aspects of SQL construction and capabilities, thereader can become a moderate expert upon completing the book and its samples.Learning how to convert a question in English into a meaningful SQL statementwill greatly facilitate your mastery of the language. Numerous examples from reallife will help you visualize how to use SQL to answer the questions about the datain your database. Just one of the “watch out for this trap” items will save you morethan the cost of the book when you avoid that problem when writing your queries.I highly recommend this book if you want to tap the full potential of your database.— Kenneth D. Snell, Ph.D., Database Designer/ProgrammerI don’t think they do this in public schools any more, and it is a shame, but do youremember in the seventh and eighth grades when you learned to diagram a sentence? Those of you who do may no longer remember how you did it, but all ofyou do write better sentences because of it. John Viescas and Mike Hernandezmust have remembered because they take everyday English queries and literallytranslate them into SQL. This is an important book for all database designers. Ittakes the complexity of mathematical Set Theory and of First Order PredicateLogic, as outlined in E. F. Codd’s original treatise on relational database design,and makes it easy for anyone to understand. If you want an elementary- throughintermediate-level course on SQL, this is the one book that is a requirement, nomatter how many others you buy.— Arvin Meyer, MCP, MVPSQL Queries for Mere Mortals, Second Edition, provides a step-by-step, easy-toread introduction to writing SQL queries. It includes hundreds of examples withdetailed explanations. This book provides the tools you need to understand, modify, and create SQL queries.— Keith W. Hare, Convenor, ISO/IEC JTC1 SC32 WG3—the International SQL Standards Committee
Even in this day of wizards and code generators, successful database developersstill require a sound knowledge of Structured Query Language (SQL, the standardlanguage for communicating with most database systems). In this book, John andMike do a marvelous job of making what’s usually a dry and difficult subject comealive, presenting the material with humor in a logical manner, with plenty of relevant examples. I would say that this book should feature prominently in the collection on the bookshelf of all serious developers, except that I’m sure it’ll get somuch use that it won’t spend much time on the shelf!— Doug Steele, Microsoft Access Developer and author
SQL QueriesforMere Mortals Second Edition
Addison-Wesley presents theFor Mere Mortals SeriesSeries Editor: Michael J. HernandezThe goal of the For Mere Mortals Series is to present you with information on importanttechnology topics in an easily accessible, common-sense manner.The primary audiencefor Mere Mortals books is that of readers who have little or no background or formaltraining in the subject matter. Books in the Series avoid dwelling on the theoretical andinstead take you right into the heart of the topic with a matter-of-fact, hands-on approach.The books are not designed to address all the intricacies of a given technology, but theydo not avoid or gloss over complex, essential issues either. Instead, they focus on providing core, foundational knowledge in a way that is easy to understand and that will properly ground you in the topic.This practical approach provides you with a smooth learningcurve and helps you to begin to solve your real-world problems immediately. It also prepares you for more advanced treatments of the subject matter, should you decide to pursue them, and even enables the books to serve as solid reference material for those of youwith more experience.The software-independent approach taken in most books withinthe Series also teaches the concepts in such a way that they can be applied to whateverparticular application or system you may need to use.Titles in the Series:Project Management for Mere Mortals Claudia M. Baca. ISBN: 0321423453User Interface Design for Mere Mortals Eric Butow. ISBN: 0321447735Database Design for Mere Mortals , Second Edition:A Hands-On Guide to Relational Database DesignMichael J. Hernandez. ISBN: 0201752840Microsoft Office Project for Mere Mortals :Solving the Mysteries of Microsoft Office ProjectPatti Jansen. ISBN: 0321423429UML for Mere Mortals Robert A. Maksimchuk and Eric J. Naiburg. ISBN: 0321246241VSTO for Mere Mortals Kathleen McGrath and Paul Stubbs. ISBN: 0321426711SQL Queries for Mere Mortals :A Hands-On Guide to Data Manipulation in SQL, Second EditionJohn L.Viescas and Michael J. Hernandez. ISBN: 0321444434For more information, check out the series web site atwww.awprofessional.com/ForMereMortalsSeries.
SQL QueriesforMere Mortals Second EditionA Hands-On Guideto Data Manipulation in SQLJohn L. ViescasMichael J. HernandezUpper Saddle River, NJ Boston Indianapolis San Francisco New York Toronto Montreal London Munich Paris MadridCapetown Sydney Tokyo Singapore Mexico City
ContentsForewordPrefacexviixixAbout the AuthorsIntroductionxxixxiiiAre You a Mere Mortal?xxiiiAbout This BookxxivWhat This Book Is NotxxvHow to Use This BookxxviReading the Diagrams Used in This BookxxviiSample Databases Used in This Bookxxx“Follow the Yellow Brick Road”xxxiiPART I Relational Databases and SQLCHAPTER 1 What Is Relational?Topics Covered in This Chapter3Types of Databases3A Brief History of the Relational ModelIn the Beginning . . .4Relational Database Software5Anatomy of a Relational Database6Tables6Fields7Records8Keys8134vii
viiiContentsViews9Relationships11What’s in It for You?15Where Do You Go from Here?Summary1716CHAPTER 2 Ensuring Your Database Structure Is SoundTopics Covered in This Chapter19Why Is This Chapter Here?19Why Worry about Sound Structures?Fine-Tuning Fields21What’s in a Name? (Part One)21Smoothing Out the Rough EdgesResolving Multipart Fields25Resolving Multivalued Fields27Fine-Tuning Tables202330What’s in a Name? (Part Two)30Ensuring a Sound Structure32Resolving Unnecessary Duplicate FieldsIdentification Is the Key39Establishing Solid Relationships3342Establishing a Deletion Rule44Setting the Type of Participation46Setting the Degree of Participation48Is That All?Summary5051CHAPTER 3 A Concise History of SQLTopics Covered in This Chapter53The Origins of SQL54Early Vendor Implementations55“. . . And Then There Was a Standard”56Evolution of the ANSI/ISO Standard58Other SQL Standards61Commercial ImplementationsWhat the Future Holds65Why Should You Learn SQL?Summary6664655319
ContentsPART II SQL Basics69CHAPTER 4 Creating a Simple QueryTopics Covered in This Chapter71Introducing SELECT72The SELECT Statement73A Quick Aside: Data versus InformationTranslating Your Request into SQL77Expanding the Field of Vision81Using a Shortcut to Request All ColumnsEliminating Duplicate RowsSorting Information87758384First Things First: Collating SequencesLet’s Now Come to Order89Saving Your Work92Sample Statements93Summary102Problems for You to Solve7188103CHAPTER 5 Getting More Than Simple ColumnsTopics Covered in This Chapter105What Is an Expression?106What Type of Data Are You Trying to Express?107Changing Data Types: The CAST Function110Specifying Explicit Values112Character String Literals112Numeric Literals114Datetime Literals115Types of Expressions117Concatenation117Mathematical Expressions121Date and Time Arithmetic124Using Expressions in a SELECT Clause128Working with a Concatenation Expression128Naming the Expression129Working with a Mathematical Expression131105ix
xContentsWorking with a Date Expression132A Brief Digression: Value Expressions133That “Nothing” Value: NullIntroducing Null136135The Problem with Nulls138Sample Statements139Summary147Problems for You to Solve149CHAPTER 6 Filtering Your Data151Topics Covered in This Chapter151Refining What You See Using WHERE151The WHERE Clause152Using a WHERE Clause154Defining Search Conditions156Comparison156Range164Set Membership167Pattern Match169Null173Excluding Rows with NOT175Using Multiple Conditions178Introducing AND and OR179Excluding Rows: Take Two184Order of Precedence187Checking for Overlapping Ranges191Nulls Revisited: A Cautionary Note193Expressing Conditions in Different Ways197Sample Statements198Summary206Problems for You to Solve207PART III Working with Multiple TablesCHAPTER 7 Thinking in SetsTopics Covered in This ChapterWhat Is a Set, Anyway?214213213211
ContentsOperations on SetsIntersection216215Intersection in Set Theory216Intersection between Result Sets217Problems You Can Solve with an IntersectionDifference222Difference in Set Theory222Difference between Result Sets224Problems You Can Solve with DifferenceUnion221227228Union in Set Theory228Combining Result Sets Using a UnionProblems You Can Solve with UnionSQL Set Operations230232233Classic Set Operations versus SQL233Finding Common Values: INTERSECT234Finding Missing Values: EXCEPT (Difference)Combining Sets: UNION239Summary242CHAPTER 8 INNER JOINs243Topics Covered in This Chapter243What Is a JOIN?243The INNER JOIN244What’s “Legal” to JOIN?244Column References245Syntax246Check Those Relationships!261Uses for INNER JOINs262Find Related Rows262Find Matching Values263Sample Statements263Two Tables264More Than Two Tables270Looking for Matching Values277Summary288Problems for You to Solve289236xi
xiiContentsCHAPTER 9 OUTER JOINs293Topics Covered in This Chapter293What Is an OUTER JOIN?293The LEFT/RIGHT OUTER JOIN295Syntax296The FULL OUTER JOIN314Syntax314317FULL OUTER JOIN on Non-Key ValuesUNION JOIN317Uses for OUTER JOINs318318Find Missing ValuesFind Partially Matched InformationSample Statements319Summary335Problems for You to SolveCHAPTER 10 UNIONs319335339Topics Covered in This ChapterWhat Is a UNION?339Writing Requests with UNION339342Using Simple SELECT Statements342Combining Complex SELECT StatementsUsing UNION More Than Once349Sorting a UNION351Uses for UNION352Sample Statements353Summary365Problems for You to Solve366CHAPTER 11 SubqueriesTopics Covered in This ChapterWhat Is a Subquery?370Row Subqueries370Table Subqueries371Scalar Subqueries372369369345
ContentsSubqueries as Column ExpressionsSyntax372372An Introduction to Aggregate Functions: COUNT and MAXSubqueries as FiltersSyntax378377379Special Predicate Keywords for SubqueriesUses for Subqueries392392Build Subqueries as Column ExpressionsUse Subqueries as Filters393Sample Statements375394Subqueries in Expressions395Subqueries in Filters400Summary409Problems for You to Solve410PART IV Summarizing and Grouping DataCHAPTER 12 Simple TotalsTopics Covered in This ChapterAggregate Functions416415415Counting Rows and Values with COUNT418Computing a Total with SUM421Calculating a Mean Value with AVG423Finding the Largest Value with MAX424Finding the Smallest Value with MIN426Using More Than One Function427Using Aggregate Functions in FiltersSample Statements431Summary438Problems for You to Solve439CHAPTER 13 Grouping DataTopics Covered in This ChapterWhy Group Data?442The GROUP BY Clause444Syntax445Mixing Columns and Expressions428441441450413xiii
xivContentsUsing GROUP BY in a Subquery in a WHERE ClauseSimulating a SELECT DISTINCT Statement453“Some Restrictions Apply”452454Column Restrictions455Grouping on Expressions457Uses for GROUP BY458Sample Statements459Summary470Problems for You to Solve471CHAPTER 14 Filtering Grouped Data473Topics Covered in This Chapter473A New Meaning of “Focus Groups”474When You Filter Makes a Difference478Should You Filter in WHERE or in HAVING?Avoiding the HAVING COUNT Trap481Uses for HAVING486Sample Statements487Summary496Problems for You to Solve496PART V Modifying Sets of DataCHAPTER 15 Updating Sets of DataTopics Covered in This ChapterWhat Is an UPDATE?501The UPDATE Statement502501Using a Simple UPDATE Expression503A Brief Aside: Transactions506Updating Multiple Columns507Using a Subquery to Filter Rows508Using a Subquery UPDATE Expression514Uses for UPDATE516Sample Statements517Summary533Problems for You to Solve478534499501
ContentsCHAPTER 16 Inserting Sets of DataTopics Covered in This ChapterWhat Is an INSERT?537The INSERT Statement539Inserting Values539537Generating the Next Primary Key ValueInserting Data by Using SELECT544Uses for INSERT550Sample Statements552Summary562Problems for You to Solve537542563CHAPTER 17 Deleting Sets of Data567Topics Covered in This Chapter567What Is a DELETE?567The DELETE Statement568Deleting All Rows569Deleting Some Rows571Uses for DELETE575Sample Statements576Summary583Problems for You to Solve584In ClosingAPPENDICES587589A SQL Standard Diagrams591B Schema for the Sample DatabasesC Date and Time FunctionsD Suggested ReadingIndex617615607601xv
This page intentionally left blank
ForewordIn the 20 years since the database language SQL was adopted as an international standard, and the 25 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, Second Edition, provides a step-by-step, easyto-read 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. HareSenior Consultant, JCC Consulting, Inc.Vice Chair, INCITS H2—the USA SQL Standards CommitteeConvenor, ISO/IEC JTC1 SC32 WG3—the InternationalSQL Standards Committeexvii
This page intentionally left blank
Preface“Language is by its very nature a communal thing;that is, it expresses never the exact thing but acompromise—that which is common to you, me, and everybody.”—Thomas Earnest 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. With this new edition, we also wanted tointroduce you to the basic ways to change data in your database using SQL.The result of our decision is in your hands. This book is unique among SQLbooks in that it focuses on SQL with little regard to any one specific databasesystem implementation. This second edition includes hundreds of new examples, and we included versions of the sample databases using the popularopen-source MySQL database system. When you finish reading this book,you’ll have the skills you need to retrieve or modify any information yourequire.xix
xxPreface and AcknowledgmentsAcknowledgmentsWriting a book such as this is always a cooperative effort. There are alwayseditors, colleagues, friends, and relatives willing to lend their support and provide valuable advice when we need it the most. These people continually provide us with encouragement, help us to remain focused, and motivate us tosee this project through to the end.First and foremost, we want to thank our acquisitions editor, Elizabeth Peterson, for prodding us to produce this second edition. Thanks also to KristinWeinberger for shepherding us along the way. And we can’t forget our finalacquisitions editor, Chuck Toporek, as well as Romny French and the production staff—they’re a great team! Special thanks to Chrysta Meadowbrooke,who did a fabulous job copyediting the final manuscript. She cleaned up lotsof inconsistencies and even pointed out some SQL examples that needed fixing! Finally, thanks to editor-in-chief Karen Gettman, who put this teamtogether and kept a watchful eye over the entire process.Next, we’d like to acknowledge our technical editors, particularly StephenForte and Keith Hare. Keith especially spent time working through all theexamples, pointing out a few errors, and making suggestions to enhance thetext. Thanks once again to all of you for your time and input and for helpingus to make this a solid treatise on SQL queries.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.
Introduction“I presume you’re mortal, and may err.”—James ShirleyThe 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
User Interface Design for Mere Mortals Eric Butow.ISBN:0321447735 Database Design for Mere Mortals , Second Edition: A Hands-On Guide to Relational Database Design Michael J.Hernandez.ISBN:0201752840 Microsoft Office Project for Mere Mortals : Solving the Mysteries of Microsoft Office Project Patti Jansen.ISBN:0321423429 UML for Mere Mortals
ISBN-10: 1118094832 ISBN-13: 978-1118094839 IT 321 INTRO TO REQURIEMENT ANALYSIS Database Design for Mere Mortals (ISBN-10: 0321884493) and SQL Queries for Mere Mortals (ISBN-10: 0321992474). IT 331 INTRO TO DATABASES Relational Database Design for Mere Mortals Publisher: A
SQL as Data Manipulation Language (DML) Insert and update data Simple SQL queries Advanced SQL queries Views. 2 FU-Berlin, DBS I 2006, Hinze / Scholz SQL / DML: Overview Insert, update, delete data Query data . SQL / DML: Simple queries More examples: All movies not by Lucas (SELECT * FROM Movie)
SQL can be effective (a common problem for database beginners), and then con-tinuing through the various aspects of SQL construction and capabilities, the reader can become a moderate expert upon completing the book and its samples. Learning how to convert a question in English into a meaningful SQL statement
Bruksanvisning för bilstereo . Bruksanvisning for bilstereo . Instrukcja obsługi samochodowego odtwarzacza stereo . Operating Instructions for Car Stereo . 610-104 . SV . Bruksanvisning i original
SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact- SQL). T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capab
MS SQL Server: MS SQL Server 2017, MS SQL Server 2016, MS SQL Server 2014, MS SQL Server 2012, MS SQL Server 2008 R2, 2008, 2008 (64 bit), 2008 Express, MS SQL Server 2005, 2005 (64 bit), 2005 Express, MS SQL Server 2000, 2000 (64 bit), 7.0 and mixed formats. To install the software, follow the steps: 1. Double-click Stellar Repair for MS SQL.exe.
Server 2005 , SQL Server 2008 , SQL Server 2008 R2 , SQL Server 2012 , SQL Server 2014 , SQL Server 2005 Express Edition , SQL Server 2008 Express SQL Server 2008 R2 Express , SQL Server 2012 Express , SQL Server 2014 Express .NET Framework 4.0, .NET Framework 2.0,
ASTM C 1628 06ASTM C 1628 06 Standard Specification for Joints for Concrete Gravity Flow Sewer Pipe Using Rubber Gaskets AS C 16 09ASTM C 1677 09 Standard Specification for Joints for Concrete Box, Using Rubber Gaskets ASTM C 1619 05 Standard Specification for Elastomeric Seals for Joining Concrete Structures ASTM C 505 05a Standard Specification for Irrigation Pipe with Rubber Gasket Joints .
