SQL: 1999 (eBook)

Understanding Relational Language Components

Jim Melton, Alan R. Simon (Autoren)

eBook Download: PDF

2001 | 1. Auflage
893 Seiten
Elsevier Science (Verlag)
978-0-08-051760-5 (ISBN)

Infos

This book is the best way to make the leap from SQL-92 to SQL:1999, but it is much more than just a simple bridge between the two. The latest from celebrated SQL experts Jim Melton and Alan Simon, SQL:1999 is a comprehensive, eminently practical account of SQL's latest incarnation and a potent distillation of the details required to put it to work. Written to accommodate both novice and experienced SQL users, SQL:1999 focuses on the language's capabilities, from the basic to the advanced, and the ways that real applications take advantage of them. Throughout, the authors illustrate features and techniques with clear and often entertaining references to their own custom database, which can be downloaded from the companion Web site.

* Gives authoritative coverage from an expert team that includes the editor of the SQL-92 and SQL:1999 standards.
* Provides a general introduction to SQL that helps you understand its constituent parts, history, and place in the realm of computer languages.
* Explains SQL:1999's more sophisticated features, including advanced value expressions, predicates, advanced SQL query expressions, and support for active databases.
* Explores key issues for programmers linking applications to SQL databases.
* Provides guidance on troubleshooting, internationalization, and changes anticipated in the next version of SQL.
* Contains appendices devoted to database design, a complete SQL:1999 example, the standardization process, and more.

---

SQL: 1999 is the best way to make the leap from SQL-92 to SQL:1999, but it is much more than just a simple bridge between the two. The latest from celebrated SQL experts Jim Melton and Alan Simon, SQL:1999 is a comprehensive, eminently practical account of SQL's latest incarnation and a potent distillation of the details required to put it to work. Written to accommodate both novice and experienced SQL users, SQL:1999 focuses on the language's capabilities, from the basic to the advanced, and the ways that real applications take advantage of them. Throughout, the authors illustrate features and techniques with clear and often entertaining references to their own custom database. - Gives authoritative coverage from an expert team that includes the editor of the SQL-92 and SQL:1999 standards. - Provides a general introduction to SQL that helps you understand its constituent parts, history, and place in the realm of computer languages. - Explains SQL:1999's more sophisticated features, including advanced value expressions, predicates, advanced SQL query expressions, and support for active databases. - Explores key issues for programmers linking applications to SQL databases. - Provides guidance on troubleshooting, internationalization, and changes anticipated in the next version of SQL. - Contains appendices devoted to database design, a complete SQL:1999 example, the standardization process, and more.

Inhalt

