SQL For Dummies - Allen G. Taylor - ebook

SQL For Dummies ebook

Allen G. Taylor

0,0
94,99 zł

Opis

Uncover the secrets of SQL and start building better relational databases today! This fun and friendly guide will help you demystify database management systems so you can create more powerful databases and access information with ease. Updated for the latest SQL functionality, SQL For Dummies, 8th Edition covers the core SQL language and shows you how to use SQL to structure a DBMS, implement a database design, secure your data, and retrieve information when you need it. * Includes new enhancements of SQL:2011, including temporal data functionality which allows you to set valid times for transactions to occur and helps prevent database corruption * Covers creating, accessing, manipulating, maintaining, and storing information in relational database management systems like Access, Oracle, SQL Server, and MySQL * Provides tips for keeping your data safe from theft, accidental or malicious corruption, or loss due to equipment failures and advice on eliminating errors in your work Don't be daunted by database development anymore - get SQL For Dummies, 8th Edition, and you'll be on your way to SQL stardom.

Ebooka przeczytasz w aplikacjach Legimi na:

Androidzie
iOS
czytnikach certyfikowanych
przez Legimi
Windows
10
Windows
Phone

Liczba stron: 639




SQL For Dummies®, 8th Edition

Visit www.dummies.com/cheatsheet/sql to view this book's cheat sheet.

Table of Contents

Cover

Title

Copyright

Introduction

About This Book

Who Should Read This Book?

Icons Used in This Book

Where to Go from Here

Part I: Getting Started with SQL

Chapter 1: Relational Database Fundamentals

Keeping Track of Things

What Is a Database?

Database Size and Complexity

What Is a Database Management System?

Flat Files

Database Models

Database Design Considerations

Chapter 2: SQL Fundamentals

What SQL Is and Isn’t

A (Very) Little History

SQL Statements

Reserved Words

Data Types

Null Values

Constraints

Using SQL in a Client/Server System

Using SQL on the Internet or an Intranet

Chapter 3: The Components of SQL

Data Definition Language

Data Manipulation Language

Data Control Language

Part II: Using SQL to Build Databases

Chapter 4: Building and Maintaining a Simple Database Structure

Using a RAD Tool to Build a Simple Database

Building POWER with SQL’s DDL

Portability Considerations

Chapter 5: Building a Multitable Relational Database

Designing a Database

Working with Indexes

Maintaining Data Integrity

Normalizing the Database

Part III: Storing and Retrieving Data

Chapter 6: Manipulating Database Data

Retrieving Data

Creating Views

Updating Views

Adding New Data

Updating Existing Data

Transferring Data

Deleting Obsolete Data

Chapter 7: Handling Temporal Data

Understanding Times and Periods in SQL:2011

Working with Application-Time Period Tables

Working with System-Versioned Tables

Tracking Even More Time Data with Bitemporal Tables

Chapter 8: Specifying Values

Values

Value Expressions

Functions

Chapter 9: Using Advanced SQL Value Expressions

CASE Conditional Expressions

CAST Data-Type Conversions

Row Value Expressions

Chapter 10: Zeroing In on the Data You Want

Modifying Clauses

FROM Clauses

WHERE Clauses

Logical Connectives

GROUP BY Clauses

HAVING Clauses

ORDER BY Clauses

Limited FETCH

Peering through a Window to Create a Result Set

Chapter 11: Using Relational Operators

UNION

INTERSECT

EXCEPT

Join Operators

ON versus WHERE

Chapter 12: Delving Deep with Nested Queries

What Subqueries Do

Chapter 13: Recursive Queries

What Is Recursion?

What Is a Recursive Query?

Where Might You Use a Recursive Query?

Where Else Might You Use a Recursive Query?

Part IV: Controlling Operations

Chapter 14: Providing Database Security

The SQL Data Control Language

User Access Levels

Granting Privileges to Users

Granting Privileges across Levels

Granting the Power to Grant Privileges

Taking Privileges Away

Using GRANT and REVOKE Together to Save Time and Effort

Chapter 15: Protecting Data

Threats to Data Integrity

Reducing Vulnerability to Data Corruption

Constraints Within Transactions

Chapter 16: Using SQL within Applications

SQL in an Application

Hooking SQL into Procedural Languages

Part V: Taking SQL to the Real World

Chapter 17: Accessing Data with ODBC and JDBC

ODBC

ODBC in a Client/Server Environment

ODBC and the Internet

ODBC and an Intranet

JDBC

Chapter 18: Operating on XML Data with SQL

How XML Relates to SQL

The XML Data Type

Mapping SQL to XML and XML to SQL

SQL Functions That Operate on XML Data

Predicates

Transforming XML Data into SQL Tables

Mapping Non-Predefined Data Types to XML

The Marriage of SQL and XML

Part VI: Advanced Topics

Chapter 19: Stepping through a Dataset with Cursors

Declaring a Cursor

Opening a Cursor

Fetching Data from a Single Row

Closing a Cursor

Chapter 20: Adding Procedural Capabilities with Persistent Stored Modules

Compound Statements

Flow of Control Statements

Stored Procedures

Stored Functions

Privileges

Stored Modules

Chapter 21: Handling Errors

SQLSTATE

WHENEVER Clause

Diagnostics Areas

Handling Exceptions

Chapter 22: Triggers

Examining Some Applications of Triggers

Creating a Trigger

Firing a Succession of Triggers

Referencing Old Values and New Values

Firing Multiple Triggers on a Single Table

Part VII: The Part of Tens

