SQL Queries for Mere Mortals: A Hands-on Guide to Data Manipulation in SQL [For Mere Mortals Series]

Unless you are working at a very advanced level, this is the only SQL book you will ever need. The authors have taken the mystery out of complex queries and explained principles and techniques with such clarity that a "Mere Mortal" will indeed be empowered to perform the superhuman. Do not walk past this book! - Graham Mandeno, Database ConsultantSQL Queries for Mere Mortals provides a step-by-step, easy-to-read introduction to writing SQL queries. It includes hundreds of examples with detailed 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 CommitteeI learned SQL primarily from the first edition of this book, and I am pleased to see a 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 that SQL can be effective (a common problem for database beginners), and then continuing 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 will greatly facilitate your mastery of the language. Numerous examples from real life will help you visualize how to use SQL to answer the questions about the data in your database. Just one of the "watch out for this trap" items will save you more than the cost of the book when you avoid that problem when writing your queries. Ihighly 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 you remember 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 of you do write better sentences because of it. John Viescas and Mike Hernandez must have remembered because they take everyday English queries and literally translate them into SQL. This is an important book for all database designers. It takes the complexity of mathematical Set Theory and of First Order Predicate Logic, 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- through intermediate-level course on SQL, this is the one book that is a requirement, no matter how many others you buy. - Arvin Meyer, MCP, MVPEven in this day of wizards and code generators, successful database developers still require a sound knowledge of Structured Query Language (SQL, the standard language for communicating with most database systems). In this book, John and Mike do a marvelous job of making what's usually a dry and difficult subject come alive, 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 so much use that it won't spend much time on the shelf! - Doug Steele, Microsoft Access Developer and authorOver the last several decades, SQL has evolved from a language known only to computer specialists to a widely used international standard of the computer industry. The number of new applications deployed each year using SQL now totals in the millions. If you are accessing corporate information from the Internet or from an internal network, you are probably using SQL. This new edition of SQL Queries for Mere Mortals helps new users learn the foundations of SQL queries, and is an essential reference guide for intermediate and advanced users.The accompanying CD contains five sample databases used for the example queries throughout the book in four different formats: Microsoft SQL Server 2000 and later, Microsoft Access 2000 and later, MySQL version 5.0 and later, and SQL scripts that can be used with most other implementations of the language. SQL Queries for Mere Mortals, 2nd Edition......Page 1 Contents......Page 8 Foreword......Page 18 Preface......Page 20 About the Authors......Page 22 Are You a Mere Mortal?......Page 24 About This Book......Page 25 What This Book Is Not......Page 26 How to Use This Book......Page 27 Reading the Diagrams Used in This Book......Page 28 Sample Databases Used in This Book......Page 31 “Follow the Yellow Brick Road”......Page 33 PART I: Relational Databases and SQL......Page 36 Types of Databases......Page 38 A Brief History of the Relational Model......Page 39 Anatomy of a Relational Database......Page 41 What’s in It for You?......Page 50 Where Do You Go from Here?......Page 51 Summary......Page 52 Why Is This Chapter Here?......Page 54 Why Worry about Sound Structures?......Page 55 Fine-Tuning Fields......Page 56 Fine-Tuning Tables......Page 65 Establishing Solid Relationships......Page 77 Is That All?......Page 85 Summary......Page 86 Topics Covered in This Chapter......Page 88 The Origins of SQL......Page 89 Early Vendor Implementations......Page 90 “. . . And Then There Was a Standard”......Page 91 Evolution of the ANSI/ISO Standard......Page 93 Commercial Implementations......Page 99 Why Should You Learn SQL?......Page 100 Summary......Page 101 PART II: SQL Basics......Page 104 Topics Covered in This Chapter......Page 106 Introducing SELECT......Page 107 The SELECT Statement......Page 108 A Quick Aside: Data versus Information......Page 110 Translating Your Request into SQL......Page 112 Eliminating Duplicate Rows......Page 119 Sorting Information......Page 122 Saving Your Work......Page 127 Sample Statements......Page 128 Summary......Page 137 Problems for You to Solve......Page 138 Topics Covered in This Chapter......Page 140 What Is an Expression?......Page 141 What Type of Data Are You Trying to Express?......Page 142 Changing Data Types: The CAST Function......Page 145 Specifying Explicit Values......Page 147 Types of Expressions......Page 152 Using Expressions in a SELECT Clause......Page 163 That “Nothing” Value: Null......Page 170 Sample Statements......Page 174 Summary......Page 182 Problems for You to Solve......Page 184 Refining What You See Using WHERE......Page 186 Defining Search Conditions......Page 191 Using Multiple Conditions......Page 213 Nulls Revisited: A Cautionary Note......Page 228 Expressing Conditions in Different Ways......Page 232 Sample Statements......Page 233 Summary......Page 241 Problems for You to Solve......Page 242 PART III: Working with Multiple Tables......Page 246 Topics Covered in This Chapter......Page 248 What Is a Set, Anyway?......Page 249 Operations on Sets......Page 250 Intersection......Page 251 Difference......Page 257 Union......Page 263 SQL Set Operations......Page 268 Summary......Page 277 What Is a JOIN?......Page 278 The INNER JOIN......Page 279 Uses for INNER JOINs......Page 297 Sample Statements......Page 298 Summary......Page 323 Problems for You to Solve......Page 324 What Is an OUTER JOIN?......Page 328 The LEFT/RIGHT OUTER JOIN......Page 330 The FULL OUTER JOIN......Page 349 Uses for OUTER JOINs......Page 353 Sample Statements......Page 354 Problems for You to Solve......Page 370 What Is a UNION?......Page 374 Writing Requests with UNION......Page 377 Uses for UNION......Page 387 Sample Statements......Page 388 Summary......Page 400 Problems for You to Solve......Page 401 Topics Covered in This Chapter......Page 404 What Is a Subquery?......Page 405 Subqueries as Column Expressions......Page 407 Subqueries as Filters......Page 412 Uses for Subqueries......Page 427 Sample Statements......Page 429 Summary......Page 444 Problems for You to Solve......Page 445 PART IV: Summarizing and Grouping Data......Page 448 Topics Covered in This Chapter......Page 450 Aggregate Functions......Page 451 Using Aggregate Functions in Filters......Page 463 Sample Statements......Page 466 Summary......Page 473 Problems for You to Solve......Page 474 Topics Covered in This Chapter......Page 476 Why Group Data?......Page 477 The GROUP BY Clause......Page 479 “Some Restrictions Apply”......Page 489 Uses for GROUP BY......Page 493 Sample Statements......Page 494 Summary......Page 505 Problems for You to Solve......Page 506 Topics Covered in This Chapter......Page 508 A New Meaning of “Focus Groups”......Page 509 When You Filter Makes a Difference......Page 513 Uses for HAVING......Page 521 Sample Statements......Page 522 Problems for You to Solve......Page 531 PART V: Modifying Sets of Data......Page 534 What Is an UPDATE?......Page 536 The UPDATE Statement......Page 537 Uses for UPDATE......Page 551 Sample Statements......Page 552 Summary......Page 568 Problems for You to Solve......Page 569 What Is an INSERT?......Page 572 The INSERT Statement......Page 574 Uses for INSERT......Page 585 Sample Statements......Page 587 Summary......Page 597 Problems for You to Solve......Page 598 What Is a DELETE?......Page 602 The DELETE Statement......Page 603 Uses for DELETE......Page 610 Sample Statements......Page 611 Summary......Page 618 Problems for You to Solve......Page 619 In Closing......Page 622 APPENDICES......Page 624 A: SQL Standard Diagrams......Page 626 B: Schema for the Sample Databases......Page 636 C: Date and Time Functions......Page 642 D: Suggested Reading......Page 650 B......Page 652 C......Page 653 D......Page 654 E......Page 655 G......Page 656 I......Page 657 M......Page 658 N......Page 659 P......Page 660 R......Page 661 S......Page 662 T......Page 664 U......Page 665 X......Page 666

