SQL The Complete Reference, 3rd Edition

✍ Scribed by James Groff, Paul Weinberg, Andrew J. Oppel

• Publisher: McGraw-Hill Osborne Media
• Year: 2009
• Tongue: English
• Leaves: 911
• Edition: 3
• Category: Library

✦ Synopsis

Get comprehensive coverage of every aspect of SQL from three leading industry experts. Revised with coverage of the latest RDBMS software versions, this one-stop guide explains how to build, populate, and administer high-performance databases and develop robust SQL-based applications. SQL: The Complete Reference, Third Edition shows you how to work with SQL commands and statements, set up relational databases, load and modify database objects, perform powerful queries, tune performance, and implement reliable security policies. Learn how to employ DDL statements and APIs, integrate XML and Java scripts, use SQL objects, build web servers, handle remote access, and perform distributed transactions. Techniques for managing in-memory, stream, and embedded databases that run on today's mobile, handheld, and wireless devices are included in this in-depth volume.

✦ Table of Contents

Contents......Page 10
Acknowledgments......Page 24
Introduction......Page 26
Part I: An Overview of SQL......Page 30
1 Introduction......Page 32
The SQL Language......Page 33
The Role of SQL......Page 35
SQL Success Factors......Page 36
A Simple Database......Page 42
Retrieving Data......Page 43
Summarizing Data......Page 45
Adding Data to the Database......Page 46
Protecting Data......Page 47
Creating a Database......Page 48
Summary......Page 49
SQL and the Evolution of Database Management......Page 50
A Brief History of SQL......Page 51
SQL Standards......Page 55
SQL and Networking......Page 61
The Proliferation of SQL......Page 65
Summary......Page 72
Early Data Models......Page 74
The Relational Data Model......Page 79
Codd's 12 Rules for Relational Databases......Page 86
Summary......Page 88
Part II: Retrieving Data......Page 90
Statements......Page 92
Names......Page 99
Data Types......Page 101
Constants......Page 106
Built-In Functions......Page 109
Missing Data (NULL Values)......Page 111
Summary......Page 112
The SELECT Statement......Page 114
Query Results......Page 117
Simple Queries......Page 119
Selecting All Columns (SELECT )......Page 122
Duplicate Rows (DISTINCT)......Page 123
Row Selection (WHERE Clause)......Page 124
The Comparison Test (=, <>, <, <=, >, >=)......Page 126
Sorting Query Results (ORDER BY Clause)......Page 139
Rules for Single-Table Query Processing......Page 141
Summary......Page 147
A Two-Table Query Example......Page 148
Simple Joins (Equi-Joins)......Page 150
SQL Considerations for Multitable Queries......Page 163
Multitable Query Performance......Page 170
The Structure of a Join......Page 171
Outer Joins......Page 173
Joins and the SQL Standard......Page 182
Summary......Page 191
Column Functions......Page 192
Grouped Queries (GROUP BY Clause)......Page 202
Group Search Conditions (HAVING Clause)......Page 211
Summary......Page 215
Using Subqueries......Page 216
Subquery Search Conditions......Page 221
Subqueries and Joins......Page 232
Nested Subqueries......Page 233
Correlated Subqueries......Page 234
Subqueries in the HAVING Clause......Page 237
Subquery Summary......Page 238
Advanced Queries......Page 240
SQL Queries: A Final Summary......Page 256
Part III: Updating Data......Page 258
Adding Data to the Database......Page 260
Deleting Data from the Database......Page 267
Modifying Data in the Database......Page 271
Summary......Page 275
11 Data Integrity......Page 276
What Is Data Integrity?......Page 277
Required Data......Page 278
Simple Validity Checking......Page 279
Entity Integrity......Page 282
Referential Integrity......Page 284
Advanced Constraint Capabilities......Page 298
Business Rules......Page 303
Summary......Page 308
12 Transaction Processing......Page 310
What Is a Transaction?......Page 311
The ANSI/ISO SQL Transaction Model......Page 313
Transactions: Behind the Scenes......Page 318
Transactions and Multiuser Processing......Page 319
Locking......Page 326
Versioning......Page 336
Summary......Page 340
Part IV: Database Structure......Page 342
The Data Definition Language......Page 344
Creating a Database......Page 346
Table Definitions......Page 347
Constraint Definitions......Page 361
Aliases and Synonyms (CREATE/DROP ALIAS)......Page 362
Indexes (CREATE/DROP INDEX)......Page 364
Managing Other Database Objects......Page 368
Database Structure......Page 371
Database Structure and the ANSI/ISO Standard......Page 377
Summary......Page 383
14 View......Page 384
Creating a View (CREATE VIEW)......Page 387
Updating a View......Page 395
Dropping a View (DROP VIEW)......Page 400
Materialized Views......Page 401
Summary......Page 403
15 SQL Security......Page 404
SQL Security Concepts......Page 405
Views and SQL Security......Page 413
Granting Privileges (GRANT)......Page 415
Revoking Privileges (REVOKE)......Page 420
Role-Based Security......Page 425
Summary......Page 427
What Is the System Catalog?......Page 428
Table Information......Page 432
Column Information......Page 436
View Information......Page 439
Remarks......Page 441
Relationship Information......Page 442
User Information......Page 444
Privileges Information......Page 446
The SQL Information Schema......Page 447
Other Catalog Information......Page 454
Summary......Page 455
Part V: Programming with SQL......Page 456
Programmatic SQL Techniques......Page 458
Simple Embedded SQL Statements......Page 468
Data Retrieval in Embedded SQL......Page 486
Cursor-Based Deletes and Updates......Page 499
Cursors and Transaction Processing......Page 504
Summary......Page 505
Limitations of Static SQL......Page 506
Dynamic SQL Concepts......Page 508
Dynamic Statement Execution (EXECUTE IMMEDIATE)......Page 509
Two-Step Dynamic Execution......Page 512
Dynamic Queries......Page 522
Dynamic SQL Dialects......Page 533
Dynamic SQL and the SQL Standard......Page 537
Summary......Page 547
19 SQL APIs......Page 550
API Concepts......Page 551
The dblib API (SQL Server)......Page 552
ODBC and the SQL/CLI Standard......Page 578
The ODBC API......Page 608
The Oracle Call Interface (OCI)......Page 615
Java Database Connectivity (JDBC)......Page 621
Summary......Page 643
Part VI: SQL Today and Tomorrow......Page 644
20 Database Processing and Stored Procedural SQL......Page 646
Procedural SQL Concepts......Page 647
A Basic Example......Page 649
Using Stored Procedures......Page 650
Advantages of Stored Procedures......Page 674
Stored Procedure Performance......Page 675
External Stored Procedures......Page 676
Triggers......Page 677
Stored Procedures, Functions, Triggers, and the SQL Standard......Page 684
Summary......Page 695
21 SQL and Data Warehousing......Page 696
Data Warehousing Concepts......Page 697
Database Architecture for Warehousing......Page 700
Warehouse Performance......Page 707
Summary......Page 709
SQL and Web Sites: Early Implementations......Page 710
Application Servers and Three-Tier Web Site Architectures......Page 711
Database Access from Application Servers......Page 713
Application Server Caching......Page 724
Summary......Page 727
23 SQL Networking and Distributed Databases......Page 728
The Challenge of Distributed Data Management......Page 729
Distributing Data: Practical Approaches......Page 733
Distributed Database Access......Page 748
The Two-Phase Commit Protocol......Page 753
Network Applications and Database Architecture......Page 756
Summary......Page 762
Object-Oriented Databases......Page 764
Object-Relational Databases......Page 768
Abstract (Structured) Data Types......Page 773
Inheritance......Page 778
Sets, Arrays, and Collections......Page 783
User-Defined Data Types......Page 791
Methods and Stored Procedures......Page 792
Object Support in the SQL Standard......Page 795
Summary......Page 796
What Is XML?......Page 798
XML Basics......Page 800
XML for Data......Page 802
Using XML with Databases......Page 806
XML and Metadata......Page 817
XML and Queries......Page 826
XML Databases......Page 831
Summary......Page 832
Very Low Latency and In-Memory Databases......Page 834
In-Memory Database Implementations......Page 837
Complex Event-Processing and Stream Databases......Page 839
Embedded Databases......Page 843
Mobile Databases......Page 845
Summary......Page 847
27 The Future of SQL......Page 848
Database Market Trends......Page 849
SQL in the Next Decade......Page 855
Summary......Page 861
Part VII: Appendixes......Page 862
A: The Sample Database......Page 864
B: DBMS Vendor Profiles......Page 870
C: SQL Syntax Reference......Page 886
Data Definition Statements......Page 887
Basic Data Manipulation Statements......Page 888
Query Expressions......Page 889
Search Conditions......Page 891
Statement Elements......Page 892
Simple Elements......Page 893
A......Page 894
C......Page 895
D......Page 896
E......Page 899
H......Page 900
J......Page 901
M......Page 902
O......Page 903
P......Page 904
R......Page 905
S......Page 906
T......Page 909
V......Page 910
X......Page 911

✦ Subjects

Библиотека;Компьютерная литература;SQL;