SQL For Dummies, 8th Edition

483 Pages·2013·43.87 MB·English

by  Taylor, Allen G.

www.allitebooks.com

www.allitebooks.com

by Allen G. Taylor

Author of Database Development For Dummies,

SQL All-in-One For Dummies, and
Crystal Reports 2008 For Dummies

SQL

8th Edition

www.allitebooks.com

SQL For Dummies®, 8th Edition

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-
ted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permis-
sion of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions
Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax (201) 748-
6008, or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and
related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be
used without written permission. All other trademarks are the property of their respective owners. John
Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO
REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF
THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITH-
OUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE
CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES
CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR
OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF
A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE
AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZA-
TION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE
OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES
THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT
MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS
WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND
WHEN IT IS READ.

For general information on our other products and services, please contact our Customer Care
Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone]. For tech-
nical support, please visit www.wiley.com/techsupport.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material
included with standard print versions of this book may not be included in e-books or in print-on-demand.
If this book refers to media such as a CD or DVD that is not included in the version you purchased, you
may download this material at http://booksupport.wiley.com. For more information about Wiley
products, visit www.wiley.com.

Library of Congress Control Number: 2013942771

ISBN 978-1-118-60796-1 (pbk); 978-1-118-65711-9 (ebk); ISBN 978-1-118-62783-9 (ebk);
ISBN 978-1-118-65718-8 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

www.allitebooks.com

Contents at a Glance

Introduction ................................................................ 1

Part I: Getting Started with SQL ................................... 3

Chapter 1: Relational Database Fundamentals ..............................................................5
Chapter 2: SQL Fundamentals ........................................................................................21
Chapter 3: The Components of SQL ..............................................................................51
Part II: Using SQL to Build Databases ......................... 79

Chapter 4: Building and Maintaining a Simple Database Structure...........................81
Chapter 5: Building a Multitable Relational Database...............................................105
Part III: Storing and Retrieving Data......................... 137

Chapter 6: Manipulating Database Data .....................................................................139
Chapter 7: Handling Temporal Data ............................................................................157
Chapter 8: Specifying Values ........................................................................................171
Chapter 9: Using Advanced SQL Value Expressions .................................................197
Chapter 10: Zeroing In on the Data You Want............................................................211
Chapter 11: Using Relational Operators .....................................................................243
Chapter 12: Delving Deep with Nested Queries .........................................................267
Chapter 13: Recursive Queries .....................................................................................285
Part IV: Controlling Operations ................................. 295

Chapter 14: Providing Database Security ...................................................................297
Chapter 15: Protecting Data .........................................................................................313
Chapter 16: Using SQL within Applications ................................................................333
Part V: Taking SQL to the Real World ........................ 347

Chapter 17: Accessing Data with ODBC and JDBC ....................................................349
Chapter 18: Operating on XML Data with SQL ...........................................................359

Part VI: Advanced Topics ......................................... 381

Chapter 19: Stepping through a Dataset with Cursors .............................................383
Chapter 20: Adding Procedural Capabilities with Persistent Stored Modules ......393
Chapter 21: Handling Errors .........................................................................................411
Chapter 22: Triggers ......................................................................................................423

www.allitebooks.com

SQL For Dummies, 8th Edition
iv

Part VII: The Part of Tens ......................................... 429

Chapter 23: Ten Common Mistakes .............................................................................431
Chapter 24: Ten Retrieval Tips ....................................................................................435
Appendix: SQL: 2011 Reserved Words ....................... 439

Index ...................................................................... 443

www.allitebooks.com

Table of Contents

Introduction ................................................................. 1

About This Book ..............................................................................................1
Who Should Read This Book? ........................................................................2
Icons Used in This Book .................................................................................2
Where to Go from Here ...................................................................................2

Part I: Getting Started with SQL .................................... 3

Chapter 1: Relational Database Fundamentals . . . . . . . . . . . . . . . . . . . . .5