Chapter 23: Ten Common Mistakes

Assuming That Your Clients Know What They Need

Ignoring Project Scope

Considering Only Technical Factors

Not Asking for Client Feedback

Always Using Your Favorite Development Environment

Using Your Favorite System Architecture Exclusively

Designing Database Tables in Isolation

Neglecting Design Reviews

Skipping Beta Testing

Not Documenting Your Process

Chapter 24: Ten Retrieval Tips

Verify the Database Structure

Try Queries on a Test Database

Double-Check Queries That Include Joins

Triple-Check Queries with Subselects

Summarize Data with GROUP BY

Watch GROUP BY Clause Restrictions

Use Parentheses with AND, OR, and NOT

Control Retrieval Privileges

Back Up Your Databases Regularly

Handle Error Conditions Gracefully

Appendix: SQL: 2011 Reserved Words

About the Author

Dedication

Author’s Acknowledgments

Publisher’s Acknowledgments

Cheat Sheet

Connect with Dummies

End User License Agreement

List of Tables

Chapter 2: SQL Fundamentals

Table 2-1 SQL:2011 Statements

Table 2-2 Data Types

Chapter 3: The Components of SQL

Table 3-2 Examples of String Concatenation

Table 3-3 Comparison Operators and Comparison Predicates

Table 3-4 Types of Protection

Chapter 5: Building a Multitable Relational Database

Table 5-1 VetLab Tables

Table 5-3 Client Name Index for the CLIENT Table

Chapter 8: Specifying Values

Table 8-1 Example Literals of Various Data Types

Chapter 10: Zeroing In on the Data You Want

Table 10-1 Modifying Clauses and Functions

Table 10-2 SQL’s Comparison Predicates

Table 10-3 SQL’s LIKE Predicate

Chapter 11: Using Relational Operators

Table 11-1 LOCATION

Table 11-2 DEPT

Table 11-3 EMPLOYEE

Table 11-4 EMPLOYEE Table

Table 11-5 PROJECTS Table

Table 11-6 SKILLS Table

Chapter 12: Delving Deep with Nested Queries

Table 12-1 PRODUCT Table

Table 12-2 COMPONENT Table

Table 12-3 COMP_USED Table

Table 12-4 CUSTOMER Table

Table 12-5 CONTACT Table

Chapter 13: Recursive Queries

Table 13-1 Flights Offered by Vannevar Airlines

Table 13-2 REACHABLEFROM After One Pass through Recursion

Table 13-3 REACHABLEFROM After Two Passes through the Recursion

Chapter 20: Adding Procedural Capabilities with Persistent Stored Modules

Table 20-1 SQLSTATE Class Values

Table 20-2 Conditions That May Be Specified in a Condition Handler

Chapter 21: Handling Errors

Table 21-1 Diagnostics Header Area

Table 21-2 Diagnostics Detail Area

List of Illustrations

Chapter 1: Relational Database Fundamentals

Figure 1-1: A block diagram of a DBMS-based information system.

Figure 1-2: A table showing a baseball player’s offensive statistics.

Figure 1-3: Each database row contains a record; each database column holds a single attribute.

Figure 1-4: The sales manager’s view derives from the CUSTOMER table.

Figure 1-5: The branch manager’s view includes only certain rows from the CUSTOMER table.

Figure 1-6: The accounts-payable manager’s view draws from two tables.

Chapter 2: SQL Fundamentals

Figure 2-1: The relationships of the XML subtypes.

Chapter 3: The Components of SQL

Figure 3-1: Use the CREATE TABLE command to create this CUSTOMER table.

Figure 3-2: You derive the NH_CUST view from the CUSTOMER table.

Figure 3-3: A sporting goods store’s database structure.

Figure 3-4: Creating a multitable view by using JOINs.

Figure 3-5: The hierarchical structure of a typical SQL database.

Chapter 4: Building and Maintaining a Simple Database Structure

Figure 4-1: The Microsoft Access opening screen.

Figure 4-2: The Datasheet view in the Access development environment.

Figure 4-3: The Design view’s starting screen.

Figure 4-4: Using a descriptive field name to define the primary key.

Figure 4-5: The table-creation window after FirstName has been defined.

Figure 4-6: The table-creation window after LastName has been defined.

Figure 4-7: The table-creation window after all fields are defined.

Figure 4-8: The table-creation window after opening up space for a second address line.

Figure 4-9: Your revised table definition should look similar to this.

Figure 4-10: Select Delete to delete a table.

Figure 4-11: The Query screen with POWER table selected.

Figure 4-12: The database views available in Query mode.

Figure 4-13: The Object tab in SQL view.

Figure 4-14: Data-definition query to create a table.

Figure 4-15: Behold the POWERSQL table.

Chapter 5: Building a Multitable Relational Database

Figure 5-1: VetLab database tables and links.

Figure 5-2: This SALES table leads to modification anomalies.

Figure 5-3: Splitting the SALES table into two tables.

Figure 5-4: In the SALES_TRACK table, the Customer_ID and Product columns constitute a composite key.

Chapter 6: Manipulating Database Data

Figure 6-1: The ORDERS_BY_STATE view for the marketing manager.

Figure 6-2: The REPORTING_LAG view for the quality-control officer.

Figure 6-3: The view created to show birthday discounts.

Chapter 10: Zeroing In on the Data You Want

Figure 10-1: A result set for retrieval of sales from 12/01/2012 to 12/07/2012.

Figure 10-2: Average sales for each salesperson.

