26,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
The latest on SQL databases SQL All -In-One For Dummies, 3rd Edition, is a one-stop shop for everything you need to know about SQL and SQL-based relational databases. Everyone from database administrators to application programmers and the people who manage them will find clear, concise explanations of the SQL language and its many powerful applications. With the ballooning amount of data out there, more and more businesses, large and small, are moving from spreadsheets to SQL databases like Access, Microsoft SQL Server, Oracle databases, MySQL, and PostgreSQL. This compendium of information covers designing, developing, and maintaining these databases. * Cope with any issue that arises in SQL database creation and management * Get current on the newest SQL updates and capabilities * Reference information on querying SQL-based databases in the SQL language * Understand relational databases and their importance to today's organizations SQL All-In-One For Dummies is a timely update to the popular reference for readers who want detailed information about SQL databases and queries.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 1118
Veröffentlichungsjahr: 2019
Ähnliche
SQL All-In-One For Dummies®, 3rd Edition
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2019 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 https://hub.wiley.com/community/support/dummies.
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: 2019934589
ISBN 978-1-119-56961-9 (pbk); ISBN 978-1-119-56960-2 (ebk); ISBN 978-1-119-56959-6 (ebk)
SQL All-In-One For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “SQL All-In-One For Dummies Cheat Sheet” in the Search box.
Table of Contents
Cover
Introduction
About This Book
Foolish Assumptions
Conventions Used in This Book
What You Don’t Have to Read
How This Book Is Organized
Icons Used in This Book
Where to Go from Here
Book 1: SQL Concepts
Chapter 1: Understanding Relational Databases
Understanding Why Today’s Databases Are Better than Early Databases
Databases, Queries, and Database Applications
Examining Competing Database Models
Why the Relational Model Won
Chapter 2: Modeling a System
Capturing the Users’ Data Model
Translating the Users’ Data Model to a Formal Entity-Relationship Model
Chapter 3: Getting to Know SQL
Where SQL Came From
Knowing What SQL Does
The ISO/IEC SQL Standard
Knowing What SQL Does Not Do
Choosing and Using an Available DBMS Implementation
Chapter 4: SQL and the Relational Model
Sets, Relations, Multisets, and Tables
Functional Dependencies
Keys
Views
Users
Privileges
Schemas
Catalogs
Connections, Sessions, and Transactions
Routines
Paths
Chapter 5: Knowing the Major Components of SQL
Creating a Database with the Data Definition Language
Operating on Data with the Data Manipulation Language (DML)
Maintaining Security in the Data Control Language (DCL)
Chapter 6: Drilling Down to the SQL Nitty-Gritty
Executing SQL Statements
Using Reserved Words Correctly
SQL’s Data Types
Handling Null Values
Applying Constraints
Book 2: Relational Database Development
Chapter 1: System Development Overview
The Components of a Database System
The System Development Life Cycle
Chapter 2: Building a Database Model
Finding and Listening to Interested Parties
Building Consensus
Building a Relational Model
Being Aware of the Danger of Anomalies
The Database Integrity versus Performance Tradeoff
Chapter 3: Balancing Performance and Correctness
Designing a Sample Database
Maintaining Integrity
Avoiding Data Corruption
Speeding Data Retrievals
Working with Indexes
Reading SQL Server Execution Plans
Chapter 4: Creating a Database with SQL
First Things First: Planning Your Database
Building Tables
Setting Constraints
Keys and Indexes
Ensuring Data Validity with Domains
Establishing Relationships between Tables
Altering Table Structure
Deleting Tables
Book 3: SQL Queries
Chapter 1: Values, Variables, Functions, and Expressions
Entering Data Values
Working with Functions
Using Expressions
Chapter 2: SELECT Statements and Modifying Clauses
Finding Needles in Haystacks with the SELECT Statement
Modifying Clauses
Tuning Queries
Chapter 3: Querying Multiple Tables with Subqueries
What Is a Subquery?
What Subqueries Do
Using Subqueries in INSERT, DELETE, and UPDATE Statements
Tuning Considerations for Statements Containing Nested Queries
Tuning Correlated Subqueries
Chapter 4: Querying Multiple Tables with Relational Operators
UNION
INTERSECT
EXCEPT
JOINS
ON versus WHERE
Join Conditions and Clustering Indexes
Chapter 5: Cursors
Declaring a Cursor
Opening a Cursor
Operating on a Single Row
Closing a Cursor
Book 4: Data Security
Chapter 1: Protecting Against Hardware Failure and External Threats
What Could Possibly Go Wrong?
Taking Advantage of RAID
Backing Up Your System
Coping with Internet Threats
Installing Layers of Protection
Chapter 2: Protecting Against User Errors and Conflicts
Reducing Data-Entry Errors
Coping with Errors in Database Design
Handling Programming Errors
Solving Concurrent-Operation Conflicts
Passing the ACID Test: Atomicity, Consistency, Isolation, and Durability
Operating with Transactions
Getting Familiar with Locking
Tuning Locks
Enforcing Serializability with Timestamps
Tuning the Recovery System
Chapter 3: Assigning Access Privileges
Working with the SQL Data Control Language
Identifying Authorized Users
Classifying Users
Granting Privileges
Revoking Privileges
Granting Roles
Revoking Roles
Chapter 4: Error Handling
Identifying Error Conditions
Getting to Know SQLSTATE
Handling Conditions
Dealing with Execution Exceptions: The WHENEVER Clause
Getting More Information: The Diagnostics Area
Examining an Example Constraint Violation
Adding Constraints to an Existing Table
Interpreting SQLSTATE Information
Handling Exceptions
Book 5: SQL and Programming
Chapter 1: Database Development Environments
Microsoft Access
Microsoft SQL Server
IBM Db2
Oracle 18c
SQL Anywhere
PostgreSQL
MySQL
Chapter 2: Interfacing SQL to a Procedural Language
Building an Application with SQL and a Procedural Language
Chapter 3: Using SQL in an Application Program
Comparing SQL with Procedural Languages
Difficulties in Combining SQL with a Procedural Language
Embedding SQL in an Application
Using SQL Modules with an Application
Chapter 4: Designing a Sample Application
Understanding the Client’s Problem
Approaching the Problem
Determining the Deliverables
Building an Entity-Relationship Model
Transforming the Model
Creating Tables
Changing Table Structure
Removing Tables
Designing the User Interface
Chapter 5: Building an Application
Designing from the Top Down
Coding from the Bottom Up
Testing, Testing, Testing
Chapter 6: Understanding SQL’s Procedural Capabilities
Embedding SQL Statements in Your Code
Introducing Compound Statements
Following the Flow of Control Statements
Using Stored Procedures
Working with Triggers
Using Stored Functions
Passing Out Privileges
Using Stored Modules
Chapter 7: Connecting SQL to a Remote Database
Native Drivers
ODBC and Its Major Components
What Happens When the Application Makes a Request
Book 6: SQL, XML, and JSON
Chapter 1: Using XML with SQL
Introducing XML
Knowing the Parts of an XML Document
Using XML Schema
Relating SQL to XML
Using the XML Data Type
Mapping SQL to XML
Operating on XML Data with SQL Functions
Working with XML Predicates
Chapter 2: Storing XML Data in SQL Tables
Inserting XML Data into an SQL Pseudotable
Creating a Table to Hold XML Data
Updating XML Documents
Discovering Oracle’s Tools for Updating XML Data in a Table
Introducing Microsoft’s Tools for Updating XML Data in a Table
Chapter 3: Retrieving Data from XML Documents
XQuery
FLWOR Expressions
XQuery versus SQL
Chapter 4: Using JSON with SQL
Using JSON with SQL
The SQL/JSON Data Model
SQL/JSON Functions
SQL/JSON Path Language
Book 7: Database Tuning Overview
Chapter 1: Tuning the Database
Analyzing the Workload
Considering the Physical Design
Choosing the Right Indexes
Tuning Indexes
Tuning Queries
Tuning Transactions
Separating User Interactions from Transactions
Minimizing Traffic between Application and Server
Precompiling Frequently Used Queries
Chapter 2: Tuning the Environment
Surviving Failures with Minimum Data Loss
Tuning the Recovery System
Tuning the Operating System
Maximizing the Hardware You Have
Adding Hardware
Working in Multiprocessor Environments
Chapter 3: Finding and Eliminating Bottlenecks
Pinpointing the Problem
Determining the Possible Causes of Trouble
Implementing General Principles: A First Step Toward Improving Performance
Tracking Down Bottlenecks
Analyzing Query Efficiency
Managing Resources Wisely
Book 8: Appendices
Appendix A: SQL: 2016 Reserved Words
Appendix B: Glossary
Index
About the Author
Advertisement Page
Connect with Dummies
End User License Agreement
List of Tables
Book 1 Chapter 4
TABLE 4-1 PROJECT Relation
TABLE 4-2 PROJECTS Relation
Book 1 Chapter 5
TABLE 5-1 PRODUCT Table
TABLE 5-2 PRODUCT Table
TABLE 5-3 PRODUCT Table
TABLE 5-4 PRODUCT Table
TABLE 5-5 PRODUCT Table
Book 1 Chapter 6
TABLE 6-1 Data Types
Book 2 Chapter 2
TABLE 2-1 Describing the Elements of a Database
Book 2 Chapter 3
TABLE 3-1 Primary Keys for Sample Relations
Book 2 Chapter 4
TABLE 4-1 Tables for Honest Abe
Book 3 Chapter 1
TABLE 1-1 Sample Literals of Various Data Types
TABLE 1-2 Photographic Paper Price List per 20 Sheets
TABLE 1-3 Sample Uses of the POSITION Statement
TABLE 1-4 Examples of String Value Expressions
Book 3 Chapter 2
TABLE 2-1 SQL’s Comparison Predicates
TABLE 2-2 SQL’s
LIKE
Predicate
Book 3 Chapter 3
TABLE 3-1 Ford Small-Block V-8s, 1960–1980
TABLE 3-2 Chevy Small-Block V-8s, 1960–1980
Book 3 Chapter 4
TABLE 4-1 LOCATION
TABLE 4-2 DEPT
TABLE 4-3 EMPLOYEE
Book 4 Chapter 1
TABLE 1-1 RAID Level Comparison
Book 4 Chapter 2
TABLE 2-1 Isolation Levels and Problems Solved
Book 4 Chapter 4
TABLE 4-1 SQLSTATE Class Values
TABLE 4-2 Conditions That May Be Specified in a Condition Handler
TABLE 4-3 Diagnostics Header Area
TABLE 4-4 Diagnostics Detail Area
Book 6 Chapter 3
TABLE 3-1 USERS
TABLE 3-2 ITEMS
TABLE 3-3 BIDS
TABLE 3-4 XQuery 1.0 Data Types and Corresponding SQL Data Types
List of Illustrations
Book 1 Chapter 1
FIGURE 1-1: A hierarchical model of the Saturn V moon rocket.
FIGURE 1-2: A hierarchical model of a sales database for a retail business.
FIGURE 1-3: A network model of transactions at an online store.
FIGURE 1-4: A relational model of transactions at an online store.
Book 1 Chapter 2
FIGURE 2-1: EMPLOYEE, an example of an entity class.
FIGURE 2-2: Duke Kahanamoku, an example of an instance of the EMPLOYEE entity cl...
FIGURE 2-3: An EMPLOYEE: TRANSACTION relationship.
FIGURE 2-4: A one-to-one relationship between PERSON and LICENSE.
FIGURE 2-5: A one-to-many relationship between PERSON and TICKET.
FIGURE 2-6: A many-to-many relationship between STUDENT and COURSE.
FIGURE 2-7: The COMPOSER: SONG: LYRICIST relationship.
FIGURE 2-8: ER diagram showing minimum cardinality, where a person must exist, b...
FIGURE 2-9: ER diagram showing minimum cardinality, where a license must exist, ...
FIGURE 2-10: The ER model for a retail transaction database.
FIGURE 2-11: A PERSON: LICENSE relationship, showing LICENSE as a weak entity.
FIGURE 2-12: The SEAT is ID-dependent on FLIGHT via the FLIGHT: SEAT relationshi...
FIGURE 2-13: The COMMUNITY supertype entity with STUDENT, FACULTY, and STAFF sub...
FIGURE 2-14: An ER diagram of a small, web-based retail business.
FIGURE 2-15: The ER diagram for Clear Creek Medical Clinic.
Book 1 Chapter 3
FIGURE 3-1: A Microsoft Access 2016 database window.
FIGURE 3-2: Menu of possible actions for the query selected.
FIGURE 3-3: Result of Team Membership of Paper Authors query.
FIGURE 3-4: The Views menu has been pulled down.
FIGURE 3-5: The SQL Editor window, showing SQL for the Team Membership of Paper ...
FIGURE 3-6: The query to select everything in the PAPERS table.
FIGURE 3-7: The result of the query to select everything in the PAPERS table.
Book 1 Chapter 5
FIGURE 5-1: The relational database containment hierarchy.
FIGURE 5-2: The ER diagram of the database for an order entry system.
FIGURE 5-3: Creating a multitable view using joins.
Book 2 Chapter 1
FIGURE 1-1: Information flow in a database system.
Book 2 Chapter 2
FIGURE 2-1: The BOOK relation.
FIGURE 2-2: The MECHANICS relation.
FIGURE 2-3: The modified MECHANICS relation.
FIGURE 2-4: The MECHANICS relation has been broken into two relations, MECH-SPEC...
FIGURE 2-5: The LIPIDLEVEL relation.
FIGURE 2-6: The ITEM-TYPE relation and the TYPE-CHOL relation.
Book 2 Chapter 3
FIGURE 3-1: The ER model for Honest Abe’s Fleet Auto Repair.
FIGURE 3-2: The CUSTOMER entity and the CUSTOMER relation.
FIGURE 3-3: The ER model of PART: INVOICE_LINE relationship.
FIGURE 3-4: A relational model representation of the one-to-one relationship in ...
FIGURE 3-5: An ER diagram of a one-to-many relationship.
FIGURE 3-6: A relational model representation of the one-to-many relationship in...
FIGURE 3-7: The ER diagram of a many-to-many relationship.
FIGURE 3-8: The relational model representation of the decomposition of the many...
FIGURE 3-9: The ER diagram for Honest Abe’s Fleet Auto Repair.
FIGURE 3-10: The relational model representation of the Honest Abe’s model in Fi...
FIGURE 3-11: Revised ER model for Honest Abe’s Fleet Auto Repair.
FIGURE 3-12: Tables and relationships in the AdventureWorks database.
FIGURE 3-13: SQL Server 2008 Management Studio execution of an SQL query.
FIGURE 3-14: The execution plan for the delivery time query.
FIGURE 3-15: The recommendations of the Database Engine Tuning Advisor.
Book 3 Chapter 2
FIGURE 2-1: The result set for retrieval of sales for May 2011.
FIGURE 2-2: Average sales for each salesperson.
FIGURE 2-3: Total sales for each salesperson.
FIGURE 2-4: Total sales for all salespeople except Saraiva.
FIGURE 2-5: Customers who have placed at least one order.
FIGURE 2-6: The SELECT DISTINCT query execution plan.
FIGURE 2-7: SELECT DISTINCT query client statistics.
FIGURE 2-8: Retrieve all employees named Janice from the Person table.
FIGURE 2-9: SELECT query execution plan using a temporary table.
FIGURE 2-10: SELECT query execution client statistics using a temporary table.
FIGURE 2-11: SELECT query result with a compound condition.
FIGURE 2-12: SELECT query execution plan with a compound condition.
FIGURE 2-13: SELECT query client statistics, with a compound condition.
FIGURE 2-14: Execution plan, minimizing occurrence of ORDER BY clauses.
FIGURE 2-15: Client statistics, minimizing occurrence of ORDER BY clauses.
FIGURE 2-16: Execution plan, queries with separate ORDER BY clauses.
FIGURE 2-17: Client statistics, queries with separate ORDER BY clauses.
FIGURE 2-18: Retrieval with a HAVING clause.
FIGURE 2-19: Retrieval with a HAVING clause execution plan.
FIGURE 2-20: Retrieval with a HAVING clause client statistics.
FIGURE 2-21: Retrieval without a HAVING clause.
FIGURE 2-22: Retrieval without a HAVING clause execution plan.
FIGURE 2-23: Retrieval without a HAVING clause client statistics.
FIGURE 2-24: Query with an OR logical connective.
Book 3 Chapter 3
FIGURE 3-1: Chevy muscle cars with horsepower to displacement ratios higher than...
FIGURE 3-2: Orders that contain products that are out of stock.
FIGURE 3-3: An execution plan for a query showing orders for out-of-stock produc...
FIGURE 3-4: Client statistics for a query showing orders for out-of-stock produc...
FIGURE 3-5: A nested query showing orders that contain products that are almost ...
FIGURE 3-6: An execution plan for a nested query showing orders for almost out-o...
FIGURE 3-7: Client statistics for a nested query showing orders for almost out-o...
FIGURE 3-8: A relational query showing orders that contain products that are alm...
FIGURE 3-9: The execution plan for a relational query showing orders for almost ...
FIGURE 3-10: Client statistics for a relational query showing orders for almost ...
FIGURE 3-11: A correlated subquery showing orders that contain products at least...
FIGURE 3-12: An execution plan for a correlated subquery showing orders at least...
FIGURE 3-13: Client statistics for a correlated subquery showing orders at least...
FIGURE 3-14: Relational query showing orders that contain products at least twic...
FIGURE 3-15: An execution plan for a relational query showing orders for almost ...
FIGURE 3-16: Client statistics for a relational query showing orders for almost ...
Book 4 Chapter 1
FIGURE 1-1: RAID striping.
FIGURE 1-2: HP WebInspect scan result.
FIGURE 1-3: IBM Security AppScan scan result.
Book 5 Chapter 2
FIGURE 2-1: Visual Basic for Applications References dialog box enables you to s...
Book 5 Chapter 4
FIGURE 4-1: An ER diagram of OLS research.
FIGURE 4-2: An ER model representation of the OLS system in Figure 4-1.
FIGURE 4-3: The MEMBERS entity (top) and the MEMBERS relation.
Book 5 Chapter 5
FIGURE 5-1: The OLS application main screen with command buttons.
FIGURE 5-2: The OLS application menu hierarchy.
FIGURE 5-3: The OLS forms menu.
FIGURE 5-4: The OLS Members form.
Book 5 Chapter 7
FIGURE 7-1: A database system using an Oracle 18c native driver.
FIGURE 7-2: A database system using ODBC API.
FIGURE 7-3: The architecture of a two-tier driver system.
FIGURE 7-4: Handles establish the connection between an application and a data s...
Book 7 Chapter 1
FIGURE 1-1: The cost of retrievals with and without an index.
Book 7 Chapter 3
FIGURE 3-1: Microsoft SQL Server Management Studio.
FIGURE 3-2: The Microsoft SQL Server Management Studio SQL editor pane.
FIGURE 3-3: A sample query.
FIGURE 3-4: The query result.
FIGURE 3-5: The Database Engine Tuning Advisor window.
FIGURE 3-6: The Tuning Advisor window, ready to tune a query.
FIGURE 3-7: The Tuning Options pane.
FIGURE 3-8: Advanced tuning options.
FIGURE 3-9: The Progress tab after a successful run.
FIGURE 3-10: The Recommendations tab after a successful run.
FIGURE 3-11: The Reports tab after a successful run.
FIGURE 3-12: The Trace Properties dialog box.
FIGURE 3-13: The Events Selection tab of the Trace Properties dialog box.
FIGURE 3-14: Trace for a simple query.
FIGURE 3-15: An Optimize Drives display of a computer’s disk drives.
Guide
Cover
Table of Contents
Begin Reading
Pages
iii
iv
1
2
3
4
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
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
55
56
57
58
59
60
61
62
63
64
65
67
68
69
70
71
72
73
74
75
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
104
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
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
210
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
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
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
422
423
424
425
426
427
428
429
430
431
432
433
434
435
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
595
596
597
598
599
600
601
602
603
604
605
606
607
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
739
740
741
742
743
744
745
746
747
Introduction
SQL is the internationally recognized standard language for dealing with data in relational databases. Developed by IBM, SQL became an international standard in 1986. The standard was updated in 1989, 1992, 1999, 2003, 2008, 2011, and 2016. It continues to evolve and gain capability. Database vendors continually update their products to incorporate the new features of the ISO/IEC standard. (For the curious out there, ISO is the International Organization for Standardization, and IEC is the International Electrotechnical Commission.)
SQL isn’t a general-purpose language, such as C++ or Java. Instead, it’s strictly designed to deal with data in relational databases. With SQL, you can carry out all the following tasks:
Create a database, including all tables and relationships.
Fill database tables with data.
Change the data in database tables.
Delete data from database tables.
Retrieve specific information from database tables.
Grant and revoke access to database tables.
Protect database tables from corruption due to access conflicts or user mistakes.
About This Book
This book isn’t just about SQL; it’s also about how SQL fits into the process of creating and maintaining databases and database applications. In this book, I cover how SQL fits into the larger world of application development and how it handles data coming in from other computers, which may be on the other side of the world or even in interplanetary space.
Here are some of the things you can do with this book:
Create a model of a proposed system and then translate that model into a database.
Find out about the capabilities and limitations of SQL.
Discover how to develop reliable and maintainable database systems.
Create databases.
Speed database queries.
Protect databases from hardware failures, software bugs, and Internet attacks.
Control access to sensitive information.
Write effective database applications.
Deal with data from a variety of nontraditional data sources by using XML.
Foolish Assumptions
I know that this is a For Dummies book, but I don’t really expect that you’re a dummy. In fact, I assume that you’re a very smart person. After all, you decided to read this book, which is a sign of high intelligence indeed. Therefore, I assume that you may want to do a few things, such as re-create some of the examples in the book. You may even want to enter some SQL code and execute it. To do that, you need at the very least an SQL editor and more likely also a database management system (DBMS) of some sort. Many choices are available, both proprietary and open source. I mention several of these products at various places throughout the book but don’t recommend any one in particular. Any product that complies with the ISO/IEC international SQL standard should be fine.
Take claims of ISO/IEC compliance with a grain of salt, however. No DBMS available today is 100 percent compliant with the ISO/IEC SQL standard. For that reason, some of the code examples I give in this book may not work in the particular SQL implementation that you’re using. The code samples I use in this book are consistent with the international standard rather than with the syntax of any particular implementation unless I specifically state that the code is for a particular implementation.
Conventions Used in This Book
By conventions, I simply mean a set of rules I’ve employed in this book to present information to you consistently. When you see a term italicized, look for its definition, which I’ve included so that you know what things mean in the context of SQL. Website addresses and email addresses appear in monofont so that they stand out from regular text. Many aspects of the SQL language — such as statements, data types, constraints, and keywords — also appear in monofont. Code appears in its own font, set off from the rest of the text, like this:
CREATE SCHEMA RETAIL1 ;
What You Don’t Have to Read
I’ve structured this book modularly — that is, it’s designed so that you can easily find just the information you need — so you don’t have to read whatever doesn’t pertain to your task at hand. Here and there throughout the book, I include sidebars containing interesting information that isn’t necessarily integral to the discussion at hand; feel free to skip them. You also don’t have to read text marked with the Technical Stuff icons, which parses out über-techy tidbits (which may or may not be your cup of tea).
How This Book Is Organized
SQL All-in-One Desk Reference For Dummies, 3rd Edition is split into eight minibooks. You don’t have to read the book sequentially; you don’t have to look at every minibook; you don’t have to review each chapter; and you don’t even have to read all the sections of any particular chapter. (You can if you want to, however; it’s a good read.) The table of contents and index can help you quickly find whatever information you need. In this section, I briefly describe what each minibook contains.
Book 1: SQL Concepts
SQL is a language specifically and solely designed to create, operate on, and manage relational databases. I start with a description of databases and how relational databases differ from other kinds. Then I move on to modeling business and other kinds of tasks in relational terms. Next, I cover how SQL relates to relational databases, provide a detailed description of the components of SQL, and explain how to use those components. I also describe the types of data that SQL deals with, as well as constraints that restrict the data that can be entered into a database.
Book 2: Relational Database Development
Many database development projects, like other software development projects, start in the middle rather than at the beginning, as they should. This fact is responsible for the notorious tendency of software development projects to run behind schedule and over budget. Many self-taught database developers don’t even realize that they’re starting in the middle; they think they’re doing everything right. This minibook introduces the System Development Life Cycle (SDLC), which shows what the true beginning of a software development project is, as well as the middle and the end.
The key to developing an effective database that does what you want is creating an accurate model of the system you’re abstracting in your database. I describe modeling in this minibook, as well as the delicate trade-off between performance and reliability. The actual SQL code used to create a database rounds out the discussion.
Book 3: SQL Queries
Queries sit at the core of any database system. The whole reason for storing data in databases is to retrieve the information you want from those databases later. SQL is, above all, a query language. Its specialty is enabling you to extract from a database exactly the information you want without cluttering what you retrieve with a lot of stuff you don’t want.
This minibook starts with a description of values, variables, expressions, and functions. Then I provide detailed coverage of the powerful tools SQL gives you to zero in on the information you want, even if that information is scattered across multiple tables.
Book 4: Data Security
Your data is one of your most valuable assets. Acknowledging that fact, I discuss ways to protect it from a diverse array of threats. One threat is outright loss due to hardware failures. Another threat is attack by hackers wielding malicious viruses and worms. In this minibook, I discuss how you can protect yourself from such threats, whether they’re random or purposeful.
I also deal extensively with other sources of error, such as the entry of bad data or the harmful interactions of simultaneous users. Finally, I cover how to control access to sensitive data and how to handle errors gracefully when they occur — as they inevitably will.
Book 5: SQL and Programming
SQL’s primary use is as a component of an application program that operates on a database. Because SQL is a data language, not a general-purpose programming language, SQL statements must be integrated somehow with the commands of a language such as Visual Basic, Java, C++, or C#. This book outlines the process with the help of a fictitious sample application, taking it from the beginning — when the need for a new application is perceived — to the release of the finished application. Throughout the example, I emphasize best practices.
Book 6: SQL and XML
XML is the language used to transport data between dissimilar data stores. The 2005 extensions to the SQL:2003 standard greatly expanded SQL’s capacity to handle XML data. This minibook covers the basics of XML and how it relates to SQL. I describe SQL functions that are specifically designed to operate on data in XML format, as well as the operations of storing and retrieving data in XML format.
Book 7: Database Tuning Overview
Depending on how they’re structured, databases can respond efficiently to requests for information or perform very poorly. Often, the performance of a database degrades over time as its structure and the data in it change or as typical types of retrievals change. This minibook describes the parts of a database that are amenable to tuning and optimization. It also gives a procedure for tracking down bottlenecks that are choking the performance of the entire system.
Book 8: Appendices
Appendix A lists words that have a special meaning in SQL:2016. You can’t use these words as the names of tables, columns, views, or anything other than what they were meant to be used for. If you receive a strange error message for an SQL statement that you entered, check whether you inadvertently used a reserved word inappropriately.
Appendix B is a glossary that provides brief definitions of many of the terms used in this book, as well as many others that relate to SQL and databases, whether they’re used in this book or not.
Icons Used in This Book
For Dummies books are known for those helpful icons that point you in the direction of really great information. This section briefly describes the icons used in this book.
The Tip icon points out helpful information that’s likely to make your job easier.
This icon marks a generally interesting and useful fact — something that you may want to remember for later use.
The Warning icon highlights lurking danger. When you see this icon, pay attention, and proceed with caution.
This icon denotes techie stuff nearby. If you’re not feeling very techie, you can skip this info.
Where to Go from Here
Book 1 is the place to go if you’re just getting started with databases. It explains why databases are useful and describes the different types. It focuses on the relational model and describes SQL’s structure and features.
Book 2 goes into detail on how to build a database that’s reliable as well as responsive. Unreliable databases are much too easy to create, and this minibook tells you how to avoid the pitfalls that lie in wait for the unwary.
Go directly to Book 3 if your database already exists and you just want to know how to use SQL to pull from it the information you want.
Book 4 is primarily aimed at the database administrator (DBA) rather than the database application developer or user. It discusses how to build a robust database system that resists data corruption and data loss.
Book 5 is for the application developer. In addition to discussing how to write a database application, it gives an example that describes in a step-by-step manner how to build a reliable application.
If you’re already an old hand at SQL and just want to know how to handle data in XML format in your SQL database, Book 6 is for you.
Book 7 gives you a wide variety of techniques for improving the performance of your database. This minibook is the place to go if your database is operating — but not as well as you think it should. Most of these techniques are things that the DBA can do, rather than the application developer or the database user. If your database isn’t performing the way you think it should, take it up with your DBA. She can do a few things that could help immensely.
Book 8 is a handy reference that helps you quickly find the meaning of a word you’ve encountered or see why an SQL statement that you entered didn’t work as expected. (Maybe you used a reserved word without realizing it.)
Book 1
SQL Concepts
Contents at a Glance
Chapter 1: Understanding Relational Databases
Understanding Why Today’s Databases Are Better than Early Databases
Databases, Queries, and Database Applications
Examining Competing Database Models
Why the Relational Model Won
Chapter 2: Modeling a System
Capturing the Users’ Data Model
Translating the Users’ Data Model to a Formal Entity-Relationship Model
Chapter 3: Getting to Know SQL
Where SQL Came From
Knowing What SQL Does
The ISO/IEC SQL Standard
Knowing What SQL Does Not Do
Choosing and Using an Available DBMS Implementation
Chapter 4: SQL and the Relational Model
Sets, Relations, Multisets, and Tables
Functional Dependencies
Keys
Views
Users
Privileges
Schemas
Catalogs
Connections, Sessions, and Transactions
Routines
Paths
Chapter 5: Knowing the Major Components of SQL
Creating a Database with the Data Definition Language
Operating on Data with the Data Manipulation Language (DML)
Maintaining Security in the Data Control Language (DCL)
Chapter 6: Drilling Down to the SQL Nitty-Gritty
Executing SQL Statements
Using Reserved Words Correctly
SQL’s Data Types
Handling Null Values
Applying Constraints
Chapter 1
Understanding Relational Databases
IN THIS CHAPTER
Working with data files and databases
Seeing how databases, queries, and database applications fit together
Looking at different database models
Charting the rise of relational databases
SQL (pronounced ess cue el, but you’ll hear some people say see quel) is the international standard language used in conjunction with relational databases — and it just so happens that relational databases are the dominant form of data storage throughout the world. In order to understand why relational databases are the primary repositories for the data of both small and large organizations, you must first understand the various ways in which computer data can be stored and how those storage methods relate to the relational database model. To help you gain that understanding, I spend a good portion of this chapter going back to the earliest days of electronic computers and recapping the history of data storage.
I realize that grand historical overviews aren’t everybody’s cup of tea, but I’d argue that it’s important to see that the different data storage strategies that have been used over the years each have their own strengths and weaknesses. Ultimately, the strengths of the relational model overshadowed its weaknesses and it became the most frequently used method of data storage. Shortly after that, SQL became the most frequently used method of dealing with data stored in a relational database.
Understanding Why Today’s Databases Are Better than Early Databases
In the early days of computers, the concept of a database was more theoretical than practical. Vannevar Bush, the twentieth-century visionary, conceived of the idea of a database in 1945, even before the first electronic computer was built. However, practical implementations of databases — such as IBM’s IMS (Information Management System), which kept track of all the parts on the Apollo moon mission and its commercial followers — did not appear for a number of years after that. For far too long, computer data was still being kept in files rather than migrated to databases.
Irreducible complexity
Any software system that performs a useful function is complex. The more valuable the function, the more complex its implementation. Regardless of how the data is stored, the complexity remains. The only question is where that complexity resides.
Any nontrivial computer application has two major components: the program and the data. Although an application’s level of complexity depends on the task to be performed, developers have some control over the location of that complexity. The complexity may reside primarily in the program part of the overall system, or it may reside in the data part. In the sections that follow, I tell you how the location of complexity in databases shifted over the years as technological improvements made that possible.
Managing data with complicated programs
In the earliest applications of computers to solve problems, all of the complexity resided in the program. The data consisted of one data record of fixed length after another, stored sequentially in a file. This is called a flat file data structure. The data file contains nothing but data. The program file must include information about where particular records are within the data file (one form of metadata, whose sole purpose is to organize the primary data you really care about). Thus, for this type of organization, the complexity of managing the data is entirely in the program.
Here’s an example of data organized in a flat file structure:
Harold Percival26262 S. Howards Mill Rd.Westminster CA92683
Jerry Appel 32323 S. River Lane Road Santa Ana CA92705
Adrian Hansen 232 Glenwood Court Anaheim CA92640
John Baker 2222 Lafayette Street Garden GroveCA92643
Michael Pens 77730 S. New Era Road Irvine CA92715
Bob Michimoto 25252 S. Kelmsley Drive Stanton CA92610
Linda Smith 444 S.E. Seventh StreetCosta Mesa CA92635
Robert Funnell 2424 Sheri Court Anaheim CA92640
Bill Checkal 9595 Curry Drive Stanton CA92610
Jed Style 3535 Randall Street Santa Ana CA92705
This example includes fields for name, address, city, state, and zip code. Each field has a specific length, and data entries must be truncated to fit into that length. If entries don’t use all the space allotted to them, storage space is wasted.
The flat file method of storing data has several consequences, some beneficial and some not. First, the beneficial consequences:
Storage requirements are minimized.
Because the data files contain nothing but data, they take up a minimum amount of space on hard disks or other storage media. The code that must be added to any one program that contains the metadata is small compared to the overhead involved with adding a database management system (DBMS) to the data side of the system. (A
database management system
is the program that controls access to — and operations on — a database.)
Operations on the data can be fast.
Because the program interacts directly with the data, with no DBMS in the middle, well-designed applications can run as fast as the hardware permits.
Wow! What could be better? A data organization that minimizes storage requirements and at the same time maximizes speed of operation seems like the best of all possible worlds. But wait a minute …
Flat file systems came into use in the 1940s. We have known about them for a long time, and yet today they are almost entirely replaced by database systems. What’s up with that? Perhaps it is the not-so-beneficial consequences:
Updating the data’s structure can be a huge task.
It is common for an organization’s data to be operated on by multiple application programs, with multiple purposes. If the metadata about the structure of data is in the program rather than attached to the data itself,
all
the programs that access that data must be modified whenever the data structure is changed. Not only does this cause a lot of redundant work (because the same changes must be made in all the programs), but it is an invitation to problems. All the programs must be modified in exactly the same way. If one program is inadvertently forgotten, the program will fail the next time you run it. Even if all the programs
are
modified, any that aren’t modified exactly as they should be will fail, or even worse, corrupt the data without giving any indication that something is wrong.
Flat file systems provide no protection of the data.
Anyone who can access a data file can read it, change it, or delete it. A flat file system doesn’t have a database management system, which restricts access to authorized users.
Speed can be compromised.
Accessing records in a large flat file can actually be slower than a similar access in a database because flat file systems do not support indexing. Indexing is a major topic that I discuss in Book 2,
Chapter 3
.
Portability becomes an issue.
If the specifics that handle how you retrieve a particular piece of data from a particular disk drive is coded into each program, what happens when your hardware becomes obsolete and you must migrate to a new system? All your applications will have to be changed to reflect the new way of accessing the data. This task is so onerous that many organizations have chosen to limp by on old, poorly performing systems instead of enduring the pain of transitioning to a system that would meet their needs much more effectively. Organizations with legacy systems consisting of millions of lines of code are pretty much trapped.
In the early days of electronic computers, storage was relatively expensive, so system designers were highly motivated to accomplish their tasks using as little storage space as possible. Also, in those early days, computers were much slower than they are today, so doing things the fastest possible way also had a high priority. Both of these considerations made flat file systems the architecture of choice, despite the problems inherent in updating the structure of a system’s data.
The situation today is radically different. The cost of storage has plummeted and continues to drop on an exponential curve. The speed at which computations are performed has increased exponentially also. As a result, minimizing storage requirements and maximizing the speed with which an operation can be performed are no longer the primary driving forces that they once were. Because systems have continually become bigger and more complex, the problem of maintaining them has likewise grown. For all these reasons, flat file systems have lost their attractiveness, and databases have replaced them in practically all application areas.
Managing data with simple programs
The major selling point of database systems is that the metadata resides on the data end of the system rather than in the program. The program doesn’t have to know anything about the details of how the data is stored. The program makes logical requests for data, and the DBMS translates those logical requests into commands that go out to the physical storage hardware to perform whatever operation has been requested. (In this context, a logical request asks for a specific piece of information, but does not specify its location on hard disk in terms of platter, track, sector, and byte.) Here are the advantages of this organization:
Because application programs need to know only what data they want to operate on, and not where that data is located, they are unaffected when the physical details of where data is stored changes.
Portability across platforms, even when they are highly dissimilar, is easy as long as the DBMS used by the first platform is also available on the second. Generally, you don’t need to change the programs at all to accommodate various platforms.
What about the disadvantages? They include the following:
Placing a database management system in between the application program and the data slows down operations on that data. This is not nearly the problem that it used to be. Modern advances, such as the use of high speed cache memories have eased this problem considerably.
Databases take up more space on disk storage than the same amount of data would take up in a flat file system. This is due to the fact that metadata is stored along with the data. The metadata contains information about how the data is stored so that the application programs don’t have to include it.
Which type of organization is better?
I bet you think you already know how I’m going to answer this question. You’re probably right, but the answer is not quite so simple. There is no one correct answer that applies to all situations. In the early days of electronic computing, flat file systems were the only viable option. To perform any reasonable computation in a timely and economical manner, you had to use whatever approach was the fastest and required the least amount of storage space. As more and more application software was developed for these systems, the organizations that owned them became locked in tighter and tighter to what they had. To change to a more modern database system requires rewriting all their applications from scratch and reorganizing all their data, a monumental task. As a result, we still have legacy flat file systems that continue to exist because switching to more modern technology isn’t feasible, both economically and in terms of the time it would take to make the transition.
Databases, Queries, and Database Applications
What are the chances that a person could actually find a needle in a haystack? Not very good. Finding the proverbial needle is so hard because the haystack is a random pile of hay with individual pieces of hay going in every direction, and the needle is located at some random place among all that hay.
A flat file system is not really very much like a haystack, but it does lack structure — and in order to find a particular record in such a file, you must use tools that lie outside of the file itself. This is like applying a powerful magnet to the haystack to find the needle.
Making data useful
For a collection of data to be useful, you must be able to easily and quickly retrieve the particular data you want, without having to wade through all the rest of the data. One way to make this happen is to store the data in a logical structure. Flat files don’t have much structure, but databases do. Historically, the hierarchical database model and the network database model were developed before the relational model. Each one organizes data in a different way, but all three produce a highly structured result. Because of that, starting in the 1970s, any new development projects were most likely done using one of the aforementioned three database models: hierarchical, network, or relational. (I explore each of these database models further in the “Examining Competing Database Models” section, later in this chapter.)
Retrieving the data you want — and only the data you want
Of all the operations that people perform on a collection of data, the retrieval of specific elements out of the collection is the most important. This is because retrievals are performed more often than any other operation. Data entry is done only once. Changes to existing data are made relatively infrequently, and data is deleted only once. Retrievals, on the other hand, are performed frequently, and the same data elements may be retrieved many times. Thus, if you could optimize only one operation performed on a collection of data, that one operation should be data retrieval. As a result, modern database management systems put a great deal of effort into making retrievals fast.
Retrievals are performed by queries. A modern database management system analyzes a query that is presented to it and decides how best to perform it. Generally, there are multiple ways of performing a query, some much faster than others. A good DBMS consistently chooses a near-optimal execution plan. Of course, it helps if the query is formulated in an optimal manner to begin with. (I discuss optimization strategies in depth in Book 7, which covers database tuning.)
THE FIRST DATABASE SYSTEM
The first true database system was developed by IBM in the 1960s in support of NASA’s Apollo moon landing program. The number of components in the Saturn V launch vehicle, the Apollo Command and Service Module, and the lunar lander far exceeded anything that had been built up to that time. Every component had to be tested more exhaustively than anything had ever been tested before because each component would have to withstand the rigors of an environment that was more hostile and more unforgiving than any environment that humans had ever attempted to work in. Flat file systems were out of the question. IBM’s solution, which IBM later transformed into a commercial database product named IMS (Information Management System), kept track of each individual component, as well as its complete history.
When the ill-fated Apollo 13’s main oxygen tank ruptured on the way to the Moon, engineers worked frantically to come up with a plan to save the lives of the three astronauts headed for the Moon. The engineers succeeded and transmitted a plan to the astronauts that worked.
After the crew had returned safely to Earth, querying IMS records about the oxygen tank that failed showed that somewhere between the oxygen tank’s manufacture and its installation in Apollo 13, it had been dropped on the floor. Engineers retested it for its ability to withstand the pressure it would have to contain during the mission, and then put it back in stock after it passed the test. But it turns out that in this case, the test did not detect the hidden damage to the tank, and NASA should not have used the oxygen tank on the Apollo 13 mission. The history stored in IMS showed that passing a pressure test is not enough to assure that a dropped tank is undamaged. No dropped tanks were ever used on subsequent Apollo missions.
Examining Competing Database Models
A database model is simply a way of organizing data elements within a database. In this section, I give you the details on the three database models that appeared first on the scene:
Hierarchical:
Organizes data into levels, where each level contains a single category of data, and parent/child relationships are established between levels
Network:
Organizes data in a way that avoids much of the redundancy inherent in the hierarchical model
Relational:
Organizes data into a structured collection of two-dimensional tables
After the introductions of the hierarchical, network, and relational models, computer scientists have continued to develop databases models that have been found useful in some categories of applications. I briefly mention some of these later in this chapter, along with their areas of applicability. However, the hierarchical, network, and relational models are the ones that have been primarily used for general business applications.
Looking at the historical background of the competing models
The first functioning database system was developed by IBM and went live at an Apollo contractor’s site on August 14, 1968. (Read the whole story in “
