Amazon no longer offers textbook rentals. We do!

SQL For Dummies^®, 6th Edition

by: Allen G. Taylor (Database Consultant, Oregon City, Oregon)

SQL For Dummies^®, 6th Edition

by: Allen G. Taylor (Database Consultant, Oregon City, Oregon)

ISBN 13:
9780470046524
ISBN 10:
047004652X
Edition: 6th
Format: Paperback
Copyright: 08/01/2006
Publisher: For Dummies

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

See how SQL interfaces with today's environments Start building and using relational databases with SQL's newest features The database may be the twenty-first century filing cabinet, but building one is a little more complex than sliding drawers into a metal box. With this book to guide you through all the newest features of SQL, you'll soon be whipping up relational databases, using SQL with XML to power data-driven Web sites, and more! Discover how to * Use SQL in a client/server system * Build a multitable relational database * Construct nested and recursive queries * Set up database security * Use SQL within applications * Map SQL to XML

Introduction

(1)

About This Book

(1)

Who Should Read This Book?

(1)

How This Book Is Organized

(2)

Part I: Basic Concepts

(1)

Part II: Using SQL to Build Databases

(1)

Part III: Storing and Retrieving Data

(1)

Part IV: Controlling Operations

(1)

Part V: Taking SQL to the Real World

(1)

Part VI: Advanced Topics

(1)

Part VII: The Part of Tens

(1)

Part VIII: Appendixes

(1)

Icons Used in This Book

(1)

Getting Started

(1)

Part I: Basic Concepts

(68)

Relational Database Fundamentals

(14)

Keeping Track of Things

(1)

What Is a Database?

(1)

Database Size and Complexity

(1)

What Is a Database Management System?

(1)

Flat Files

(2)

Database Models

(8)

Relational model

(1)

Why relational is better

(1)

Components of a relational database

(1)

Holidays bring families together

(2)

Enjoy the view

(3)

Schemas, domains, and constraints

(1)

The object model challenges the relational model

(1)

The object-relational model

(1)

Database Design Considerations

(1)

SQL Fundamentals

(26)

What SQL Is and Isn't

(2)

A (Very) Little History

(1)

SQL Commands

(1)

Reserved Words

(1)

Data Types

(16)

Exact numerics

(2)

Approximate numerics

(2)

Character strings

(2)

Booleans

(1)

Datetimes

(2)

Intervals

(1)

XML type

(1)

ROW types

(1)

Collection types

(1)

REF types

(1)

User-defined types

(3)

Data type summary

(2)

Null Values

(1)

Constraints

(1)

Using SQL in a Client/Server System

(2)

The server

(1)

The client

(1)

Using SQL on the Internet/Intranet

(2)

The Components of SQL

(26)

Data Definition Language

(11)

When ``Just do it!'' is not good advice

(1)

Creating tables

(2)

A room with a view

(5)

Collecting tables into schemas

(1)

Ordering by catalog

(1)

Getting familiar with DDL commands

(1)

Data Manipulation Language

(7)

Value expressions

(3)

Predicates

(1)

Logical connectives

(1)

Set functions

(2)

Subqueries

(1)

Data Control Language

(7)

Transactions

(1)

Users and privileges

(3)

Referential integrity constraints can jeopardize your data

(2)

Delegating responsibility for security

(1)

Part II: Using SQL to Build Databases

(48)

Building and Maintaining a Simple Database Structure

(16)

Building a Simple Database Using a RAD Tool

(10)

Deciding what to track

(1)

Creating a table with Design View

(3)

Altering the table structure

(2)

Identifying a primary key

(1)

Creating an index

(2)

Deleting a table

(1)

Building PowerDesign with SQL's DDL

(4)

Using SQL with Microsoft Access

(1)

Creating a table

(1)

Creating an index

(1)

Altering the table structure

(1)

Deleting a table

(1)

Deleting an index

(1)

Portability Considerations