Figure 10-3: Total sales for each salesperson.

Figure 10-4: Total sales for all salespeople except Bennett.

Chapter 13: Recursive Queries

Figure 13-1: Result of calling spiral(1).

Figure 13-2: Descending through recursive calls, and then climbing back up to terminate.

Figure 13-3: Route map for Vannevar Airlines.

Chapter 14: Providing Database Security

Figure 14-1: The access-privilege hierarchy.

Figure 14-2: Artwork table hierarchy.

Chapter 16: Using SQL within Applications

Figure 16-1: The Design View of MBRT Papers query.

Figure 16-2: One of your View menu options is SQL View.

Figure 16-3: An SQL statement that retrieves the names of all the papers written by members of the MBRT.

Chapter 17: Accessing Data with ODBC and JDBC

Figure 17-1: A client/server system versus a web-based database system.

Figure 17-2: A web-based database system with a server extension.

Figure 17-3: A web database application, using a Java applet.

Chapter 21: Handling Errors

Figure 21-1: SQLSTATE of 00000 indicates successful completion of an SQL:statement.

Guide

Cover

Table of Contents

Begin Reading

Pages

C1

i

ii

iii

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

333

334

335

336

337

338

339

340

341

342

343

344

345

347

348

349

350

351

352

353

354

355

356

357

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

461

SQL FOR DUMMIES®

A Wiley Brand

8th Edition

Allen G. Taylor

Author of Database Development For Dummies, SQL All-in-One For Dummies, andCrystal Reports 2008 For Dummies

SQL For Dummies®

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 permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission 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, (201) 748-6011, 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 without 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 organization 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 317-572-4002. For technical 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

Introduction

Welcome to database development using SQL, the industry-standard database query language. Many database management system (DBMS) tools run on a variety of hardware platforms. The differences among the tools can be great, but all serious products have one thing in common: They support SQL data access and manipulation. If you know SQL, you can build relational databases and get useful information out of them.

About This Book

Relational database management systems are vital to many organizations. People often think that creating and maintaining these systems must be extremely complex activities — the domain of database gurus who possess enlightenment beyond that of mere mortals. This book sweeps away the database mystique. In this book, you

Get to the roots of databases.

Find out how a DBMS is structured.

Discover the major functional components of SQL.

Build a database.

Protect a database from harm.

Operate on database data.

Determine how to get the information you want out of a database.

The purpose of this book is to help you build relational databases and get valuable information out of them by using SQL. SQL is the international standard language used to create and maintain relational databases. This edition covers the latest version of the standard, SQL:2011.

This book doesn’t tell you how to design a database (I do that in Database Development For Dummies, also published by Wiley). Here I assume that you or somebody else has already created a valid design. I then illustrate how you implement that design by using SQL. If you suspect that you don’t have a good database design, then — by all means — fix your design before you try to build the database. The earlier you detect and correct problems in a development project, the cheaper the corrections will be.

Who Should Read This Book?

If you need to store or retrieve data from a DBMS, you can do a much better job with a working knowledge of SQL. You don’t need to be a programmer to use SQL, and you don’t need to know programming languages, such as Java, C, or BASIC. SQL’s syntax is like that of English.

If you are a programmer, you can incorporate SQL into your programs. SQL adds powerful data manipulation and retrieval capabilities to conventional languages. This book tells you what you need to know to use SQL’s rich assortment of tools and features inside your programs.

Icons Used in This Book

Tips save you a lot of time and keep you out of trouble.

Pay attention to the information marked by this icon — you may need it later.

Heeding the advice that this icon points to can save you from major grief. Ignore it at your peril.

This icon alerts you to the presence of technical details that are interesting but not absolutely essential to understanding the topic being discussed.

Where to Go from Here

Now for the fun part! Databases are the best tools ever invented for keeping track of the things you care about. After you understand databases and can use SQL to make them do your bidding, you wield tremendous power. Coworkers come to you when they need critical information. Managers seek your advice. Youngsters ask for your autograph. But most importantly, you know, at a very deep level, how your organization really works.

Part IGetting Started with SQL

Visit www.dummies.com for great Dummies content online.

In this part…

  The essentials of relational databases

  Basic SQL concepts

  Fundamental database tools

  Visit

www.dummies.com/extras/sql

for great Dummies content online.

Chapter 1Relational Database Fundamentals

In This Chapter

Organizing information

Defining “database” in digital terms

Deciphering DBMS

Looking at the evolution of database models

Defining “

relational

database” (can you relate?)

Considering the challenges of database design

SQL (pronounced ess-que-ell, not see’qwl, though database geeks still argue about that) is a language specifically designed with databases in mind. SQL enables people to create databases, add new data to them, maintain the data in them, and retrieve selected parts of the data. Developed in the 1970s at IBM, SQL has grown and advanced over the years to become the industry standard. It is governed by a formal standard maintained by the International Standards Organization (ISO).

Various kinds of databases exist, each adhering to a different model of how the data in the database is organized.

SQL was originally developed to operate on data in databases that follow the relational model. Recently, the international SQL standard has incorporated part of the object model, resulting in hybrid structures called object-relational databases. In this chapter, I discuss data storage, devote a section to how the relational model compares with other major models, and provide a look at the important features of relational databases.

Before I talk about SQL, however, I want to nail down what I mean by the term database. Its meaning has changed, just as computers have changed the way people record and maintain information.

Keeping Track of Things