Front Cover 1
SQL:1999 Understanding Relational Language Components 4
Copyright Page 5
Contents 10
Preface 26
Chapter 1. Database and Technology Foundations 34
1.1 Introduction 34
1.2 Introduction to Database Technology 35
1.3 Database Languages 49
1.4 The Transaction Model 52
1.5 Chapter Summary 55
Chapter 2. Introduction to SQL:1999 56
2.1 Introduction 56
2.2 SQL History and Background 56
2.3 SQL Concepts 59
2.4 SQL Data Types 65
2.5 Logic and Null Values 82
2.6 Data Conversions 85
2.7 SQL Statements 87
2.8 Static Versus Dynamic Execution 87
2.9 Conforming to SQL:1999 88
2.10 Relationship to Products 89
2.11 Identifiers 90
2.12 Chapter Summary 91
Chapter 3. Basic Table Creation and Data Manipulation 92
3.1 Introduction 92
3.2 Our Example 93
3.3 Basic Table Creation 94
3.4 Set Orientation 98
3.5 Basic Data Retrieval 99
3.6 Updating Information 108
3.7 Inserting Information 112
3.8 Deleting Information 114
3.9 Chapter Summary 115
Chapter 4. Basic Data Definition Language (DDL) 116
4.1 Introduction 116
4.2 Data Definition Fundamentals 116
4.3 Basic DDL Statements 125
4.4 WITH CHECK OPTION 144
4.5 Chapter Summary 145
Chapter 5. Values, Basic Functions, and Expressions 146
5.1 Introduction 146
5.2 Types of SQL Values 146
5.3 Literals 147
5.4 Parameters 152
5.5 Special Values 158
5.6 Column References and Field References 159
5.7 Some Terminology 165
5.8 Set Functions 165
5.9 Value Functions 176
5.10 Value Expressions 186
5.11 Chapter Summary 195
Chapter 6. Advanced Value Expressions: CASE, CAST, and Row Value Expressions 198
6.1 Introduction 198
6.2 The CASE Expression 199
6.3 The CAST Expression 204
6.4 Row Value Constructors 207
6.5 Table Value Constructors 209
6.6 Array Value Constructors 210
6.7 Chapter Summary 211
Chapter 7. Predicates 212
7.1 Introduction 212
7.2 What Is a Predicate? 213
7.3 Subqueries 213
7.4 Comparison Predicate 214
7.5 IS NULL Predicate 220
7.6 IN Predicate 222
7.7 LIKE and SIMILAR Predicates 223
7.8 EXISTS and UNIQUE Predicates 238
7.9 OVERLAPS Predicate 240
7.10 SOME, ANY, and ALL 242
7.11 MATCH Predicate 245
7.12 DISTINCT Predicate 248
7.13 Type Predicate 250
7.14 Search Conditions 250
7.15 Chapter Summary 254
Chapter 8. Working with Multiple Tables: The Relational Operators 256
8.1 Introduction 256
8.2 Join Operations: An Overview 256
8.3 Types of Join Operations 258
8.4 The UNION Operator 282
8.5 UNION JOIN 287
8.6 The INTERSECT and EXCEPT Operators 289
8.7 Another Example 293
8.8 Chapter Summary 296
Chapter 9. Advanced SQL Query Expressions 298
9.1 Introduction 298
9.2 Query Specifications 299
9.3 Functional Dependencies: Rules for Updatability 318
9.4 Query Expressions 326
9.5 Joined Table 334
9.6 Grouped Tables 342
9.7 Result Data Types of Columns of UNION, EXCEPT, and INTERSECT 345
9.8 Sequence of Evaluation within Query Expression 347
9.9 Table Value Constructor 347
9.10 Subqueries 350
9.11 Table References 354
9.12 CUBE and ROLLUP 359
9.13 Recursive Queries 371
9.14 Chapter Summary 386
Chapter 10. Constraints, Assertions, and Referential Integrity 388
10.1 Introduction 388
10.2 Column Constraints and Table Constraints 391
10.3 Assertions 406
10.4 PRIMARY KEY 409
10.5 Referential Integrity 412
10.6 Multiple Cascades 423
10.7 More about Referential Integrity Constraints 425
10.8 More about Constraints and Assertions 426
10.9 Chapter Summary 427
Chapter 11. Active Databases and Triggers 428
11.1 Introduction 428
11.2 Referential Actions Redux 429
11.3 Triggers 429
11.4 Interaction between Referential Actions and Triggers 442
11.5 Chapter Summary 443
Chapter 12. Accessing SQL from the Real World 444
12.1 Introduction 444
12.2 Data Access Methods and Data Types 445
12.3 Applications Interface Mechanisms for SQL 447
12.4 Direct Invocation 447
12.5 Embedded SQL 448
12.6 Module Language 464
12.7 The Impedance Mismatch 470
12.8 Other Binding Styles 471
12.9 Package Interfaces.SQL 473
12.10 Chapter Summary 479
Chapter 13. Cursors 480
13.1 Introduction 480
13.2 Cursors: The Basics 480
13.3 OPEN and CLOSE 496
13.4 FETCH 497
13.5 Cursor Positioning 498
13.6 Chapter Summary 500
Chapter 14. Privileges, Users, and Security 502
14.1 Introduction 502
14.2 Users and Roles 505
14.3 GRANT 515
14.4 WITH GRANT OPTION and WITH ADMIN OPTION 525
14.5 Changing User Identifiers and Role Names 526
14.6 Other Rules 528
14.7 REVOKE 529
14.8 Additional Details of REVOKE 533
14.9 Chapter Summary 541
Chapter 15. Transaction Management 544
15.1 Introduction 544
15.2 SQL:1999 Transaction Syntax 545
15.3 SQL:1999 Isolation Levels 549
15.4 Mixed DML and DDL 556
15.5 Transaction Termination 556
15.6 Transactions and Constraints 557
15.7 Savepoints—Establishment and Removal 559
15.8 Additional Transaction Termination Actions 561
15.9 Chapter Summary 564
Chapter 16. Connections and Remote Database Access 566
16.1 Introduction 566
16.2 Establishing Connections 567
16.3 Connection Statements 569
16.4 RDA (Remote Database Access) 572
16.5 Termination Statements and Connections 573
16.6 Chapter Summary 573
Chapter 17. Routines and Routine Invocation (Functions and Procedures) 574
17.1 Introduction 574
17.2 Relationship of SQL:1999's Routines to SQL/PSM 575
17.3 SQL-Invoked Routines 576
17.4 External Routines 590
17.5 SQL Routines 595
17.6 Limitations of SQL for Stored Routines 596
17.7 Polymorphism 597
17.8 Routine Resolution Outline 597
17.9 Chapter Summary 600
Chapter 18. Dynamic SQL 602
18.1 Introduction 602
18.2 What Is Dynamic SQL? 602
18.3 The Philosophy of Dynamic SQL 604
18.4 Outline of Dynamic SQL Processing 607
18.5 The EXECUTE IMMEDIATE Statement 622
18.6 PREPARE and EXECUTE Statements 623
18.7 The DESCRIBE Statement 627
18.8 Rows, Collections, and the DESCRIBE Statement 635
18.9 The EXECUTE Statement Revisited 640
18.10 Dynamic SQL and Cursors 643
18.11 A Dynamic SQL Example 649
18.12 Chapter Summary 656
Chapter 19. Call-Level Interface (CLI) 658
19.1 Introduction 658
19.2 A Brief History of SQL/CLI and ODBC 659
19.3 Brief Comparison of SQL/CLI and Dynamic SQL 660
19.4 The SQL/CLI Model 662
19.5 Handles 663
19.6 Null Pointers 667
19.7 Database Connections and Implicit Connections 667
19.8 CLI Descriptor Areas Versus Dynamic Descriptor Areas 670
19.9 Statement Preparation and Execution 676
19.10 Deferred Parameters 683
19.11 Transaction Management 690
19.12 Diagnostics Facilities 691
19.13 Metadata Access and Catalog Routines 695
19.14 CLI Routines Summary 696
19.15 Annotated CLI Example 710
19.16 Chapter Summary 714
Chapter 20. Diagnostics and Error Management 716
20.1 Introduction 716
20.2 SQLSTATE 716
20.3 Whatever Happened to SQLCODE? 728
20.4 SQL Statement Codes 729
20.5 Chapter Summary 732
Chapter 21. Internationalization Aspects of SQL:1999 734
21.1 Introduction 734
21.2 Character Sets and Collations 735
21.3 Translations and Conversions 744
21.4 Mapping Host Language Capabilities to SQL's Character Set Facilities 745
21.5 Equal, Distinct, Identical 746
21.6 Chapter Summary 747
Chapter 22. Information Schema 748
22.1 Introduction 748
22.2 Metadata, Repositories, and the INFORMATION_SCHEMA 748
22.3 DEFINITION_SCHEMA and Base Tables 749
22.4 Self-Description 750
22.5 INFORMATION_SCHEMAand Privileges 751
22.6 INFORMATION_SCHEMA Extensions 751
22.7 Identifier Representation 752
22.8 The DEFINITION_SCHEMA 755
22.9 The INFORMATION_SCHEMA 765
22.10 Short-Name Views 770
22.11 Chapter Summary 772
Chapter 23. A Look to the Future 774
23.1 Introduction 774
23.2 SQL:200n Overview 775
23.3 XML and SQL 778
23.4 Chapter Summary 779
Appendix A: Designing SQL:1999 Databases 780
A.1 Introduction 780
A.2 Overview of Database Design 781
A.3 Impact of Data Warehousing on Designing SQL:1999 Databases 784
A.4 Physical Database Design 787
A.5 Use of CASE and Data Modeling Tools 788
A.6 Appendix Summary 789
Appendix B: An SQL:1999 Application Example 790
B.1 Introduction 790
B.2 The Schema Definition 790
B.3 Application Code 797
Appendix C: The SQL:1999 Annexes 808
C.1 Introduction 808
C.2 Differences between SQL-92 and SQL:1999 808
C.3 Implementation-Defined and Implementation-Dependent 813
C.4 Deprecated Features 833
C.5 SQL:1999 Conformance Claims 834
C.6 Appendix Summary 849
Appendix D: Relevant Standards Bodies 850
D.1 Introduction 850
D.2 Contacting ISO 852
D.3 Selected National Standards Bodies 852
Appendix E: Status Codes 858
E.1 Values of SQLSTATE 858
Appendix F: The SQL Standardization Process 866
F.1 Introduction 866
F.2 The Various Standards Bodies 866
F.3 History of the SQL Standard 871
R4 NIST and the FIPS 874
F.5 Other SQL-Related Organizations 875
F.6 Appendix Summary 877
Index 878
About the Authors 928

Produkt-Details

• Erscheint lt. Verlag: 30.5.2001
• Sprache: englisch
• Themenwelt: Sachbuch/Ratgeber
• Themenwelt: Mathematik / Informatik ► Informatik ► Datenbanken
• Themenwelt: Mathematik / Informatik ► Informatik ► Programmiersprachen / -werkzeuge
• Themenwelt: Mathematik / Informatik ► Informatik ► Software Entwicklung
• ISBN-10: 0-08-051760-9 / 0080517609
• ISBN-13: 978-0-08-051760-5 / 9780080517605