(1)

Building a Multitable Relational Database

(30)

Designing a Database

(9)

Step 1: Defining objects

(1)

Step 2: Identifying tables and columns

(1)

Step 3: Defining tables

(4)

Domains, character sets, collations, and translations

(1)

Getting into your database fast with keys

(2)

Working with Indexes

100

(4)

What's an index, anyway?

101

(1)

Why you should want an index

102

(1)

Maintaining an index

103

(1)

Maintaining Integrity

104

(10)

Entity integrity

104

(1)

Domain integrity

105

(1)

Referential integrity

106

(3)

Just when you thought it was safe

109

(1)

Potential problem areas

109

(2)

Constraints

111

(3)

Normalizing the Database

114

(7)

First normal form

116

(1)

Second normal form

117

(1)

Third normal form

118

(1)

Domain-key normal form (DK/NF)

119

(1)

Abnormal form

120

(1)

Part III: Storing and Retrieving Data

121

(132)

Manipulating Database Data

123

(18)

Retrieving Data

124

(1)

Creating Views

125

(4)

From tables

126

(1)

With a selection condition

127

(1)

With a modified attribute

128

(1)

Updating Views

129

(1)

Adding New Data

130

(5)

Adding data one row at a time

130

(2)

Adding data only to selected columns

132

(1)

Adding a block of rows to a table

132

(3)

Updating Existing Data

135

(3)

Transferring Data

138

(1)

Deleting Obsolete Data

139

(2)

Specifying Values

141

(22)

Values

141

(6)

Row values

142

(1)

Literal values

142

(2)

Variables

144

(2)

Special variables

146

(1)

Column references

146

(1)

Value Expressions

147

(4)

String value expressions

148

(1)

Numeric value expressions

149

(1)

Datetime value expressions

149

(1)

Interval value expressions

150

(1)

Conditional value expressions

150

(1)

Functions

151

(12)

Summarizing by using set functions

151

(3)

Value functions

154

(9)

Using Advanced SQL Value Expressions

163

(12)

CASE Conditional Expressions

163

(7)

Using CASE with search conditions

164

(2)

Using CASE with values

166

(2)

A special CASE --- NULLIF

168

(2)

Another special CASE --- COALESCE

170

(1)

CAST Data-Type Conversions

170

(3)

Using CAST within SQL

172

(1)

Using CAST between SQL and the host language

172

(1)

Row Value Expressions

173

(2)

Zeroing In on the Data You Want

175

(26)

Modifying Clauses

175

(2)

From Clauses

177

(1)

Where Clauses

177

(17)

Comparison predicates

179

(1)

Between

180

(1)

In and Not In

181

(1)

Like and Not Like

182

(2)

Similar

184

(1)

Null

184

(1)

All, Some, Any

185

(3)

Exists

188

(1)

Unique

189

(1)

Distinct

189

(1)

Overlaps

190

(1)

Match

190

(2)

Referential integrity rules and the Match predicate

192

(2)

Logical Connectives

194

(2)

And

194

(1)

195

(1)

NOT

195

(1)

Group by Clauses

196

(1)

Having Clauses

197

(1)

Order by Clauses

198

(3)

Using Relational Operators

201

(24)

Union

201

(3)

The Union All operation

203

(1)

The Corresponding operation

203

(1)

Intersect

204

(1)

Except

205

(1)

Various Joins

206

(17)

Basic join

206

(2)

Equi-join

208

(2)

Cross join

210

(1)

Natural join

210

(1)

Condition join

211

(1)

Column-name join

211

(1)

Inner join

212

(1)

Outer join

213

(3)

Union join

216

(7)

On versus Where

223

(2)

Delving Deep with Nested Queries

225

(18)

What Subqueries Do

226

(17)

Nested queries that return sets of rows

227

(3)

Nested queries that return a single value

230

(3)

The All, Some, and Any quantifiers

233

(2)

