Transcription
SQL QueriesforMere MortalsThird EditionA Hands-On Guideto Data Manipulation in SQLJohn L. ViescasMichael J. HernandezrATAddison-WesleyTTUpper Saddle River, NJ Boston Indianapolis San Francisco New York Toronto Montreal London Munich Paris MadridCapetown Sydney Tokyo Singapore Mexico City
! ContentsForewordPrefacexvxviAbout the AuthorsIntroductionxviiixxAre You a Mere Mortal?xxAbout This BookxxiWhat This Book Is NotxxiiiH o w to Use This BookxxiiiReading the Diagrams Used in This BookxxivSample Databases Used in This Bookxxviii"Follow the Yellow Brick Road"xxxPart IRelational Databases a n d SQLCHAPTER 1W h a t Is Relational?Types of Databases3A Brief History of the Relational ModelIn the Beginning . . .4Relational Database Systems5Anatomy of a Relational Database7Tables7Fields9Records9Keys9Views1 1Relationships12What's in It for You?17Where Do You Go from Here?18Summary19134v
viContentsCHAPTER 2Ensuring Your Database Structure Is SoundWhy Is This Chapter Here?21Why Worry about Sound Structures?22Fine-Tuning Fields23What's in a Name? (Part One)23Smoothing Out the Rough Edges25Resolving Multipart Fields27Resolving Multivalued Fields30Fine-Tuning Tables32What's in a Name? (Part Two)33Ensuring a Sound Structure35Resolving Unnecessary Duplicate Fields36Identification Is the Key42Establishing Solid Relationships45Establishing a Deletion Rule48Setting the Type of Participation49Setting the Degree of Participation52Is That All?54Summary55CHAPTER 3A Concise History o f SQLThe Origins of SQLEarly Vendor Implementations59". . . And Then There Was a Standard"60Evolution of the ANSI/ISO Standard62Other SQL Standards65Commercial Implementations68What the Future Holds69Why Should You Learn SQL?69Which Version of SQL Does This Book Cover?Summary70Part IISQL BasicsCHAPTER 4The SELECT Statement7073Creating a Simple Q u e r yIntroducing SELECT575876777521
ContentsA Quick Aside: Data versus Information79Translating Your Request into SQL81Expanding the Field of Vision85Using a Shortcut to Request All Columns87Eliminating Duplicate Rows88Sorting Information91First Things First: Collating Sequences92Let's Now Come to Order93Saving Your Work96Sample Statements97Summary106Problems for You to Solve107CHAPTER 5G e t t i n g More T h a n Simple ColumnsWhat Is an Expression?110What Type of Data Are You Trying to Express?1 1 1Changing Data Types: The CAST Function1 14Specifying Explicit Values1 16Character String LiteralsI16Numeric Literals1 18Datetime Literals119Types of Expressions121Concatenation122Mathematical Expressions125Date and Time Arithmetic129Using Expressions in a SELECT Clause133Working with a Concatenation Expression134Naming the Expression135Working with a Mathematical Expression137Working with a Date Expression138A Brief Digression: Value Expressions139That "Nothing" Value: Null141Introducing Null142The Problem with Nulls143Sample Statements144Summary153Problems for You to Solve154109
viiiContentsCHAPTER 6Filtering Your D a t aRefining What You See Using WHEREThe WHERE Clause1 58Using a WHERE Clause160Defining Search Conditions162Comparison163Range170Set Membership1 73Pattern MatchI 75Null179Excluding Rows with NOT1811571 57Using Multiple Conditions184Introducing AND and OR185Excluding Rows: Take Two191Order of Precedence193Checking for Overlapping Ranges1 97Nulls Revisited: A Cautionary Note199Expressing Conditions in Different Ways203Sample Statements204Summary21 2Problems for You to Solve213Part IIIWorking with Multiple TablesCHAPTER 7T h i n k i n g i n Sets219What Is a Set, Anyway?220Operations on Sets221Intersection222Intersection in Set Theory222Intersection between Result Sets224Problems You Can Solve with an Intersection227Difference228Difference in Set Theory228Difference between Result Sets230Problems You Can Solve with Difference233Union234Union in Set Theory234Combining Result Sets Using a Union236Problems You Can Solve with Union238217
ContentsSQL Set Operations239Classic Set Operations versus SQL239Finding Common Values: INTERSECT240Finding Missing Values: EXCEPT (DIFFERENCE)Combining Sets: UNION245Summary248CHAPTER 8I N N E R JOINS249What Is a JOIN?249The INNER JOIN250What'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 SolveCHAPTER 9283295OUTER JOINS299What Is an OUTER JOIN?299The LEFT/RIGHT OUTER JOIN301Syntax302The FULL OUTER JOIN320Syntax320FULL OUTER JOIN on Non-Key Values323UNION JOIN323Uses for OUTER JOINs324Find Missing Values324Find Partially Matched Information325Sample Statements325Summary341Problems for You to Solve341243ix
xContentsCHAPTER 1 0UNIONS345What Is a UNION?345Writing Requests w i t h UNION348Using Simple SELECT Statements348Combining Complex SELECT StatementsUsing UNION More Than Once355Sorting a UNION357Uses for UNION358Sample Statements359Summary371Problems for You to Solve372CHAPTER 11Subqueries351375What Is a Subquery?376Row Subqueries376Table Subqueries377Scalar Subqueries378Subqueries as Column Expressions378Syntax378An Introduction to Aggregate Functions: COUNT and MAXSubqueries as Filters384Syntax384Special Predicate Keywords for Subqueries386Uses for Subqueries397Build Subqueries as Column ExpressionsUse Subqueries as Filters398Sample Statements399Subqueries in Expressions399Subqueries in Filters405Summary413Problems for You to Solve414Part IV397Summarizing and Grouping DataCHAPTER 1 2Simple Totals419Aggregate Functions420Counting Rows and Values with COUNTComputing a Total with SUM425381422417
ContentsCalculating a Mean Value with AVG427Finding the Largest Value with MAX428Finding the Smallest Value with MIN430Using More Than One Function431Using Aggregate Functions in Filters432Sample Statements435Summary442Problems for You to SolveCHAPTER 13443Grouping D a t a445Why 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 1 4Filtering Grouped D a t aA New Meaning of "Focus Groups"478Where You Filter Makes a Difference482Should You Filter in WHERE or in HAVING?Avoiding the HAVING COUNT Trap485Uses for HAVING490Sample Statements491Summary499Problems for You to Solve500477482456xi
xiiContentsPart VModifying Sets of DataCHAPTER 1 5503U p d a t i n g Sets o f D a t aWhat Is an UPDATE?505505The UPDATE Statement506Using a Simple UPDATE Expression507A Brief Aside: Transactions510Updating Multiple Columns511Using a Subquery to Filter Rows512Using a Subquery UPDATE Expression518Uses for UPDATE520Sample StatementsSummary538521Problems for You to SolveCHAPTER 1 6538Inserting Sets of DataWhat Is an INSERT?541The INSERT Statement543Inserting Values543Generating the Next Primary Key ValueInserting Data by Using SELECT548Uses for INSERT555Sample StatementsSummary568547556Problems for You to SolveCHAPTER 17541568D e l e t i n g Sets of DataWhat Is a DELETE?571The DELETE Statement572Deleting All Rows573Deleting Some Rows575Uses for DELETE579Sample Statements580Summary588Problems for You to Solve589571
ContentsPart VIIntroduction to Solving Tough ProblemsCHAPTER 1 8"NOT" a n d " A N D " ProblemsA Short Review of Sets593Sets with Multiple AND Criteria594Sets with Multiple NOT Criteria595Sets Including Some Criteria but Excluding OthersFinding Out the "Not" Case597Using OUTER JOIN598Using NOT IN601Using NOT EXISTS603Using GROUP BY/HAVING604Finding Multiple Matches in the Same TableUsing INNER JOIN608Using IN610Using EXISTS612Using GROUP BY/HAVING614Sample Statements618Summary636Problems for You to Solve637CHAPTER 1 9Condition TestingConditional Expressions (CASE)Why Use CASE?642Syntax642xiii591593596607641641Solving Problems w i t h CASE647Solving Problems with Simple CASE647Solving Problems with Searched CASE652Using CASE in a WHERE Clause655Sample Statements655Summary669Problems for You to Solve669CHAPTER 2 0Using U n l i n k e d Data a n d "Driver" TablesWhat Is Unlinked Data?672Deciding When to Use a CROSS JOINSolving Problems with Unlinked Data675676671
xivContentsSolving Problems Using "Driver" TablesSetting Up a Driver Table679Using a Driver Table682679Sample Statements686Examples Using Unlinked Tables687Examples Using Driver Tables697Summary705Problems for You to Solve705I n Closing709AppendicesA711SQL S t a n d a r d Diagrams713BSchema for t h e 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 Database732CD a t e a n d Time Types, O p e r a t i o n s , a n d FunctionsIBM DB2733Microsoft Office AccessMicrosoft SQL ServerDMySQL740Oracle743736738Suggested R e a d i n gDatabase Books745Books on SQL745Index747745733
SQL Queries for Mere Mortals Third Edition A Hands-On Guide to Data Manipulation in SQL John L. Viescas Michael J. Hernandez rATAddison-Wesley TT Upper S
