Amazon no longer offers textbook rentals. We do!

The Practical SQL Handbook Using SQL Variants

by: Bowman, Judith S.; Emerson, Sandra L.; Darnovsky, Marcy

The Practical SQL Handbook Using SQL Variants

by: Bowman, Judith S.; Emerson, Sandra L.; Darnovsky, Marcy

ISBN 13:
9780201703092
ISBN 10:
0201703092
Edition: 4th
Format: Paperback
Copyright: 06/26/2001
Publisher: Addison-Wesley Professional

Rent

Sorry, this item is currently unavailable.

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

Extend or Purchase Your Rental at Any Time

Need to keep your rental past your due date? At any time before your due date you can extend or purchase your rental through your account.

Summary

This latest edition of the best-selling implementation guide to the Structured Query Language teaches SQL fundamentals while providing practical solutions for critical business applications. The Practical SQL Handbook, Fourth Editionnow includes expanded platform SQL coverage and extensive real-world examples based on feedback from actual SQL users. The Practical SQL Handbookbegins with a step-by-step introduction to SQL basics and examines the issues involved in designing SQL-based database applications. It fully explores SQL's most popular implementations from industry leaders, Oracle, Microsoft, Sybase, and Informix. Highlights include: Detailed coverage of SQL commands for creating databases, tables, and indexes, and for adding, changing, and deleting data Using the SELECT command to retrieve specific data Handling NULL values (missing information) in a relational database Joining tables, including self joins and outer joins (ANSI and WHERE-clause syntax) Working with nested queries (subqueries) to get data from multiple tables Creating views (virtual tables) to provide customized access to data Using SQL functions A bonus CD-ROM contains a time-limited, full-feature version of the SybaseÂ® Adaptive Server Anywhereâ„¢ software as well as the sample database, scripts, and examples included in the book. The Practical SQL Handbookis the most complete reference available for day-to-day SQL implementations. 0201703092B05222001

List of Figures

xix

Foreword to the Fourth Edition

xxi

Preface

xxiii

Why New Editions?

xxiii

Changes for Recent Editions

xxiv

Include More Real-World Examples

xxiv

Emphasize SQL-92

xxiv

Provide Software for Hands-On Practice

xxv

The Fourth Edition

xxv

Test on More Systems

xxv

Show Examples of Vendor-Specific Differences

xxvi

Acknowledgments

xxvi

Introduction

xxvii

The Beginnings of SQL

xxvii

The Commercialization of SQL

xxviii

Who Should Use This Book

xxix

The Focus of This Book

xxix

How to Learn SQL with This Book

xxxi

The Structure of This Book

xxxii

How to Use This Book

xxxiii

Save your Practice SQL Statements in Operating System Files

xxxiii

Save Your Successes

xxxiii

Structure an Application's Queries into Separate Modules or Subroutines

xxxiii

Make Yourself a Crib Sheet

xxxiii

Improve on Our Solutions

xxxiii

An Overview of the Book

xxxiv

SQL and Relational Database Management

xxxiv

Designing Databases

xxxiv

Creating and Filling a Database

xxxiv

Selecting Data from the Database

xxxiv

Sorting Data and Other Selection Techniques

xxxiv

Grouping Data and Reporting from It

xxxv

Joining Tables for Comprehensive Data Analysis

xxxv

Structuring Queries with Subqueries

xxxv

Creating and Using Views

xxxv

Security, Transactions, Performance, and Integrity

xxxv

Solving Business Problems

xxxv

Syntax Summary for the SQL Used in This Book

xxxvi

Industry SQL Equivalents

xxxvi

Glossary

xxxvi

The Bookbiz Sample Database

xxxvi

Resources

xxxvi

SQL and Relational Database Management

(20)

In This Chapter

(1)

Relational Database Management

(2)

The Relational Model: It's All Tables

(1)

Independence Forever

(1)

A High-Level Language

(3)

Data Manipulation

(1)

Data Definition

(1)

Data Administration

(1)

Relational Operations

(5)

Projection

(1)

Selection

(1)

Join

(3)

Alternatives for Viewing Data

(2)

NULLs

(1)

Security