Nested queries that are an existence test

235

(1)

Other correlated subqueries

236

(4)

Update, Delete, and Insert statements

240

(3)

Recursive Queries

243

(10)

What Is Recursion?

243

(3)

Houston, we have a problem

244

(1)

Failure is not an option

244

(2)

What Is a Recursive Query?

246

(1)

Where Might You Use a Recursive Query?

247

(5)

Querying the hard way

248

(1)

Saving time with a recursive query

249

(3)

Where Else Might You Use a Recursive Query?

252

(1)

Part IV: Controlling Operations

253

(48)

Providing Database Security

255

(14)

The SQL Data Control Language

256

(1)

User Access Levels

256

(2)

The database administrator

256

(1)

Database object owners

257

(1)

The public

257

(1)

Granting Privileges to Users

258

(7)

Roles

260

(1)

Inserting data

260

(1)

Looking at data

260

(1)

Modifying table data

261

(1)

Deleting obsolete rows from a table

262

(1)

Referencing related tables

262

(1)

Using domains, character sets, collations, and translations

263

(1)

Causing SQL statements to be executed

264

(1)

Granting the Power to Grant Privileges

265

(1)

Taking Privileges Away

266

(2)

Using GRANT and REVOKE Together to Save Time and Effort

268

(1)

Protecting Data

269

(18)

Threats to Data Integrity

269

(4)

Platform instability

270

(1)

Equipment failure

270

(1)

Concurrent access

271

(2)

Reducing Vulnerability to Data Corruption

273

(9)

Using SQL transactions

274

(1)

The default transaction

275

(1)

Isolation levels

276

(2)

The implicit transaction-starting statement

278

(1)

Set Transaction

278

(1)

Commit

279

(1)

Rollback

279

(1)

Locking database objects

280

(1)

Backing up your data

281

(1)

Savepoints and subtransactions

281

(1)

Constraints Within Transactions

282

(5)

Using SQL within Applications

287

(14)

SQL in an Application

288

(3)

Keeping an eye out for the asterisk

288

(1)

SQL strengths and weaknesses

289

(1)

Procedural language strengths and weaknesses

289

(1)

Problems in combining SQL with a procedural language

290

(1)

Hooking SQL into Procedural Languages

291

(10)

Embedded SQL

291

(3)

Module language

294

(2)

Object-oriented RAD tools

296

(1)

Using SQL with Microsoft Access

297

(4)

Part V: Taking SQL to the Real World

301

(32)

Accessing Data with ODBC and JDBC

303

(10)

ODBC

304

(1)

The ODBC interface

304

(1)

Components of ODBC

304

(1)

ODBC in a Client/Server Environment

305

(1)

ODBC and the Internet

306

(13)

Server extensions

307

(1)

Client extensions

308

(11)

ODBC and an Intranet

319

JDBC

310

(3)

Operating on XML Data with SQL

313

(20)

How XML Relates to SQL

313

(1)

The XML Data Type

314

(2)

When to use the XML type

314

(1)

When not to use the XML type

315

(1)

Mapping SQL to XML and XML to SQL

316

(4)

Mapping character sets

316

(1)

Mapping identifiers

316

(1)

Mapping data types

317

(1)

Mapping tables

318

(1)

Handling null values

318

(1)

Generating the XML Schema

319

(1)

SQL Functions that Operate on XML Data

320

(4)

XMLELEMENT

320

(1)

XMLFOREST

321

(1)

XMLCONCAT

321

(1)

XMLAGG

322

(1)

XMLCOMMENT

322

(1)

XMLPARSE

323

(1)

XMLPI

323

(1)

XMLQUERY

323

(1)

XMLCAST

324

(1)

Predicates

324

(2)

Document

325

(1)

Content

325

(1)

XMLEXISTS

325

(1)

Valid

326

(1)

Transforming XML Data into SQL Tables

326

(2)

