SQL Queries for Mere Mortals : A Hands-On Guide to Data Manipulation in SQL

by Hernandez, Michael J.; Viescas, John L.

• ISBN13: 9780201433364
• ISBN10: 0201433362
• Edition: CD
• Format: Paperback w/CD
• Copyright: 2000-01-01
• Publisher: Addison-Wesley Professional

Summary

In the past few years, SQL has evolved from a language known only to computer specialists to a widely used, international standard of the computer industry. The number of SQL-compatible databases shipping each year now totals in the millions. If you are accessing corporate information from the Internet or from an internal network, you are probably using SQL. SQL Queries for Mere Mortals will help new users learn the foundations of SQL queries, and will prove an essential reference guide for intermediate and advanced users. The accompanying CD contains five sample databases used for the example queries throughout the book, plus an evaluation copy of Microsoft SQL Server version 7. 0201433362B04062001

Table of Contents

Foreword	xiii
Preface and Acknowledgments	xv
About the Authors	xix
Introduction	xxiii
Are You a ``Mere Mortal''?	xxiii
About This Book	xxiv
How to Use This Book	xxv
Reading the Diagrams Used in This Book	xxvi
Sample Databases Used in This Book	xxx
``Follow the Yellow Brick Road''	xxxi
PART I Relational Databases and SQL	1	(64)
What Is Relational?	3	(16)
Topics Covered in This Chapter	3	(1)
Types of Databases	3	(1)
A Brief History of the Relational Model	4	(2)
In the Beginning...	4	(1)
Relational Databases Software	5	(1)
Anatomy of a Relational Database	6	(9)
Tables	6	(1)
Fields	7	(1)
Records	8	(1)
Keys	8	(2)
Views	10	(1)
Relationships	10	(5)
What's in It for Me?	15	(2)
``Where Do I Go from Here?''	16	(1)
Summary	17	(2)
Ensuring Your Database Structure Is Sound	19	(32)
Topics Covered in This Chapter	19	(1)
Why Is This Chapter Here?	19	(1)
Why Worry about Sound Structures?	20	(1)
Fine-tuning Fields	21	(8)
What's in a Name? (Part One)	21	(2)
Smoothing Out the Rough Edges	23	(2)
Resolving Multipart Fields	25	(2)
Resolving Multivalued Fields	27	(2)
Fine-tuning Tables	29	(12)
What's in a Name? (Part Two)	29	(2)
Ensuring a Sound Structure	31	(2)
Resolving Unnecessary Duplicate Fields	33	(4)
Identification Is the Key	37	(4)
Establishing Solid Relationships	41	(7)
Establishing a Deletion Rule	43	(2)
Setting the Type of Participation	45	(1)
Setting the Degree of Participation	45	(3)
Is That All?	48	(1)
Summary	49	(2)
A Concise History of SQL	51	(14)
Topics Covered in This Chapter	51	(1)
The Origins of SQL	52	(1)
Early Vendor Implementations	53	(1)
``...And Then There Was a Standard''	54	(2)
Evolution of the ANSI/ISO Standard	56	(4)
Other SQL Standards	59	(1)
Commercial Implementations	60	(1)
What the Future Holds	60	(1)
Why Should You Learn SQL?	61	(2)
Summary	63	(2)
PART II SQL Basics	65	(134)
Creating a Simple Query	67	(32)
Topics Covered in This Chapter	67	(1)
Introducing Select	67	(1)
The Select Statement	68	(3)
Major Clauses in a Select Statement	69	(2)
A Quick Aside: Data vs. Information	71	(1)
Translating Your Request into SQL	72	(8)
Expanding the Field of Vision	77	(3)
Eliminating Duplicate Rows	80	(2)
Sorting Information	82	(4)
First Things First: Collating Sequences	83	(1)
Let's Now Come to Order	83	(3)
Saving Your Work	86	(1)
Sample Statements	87	(9)
Summary	96	(1)
Problems for You to Solve	97	(2)
Getting More Than Simple Columns	99	(44)
Topics Covered in This Chapter	99	(1)
The Select Clause: Take Two	100	(5)
Specifying Explicit Values	101	(4)
Moving Beyond Basic Information	105	(1)
What Is an Expression?	105	(1)
What Are You Trying to Express?	106	(2)
Data Types in SQL	106	(2)
Types of Expressions	108	(11)
Concatenation	109	(4)
Mathematical	113	(3)
Date and Time Arithmetic	116	(3)
Using Expressions in a Select Clause	119	(8)
A Brief Digression: Value Expressions	125	(2)
That ``Nothing'' Value---Null	127	(4)
Introducing Null	128	(3)
Sample Statements	131	(8)
Summary	139	(2)
Problems for You to Solve	141	(2)
Filtering Your Data	143	(56)
Topics Covered in This Chapter	143	(1)
Refining What You See Using Where	144	(4)
The Where Clause	144	(2)
Using a Where Clause	146	(2)
Defining Search Conditions	148	(22)
Comparison	148	(8)
Range	156	(3)
Set Membership	159	(2)
Pattern Match	161	(4)
Null	165	(2)
Excluding Rows with Not	167	(3)
Using Multiple Conditions	170	(13)
Introducing AND and OR	170	(6)
Excluding Rows: Take Two	176	(2)
Order of Precedence	178	(5)
Nulls Revisited: A Cautionary Note	183	(4)
Expressing Conditions in Different Ways	187	(1)
Sample Statements	188	(7)
Summary	195	(1)
Problems for You to Solve	196	(3)
PART III Working with Multiple Tables	199	(186)
Thinking in Sets	201	(32)
Topics Covered in This Chapter	201	(1)
What Is a Set, Anyway?	202	(1)
Operations on Sets	203	(1)
Intersection	204	(6)
Intersection in Set Theory	204	(1)
Intersection between Result Sets	205	(3)
Problems You Can Solve with Intersect	208	(2)
Difference	210	(7)
Difference in Set Theory	210	(2)
Difference between Result Sets	212	(4)
Problems You Can Solve with Difference	216	(1)
Union	217	(5)
Union in Set Theory	217	(2)
Combining Result Sets Using Union	219	(2)
Problems You Can Solve with Union	221	(1)
SQL Set Operations	222	(9)
``Classical'' Set Operations vs. SQL	222	(1)
Finding Common Values---Intersect	222	(4)
Finding Missing Values---Except (Difference)	226	(2)
Combining Sets---Union	228	(3)
Summary	231	(2)
Inner Joins	233	(42)
Topics Covered in This Chapter	233	(1)
What is a Join?	233	(1)
The Inner Join	234	(16)
What's ``Legal'' to Join?	234	(1)
Syntax	235	(14)
Check Those Relationships!	249	(1)
Uses for Inner Joins	250	(2)
Find Related Rows	250	(1)
Find Matching Values	251	(1)
Sample Statements	252	(19)
Two Tables	252	(5)
More Than Two Tables	257	(6)
Looking for Matching Values	263	(8)
Summary	271	(1)
Problems for You to Solve	272	(3)
Outer Joins	275	(42)
Topics Covered in This Chapter	275	(1)
What Is an Outer Join?	275	(2)
The Left/Right Outer Join	277	(18)
Syntax	278	(17)
The Full Outer Join	295	(4)
Syntax	296	(2)
Full Outer Join on Non-Key Values	298	(1)
Union Join	299	(1)
Uses for Outer Joins	299	(2)
Find Missing Values	300	(1)
Find Partially Matched Information	300	(1)
Sample Statements	301	(12)
Summary	313	(1)
Problems for You to Solve	314	(3)
Unions	317	(28)
Topics Covered in This Chapter	317	(1)
What Is a Union?	317	(3)
Writing Requests with Union	320	(10)
Using Simple Select Statements	320	(3)
Combining Complex Select Statements	323	(3)
Using Union More Than Once	326	(2)
Sorting a Union	328	(2)
Uses for Union	330	(1)
Sample Statements	331	(11)
Summary	342	(1)
Problems for You to Solve	342	(3)
Subqueries	345	(40)
Topics Covered in This Chapter	345	(1)
What Is a Subquery?	346	(2)
Row Subqueries	346	(1)
Table Subqueries	347	(1)
Scalar Subqueries	347	(1)
Subqueries as Column Expressions	348	(5)
Syntax	348	(3)
An Introduction to Aggregate Functions---Count and Max	351	(2)
Subqueries as Filters	353	(13)
Syntax	353	(2)
Special Predicate Keywords for Subqueries	355	(11)
Uses for Subqueries	366	(2)
Column Expressions	366	(1)
Filters	366	(2)
Sample Statements	368	(12)
Subqueries in Expressions	369	(5)
Subqueries in Filters	374	(6)
Summary	380	(1)
Problems for You to Solve	381	(4)
PART IV Summarizing and Grouping Data	385	(82)
Simple Totals	387	(26)
Topics Covered in This Chapter	387	(1)
Aggregate Functions	388	(12)
Counting Rows and Values with Count	389	(3)
Computing a Total with SUM	392	(2)
Calculating a Mean Value with AVG	394	(1)
Finding the Largest Value with MAX	395	(2)
Finding the Smallest Value with MIN	397	(1)
Using More Than One Function	398	(2)
Using Aggregate Functions in Filters	400	(2)
Sample Statements	402	(7)
Summary	409	(1)
Problems for You to Solve	409	(4)
Grouping Data	413	(28)
Topics Covered in This Chapter	413	(1)
Why Group Data?	414	(2)
The Group By Clause	416	(9)
Syntax	417	(4)
Mixing Columns and Expressions	421	(2)
Using Group By in a Subquery in a Where Clause	423	(1)
Simulating a Select Distinct Statement	424	(1)
``Some Restrictions Apply''	425	(4)
Column Restrictions	425	(2)
Grouping on Expressions	427	(2)
Uses for Group By	429	(1)
Sample Statements	429	(10)
Summary	439	(1)
Problems for You to Solve	439	(2)
Filtering Grouped Data	441	(26)
Topics Covered in This Chapter	441	(1)
A New Meaning of ``Focus Groups''	442	(4)
When You Filter Makes a Difference	446	(7)
Should You Filter in Where or in Having?	446	(2)
Avoiding the Having Count Trap	448	(5)
Uses for Having	453	(1)
Sample Statements	454	(8)
Summary	462	(1)
Problems for You to Solve	462	(5)
In Closing	465	(2)
APPENDICES	467	(18)
A SQL Standard Diagrams	469	(8)
B Schema for the Sample Databases	477	(6)
C Recommended Reading References	483	(2)
Index	485