Today people use computers to perform many tasks formerly done with other tools. Computers have replaced typewriters for creating and modifying documents. They’ve surpassed electromechanical calculators as the best way to do math. They’ve also replaced millions of pieces of paper, file folders, and file cabinets as the principal storage medium for important information. Compared with those old tools, of course, computers do much more, much faster — and with greater accuracy. These increased benefits do come at a cost, however: Computer users no longer have direct physical access to their data.

When computers occasionally fail, office workers may wonder whether computerization really improved anything at all. In the old days, a manila file folder “crashed” only if you dropped it — then you merely knelt down, picked up the papers, and put them back in the folder. Barring earthquakes or other major disasters, file cabinets never “went down,” and they never gave you an error message. A hard-drive crash is another matter entirely: You can’t “pick up” lost bits and bytes. Mechanical, electrical, and human failures can make your data go away into the Great Beyond, never to return.

Taking the necessary precautions to protect yourself from accidental data loss allows you to start cashing in on the greater speed and accuracy that computers provide.

If you’re storing important data, you have four main concerns:

Storing data has to be quick and easy because you’re likely to do it often.

The storage medium must be reliable. You don’t want to come back later and find some (or all) of your data missing.

Data retrieval has to be quick and easy, regardless of how many items you store.

You need an easy way to separate the exact information you want

now

from the tons of data that you

don’t

want right now.

State-of-the-art computer databases satisfy these four criteria. If you store more than a dozen or so data items, you probably want to store those items in a database.

What Is a Database?

The term database has fallen into loose use lately, losing much of its original meaning. To some people, a database is any collection of data items (phone books, laundry lists, parchment scrolls . . . whatever). Other people define the term more strictly.

In this book, I define a database as a self-describing collection of integrated records. And yes, that does imply computer technology, complete with programming languages such as SQL.

A record is a representation of some physical or conceptual object. Say, for example, that you want to keep track of a business’s customers. You assign a record for each customer. Each record has multiple attributes, such as name, address, and telephone number. Individual names, addresses, and so on are the data.

A database consists of both data and metadata. Metadata is the data that describes the data’s structure within a database. If you know how your data is arranged, then you can retrieve it. Because the database contains a description of its own structure, it’s self-describing. The database is integrated because it includes not only data items but also the relationships among data items.

The database stores metadata in an area called the data dictionary, which describes the tables, columns, indexes, constraints, and other items that make up the database.

Because a flat-file system (described later in this chapter) has no metadata, applications written to work with flat files must contain the equivalent of the metadata as part of the application program.

Database Size and Complexity

Databases come in all sizes, from simple collections of a few records to mammoth systems holding millions of records. Most databases fall into one of three categories, which are based on the size of the database itself, the size of the equipment it runs on, and the size of the organization that is maintaining it:

A

personal database

is designed for use by a single person on a single computer. Such a database usually has a rather simple structure and a relatively small size.

A

departmental or workgroup database

is used by the members of a single department or workgroup within an organization. This type of database is generally larger than a personal database and is necessarily more complex; such a database must handle multiple users trying to access the same data at the same time.

An

enterprise database

can be huge. Enterprise databases may model the critical information flow of entire large organizations.

What Is a Database Management System?

Glad you asked. A database management system (DBMS) is a set of programs used to define, administer, and process databases and their associated applications. The database being managed is, in essence, a structure that you build to hold valuable data. A DBMS is the tool you use to build that structure and operate on the data contained within the database.

You can find many DBMS programs on the market today. Some run on large and powerful machines, and some on personal computers, notebooks, and tablets. A strong trend, however, is for such products to work on multiple platforms or on networks that contain different classes of machines. An even stronger trend is to store data in data centers or even to store it out in the cloud, which could be a public cloud run by a large company such as Amazon, Google, or Microsoft, via the Internet, or it could be a private cloud operated by the same organization that is storing the data on its own intranet.

These days, cloud is a buzzword that is bandied about incessantly in techie circles. Like the puffy white things up in the sky, it has indistinct edges and seems to float somewhere out there. In reality, it is a collection of computing resources that is accessible via a browser, either over the Internet or on a private intranet. The thing that distinguishes the computing resources in the cloud from similar computing resources in a physical data center is the fact that the resources are accessible via a browser rather than an application program that directly accesses those resources.

A DBMS that runs on platforms of multiple classes, large and small, is called scalable.

The value is not in the data, but in the structure

Years ago, some clever person calculated that if you reduce human beings to their components of carbon, hydrogen, oxygen, and nitrogen atoms (plus traces of others), they would be worth only 97 cents. However droll this assessment, it’s misleading. People aren’t composed of mere isolated collections of atoms. Our atoms combine into enzymes, proteins, hormones, and many other substances that would cost millions of dollars per ounce on the pharmaceutical market. The precise structure of these combinations of atoms is what gives them greater value. By analogy, database structure makes possible the interpretation of seemingly meaningless data. The structure brings to the surface patterns, trends, and tendencies in the data. Unstructured data — like uncombined atoms — has little or no value.

Whatever the size of the computer that hosts the database — and regardless of whether the machine is connected to a network — the flow of information between database and user is always the same. Figure 1-1 shows that the user communicates with the database through the DBMS. The DBMS masks the physical details of the database storage so that the application has to concern itself only with the logical characteristics of the data, not with how the data is stored.

Figure 1-1: A block diagram of a DBMS-based information system.

Flat Files