(1)

Integrity

(1)

SQL Functions

(1)

Summary

(3)

Designing Databases

(30)

In This Chapter

(1)

Design Considerations

(8)

Do You Need an Expert?

(1)

Data Definition and Customization

(1)

How to Approach Database Design

(1)

Getting Started

(2)

The Characteristics of a Good Design

(1)

Introducing the Sample Database

(2)

Data Entities and Relationship

(10)

Entities: Things with an Independent Existence

(1)

Diagramming Entities and Attributes

(1)

Primary Keys

(2)

One-to-Many Relationship

(1)

Representing the Relationship

(2)

Foreign Keys

(1)

Many-to-Many Relationships

(1)

One-to-One Relationships

(1)

The Entity-Relationship Approach Summarized

(1)

The Normalization Guidelines

(7)

First Normal Form

(1)

Second Normal Form

(2)

Third Normal Form

(2)

Fourth Normal Form

(1)

Reviewing the Database Design

(4)

Summarizing the bookbiz Database

(1)

Testing Your Database Design

(1)

Other Database Definition Considerations

(1)

Summary

(1)

Creating and Filling a Database

(44)

In This Chapter

(1)

From Theory to Practice: Installing bookbiz

(5)

Distinguishings SQL Examples and Syntax

(3)

Coping with Failure

(1)

Working with Databases

(2)

Getting Started

(1)

Recognizing Roles

(1)

Reserving Space for Database Objects

(1)

Choosing Databases

(1)

Creating Simple Tables

(10)

Choosing Datatypes

(2)

Figuring Datatype Lengths

(1)

Specifying Precision and Scale

(1)

Assigning NULL and NOT NULL

(2)

Planning Tables

(1)

Defining the Tables in Bookbiz

(2)

Creating Indexes

(4)

The Create Index Statement

(1)

Composite Indexes

(1)

Unique Indexes

(1)

How, What, and Why to Index

(1)

Creating Tables with SQL-92 Constraints

(7)

Sketching Constraints

(2)

Implementing Constraints on Individual Columns

(2)

Implementing Multicolumn Constraints

(2)

Changing and Deleting Databases and Objects

(4)

Changing Databases

(1)

Changing Table Definitions

(1)

Using Alter Table

(1)

Creating Views and Tables

(1)

Removing Databases

(1)

Removing Tables

(1)

Removing Indexes

(1)

Adding, Changing, and Removing Data

(11)

Adding New Rows: Insert

(1)

Inserting Data into All Columns with Values

(1)

Inserting Data into Some Columns with Values

(1)

Inserting Data into All Columns with Select

(1)

Using Expressions

(1)

Inserting Data into Some Columns with Select

(1)

Changing Existing Data: Update

(1)

Specifying the Table: Update Clause

(1)

Specifying Columns: Set Clause

(2)

Specifying Rows: Where Clause

(2)

Removing Data: Delete

(1)

Summary

(1)

Selecting Data from the Database

(48)

In This Chapter

(1)

Select Overview and Syntax

(5)

Basic Select Syntax

(1)

Select list and Search condition Expressions

(1)

Combining Select, From and Where

(2)

Full Select Syntax

(1)

Select Statement Clause Order

(1)

Naming Conventions

(1)

Choosing Columns: The Select Clause

100

(14)

Choosing All Columns: Select

101

(1)

Choosing Specific Columns

102

(1)

Rearranging Result Columns

102

(1)

More Than Column Names

103

(1)

Display Label Conventions

103

(2)

Display Label Limitations

105

(1)

Character Strings in Query Results

106

(1)

Combining Columns, Display Headings, and Text

107

(1)

Computations with Constants

107

(2)

Computed Column Display Headings

109

(1)

Computations with Column Names

110

(1)

Arithmetic Operator Precedence

111

(3)

Specifying Tables: The From Clause Table List

114

(2)

Using Table Aliases

114

(1)

Skipping From

115

(1)

Selecting Rows: The Where Clause

116

(26)

Comparison Operators

117

(1)

Comparing Numbers

118

(1)

Comparing Character Values

119

(1)

Comparing Imaginary Values

119

(1)

