- ホーム
- > 洋書
- > 英文書
- > Computer / Databases
Full Description
The #1 Easy, Common-Sense Guide to SQL Queries-Updated for Today's Databases, Standards, and ChallengesSQL Queries for Mere Mortals (R) has earned worldwide praise as the clearest, simplest tutorial on writing effective SQL queries. The authors have updated this hands-on classic to reflect new SQL standards and database applications and teach valuable new techniques.Step by step, John L. Viescas and Michael J. Hernandez guide you through creating reliable queries for virtually any modern SQL-based database. They demystify all aspects of SQL query writing, from simple data selection and filtering to joining multiple tables and modifying sets of data.Three brand-new chapters teach you how to solve a wide range of challenging SQL problems. You'll learn how to write queries that apply multiple complex conditions on one table, perform sophisticated logical evaluations, and think "outside the box" using unlinked tables.Coverage includes-- Getting started: understanding what relational databases are, and ensuring that your database structures are sound-- SQL basics: using SELECT statements, creating expressions, sorting information with ORDER BY, and filtering data using WHERE-- Summarizing and grouping data with GROUP BY and HAVING clauses-- Drawing data from multiple tables: using INNER JOIN, OUTER JOIN, and UNION operators, and working with subqueries-- Modifying data sets with UPDATE, INSERT, and DELETE statements Advanced queries: complex NOT and AND, conditions, if-then-else using CASE, unlinked tables, driver tables, and morePractice all you want with downloadable sample databases for today's versions of Microsoft Office Access, Microsoft SQL Server, and the open source MySQL database. Whether you're a DBA, developer, user, or student, there's no better way to master SQL.informit.com/awforMereMortals.com
Contents
Foreword xviiPreface xixAbout the Authors xxiIntroduction xxiiiAre You a Mere Mortal? xxiiiAbout This Book xxivWhat This Book Is Not xxviHow to Use This Book xxviReading the Diagrams Used in This Book xxviiSample Databases Used in This Book xxxi"Follow the Yellow Brick Road" xxxiiiPart I Relational Databases and SQL 1Chapter 1 What Is Relational? 3Types of Databases 3A Brief History of the Relational Model 4In the Beginning . . . 4Relational Database Systems 5Anatomy of a Relational Database 7Tables 7Fields 9Records 9Keys 9Views 11Relationships 12What's in It for You? 17Where Do You Go from Here? 18Summary 19Chapter 2 Ensuring Your Database Structure Is Sound 21Why Is This Chapter Here? 21Why Worry about Sound Structures? 22Fine-Tuning Fields 23What's in a Name? (Part One) 23Smoothing Out the Rough Edges 25Resolving Multipart Fields 27Resolving Multivalued Fields 30Fine-Tuning Tables 32What's in a Name? (Part Two) 33Ensuring a Sound Structure 35Resolving Unnecessary Duplicate Fields 36Identification Is the Key 42Establishing Solid Relationships 45Establishing a Deletion Rule 48Setting the Type of Participation 49Setting the Degree of Participation 52Is That All? 54Summary 55Chapter 3 A Concise History of SQL 57The Origins of SQL 58Early Vendor Implementations 59". . . And Then There Was a Standard" 60Evolution of the ANSI/ISO Standard 62Other SQL Standards 65Commercial Implementations 68What the Future Holds 69Why Should You Learn SQL? 69Which Version of SQL Does This Book Cover? 70Summary 70Part II SQL Basics 73Chapter 4 Creating a Simple Query 75Introducing SELECT 76The SELECT Statement 77A Quick Aside: Data versus Information 79Translating Your Request into SQL 81Expanding the Field of Vision 85Using a Shortcut to Request All Columns 87Eliminating Duplicate Rows 88Sorting Information 91First Things First: Collating Sequences 92Let's Now Come to Order 93Saving Your Work 96Sample Statements 97Summary 106Problems for You to Solve 107Chapter 5 Getting More Than Simple Columns 109What Is an Expression? 110What Type of Data Are You Trying to Express? 111Changing Data Types: The CAST Function 114Specifying Explicit Values 116Character String Literals 116Numeric Literals 118Datetime Literals 119Types of Expressions 121Concatenation 122Mathematical Expressions 125Date and Time Arithmetic 129Using Expressions in a SELECT Clause 133Working with a Concatenation Expression 134Naming the Expression 135Working with a Mathematical Expression 137Working with a Date Expression 138A Brief Digression: Value Expressions 139That "Nothing" Value: Null 141Introducing Null 142The Problem with Nulls 143Sample Statements 144Summary 153Problems for You to Solve 154Chapter 6 Filtering Your Data 157Refining What You See Using WHERE 157The WHERE Clause 158Using a WHERE Clause 160Defining Search Conditions 162Comparison 163Range 170Set Membership 173Pattern Match 175Null 179Excluding Rows with NOT 181Using Multiple Conditions 184Introducing AND and OR 185Excluding Rows: Take Two 191Order of Precedence 193Checking for Overlapping Ranges 197Nulls Revisited: A Cautionary Note 199Expressing Conditions in Different Ways 203Sample Statements 204Summary 212Problems for You to Solve 213Part III Working with Multiple Tables 217Chapter 7 Thinking in Sets 219What Is a Set, Anyway? 220Operations on Sets 221Intersection 222Intersection in Set Theory 222Intersection between Result Sets 224Problems You Can Solve with an Intersection 227Difference 228Difference in Set Theory 228Difference between Result Sets 230Problems You Can Solve with Difference 233Union 234Union in Set Theory 234Combining Result Sets Using a Union 236Problems You Can Solve with Union 238SQL Set Operations 239Classic Set Operations versus SQL 239Finding Common Values: INTERSECT 240Finding Missing Values: EXCEPT (DIFFERENCE) 243Combining Sets: UNION 245Summary 248Chapter 8 INNER JOINs 249What Is a JOIN? 249The INNER JOIN 250What's "Legal" to JOIN? 250Column References 251Syntax 252Check Those Relationships! 267Uses for INNER JOINs 268Find Related Rows 268Find Matching Values 269Sample Statements 269Two Tables 270More Than Two Tables 276Looking for Matching Values 283Summary 294Problems for You to Solve 295Chapter 9 OUTER JOINs 299What Is an OUTER JOIN? 299The LEFT/RIGHT OUTER JOIN 301Syntax 302The FULL OUTER JOIN 320Syntax 320FULL OUTER JOIN on Non-Key Values 323UNION JOIN 323Uses for OUTER JOINs 324Find Missing Values 324Find Partially Matched Information 325Sample Statements 325Summary 341Problems for You to Solve 341Chapter 10 UNIONs 345What Is a UNION? 345Writing Requests with UNION 348Using Simple SELECT Statements 348Combining Complex SELECT Statements 351Using UNION More Than Once 355Sorting a UNION 357Uses for UNION 358Sample Statements 359Summary 371Problems for You to Solve 372Chapter 11 Subqueries 375What Is a Subquery? 376Row Subqueries 376Table Subqueries 377Scalar Subqueries 378Subqueries as Column Expressions 378Syntax 378An Introduction to Aggregate Functions: COUNT and MAX 381Subqueries as Filters 384Syntax 384Special Predicate Keywords for Subqueries 386Uses for Subqueries 397Build Subqueries as Column Expressions 397Use Subqueries as Filters 398Sample Statements 399Subqueries in Expressions 399Subqueries in Filters 405Summary 413Problems for You to Solve 414Part IV Summarizing and Grouping Data 417Chapter 12 Simple Totals 419Aggregate Functions 420Counting Rows and Values with COUNT 422Computing a Total with SUM 425Calculating a Mean Value with AVG 427Finding the Largest Value with MAX 428Finding the Smallest Value with MIN 430Using More Than One Function 431Using Aggregate Functions in Filters 432Sample Statements 435Summary 442Problems for You to Solve 443Chapter 13 Grouping Data 445Why Group Data? 446The GROUP BY Clause 448Syntax 449Mixing Columns and Expressions 454Using GROUP BY in a Subquery in a WHERE Clause 456Simulating a SELECT DISTINCT Statement 457"Some Restrictions Apply" 458Column Restrictions 459Grouping on Expressions 461Uses for GROUP BY 462Sample Statements 463Summary 474Problems for You to Solve 475Chapter 14 Filtering Grouped Data 477A New Meaning of "Focus Groups" 478Where You Filter Makes a Difference 482Should You Filter in WHERE or in HAVING? 482Avoiding the HAVING COUNT Trap 485Uses for HAVING 490Sample Statements 491Summary 499Problems for You to Solve 500Part V Modifying Sets of Data 503Chapter 15 Updating Sets of Data 505What Is an UPDATE? 505The UPDATE Statement 506Using a Simple UPDATE Expression 507A Brief Aside: Transactions 510Updating Multiple Columns 511Using a Subquery to Filter Rows 512Using a Subquery UPDATE Expression 518Uses for UPDATE 520Sample Statements 521Summary 538Problems for You to Solve 538Chapter 16 Inserting Sets of Data 541What Is an INSERT? 541The INSERT Statement 543Inserting Values 543Generating the Next Primary Key Value 547Inserting Data by Using SELECT 548Uses for INSERT 555Sample Statements 556Summary 568Problems for You to Solve 568Chapter 17 Deleting Sets of Data 571What Is a DELETE? 571The DELETE Statement 572Deleting All Rows 573Deleting Some Rows 575Uses for DELETE 579Sample Statements 580Summary 588Problems for You to Solve 589Part VI Introduction to Solving Tough Problems 591Chapter 18 "NOT" and "AND" Problems 593A Short Review of Sets 593Sets with Multiple AND Criteria 594Sets with Multiple NOT Criteria 595Sets Including Some Criteria but Excluding Others 596Finding Out the "Not" Case 597Using OUTER JOIN 598Using NOT IN 601Using NOT EXISTS 603Using GROUP BY/HAVING 604Finding Multiple Matches in the Same Table 607Using INNER JOIN 608Using IN 610Using EXISTS 612Using GROUP BY/HAVING 614Sample Statements 618Summary 636Problems for You to Solve 637Chapter 19 Condition Testing 641Conditional Expressions (CASE) 641Why Use CASE? 642Syntax 642Solving Problems with CASE 647Solving Problems with Simple CASE 647Solving Problems with Searched CASE 652Using CASE in a WHERE Clause 655Sample Statements 655Summary 669Problems for You to Solve 669Chapter 20 Using Unlinked Data and "Driver" Tables 671What Is Unlinked Data? 672Deciding When to Use a CROSS JOIN 675Solving Problems with Unlinked Data 676Solving Problems Using "Driver" Tables 679Setting Up a Driver Table 679Using a Driver Table 682Sample Statements 686Examples Using Unlinked Tables 687Examples Using Driver Tables 697Summary 705Problems for You to Solve 705In Closing 709Appendices 711Appendix A SQL Standard Diagrams 713Appendix B Schema for the Sample Databases 723Sales Orders Example Database 724Sales Orders Modify Database 725Entertainment Agency Example Database 726Entertainment Agency Modify Database 727School Scheduling Example Database 728School Scheduling Modify Database 729Bowling League Example Database 730Bowling League Modify Database 731Recipes Database 732Appendix C Date and Time Types, Operations, and Functions 733IBM DB2 733Microsoft Office Access 736Microsoft SQL Server 738MySQL 740Oracle 743Appendix D Suggested Reading 745Database Books 745Books on SQL 7459780321992475 TOC 5/20/2014