Where structured data is concerned, the flat file is as simple as it gets. No, a flat file isn’t a folder that’s been squashed under a stack of books. Flat files are so called because they have minimal structure. If they were buildings, they’d barely stick up from the ground. A flat file is simply a collection of data records, one after another, in a specified format — the data, the whole data, and nothing but the data — in effect, a list. In computer terms, a flat file is simple. Because the file doesn’t store structural information (metadata), its overhead (stuff in the file that is not data but takes up storage space) is minimal.

Say that you want to keep track of the names and addresses of your company’s customers in a flat file system. The system may have a structure something like this:

Harold Percival  26262 S. Howards Mill Rd  Westminster  CA92683

Jerry Appel      32323 S. River Lane Rd    Santa Ana    CA92705

Adrian Hansen    232 Glenwood Court        Anaheim      CA92640

John Baker       2222 Lafayette St         Garden Grove CA92643

Michael Pens     77730 S. New Era Rd       Irvine       CA92715

Bob Michimoto    25252 S. Kelmsley Dr      Stanton      CA92610

Linda Smith      444 S.E. Seventh St       Costa Mesa   CA92635

Robert Funnell   2424 Sheri Court          Anaheim      CA92640

Bill Checkal     9595 Curry Dr             Stanton      CA92610

Jed Style        3535 Randall St           Santa Ana    CA92705

As you can see, the file contains nothing but data. Each field has a fixed length (the Name field, for example, is always exactly 15 characters long), and no structure separates one field from another. The person who created the database assigned field positions and lengths. Any program using this file must "know" how each field was assigned, because that information is not contained in the database itself.

Such low overhead means that operating on flat files can be very fast. On the minus side, however, application programs must include logic that manipulates the file’s data at a very detailed level. The application must know exactly where and how the file stores its data. Thus, for small systems, flat files work fine. The larger a system is, however, the more cumbersome a flat-file system becomes.

Using a database instead of a flat-file system eliminates duplication of effort. Although database files themselves may have more overhead, the applications can be more portable across various hardware platforms and operating systems. A database also makes writing application programs easier because the programmer doesn’t need to know the physical details of where and how the data is stored.

The reason databases eliminate duplication of effort is because the DBMS handles the data-manipulation details. Applications written to operate on flat files must include those details in the application code. If multiple applications all access the same flat-file data, these applications must all (redundantly) include that data-manipulation code. If you’re using a DBMS, however, you don’t need to include such code in the applications at all.

Clearly, if a flat-file-based application includes data-manipulation code that runs only on a particular operating system (OS), migrating the application to a different OS is a headache waiting to happen. You have to change all the OS-specific code — and that’s just for openers. Migrating a similar DBMS-based application to another OS is much simpler — fewer complicated steps, fewer aspirin consumed.

Database Models

The first databases, back at the dawn of time (1950s), were structured according to a hierarchical model. They suffered from redundancy problems, and their structural inflexibility made database modification difficult. They were soon followed by databases that adhered to the network model, which strove to eliminate the main disadvantages of the hierarchical model. Network databases have minimal redundancy but pay for that advantage with structural complexity.

Some years later, Dr. E. F. Codd at IBM developed the relational model, which featured minimal redundancy and an easily understood structure. The SQL language was developed to operate on relational databases. Relational databases eventually consigned the hierarchical and network databases to the dustbin of history.

A new phenomenon is the emergence of the so-called NoSQL databases, which lack the structure of the relational databases and do not use the SQL language. I don’t cover NoSQL databases in this book.

Relational model

Dr. Codd first formulated the relational database model in 1970, and this model started appearing in products about a decade later. Ironically, IBM did not deliver the first relational DBMS. That distinction went to a small start-up company, which named its product Oracle.

Relational databases have almost completely replaced earlier database types. That’s largely because you can change the structure of a relational database without having to change or modify applications that were based on the old structures. Suppose, for example, that you add one or more new columns to a database table. You don’t need to change any previously written applications that process that table — unless, of course, you alter one or more of the columns that those applications have to use.

Of course, if you remove a column that an existing application has to use, you experience problems no matter what database model you follow. One of the quickest ways to make a database application crash is to ask it to retrieve a kind of data that your database doesn’t contain.

Components of a relational database

Relational databases gain their flexibility because their data resides in tables that are largely independent of each other. You can add, delete, or change data in a table without affecting the data in the other tables, provided that the affected table is not a parent of any of the other tables. (Parent-child table relationships are explained in Chapter 5, and no, they don’t involve discussing allowances over dinner.) In this section, I show what these tables consist of and how they relate to the other parts of a relational database.

Dealing with your relations

At holiday time, many of my relatives come to my house and sit down at my table. Databases have relations, too, but each of their relations has its own table. A relational database is made up of one or more relations.

A relation is a two-dimensional array of rows and columns, containing single-valued entries and no duplicate rows. Each cell in the array can have only one value, and no two rows may be identical. If that’s a little hard to picture, here’s an example that will put you in the right ballpark. . . .

Most people are familiar with two-dimensional arrays of rows and columns, in the form of electronic spreadsheets such as Microsoft Excel. A major-league baseball player’s offensive statistics, as listed on the back of a baseball card, are an example of such an array. On the baseball card are columns for year, team, games played, at-bats, hits, runs scored, runs batted in, doubles, triples, home runs, bases on balls, steals, and batting average. A row covers each year that the player has played in the Major Leagues. You can also store this data in a relation (a table), which has the same basic structure. Figure 1-2 shows a relational database table holding the offensive statistics for a single major-league player. In practice, such a table would hold the statistics for an entire team — or perhaps the whole league.