Mapping Non-Predefined Data Types to XML

328

(4)

Domain

328

(1)

Distinct UDT

329

(1)

Row

329

(1)

Array

330

(1)

Multiset

331

(1)

The Marriage of SQL and XML

332

(1)

Part VI: Advanced Topics

333

(40)

Stepping through a Dataset with Cursors

335

(10)

Declaring a Cursor

336

(4)

The query expression

337

(1)

The Order by clause

337

(1)

The updatability clause

338

(1)

Sensitivity

339

(1)

Scrollability

340

(1)

Opening a Cursor

340

(2)

Fetching Data from a Single Row

342

(2)

Syntax

342

(1)

Orientation of a scrollable cursor

343

(1)

Positioned Delete and Update statements

343

(1)

Closing a Cursor

344

(1)

Adding Procedural Capabilities with Persistent Stored Modules

345

(16)

Compound Statements

345

(7)

Atomicity

346

(1)

Variables

347

(1)

Cursors

348

(1)

Conditions

348

(1)

Handling conditions

349

(2)

Conditions that aren't handled

351

(1)

Assignment

352

(1)

Flow of Control Statements

352

(5)

If. . .Then. . .Else. . .End If

352

(1)

Case. . .End Case

353

(1)

Loop. . .Endloop

354

(1)

Leave

355

(1)

While. . .Do. . .End While

355

(1)

Repeat. . .Until. . .End Repeat

356

(1)

For. . .Do. . .End For

356

(1)

Iterate

356

(1)

Stored Procedures

357

(1)

Stored Functions

358

(1)

Privileges

359

(1)

Stored Modules

359

(2)

Handling Errors

361

(12)

SQLSTATE

361

(2)

Whenever Clause

363

(1)

Diagnostics Areas

364

(7)

The diagnostics header area

364

(2)

The diagnostics detail area

366

(2)

Constraint violation example

368

(1)

Adding constraints to an existing table

369

(1)

Interpreting the information returned by SQLSTATE

370

(1)

Handling Exceptions

371

(2)

Part VII: The Part of Tens

373

(10)

Ten Common Mistakes

375

(4)

Assuming That Your Clients Know What They Need

375

(1)

Ignoring Project Scope

376

(1)

Considering Only Technical Factors

376

(1)

Not Asking for Client Feedback

376

(1)

Always Using Your Favorite Development Environment

377

(1)

Using Your Favorite System Architecture Exclusively

377

(1)

Designing Database Tables in Isolation

377

(1)

Neglecting Design Reviews

378

(1)

Skipping Beta Testing

378

(1)

Not Documenting Your Process

378

(1)

Ten Retrieval Tips

379

(4)

Verify the Database Structure

379

(1)

Try Queries on a Test Database

380

(1)

Double-Check Queries That Include Joins

380

(1)

Triple-Check Queries with Subselects

380

(1)

Summarize Data with Group by

380

(1)

Watch Group by Clause Restrictions

381

(1)

Use Parentheses with AND, OR, and NOT

381

(1)

Control Retrieval Privileges

381

(1)

Back Up Your Databases Regularly

382

(1)

Handle Error Conditions Gracefully

382

(1)

Part VIII: Appendixes

383

(14)

Appendix A: SQL:2003 Reserved Words

385

(4)

Appendix B: Glossary

389

(8)

Index

397

Amazon no longer offers textbook rentals. We do!

SQL For Dummies^®, 6th Edition

SQL For Dummies^®, 6th Edition

9780470046524

047004652X

Summary

Author Biography

Table of Contents

Supplemental Materials

Write a Review

Amazon no longer offers textbook rentals. We do!

SQL For Dummies®, 6th Edition

SQL For Dummies®, 6th Edition

9780470046524

047004652X

Summary

Author Biography

Table of Contents

Supplemental Materials

Write a Review

SQL For Dummies^®, 6th Edition

SQL For Dummies^®, 6th Edition