Viescas, J: SQL Queries for Mere Mortals

Summary 106 Problems for You to Solve 107 Chapter 5 Getting More Than Simple Columns 109 What Is an Expression? 110 What Type of Data Are You Trying to Express? 111 Changing Data Types: The CAST Function 114 Specifying Explicit Values 116 Character String Literals 116 Numeric Literals 118 Datetime Literals 119 Types of Expressions 121 Concatenation 122 Mathematical Expressions 125 Date and Time Arithmetic 129 Using Expressions in a SELECT Clause 133 Working with a Concatenation Expression 134 Naming the Expression 135 Working with a Mathematical Expression 137 Working with a Date Expression 138 A Brief Digression: Value Expressions 139 That "Nothing" Value: Null 141 Introducing Null 142 The Problem with Nulls 143 Sample Statements 144 Summary 153 Problems for You to Solve 154 Chapter 6 Filtering Your Data 157 Refining What You See Using WHERE 157 The WHERE Clause 158 Using a WHERE Clause 160 Defining Search Conditions 162 Comparison 163 Range 170 Set Membership 173 Pattern Match 175 Null 179 Excluding Rows with NOT 181 Using Multiple Conditions 184 Introducing AND and OR 185 Excluding Rows: Take Two 191 Order of Precedence 193 Checking for Overlapping Ranges 197 Nulls Revisited: A Cautionary Note 199 Expressing Conditions in Different Ways 203 Sample Statements 204 Summary 212 Problems for You to Solve 213 Part III Working with Multiple Tables 217 Chapter 7 Thinking in Sets 219 What Is a Set, Anyway? 220 Operations on Sets 221 Intersection 222 Intersection in Set Theory 222 Intersection between Result Sets 224 Problems You Can Solve with an Intersection 227 Difference 228 Difference in Set Theory 228 Difference between Result Sets 230 Problems You Can Solve with Difference 233 Union 234 Union in Set Theory 234 Combining Result Sets Using a Union 236 Problems You Can Solve with Union 238 SQL Set Operations 239 Classic Set Operations versus SQL 239 Finding Common Values: INTERSECT 240 Finding Missing Values: EXCEPT (DIFFERENCE) 243 Combining Sets: UNION 245 Summary 248 Chapter 8 INNER JOINs 249 What Is a JOIN? 249 The INNER JOIN 250 What's "Legal" to JOIN? 250 Column References 251 Syntax 252 Check Those Relationships! 267 Uses for INNER JOINs 268 Find Related Rows 268 Find Matching Values 269 Sample Statements 269 Two Tables 270 More Than Two Tables 276 Looking for Matching Values 283 Summary 294 Problems for You to Solve 295 Chapter 9 OUTER JOINs 299 What Is an OUTER JOIN? 299 The LEFT/RIGHT OUTER JOIN 301 Syntax 302 The FULL OUTER JOIN 320 Syntax 320 FULL OUTER JOIN on Non-Key Values 323 UNION JOIN 323 Uses for OUTER JOINs 324 Find Missing Values 324 Find Partially Matched Information 325 Sample Statements 325 Summary 341 Problems for You to Solve 341 Chapter 10 UNIONs 345 What Is a UNION? 345 Writing Requests with UNION 348 Using Simple SELECT Statements 348 Combining Complex SELECT Statements 351 Using UNION More Than Once 355 Sorting a UNION 357 Uses for UNION 358 Sample Statements 359 Summary 371 Problems for You to Solve 372 Chapter 11 Subqueries 375 What Is a Subquery? 376 Row Subqueries 376 Table Subqueries 377 Scalar Subqueries 378 Subqueries as Column Expressions 378 Syntax 378 An Introduction to Aggregate Functions: COUNT and MAX 381 Subqueries as Filters 384 Syntax 384 Special Predicate Keywords for Subqueries 386 Uses for Subqueries 397 Build Subqueries as Column Expressions 397 Use Subqueries as Filters 398 Sample Statements 399 Subqueries in Expressions 399 Subqueries in Filters 405 Summary 413 Problems for You to Solve 414 Part IV Summarizing and Grouping Data 417 Chapter 12 Simple Totals 419 Aggregate Functions 420 Counting Rows and Values with COUNT 422 Computing a Total with SUM 425 Calculating a Mean Value with AVG 427 Finding the Largest Value with MAX 428 Finding the Smallest Value with MIN 430 Using More Than One Function 431 Using Aggregate Functions in Filters 432 Sample Statements 435 Summary 442 Problems for You to Solve 443 Chapter 13 Grouping Data 445 Why Group Data? 446 The GROUP BY Clause 448 Syntax 449 Mixing Columns and Expressions 454 Using GROUP BY in a Subquery in a WHERE Clause 456 Simulating a SELECT DISTINCT Statement 457 "Some Restrictions Apply" 458 Column Restrictions 459 Grouping on Expressions 461 Uses for GROUP BY 462 Sample Statements 463 Summary 474 Problems for You to Solve 475 Chapter 14 Filtering Grouped Data 477 A New Meaning of "Focus Groups" 478 Where You Filter Makes a Difference 482 Should You Filter in WHERE or in HAVING? 482 Avoiding the HAVING COUNT Trap 485 Uses for HAVING 490 Sample Statements 491 Summary 499 Problems for You to Solve 500 Part V Modifying Sets of Data 503 Chapter 15 Updating Sets of Data 505 What Is an UPDATE? 505 The UPDATE Statement 506 Using a Simple UPDATE Expression 507 A Brief Aside: Transactions 510 Updating Multiple Columns 511 Using a Subquery to Filter Rows 512 Using a Subquery UPDATE Expression 518 Uses for UPDATE 520 Sample Statements 521 Summary 538 Problems for You to Solve 538 Chapter 16 Inserting Sets of Data 541 What Is an INSERT? 541 The INSERT Statement 543 Inserting Values 543 Generating the Next Primary Key Value 547 Inserting Data by Using SELECT 548 Uses for INSERT 555 Sample Statements 556 Summary 568 Problems for You to Solve 568 Chapter 17 Deleting Sets of Data 571 What Is a DELETE? 571 The DELETE Statement 572 Deleting All Rows 573 Deleting Some Rows 575 Uses for DELETE 579 Sample Statements 580 Summary 588 Problems for You to Solve 589 Part VI Introduction to Solving Tough Problems 591 Chapter 18 "NOT" and "AND" Problems 593 A Short Review of Sets 593 Sets with Multiple AND Criteria 594 Sets with Multiple NOT Criteria 595 Sets Including Some Criteria but Excluding Others 596 Finding Out the "Not" Case 597 Using OUTER JOIN 598 Using NOT IN 601 Using NOT EXISTS 603 Using GROUP BY/HAVING 604 Finding Multiple Matches in the Same Table 607 Using INNER JOIN 608 Using IN 610 Using EXISTS 612 Using GROUP BY/HAVING 614 Sample Statements 618 Summary 636 Problems for You to Solve 637 Chapter 19 Condition Testing 641 Conditional Expressions (CASE) 641 Why Use CASE? 642 Syntax 642 Solving Problems with CASE 647 Solving Problems with Simple CASE 647 Solving Problems with Searched CASE 652 Using CASE in a WHERE Clause 655 Sample Statements 655 Summary 669 Problems for You to Solve 669 Chapter 20 Using Unlinked Data and "Driver" Tables 671 What Is Unlinked Data? 672 Deciding When to Use a CROSS JOIN 675 Solving Problems with Unlinked Data 676 Solving Problems Using "Driver" Tables 679 Setting Up a Driver Table 679 Using a Driver Table 682 Sample Statements 686 Examples Using Unlinked Tables 687 Examples Using Driver Tables 697 Summary 705 Problems for You to Solve 705 In Closing 709 Appendices 711 Appendix A SQL Standard Diagrams 713 Appendix B Schema for the Sample Databases 723 Sales Orders Example Database 724 Sales Orders Modify Database 725 Entertainment Agency Example Database 726 Entertainment Agency Modify Database 727 School Scheduling Example Database 728 School Scheduling Modify Database 729 Bowling League Example Database 730 Bowling League Modify Database 731 Recipes Database 732 Appendix C Date and Time Types, Operations, and Functions 733 IBM DB2 733 Microsoft Office Access 736 Microsoft SQL Server 738 MySQL 740 Oracle 743 Appendix D Suggested Reading 745 Database Books 745 Books on SQL 745 9780321992475 TOC 5/20/2014

landmark 20 consecutive years as an MVP in 2013. You can visit John's Web site at www.viescas.com or contact him by e-mail at john@viescas.com. Michael J. Hernandez has been an independent relational database consultant specializing in relational database design. He has more than 20 years of experience in the technology industry, developing database applications for a wide variety of clients. He's been a contributing author to a wide variety of magazine columns, white papers, books, and periodicals, and is coauthor of the best-selling SQL Queries for Mere Mortals. Mike has been a top-rated and noted technical trainer for the government,the military, the private sector, and companies throughout the United States. He has 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. His greatest interest is still the guitar, as he's been a practicing guitarist for more than 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 bad puns, and even reads Tarot cards. He says he's never going to retire, per se, but rather just change whatever it is he's doing whenever he finally gets tired of it and move on to something else that interests him.