Unless you are working at a very advanced level, this is the only SQL book you will ever need. The authors have taken the mystery out of complex queries and explained principles and techniques with such clarity that a "Mere Mortal" will indeed be empowered to perform the superhuman. Do not walk past this book!
--Graham Mandeno, Database Consultant

SQL Queries for Mere Mortals provides a step-by-step, easy-to-read introduction to writing SQL queries. It includes hundreds of examples with detailed 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

I learned SQL primarily from the first edition of this book, and I am pleased to see a 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 that SQL can be effective (a common problem for database beginners), and then continuing 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 will greatly facilitate your mastery of the language. Numerous examples from real life will help you visualize how to use SQL to answer the questions about the data in your database. Just one of the "watch out for this trap" items will save you more than 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/Programmer

I don't think they do this in public schools any more, and it is a shame, but do you remember 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 of you do write better sentences because of it. John Viescas and Mike Hernandez must have remembered because they take everyday English queries and literally translate them into SQL. This is an important book for all database designers. It takes the complexity of mathematical Set Theory and of First Order Predicate Logic, 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- through intermediate-level course on SQL, this is the one book that is a requirement, no matter how many others you buy.
--Arvin Meyer, MCP, MVP

Even in this day of wizards and code generators, successful database developers still require a sound knowledge of Structured Query Language (SQL, the standard language for communicating with most database systems). In this book, John and Mike do a marvelous job of making what's usually a dry and difficult subject come alive, 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 so much use that it won't spend much time on the shelf!
-- Doug Steele, Microsoft Access Developer and author