Columns in the array are self-consistent: A column has the same meaning in every row. If a column contains a player’s last name in one row, the column must contain a player’s last name in all rows. The order in which the rows and columns appear in the array has no significance. As far as the DBMS is concerned, it doesn’t matter which column is first, which is next, and which is last. The same is true of rows. The DBMS processes the table the same way regardless of the organization.

Figure 1-2:A table showing a baseball player’s offensive statistics.

Every column in a database table embodies a single attribute of the table, just like that baseball card. The column’s meaning is the same for every row of the table. A table may, for example, contain the names, addresses, and telephone numbers of all an organization’s customers. Each row in the table (also called a record, or a tuple) holds the data for a single customer. Each column holds a single attribute — such as customer number, customer name, customer street, customer city, customer state, customer postal code, or customer telephone number. Figure 1-3 shows some of the rows and columns of such a table.

The relations in this database model correspond to tables in any database based on the model. Try to say that ten times fast.

Figure 1-3: Each database row contains a record; each database column holds a single attribute.

Enjoy the view

One of my favorite views is of the Yosemite Valley from the mouth of the Wawona Tunnel, late on a spring afternoon. Golden light bathes the sheer face of El Capitan, Half Dome glistens in the distance, and Bridal Veil Falls forms a silver cascade of sparkling water, while wispy clouds weave a tapestry across the sky. Databases have views as well — even if they’re not quite that picturesque. The beauty of database views is their sheer usefulness when you’re working with your data.

Tables can contain many columns and rows. Sometimes all that data interests you, and sometimes it doesn’t. Only some columns of a table may interest you, or perhaps you want to see only rows that satisfy a certain condition. Some columns of one table and some other columns of a related table may interest you. To eliminate data that isn’t relevant to your current needs, you can create a view — a subset of a database that an application can process. It may contain parts of one or more tables.

Views are sometimes called virtual tables. To the application or the user, views behave the same as tables. Views, however, have no independent existence. Views allow you to look at data, but views are not part of the data.

Say, for example, that you're working with a database that has a CUSTOMER table and an INVOICE table. The CUSTOMER table has the columns CustomerID, FirstName, LastName, Street, City, State, Zipcode, and Phone. The INVOICE table has the columns InvoiceNumber, CustomerID, Date, TotalSale, TotalRemitted, and FormOfPayment.

A national sales manager wants to look at a screen that contains only the customer's first name, last name, and telephone number. Creating from the CUSTOMER table a view that contains only the FirstName, LastName, and Phone columns enables the manager to view what he or she needs without having to see all the unwanted data in the other columns. Figure 1-4 shows the derivation of the national sales manager's view.

A branch manager may want to look at the names and phone numbers of all customers whose zip codes fall between 90000 and 93999 (southern and central California). A view that places a restriction on the rows it retrieves, as well as the columns it displays, does the job. Figure 1-5 shows the sources for the columns in the branch manager’s view.

Figure 1-4: The sales manager’s view derives from the CUSTOMER table.

Figure 1-5:The branch manager’s view includes only certain rows from the CUSTOMER table.

The accounts-payable manager may want to look at customer names from the CUSTOMER table and Date, TotalSale, TotalRemitted, and FormOfPayment from the INVOICE table, where TotalRemitted is less than TotalSale. The latter would be the case if full payment hasn't yet been made. This need requires a view that draws from both tables. Figure 1-6 shows data flowing into the accounts-payable manager's view from both the CUSTOMER and INVOICE tables.

Views are useful because they enable you to extract and format database data without physically altering the stored data. They also protect the data that you don’t want to show, because they don’t contain it. Chapter 6 illustrates how to create a view by using SQL.

Figure 1-6: The accounts-payable manager’s view draws from two tables.

Schemas, domains, and constraints

A database is more than a collection of tables. Additional structures, on several levels, help to maintain the data’s integrity. A database’s schema provides an overall organization to the tables. The domain of a table column tells you what values you may store in the column. You can apply constraints to a database table to prevent anyone (including yourself) from storing invalid data in the table.

Schemas

The structure of an entire database is its schema, or conceptual view. This structure is sometimes also called the complete logical view of the database. The schema is metadata — as such, it’s part of the database. The metadata itself, which describes the database’s structure, is stored in tables that are just like the tables that store the regular data. Even metadata is data; that’s the beauty of it.

Domains

An attribute of a relation (that is, a column of a table) can assume some finite number of values. The set of all such values is the domain of the attribute.

Say, for example, that you're an automobile dealer who handles the newly introduced Curarri GT 4000 sports coupe. You keep track of the cars you have in stock in a database table that you name INVENTORY. You name one of the table columns Color, which holds the exterior color of each car. The GT 4000 comes in only four colors: blazing crimson, midnight black, snowflake white, and metallic gray. Those four colors are the domain of the Color attribute.

Constraints

Constraints are an important, although often overlooked, component of a database. Constraints are rules that determine what values the table attributes can assume.

By applying tight constraints to a column, you can prevent people from entering invalid data into that column. Of course, every value that is legitimately in the domain of the column must satisfy all the column’s constraints. As I mention in the preceding section, a column’s domain is the set of all values that the column can contain. A constraint is a restriction on what a column may contain. The characteristics of a table column, plus the constraints that apply to that column, determine the column’s domain.

In the auto dealership example, you can constrain the database to accept only those four values (mentioned in the preceding section) in the Color column. If a data entry operator then tries to enter in the Color column a value of, for example, forest green, the system refuses to accept the entry. Data entry can't proceed until the operator enters a valid value into the Color field.