Keeping Track of Things .................................................................................6
What Is a Database? ........................................................................................7
Database Size and Complexity .......................................................................7
What Is a Database Management System? ...................................................8
Flat Files ............................................................................................................9
Database Models ...........................................................................................11

Relational model ..................................................................................11
Components of a relational database................................................12
Dealing with your relations ................................................................12
Enjoy the view ......................................................................................14
Schemas, domains, and constraints ..................................................16
The object model challenged the relational model .........................18
The object-relational model ...............................................................18

Database Design Considerations .................................................................19

Chapter 2: SQL Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21

What SQL Is and Isn’t ....................................................................................21
A (Very) Little History ...................................................................................23
SQL Statements ..............................................................................................24
Reserved Words .............................................................................................26
Data Types ......................................................................................................26

Exact numerics .....................................................................................27
Approximate numerics ........................................................................29
Character strings .................................................................................30
Binary strings .......................................................................................32
Booleans ................................................................................................33
Datetimes ..............................................................................................33
Intervals ................................................................................................35
XML type ...............................................................................................35
ROW types ............................................................................................38
Collection types ...................................................................................39
REF types ..............................................................................................41

www.allitebooks.com

SQL For Dummies, 8th Edition
vi

User-defined types ...............................................................................41
Data type summary..............................................................................44

Null Values ......................................................................................................46
Constraints .....................................................................................................46
Using SQL in a Client/Server System ...........................................................47

The server .............................................................................................47
The client ..............................................................................................48

Using SQL on the Internet or an Intranet ....................................................49

Chapter 3: The Components of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Data Definition Language ..............................................................................52

When “Just do it!” is not good advice ...............................................52
Creating tables .....................................................................................53
A room with a view ..............................................................................55
Collecting tables into schemas ..........................................................61
Ordering by catalog .............................................................................61
Getting familiar with DDL statements ...............................................62

Data Manipulation Language ........................................................................64

Value expressions ................................................................................64
Predicates .............................................................................................68
Logical connectives .............................................................................69
Set functions .........................................................................................69
Subqueries ............................................................................................71

Data Control Language ..................................................................................71

Transactions .........................................................................................71
Users and privileges ............................................................................73
Referential integrity constraints can jeopardize your data ...........75
Delegating responsibility for security ...............................................77

Part II: Using SQL to Build Databases ......................... 79

Chapter 4: Building and Maintaining a Simple Database Structure . . . 81

Using a RAD Tool to Build a Simple Database ...........................................82

Deciding what to track ........................................................................82
Creating a database table ...................................................................83
Altering the table structure ................................................................90
Creating an index .................................................................................92
Deleting a table.....................................................................................94

Building POWER with SQL’s DDL .................................................................95

Using SQL with Microsoft Access ......................................................95
Creating a table ....................................................................................97
Creating an index ...............................................................................101
Altering the table structure ..............................................................102
Deleting a table...................................................................................102
Deleting an index ...............................................................................103

Portability Considerations .........................................................................103

www.allitebooks.com

vii
Table of Contents

Chapter 5: Building a Multitable Relational Database . . . . . . . . . . . .105

Designing a Database ..................................................................................105

Step 1: Defining objects .....................................................................106
Step 2: Identifying tables and columns ...........................................106
Step 3: Defining tables .......................................................................107
Domains, character sets, collations, and translations ..................111
Getting into your database fast with keys ......................................112

Working with Indexes ..................................................................................114

What’s an index, anyway? .................................................................115
Why you should want an index ........................................................116
Maintaining an index .........................................................................117

Maintaining Data Integrity ..........................................................................118

Entity integrity ...................................................................................118
Domain integrity ................................................................................119
Referential integrity ...........................................................................120
Just when you thought it was safe . . ..............................................123
Potential problem areas ....................................................................124
Constraints .........................................................................................126

Normalizing the Database ..........................................................................129