Over the last several decades, SQL has evolved from a language known only to computer specialists to a widely used international standard of the computer industry. The number of new applications deployed each year using SQL now totals in the millions. If you are accessing corporate information from the Internet or from an internal network, you are probably using SQL. This new edition of SQL Queries for Mere Mortals helps new users learn the foundations of SQL queries, and is an essential reference guide for intermediate and advanced users.

The accompanying CD contains five sample databases used for the example queries throughout the book in four different formats: Microsoft SQL Server 2000 and later, Microsoft Access 2000 and later, MySQL version 5.0 and later, and SQL scripts that can be used with most other implementations of the language.

This is the eBook version of the printed book. If the print book includes a CD-ROM, this content is not included within the eBook version. Unless you are working at a very advanced level, this is the only SQL book you will ever need. The authors have taken the mystery out of complex queries and explained principles and techniques with such clarity that a'Mere Mortal'will indeed be empowered to perform the superhuman. Do not walk past this book! --Graham Mandeno, Database Consultant SQL Queries for Mere Mortals provides a step-by-step, easy-to-read introduction to writing SQL queries. It includes hundreds of examples with detailed 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 I learned SQL primarily from the first edition of this book, and I am pleased to see a 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 that SQL can be effective (a common problem for database beginners), and then continuing 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 will greatly facilitate your mastery of the language. Numerous examples from real life will help you visualize how to use SQL to answer the questions about the data in your database. Just one of the'watch out for this trap'items will save you more than 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/Programmer I don't think they do this in public schools any more, and it is a shame, but do you remember 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 of you do write better sentences because of it. John Viescas and Mike Hernandez must have remembered because they take everyday English queries and literally translate them into SQL. This is an important book for all database designers. It takes the complexity of mathematical Set Theory and of First Order Predicate Logic, 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- through intermediate-level course on SQL, this is the one book that is a requirement, no matter how many others you buy. --Arvin Meyer, MCP, MVP Even in this day of wizards and code generators, successful database developers still require a sound knowledge of Structured Query Language (SQL, the standard language for communicating with most database systems). In this book, John and Mike do a marvelous job of making what's usually a dry and difficult subject come alive, 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 so much use that it won't spend much time on the shelf! -- Doug Steele, Microsoft Access Developer and author Over the last several decades, SQL has evolved from a language known only to computer specialists to a widely used international standard of the computer industry. The number of new applications deployed each year using SQL now totals in the millions. If you are accessing corporate information from the Internet or from an internal networ Unless you are working at a very advanced level, this is the only SQL book you will ever need. The authors have taken the mystery out of complex queries and explained principles and techniques with such clarity that a "Mere Mortal" will indeed be empowered to perform the superhuman. Do not walk past this book! --Graham Mandeno, Database Consultant SQL Queries for Mere Mortals provides a step-by-step, easy-to-read introduction to writing SQL queries. It includes hundreds of examples with detailed 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 I learned SQL primarily from the first edition of this book, and I am pleased to see a 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 that SQL can be effective (a common problem for database beginners), and then continuing 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 will greatly facilitate your mastery of the language. Numerous examples from real life will help you visualize how to use SQL to answer the questions about the data in your database. Just one of the "watch out for this trap" items will save you more than 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/Programmer I don't think they do this in public schools any more, and it is a shame, but do you remember 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 of you do write better sentences Because of it. John Viescas and Mike Hernandez must have remembered because they take everyday English queries and literally translate them into SQL. This is an important book for all database designers. It takes the complexity of mathematical Set Theory and of First Order Predicate Logic, 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- through intermediate-level course on SQL, this is the one book that is a requirement, no matter how many others you buy. --Arvin Meyer, MCP, MVP SQL Queries for Mere Mortals, Second Edition, provides a step-by-step, easy-to-read introduction to writing SQL queries. It includes hundreds of examples with detailed 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 developers still require a sound knowledge of Structured Query Language (SQL, the standard language for communicating with most database systems). In this book, John and Mike do a marvelous job of making what's usually a dry and difficult subject come alive, 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 so much use that it won't spend much time on the shelf! -- Doug Steele, Microsoft Access Developer and author Over the last several decades, SQL has evolved from a language known only to computer specialists to a widely used international standard of the computer industry. The number of new applications deployed each year using SQL now totals in the millions. If you are accessing corporate information from the Internet or from an internal network, you are probably using SQL