Finding Values Not Equal to Some Value

120

(1)

Connecting Conditions with Logical Operators

120

(1)

AND

121

(1)

121

(1)

Semantic Issues with OR and AND

122

(2)

NOT

124

(1)

Logical Operator Precedence

124

(3)

Ranges (Between and Not Between)

127

(1)

NOT BETWEEN

128

(2)

Lists (IN and NOT IN)

130

(1)

Selecting Null Values

131

(2)

IS NULL

133

(1)

IS NULL and Other Comparison Operators

134

(1)

Matching Character Strings: LIKE

134

(1)

LIKE Examples

135

(2)

NOT LIKE

137

(1)

Escaping

138

(1)

Like, Is IN LIKE Equals

139

(1)

Equals

139

(1)

139

(1)

140

(1)

Comparing the Three

141

(1)

Summary

142

(1)

Sorting Data and Other Selection Techniques

143

(32)

In This Chapter

143

(1)

A New Batch of Select Statement Clauses

143

(1)

Sorting Query Results: Order By

144

(11)

Order By Syntax

144

(2)

Character Sets and Sort Orders

146

(1)

Checking Sort Order

146

(2)

Sorts Within Sorts

148

(1)

Sort Up, Sort Down

149

(1)

What About More Complexs Expressions?

150

(1)

Sorting by Position

151

(1)

Sorting by Display Label

152

(1)

Sorting by Complex Expression

153

(1)

How Do You Sort Nulls?

154

(1)

Eliminating Duplicate Rows: Distinct and All

155

(7)

Distinct Syntax

157

(1)

Distinct with Multiple Select List Items

158

(2)

Are Nulls Distinct?

160

(1)

Using Distinct*

160

(1)

Distinct and Non-Select-List Order By

160

(2)

Aggregate Functions

162

(11)

Aggregate Syntax

164

(1)

Count and Count(*)

165

(1)

Aggregates and Datatypes

165

(1)

Distinct Aggregates

166

(1)

Distinct Limitations

167

(2)

Aggregates and Where

169

(1)

Null Values and the Aggregate Functions

170

(3)

Summary

173

(2)

Grouping Data and Reporting from It

175

(30)

In This Chapter

175

(1)

Grouping and Aggregates

175

(1)

The Group By Clause

175

(17)

Group By Syntax

176

(1)

Groups Within Groups

177

(1)

Cautions and Restrictions

178

(1)

Using Complex Expressions

179

(1)

Multiple Summary Values for Multiple Levels of Grouping

179

(3)

Nulls and Groups

182

(1)

Count(*) and Count ()

183

(1)

Multiple Nulls in a Group

184

(2)

Group By: Aggregate Interactions

186

(1)

Group By Without Aggregates

186

(1)

Group By with Aggregates

187

(2)

Group By with Where

189

(2)

Group By and Order By

191

(1)

The Having Clause

192

(5)

Garden-Variety Having

193

(1)

Having Restrictions

193

(3)

Multiple Having Conditions

196

(1)

Where, Group By, Having, Order By

197

(1)

All About Nulls

197

(6)

Nulls and Database Design

198

(1)

Comparisons Involving Nulls

198

(1)

Nulls and Computations

199

(2)

Defaults as Alternatives to Nulls

201

(1)

Functions That Work with Nulls

201

(2)

Null Functions and ``What-If'' Calculations

203

(1)

Summary

203

(2)

Joining Tables for Comprehensive Data Analysis

205

(38)

In This Chapter

205

(1)

Defining Joins

205

(1)

Why Joins Are Necessary

206

(2)

Associating Data from Separate Tables

206

(1)

Providing Flexibility

207

(1)

Getting a Good Join

208

(5)

From/Where Join Syntax

209

(1)

SQL-92 Join Syntax

210

(2)

Analyzing a Join

212

(1)

Improving the Readability of Join Results

213

(5)

Avoiding Duplication

213

(1)

Limiting the Width of the Display

214

(2)

Using Aliases in the From Clause Table/View List

216

(2)

Specifying Join Conditions

218

(4)

Joins Based on Equality

218

(1)

Equijoins

219

(1)