Modification anomalies and normal forms .....................................129
First normal form ...............................................................................132
Second normal form ..........................................................................132
Third normal form .............................................................................134
Domain-key normal form (DK/NF) ...................................................134
Abnormal form ...................................................................................135

Part III: Storing and Retrieving Data ......................... 137

Chapter 6: Manipulating Database Data . . . . . . . . . . . . . . . . . . . . . . . .139

Retrieving Data ............................................................................................139
Creating Views .............................................................................................141

From tables .........................................................................................142
With a selection condition ................................................................143
With a modified attribute ..................................................................144

Updating Views ............................................................................................145
Adding New Data .........................................................................................146

Adding data one row at a time .........................................................146
Adding data only to selected columns ............................................148
Adding a block of rows to a table ....................................................148

Updating Existing Data ................................................................................151
Transferring Data .........................................................................................154
Deleting Obsolete Data ...............................................................................156

www.allitebooks.com

SQL For Dummies, 8th Edition
viii

Chapter 7: Handling Temporal Data . . . . . . . . . . . . . . . . . . . . . . . . . . . .157

Understanding Times and Periods in SQL:2011 .......................................158
Working with Application-Time Period Tables ........................................159

Designating primary keys in application-time period tables ..............162
Applying referential constraints to application-time

period tables ...................................................................................163

Querying application-time period tables ........................................164

Working with System-Versioned Tables ...................................................165

Designating primary keys in system-versioned tables..................167
Applying referential constraints to system-versioned tables ......168
Querying system-versioned tables ..................................................168

Tracking Even More Time Data with Bitemporal Tables ........................169

Chapter 8: Specifying Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .171

Values ............................................................................................................171

Row values ..........................................................................................172
Literal values ......................................................................................172
Variables .............................................................................................174
Special variables ................................................................................176
Column references .............................................................................176

Value Expressions .......................................................................................177

String value expressions ...................................................................178
Numeric value expressions ..............................................................179
Datetime value expressions..............................................................179
Interval value expressions ................................................................180
Conditional value expressions .........................................................180

Functions ......................................................................................................181

Summarizing by using set functions ................................................181
Value functions ..................................................................................184

Chapter 9: Using Advanced SQL Value Expressions . . . . . . . . . . . . . .197

CASE Conditional Expressions ...................................................................197

Using CASE with search conditions .................................................198
Using CASE with values .....................................................................200
A special CASE — NULLIF .................................................................202
Another special CASE — COALESCE ...............................................204

CAST Data-Type Conversions ....................................................................205

Using CAST within SQL .....................................................................206
Using CAST between SQL and the host language ..........................206

Row Value Expressions ...............................................................................207

Chapter 10: Zeroing In on the Data You Want . . . . . . . . . . . . . . . . . . . .211

Modifying Clauses .......................................................................................211
FROM Clauses ..............................................................................................213

www.allitebooks.com

ix
Table of Contents

WHERE Clauses ............................................................................................213

Comparison predicates .....................................................................215
BETWEEN ............................................................................................215
IN and NOT IN .....................................................................................217
LIKE and NOT LIKE ............................................................................218
SIMILAR ...............................................................................................220
NULL ....................................................................................................220
ALL, SOME, ANY .................................................................................221
EXISTS .................................................................................................224
UNIQUE ................................................................................................225
DISTINCT .............................................................................................225
OVERLAPS ...........................................................................................226
MATCH ................................................................................................226
Referential integrity rules and the MATCH predicate .......................228

Logical Connectives ....................................................................................230

AND ......................................................................................................230
OR ........................................................................................................231
NOT ......................................................................................................232

GROUP BY Clauses ......................................................................................232
HAVING Clauses ...........................................................................................234
ORDER BY Clauses ......................................................................................235
Limited FETCH .............................................................................................236
Peering through a Window to Create a Result Set ..................................238

Partitioning a window into buckets with NTILE ............................239
Navigating within a window .............................................................239
Nesting window functions ...............................................................241
Evaluating groups of rows ................................................................242

