9780201433364

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

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

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

"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

Amazon no longer offers textbook rentals. We do!

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

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

0201433362

Summary

Table of Contents

Supplemental Materials

Excerpts