You may wonder what happens when Curarri AutoWerks decides to offer a forest-green version of the GT 4000 as a mid-year option. The answer is (drum roll, please) job security for database-maintenance programmers. This kind of thing happens all the time and requires updates to the database structure. Only people who know how to modify the database structure (such as you) will be able to prevent a major snafu.

The object model challenged the relational model

The relational model has been fantastically successful in a wide variety of application areas. However, it does not do everything that anyone would ever want. The limitations have been made more visible by the rise in popularity of object-oriented programming languages such as C++, Java, and C#. Such languages are capable of handling more complex problems than traditional languages due to their advanced features, such as user-extensible type systems, encapsulation, inheritance, dynamic binding of methods, complex and composite objects, and object identity.

I am not going to explain all that jargon in this book (although I do touch on some of these terms later). Suffice it to say that the classic relational model doesn’t mesh well with many of these features. As a result, database management systems based on the object model have been developed. However, the idea never really took off. Although object-oriented programming languages have become very popular, object-oriented databases have not.

Final score: Relational databases 1, object-oriented databases 0.

The object-relational model

Database designers, like everyone else, are constantly searching for the best of all possible worlds. They mused, “Wouldn’t it be great if we could have the advantages of an object-oriented database system and still retain compatibility with the relational system that we know and love?” This kind of thinking led to the hybrid object-relational model. Object-relational DBMSs extend the relational model to include support for object-oriented data modeling. Object-oriented features have been added to the international SQL standard, allowing relational DBMS vendors to transform their products into object-relational DBMSs, while retaining compatibility with the standard. Thus, whereas the SQL-92 standard describes a purely relational database model, SQL:1999 describes an object-relational database model. SQL:2003 has more object-oriented features, and subsequent versions of the SQL standard have gone even further in that direction.

In this book, I describe ISO/IEC international standard SQL. (If you’re curious, IEC stands for International Electrotechnical Commission, but nobody really cares about that. How many people know what the letters in the acronym LASER stand for?) The system described by the ISO/IEC SQL standard is primarily a relational database model. I also include the object-oriented extensions to the standard that were introduced in SQL:1999 and the additional extensions included in later versions. The object-oriented features of the new standard allow developers to apply SQL databases to problems that are too complex to address with the older, purely relational, paradigm. Vendors of DBMS systems are incorporating the object-oriented features in the ISO standard into their products. Some of these features have been present for years, but others are yet to be included.

Database Design Considerations

A database is a representation of a physical or conceptual structure, such as an organization, an automobile assembly, or the performance statistics of all the major-league baseball clubs. The accuracy of the representation depends on the level of detail of the database design. The amount of effort that you put into database design should depend on the type of information you want to get out of the database. Too much detail is a waste of effort, time, and hard-drive space. Too little detail may render the database worthless.

Decide how much detail you need now and how much you may need in the future — and then provide exactly that level of detail in your design (no more and no less). But don’t be surprised if you have to adjust the design eventually to meet changing real-world needs.

Today’s database management systems, complete with attractive graphical user interfaces and intuitive design tools, can give the would-be database designer a false sense of security. These systems make designing a database seem comparable to building a spreadsheet or engaging in some other relatively straightforward task. No such luck. Database design is difficult. If you do it incorrectly, not only is your database likely to suffer from poor performance, but it also may well become gradually more corrupt as time goes on. Often the problem doesn’t turn up until after you devote a great deal of effort to data entry. By the time you know that you have a problem, it’s already serious. In many cases, the only solution is to completely redesign the database and reenter all the data. The up side is that by the time you finish your second version of the same database, you realize how much better you understand database design.

Chapter 2SQL Fundamentals

In This Chapter

Understanding SQL

Clearing up SQL misconceptions

Taking a look at the different SQL standards

Getting familiar with standard SQL commands and reserved words

Representing numbers, characters, dates, times, and other data types

Exploring null values and constraints

Putting SQL to work in a client/server system

Considering SQL on a network

SQL is a flexible language that you can use in a variety of ways. It’s the most widely used tool for communicating with a relational database. In this chapter, I explain what SQL is and isn’t — specifically, what distinguishes SQL from other types of computer languages. Then I introduce the commands and data types that standard SQL supports and I explain two key concepts: null values and constraints. Finally, I give an overview of how SQL fits into the client/server environment, as well as the Internet and organizational intranets.

What SQL Is and Isn’t

The first thing to understand about SQL is that SQL isn’t a procedurallanguage, as are BASIC, C, C++, C#, and Java. To solve a problem in one of those procedural languages, you write a procedure — a sequence of commands that performs one specific operation after another until the task is complete. The procedure may be a straightforward linear sequence or may loop back on itself, but in either case, the programmer specifies the order of execution.

SQL, on the other hand, is nonprocedural. To solve a problem using SQL, simply tell SQL what you want (as if you were talking to Aladdin’s genie) instead of telling the system how to get you what you want. The database management system (DBMS) decides the best way to get you what you request.

All right. I just told you that SQL is not a procedural language — and that's essentially true. However, millions of programmers out there (and you're probably one of them) are accustomed to solving problems in a procedural manner. So, in recent years, there has been a lot of pressure to add some procedural functionality to SQL — and SQL now incorporates features of a procedural language: BEGIN blocks, IF statements, functions, and (yes) procedures. With these facilities added, you can store programs at the server, where multiple clients can use your programs repeatedly.

