Foreword xvii Preface xix About the Authors xxi Introduction xxiii Are You a Mere Mortal? xxiii About This Book xxiv What This Book Is Not xxvi How to Use This Book xxvi Reading the Diagrams Used in This Book xxvii Sample Databases Used in This Book xxxi Follow the Yellow Brick Road xxxiii Part I Relational Databases and SQL 1 Chapter 1 What Is Relational? 3 Types of Databases 3 A Brief History of the Relational Model 4 In the Beginning . . . 4 Relational Database Systems 5 Anatomy of a Relational Database 7 Tables 7 Fields 9 Records 9 Keys 9 Views 11 Relationships 12 What's in It for You? 17 Where Do You Go from Here? 18 Summary 19 Chapter 2 Ensuring Your Database Structure Is Sound 21 Why Is This Chapter Here? 21 Why Worry about Sound Structures? 22 Fine-Tuning Fields 23 What's in a Name? (Part One) 23 Smoothing Out the Rough Edges 25 Resolving Multipart Fields 27 Resolving Multivalued Fields 30 Fine-Tuning Tables 32 What's in a Name? (Part Two) 33 Ensuring a Sound Structure 35 Resolving Unnecessary Duplicate Fields 36 Identification Is the Key 42 Establishing Solid Relationships 45 Establishing a Deletion Rule 48 Setting the Type of Participation 49 Setting the Degree of Participation 52 Is That All? 54 Summary 55 Chapter 3 A Concise History of SQL 57 The Origins of SQL 58 Early Vendor Implementations 59 . . . And Then There Was a Standard 60 Evolution of the ANSI/ISO Standard 62 Other SQL Standards 65 Commercial Implementations 68 What the Future Holds 69 Why Should You Learn SQL? 69 Which Version of SQL Does This Book Cover? 70 Summary 70 Part II SQL Basics 73 Chapter 4 Creating a Simple Query 75 Introducing SELECT 76 The SELECT Statement 77 A Quick Aside: Data versus Information 79 Translating Your Request into SQL 81 Expanding the Field of Vision 85 Using a Shortcut to Request All Columns 87 Eliminating Duplicate Rows 88 Sorting Information 91 First Things First: Collating Sequences 92 Let's Now Come to Order 93 Saving Your Work 96 Sample Statements 97 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
John L. Viescas is an independent database consultant with more 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 in Dallas, Texas, where he directed a staff of more than 30 people and was responsible for research, product development, and customer support of database products for IBM mainframe computers. While working at Applied Data Research, John completed a degree in business finance at the University of Texas at Dallas, graduating cum laude. John joined Tandem Computers, Inc., in 1988, where he was responsible for the development and implementation of database marketing programs in Tandem's U.S. Western Sales region. He developed and delivered technical seminars 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 among the 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 from Tandem. He has since written four editions of Running, three editions of Microsoft 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 systems management consulting for a variety of small to large businesses around the world, 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) since 1993 by Microsoft Product Support Services for his assistance with technical questions on public support forums. He set a 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 email@example.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.