Chapter 11: Using Relational Operators . . . . . . . . . . . . . . . . . . . . . . . . .243

UNION ...........................................................................................................243

The UNION ALL operation ................................................................245
The CORRESPONDING operation.....................................................245

INTERSECT ...................................................................................................246
EXCEPT .........................................................................................................248
Join Operators .............................................................................................249

Basic join .............................................................................................249
Equi-join ..............................................................................................251
Cross join ............................................................................................253
Natural join .........................................................................................253
Condition join .....................................................................................254
Column-name join ..............................................................................254
Inner join .............................................................................................255
Outer join ............................................................................................256
Union join ............................................................................................259

ON versus WHERE .......................................................................................266

SQL For Dummies, 8th Edition
x

Chapter 12: Delving Deep with Nested Queries . . . . . . . . . . . . . . . . . .267

What Subqueries Do ....................................................................................268

Nested queries that return sets of rows .........................................269
Nested queries that return a single value .......................................272
The ALL, SOME, and ANY quantifiers..............................................275
Nested queries that are an existence test ......................................277
Other correlated subqueries ............................................................278
UPDATE, DELETE, and INSERT.........................................................282
Retrieving changes with pipelined DML .........................................284

Chapter 13: Recursive Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .285

What Is Recursion? ......................................................................................285

Houston, we have a problem ............................................................287
Failure is not an option .....................................................................287

What Is a Recursive Query? .......................................................................288
Where Might You Use a Recursive Query? ...............................................289

Querying the hard way ......................................................................290
Saving time with a recursive query .................................................291

Where Else Might You Use a Recursive Query? ......................................293

Part IV: Controlling Operations .................................. 295

Chapter 14: Providing Database Security . . . . . . . . . . . . . . . . . . . . . . .297

The SQL Data Control Language ................................................................298
User Access Levels ......................................................................................298

The database administrator .............................................................298
Database object owners ....................................................................299
The public ...........................................................................................300

Granting Privileges to Users .......................................................................300

Roles ....................................................................................................301
Inserting data......................................................................................302
Looking at data ...................................................................................302
Modifying table data ..........................................................................303
Deleting obsolete rows from a table................................................304
Referencing related tables ................................................................304
Using domains ....................................................................................305
Causing SQL statements to be executed .........................................306

Granting Privileges across Levels .............................................................307
Granting the Power to Grant Privileges ....................................................309
Taking Privileges Away ...............................................................................310
Using GRANT and REVOKE Together to Save Time and Effort ..............311

xi
Table of Contents

Chapter 15: Protecting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .313

Threats to Data Integrity ............................................................................313

Platform instability ............................................................................314
Equipment failure ..............................................................................314
Concurrent access .............................................................................315

Reducing Vulnerability to Data Corruption .............................................317

Using SQL transactions .....................................................................318
The default transaction .....................................................................319
Isolation levels ...................................................................................320
The implicit transaction-starting statement...................................322
SET TRANSACTION ............................................................................323
COMMIT ..............................................................................................324
ROLLBACK ..........................................................................................324
Locking database objects .................................................................324
Backing up your data.........................................................................325
Savepoints and subtransactions ......................................................325

Constraints Within Transactions ...............................................................327

Chapter 16: Using SQL within Applications . . . . . . . . . . . . . . . . . . . . .333

SQL in an Application ..................................................................................333

Keeping an eye out for the asterisk .................................................334
SQL strengths and weaknesses ........................................................334
Procedural languages’ strengths and weaknesses ........................335
Problems in combining SQL with a procedural language .............335

Hooking SQL into Procedural Languages .................................................336

Embedded SQL ...................................................................................336
Module language ................................................................................339
Object-oriented RAD tools ................................................................342
Using SQL with Microsoft Access ....................................................343

Part V: Taking SQL to the Real World ........................ 347

Chapter 17: Accessing Data with ODBC and JDBC . . . . . . . . . . . . . . .349

ODBC .............................................................................................................350