Natural Joins

219

(1)

Joins Not Based on Equality

220

(1)

Joining More Than Two Tables

221

(1)

Exploring Exotic Joins

222

(8)

Joining a Table with Itself: The Self-Join

222

(1)

Unraveling Relationships

223

(1)

Using Not-Equal Comparisons

224

(1)

Showing the Background: Outer Joins

225

(1)

Left Outer Join

225

(2)

Right Outer Join

227

(1)

Full Outer Join

228

(1)

Outer Joins and Other Conditions

229

(1)

Avoiding a Common Source of Errors

230

(3)

Understanding the Cartesian Product

230

(2)

Using the Cartesian Product

232

(1)

Constraining the Cartesian Product

233

(1)

Going Beyond Joins: Union, Intersect, Minus

233

(8)

Union

234

(1)

Union Rules

235

(3)

If I Had a Union

238

(1)

Intersect and Minus

239

(2)

Summary

241

(2)

Structuring Queries with Subqueries

243

(40)

In This Chapter

243

(1)

What Is a Subquery?

243

(1)

Simplified Subquery Syntax

244

(1)

How Do Subqueries Work?

244

(5)

Simple Subquery Processing

245

(1)

Qualifying Column Names

246

(1)

Correlated Subquery Processing

247

(1)

Qualifying Column Names

248

(1)

Simple-Correlated Performance Issues

249

(1)

Joins or Subqueries?

249

(3)

Subqueries!

249

(2)

Joins!

251

(1)

Subqueries vs. Self-Joins?

251

(1)

Which Is Better?

252

(1)

Subquery Rules

252

(1)

Subqueries Returning Zero or More Values

253

(13)

Subqueries Introduced with In

254

(2)

Subqueries Introduced with Not In

256

(1)

Correlated Subqueries Introduced with In

257

(1)

Correlated In Subqueries on a Single Table

258

(1)

Correlated In Subqueries in a Having Clause

259

(1)

Subqueries Introduced with Comparison Operators and Any or All

259

(1)

Understanding All and Any

260

(1)

Subqueries with All

260

(2)

Subqueries with Any

262

(1)

Comparing In, Any, and All

263

(3)

Subqueries Returning a Single Value

266

(4)

Aggregate Functions Guarantee a Single Value

267

(1)

Group By and Having Must Return a Single Value

268

(1)

Correlated Subqueries with Comparison Operators

269

(1)

Subqueries Testing Existence

270

(6)

Not Exists Seeks the Empty Set

273

(1)

Using Exists to Find Intersection and Difference

274

(1)

Exists Alternatives

275

(1)

Subqueries in Multiple Levels of Nesting

276

(1)

Subqueries in Update, Delete, and Insert Statements

277

(1)

Subqueries in From and Select Clauses

278

(4)

Subqueries in the From Clause

278

(2)

Subqueries in the Select Clause

280

(2)

Summary

282

(1)

Creating and Using Views

283

(32)

In This Chapter

283

(1)

With a View Toward Flexibility

283

(1)

View Commands

284

(3)

Creating Views

284

(1)

Displaying Data Through Views

285

(2)

Dropping Views

287

(1)

Advantages of Views

287

(7)

Focus, Simplification, and Customization

292

(1)

Security

292

(1)

Independence

292

(2)

How Views Work

294

(12)

Naming View Columns

296

(1)

Complex Expressions

296

(1)

Duplicate Column Names

297

(1)

Creating Views with Multiple Underlying Objects

298

(1)

Using Subqueries and Joins

298

(1)

Deriving Views from Views

299

(1)

Resolving Views

300

(1)

Adding Columns

300

(1)

Breaking Object Chains

301

(2)

Using With Check Option

303

(3)

Data Modification Through Views

306

(4)

The Rules According to ANSI

306

(1)

Computed Columns

307

(1)

Aggregates

308

(1)

Multiple Underlying Objects

309

(1)

Creating Copies of Data

310

(2)

Summary

312

(3)

Security, Transactions, Performance, and Integrity

315

(30)

In This Chapter

315

(1)

Database Management in the Real World

315

(1)

Data Security

316

(10)