Excerpts

"Language is by its very nature a communal thing; that is, it expresses never the exact thing but a compromise--that which is common to you, me, and everybody." --Thomas Earnest Hulme,Speculations Learning how to retrieve information from a database is commonly a perplexing exercise. However, it can be a relatively easy task--as long as you understand the question you're posing to the database. Once you understand the question, you can translate it into the language used by any database system, which in most cases is Structured Query Language (SQL). You have to translate your request into an SQL statement so that your database system knows what information you want to retrieve. SQL provides the means for you and your database system to communicate with each other. Throughout our many years as database consultants, we've found that the number of people who merely need to retrieve information from a database far outnumber those who are charged with the task of creating programs and applications for a database. Unfortunately, no books focus solely on the subject of retrieving information, particularly from a "mere mortals" viewpoint. There are numerous good books on SQL, to be sure, but most are targeted toward database programming and development. With this in mind, we decided it was time to write a book that would help people learn how to query a database properly and effectively. The result of our decision is in your hands. This book is unique among SQL books in that it focuses only on the querying portion of SQL. When you finish reading this book, you'll have the skills you need to retrieve any information you require. Writing a book such as this is always a cooperative effort. There are always editors, colleagues, friends, and relatives willing to lend their support and provide valuable advice when we need it the most. These folks continually provide us with encouragement, help us to remain focused, and motivate us to see this project through to the end. First and foremost, we want to thank our editor, Mary O'Brien, for the opportunity to write this book. She saw the potential of an idea we had and pursued it with great dedication. We'd also like to thank Mary and her assistant, Mariann Kourafas, for their great patience and unwavering support throughout the many months we were writing this book. And we can't forget Marilyn Rash and the production staff--great job, guys! Next, we'd like to acknowledge our technical editors Malcom C. Rubel, Michael Blaha, Alexander Tarasul, and Keith W. Hare. Malcom, as always, it's great to have you on the team! Michael and Alexander, thanks for all your thoughtful comments and suggestions. And a special thanks to Keith--he corrected a few minor errors we had in the SQL history timeline and provided much of the information for the What the Future Holds section of Chapter 3. Thanks once again to all of you for your time and input and for helping us to make this a solid treatise on SQL queries. Finally, a very special thanks to Joe Celko for providing the Foreword. Joe is an SQL expert, a colleague, and a good friend. We have a lot of respect for Joe's knowledge and expertise on the subject, and we're pleased to have his thoughts and comments at the beginning of our book. Michael J. Hernandez & John L. Viescas I want to give my most sincere thanks to my dear friend and colleague, John L. Viescas, for the opportunity of co-authoring this book with him. It was John who had the initial idea for this book, and over dinner one evening he talked me into writing it with him. John has been in the business a long time and is an established, respected author. It is my honor to share authorship with him on this work. Finally, I want to thank my wife Kendra. Once again, she has exhibi