The ODBC interface ...........................................................................350
Components of ODBC ........................................................................351

ODBC in a Client/Server Environment ......................................................352
ODBC and the Internet ................................................................................352

Server extensions...............................................................................353
Client extensions ................................................................................354

ODBC and an Intranet .................................................................................355
JDBC ..............................................................................................................355

SQL For Dummies, 8th Edition
xii

Chapter 18: Operating on XML Data with SQL . . . . . . . . . . . . . . . . . . .359

How XML Relates to SQL ............................................................................359
The XML Data Type .....................................................................................360

When to use the XML type................................................................361
When not to use the XML type .........................................................362

Mapping SQL to XML and XML to SQL ......................................................362

Mapping character sets ....................................................................362
Mapping identifiers ............................................................................363
Mapping data types ...........................................................................364
Mapping tables ...................................................................................364
Handling null values ..........................................................................365
Generating the XML Schema ............................................................366

SQL Functions That Operate on XML Data ..............................................367

XMLDOCUMENT.................................................................................367
XMLELEMENT ....................................................................................367
XMLFOREST ........................................................................................368
XMLCONCAT ......................................................................................368
XMLAGG ..............................................................................................369
XMLCOMMENT ..................................................................................369
XMLPARSE ..........................................................................................370
XMLPI ..................................................................................................370
XMLQUERY .........................................................................................370
XMLCAST ............................................................................................371

Predicates .....................................................................................................371

DOCUMENT ........................................................................................371
CONTENT ............................................................................................372
XMLEXISTS..........................................................................................372
VALID ...................................................................................................372

Transforming XML Data into SQL Tables .................................................373
Mapping Non-Predefined Data Types to XML ..........................................375

Domain ................................................................................................375
Distinct UDT .......................................................................................376
Row ......................................................................................................377
Array ....................................................................................................378
Multiset ...............................................................................................379

The Marriage of SQL and XML ...................................................................379

Part VI: Advanced Topics .......................................... 381

Chapter 19: Stepping through a Dataset with Cursors . . . . . . . . . . . .383

Declaring a Cursor .......................................................................................384

Query expression ...............................................................................385
ORDER BY clause ...............................................................................385

xiii
Table of Contents

Updatability clause ............................................................................387
Sensitivity ...........................................................................................387
Scrollability .........................................................................................388

Opening a Cursor .........................................................................................388
Fetching Data from a Single Row ...............................................................390

Syntax ..................................................................................................390
Orientation of a scrollable cursor ...................................................391
Positioned DELETE and UPDATE statements ................................391

Closing a Cursor ..........................................................................................392

Chapter 20: Adding Procedural Capabilities with Persistent
Stored Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .393

Compound Statements ................................................................................393

Atomicity .............................................................................................394
Variables .............................................................................................395
Cursors ................................................................................................396
Conditions ...........................................................................................396
Handling conditions ..........................................................................397
Conditions that aren’t handled ........................................................400
Assignment .........................................................................................400

Flow of Control Statements ........................................................................400

IF…THEN…ELSE…END IF .................................................................401
CASE…END CASE ...............................................................................401
LOOP…ENDLOOP ..............................................................................402
LEAVE ..................................................................................................403
WHILE…DO…END WHILE .................................................................404
REPEAT…UNTIL…END REPEAT ......................................................404
FOR…DO…END FOR .........................................................................405
ITERATE ..............................................................................................405

Stored Procedures .......................................................................................406
Stored Functions ..........................................................................................407
Privileges ......................................................................................................408
Stored Modules ............................................................................................409

Chapter 21: Handling Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .411

SQLSTATE .....................................................................................................411
WHENEVER Clause ......................................................................................413
Diagnostics Areas ........................................................................................414

Diagnostics header area....................................................................414
Diagnostics detail area ......................................................................416
Constraint violation example ...........................................................418
Adding constraints to an existing table ..........................................419
Interpreting the information returned by SQLSTATE ...................419

Handling Exceptions ...................................................................................420