User Identification and Special Users

317

(1)

Creating Users and Groups

318

(1)

Database Administrators

319

(1)

Object Owners

319

(1)

Public Group

320

(1)

The Grant and Revoke Commands

320

(1)

Grant and Revoke Syntax

320

(2)

Grant and Revoke Strategies

322

(2)

Views as Security Mechanisms

324

(2)

Transactions

326

(4)

Transactions and Concurrency

326

(1)

Transactions and Recovery

327

(1)

User-Defined Transactions

327

(1)

User-defined Transaction Syntax

328

(1)

Preventing Data Modification Errors

329

(1)

Performance

330

(8)

Benchmarking

331

(1)

Design and Indexing

332

(1)

Optimizing Queries

333

(1)

Optimizers

333

(1)

Queries

334

(2)

Other Tools for Monitoring and Boosting Performance

336

(1)

Logging

336

(1)

Monitoring Execution Plans

336

(2)

Index Management

338

(1)

Data Integrity

338

(5)

Domain Constraints

339

(2)

Entity Integrity

341

(1)

Referential Integrity

341

(1)

Stored Procedures and Triggers

342

(1)

Summary

343

(2)

Solving Business Problems

345

(34)

In This Chapter

345

(1)

Using SQL on the Job

345

(1)

Thinking Conditionally

346

(7)

Case/Decode

346

(5)

Changing Null Displays

351

(2)

Formatting and Displaying Data

353

(8)

Displaying One Columns as Two

353

(3)

Displaying Two Columns as One

356

(2)

Converting from One Datatype to Another

358

(3)

Playing with Patterns

361

(9)

Matching Uppercase and Lowercase Letters

361

(2)

Finding Data Within a Range When You Don't Know the Values

363

(2)

Locating Date Data

365

(4)

Displaying Data by Time Units

369

(1)

Avoiding Mistakes

370

(8)

Distinguishing Distincts

371

(1)

Distinct with Columns and Expressions

372

(1)

Distinct with Aggregates

373

(2)

Distinct and Distinct

375

(1)

Removing Duplicates

375

(2)

Finding the ``First'' Entry

377

(1)

Summary

378

(1)

Appendix A: Syntax Summary for the SQL Used in This Book

379

(2)

In This Appendix

379

(1)

Formatting

379

(1)

Syntax Conventions

380

(1)

Statement List

380

(1)

Appendix B: Industry SQL Equivalents

381

(6)

In This Appendix

381

(1)

Comparisons

381

(1)

Naming Convention Comparison

381

(1)

Datatype Comparison

382

(2)

Function Comparison

384

(3)

Character Functions

384

(1)

Conditional Functions

385

(1)

Date and Time Functions

385

(2)

Appendix C: Glossary

387

(16)

Appendix D: The bookbiz Sample Database

403

(48)

In This Appendix

403

(1)

Database Details

403

(1)

Table Charts

403

(11)

Publishers Table

403

(1)

Authors Table

404

(1)

Titles Table

405

(3)

Titleauthors Table

408

(1)

Sales Table

409

(1)

Salesdetails Table

410

(1)

Editors Table

411

(1)

Titleditors Table

412

(1)

Roysched Table

413

(1)

Create Statements for the bookbiz Database

414

(13)

Adaptive Server Anywhere Creates

415

(3)

Transact-SQL Creates

418

(4)

Oracle Creates

422

(2)

Informix Creates

424

(3)

Insert Statements

427

(19)

Create View Statements

446

(5)

Appendix E: Resources

451

(4)

In This Appendix

451

(1)

Books

451

(3)

General

451

(1)

Informix

452

(1)

Microsoft SQL Server

452

(1)

mSQL/MySQL

452

(1)

Oracle

453

(1)

Sybase

453

(1)

Transact-SQL

453

(1)

Web Sites

454

(1)

Vendor Sites

454

(1)

Amazon no longer offers textbook rentals. We do!

The Practical SQL Handbook Using SQL Variants

The Practical SQL Handbook Using SQL Variants

9780201703092

0201703092

Summary

Author Biography

Table of Contents

Supplemental Materials

Excerpts

Write a Review