To illustrate what I mean by “tell the system what you want,” suppose you have an EMPLOYEE table from which you want to retrieve the rows that correspond to all your senior people. You want to define a senior person as anyone older than age 40 or anyone earning more than $100,000 per year. You can make the desired retrieval by using the following query:

SELECT * FROM EMPLOYEE WHERE Age > 40 OR Salary > 100000 ;

This statement retrieves all rows from the EMPLOYEE table where either the value in the Age column is greater than 40 or the value in the Salary column is greater than 100,000. In SQL, you don't have to specify how the information is retrieved. The database engine examines the database and decides for itself how to fulfill your request. You need only specify what data you want to retrieve.

A query is a question you ask the database. If any of the data in the database satisfies the conditions of your query, SQL retrieves that data.

Current SQL implementations lack many of the basic programming constructs that are fundamental to most other languages. Real-world applications usually require at least some of these programming constructs, which is why SQL is actually a data sublanguage. Even with the extensions that were added in 1999, 2003, 2005, and 2008, you still have to use SQL in combination with a procedural language (such as C++) to create a complete application.

You can extract information from a database in one of two ways:

Make an

ad hoc query

from your keyboard by just typing an SQL statement and reading the results from the screen.

Queries from the keyboard are appropriate when you want a quick answer to a specific question. To meet an immediate need, you may require information that you never needed before from a database. You’re likely never to need that information again, either, but you need it now. Enter the appropriate SQL query statement from the keyboard, and in due time, the result appears on your screen.

Execute a program that collects information from the database and then reports on the information either onscreen or in a printed report.

Incorporating an SQL query directly into a program is a good way to run a complex query that you’re likely to run again in the future. That way, you can formulate a query just once for use as often as you want.

Chapter 16

explains how to incorporate SQL code into programs written in another programming language.

A (Very) Little History

SQL originated in one of IBM’s research laboratories, as did relational database theory. In the early 1970s, as IBM researchers developed early relational DBMS (or RDBMS) systems, they created a data sublanguage to operate on these systems. They named the pre-release version of this sublanguage SEQUEL (Structured English QUEry Language). However, when it came time to formally release their query language as a product, they found that another company had already trademarked the product name “Sequel.” Therefore, the marketing geniuses at IBM decided to give the released product a name that was different from SEQUEL but still recognizable as a member of the same family. So they named it SQL (pronounced ess-que-ell).

The syntax of SQL is a form of structured English, which is where its original name came from. However, SQL is not a structured language in the sense that computer scientists understand that term. Thus, despite the assumptions of many people, SQL is not an acronym standing for “structured query language.” It is a sequence of three letters that don’t stand for anything, just like the name of the C language does not stand for anything.

IBM’s work with relational databases and SQL was well known in the industry even before IBM introduced its SQL/DS relational database (RDBMS) product in 1981. By that time, Relational Software, Inc. (now Oracle Corporation) had already released its first RDBMS. These early products immediately set the standard for a new class of database management systems. They incorporated SQL, which became the de facto standard for data sublanguages. Vendors of other relational database management systems came out with their own versions of SQL. Typically these other implementations contained all the core functionality of the IBM products, extended in ways that took advantage of the particular strengths of their own RDBMS product. As a result, although nearly all vendors used some form of SQL, compatibility between platforms was poor.

An implementation is a particular RDBMS running on a specific hardware platform.

Soon a movement began to create a universally recognized SQL standard to which everyone could adhere. In 1986, ANSI (the American National Standards Institute) released a formal standard it named SQL-86. ANSI updated that standard in 1989 to SQL-89 and again in 1992 to SQL-92. As DBMS vendors proceed through new releases of their products, they try to bring their implementations ever closer to this standard. This effort has brought the goal of true SQL portability much closer to reality.

The most recent full version of the SQL standard is SQL:2011 (ISO/IEC 9075-X:2011). In this book, I describe SQL as SQL:2011 defines the language. Every specific SQL implementation differs from the standard to a certain extent. Because the complete SQL standard is comprehensive, currently available implementations are unlikely to support it fully. However, DBMS vendors are working to support a core subset of the standard SQL language. The full ISO/IEC standard is available for purchase at http://webstore.ansi.org, but you probably don't want to buy it unless you intend to create your own ISO/IEC SQL standard database management system. The standard is highly technical and virtually incomprehensible to anyone other than a computer language scholar.

SQL Statements

The SQL command language consists of a limited number of statements that perform three functions of data handling: Some of them define data, some manipulate data, and others control data. I cover the data-definition statements and data-manipulation statements in Chapters 4 through 12; I detail the data-control statements in Chapters 13 and 14.

To comply with SQL:2011, an implementation must include a basic set of core features. It may also include extensions to the core set (which the SQL:2011 specification also describes). Table 2-1 lists the core plus the extended SQL:2011 statements. It’s quite a list. If you’re among those programmers who love to try out new capabilities, rejoice.

Table 2-1 SQL:2011 Statements

ADD

DEALLOCATE PREPARE

FREE LOCATOR

ALLOCATE CURSOR

DECLARE

GET DESCRIPTOR

ALLOCATE DESCRIPTOR

DECLARE LOCAL TEMPORARY TABLE

GET DIAGNOSTICS

ALTER DOMAIN

DELETE

GRANT PRIVILEGE

ALTER ROUTINE

DESCRIBE INPUT

GRANT ROLE

ALTER SEQUENCE GENERATOR

DESCRIBE OUTPUT

HOLD LOCATOR

ALTER TABLE

DISCONNECT

INSERT

ALTER TRANSFORM

DROP

MERGE

ALTER TYPE