Oracle SQL (eBook)

Front Cover 1
Oracle SQL Jumpstart with Examples 4
Copyright Page 5
Table of Contents 8
Foreword 20
Preface 22
Acknowledgements 30
Chapter 1. Introduction to Oracle SQL 32
1.1 A Little History 32
1.2 The Basics of Relational Data Modeling 39
1.3 Structured Query Language (SQL) 45
1.4 Software Useful for Reading this Book 47
1.5 Syntax Conventions Used in This Book 48
1.6 SQL Tools 50
1.7 The MUSIC Schema 65
Chapter 2. New Features of Oracle SQL 70
2.1 New Features in Oracle Database 10g 70
2.2 New Features in Oracle Database 9i 77
Chapter 3. Oracle Database Architecture 82
3.1 The Basic Concepts 82
3.2 The Oracle Instance 89
3.3 Oracle Database Physical Architecture 91
3.4 Database Startup and Shutdown 96
3.5 Enhancing the Physical Architecture 98
Chapter 4. The SELECT Statement 104
4.1 The Basic SELECT Statement 104
4.2 Types of SELECT Queries 112
4.3 Other Aspects of the SELECT Statement 119
Chapter 5. Filtering Rows 128
5.1 WHERE Clause Syntax 128
5.2 WHERE Clause Expression Conditions 132
5.3 Logical Operators in the WHERE Clause 136
5.4 Top-N Queries 136
Chapter 6. Sorting Rows 140
6.1 ORDER BY Clause Syntax 140
6.2 Sorting and Null Values 144
6.3 Sorting Methods 148
Chapter 7. Operators, Conditions, and Pseudocolumns 154
7.1 Precedence 155
7.2 Operators 155
7.3 Conditions 162
7.4 Pseudocolumns 165
Chapter 8. Using SQL*Plus 168
8.1 Environmental Settings 168
8.2 Using Scripts and Variables 179
8.3 Formatting Query Output in SQL*Plus 184
8.4 Using iSQL*Plus 196
Chapter 9. Single-Row Functions 206
9.1 Types of Functions 207
9.2 Single-Row Functions 207
9.3 Combining Functions 227
Chapter 10. Joining Tables 236
10.1 Join Formats 237
10.2 Types of Joins 238
10.3 Examining Different Types of Joins 241
Chapter 11. Grouping and Summarizing Data 266
11.1 GROUP BY Clause Syntax 266
11.2 Types of Group Functions 268
11.3 Special Grouping Function Behavior 276
11.4 Using the GROUP BY Clause 280
11.5 10g The SPREADSHEET (MODEL) Clause 291
Chapter 12. Subqueries 298
12.1 Types of Subqueries 298
12.2 Where Can Subqueries Be Used? 300
12.3 Comparison Conditions and Subqueries 300
12.4 Demonstrating Subqueries 301
Chapter 13. Unusual Query Types 316
13.1 Composite Queries 316
13.2 Hierarchical Queries 320
13.3 Flashback and Versions Queries 323
13.4 Parallel Queries 328
Chapter 14. Expressions 332
14.1 Types of Expressions 333
14.2 10g Regular Expressions 336
14.3 10g Oracle Expression Filter 340
Chapter 15. Data Manipulation Language (DML) 346
15.1 What Is DML? 346
15.2 Transaction Control 348
15.3 Adding Data (INSERT) 355
15.4 Changing Data (UPDATE) 361
15.5 Deleting Data (DELETE) 364
15.6 10g Merging New and Old Data (MERGE) 367
Chapter 16. Datatypes and Collections 370
16.1 Simple Datatypes 370
16.2 Complex and Object Datatypes 373
16.3 Special Datatypes 386
Chapter 17. XML in Oracle 388
17.1 What Is XML? 388
17.2 Using XML in Oracle 391
17.3 Metadata Views 411
Chapter 18. Tables 414
18.1 What Is a Table? 414
18.2 CREATE TABLE Syntax 418
18.3 Creating Different Table Types 419
18.4 Changing Table Structure 438
18.5 Dropping a Table 445
18.6 Adding Comments to Tables 447
18.7 10g The Recycle Bin 451
18.8 Metadata Views 452
Chapter 19. Views 456
19.1 What Is a View? 456
19.2 Types and Uses of Views 457
19.3 CREATE VIEW Syntax 458
19.4 Changing and Dropping Views 464
19.5 Working with Views 466
19.6 Metadata Views 472
19.7 Data Dictionary Views (Metadata) 473
Chapter 20. Constraints 478
20.1 What Are Constraints? 479
20.2 Managing Constraints 480
20.3 Adding, Modifying, and Dropping Constraints 491
20.4 Metadata Views 500
Chapter 21. Indexes and Clusters 502
21.1 Indexes 502
21.2 Clusters 515
21.3 Metadata Views 518
Chapter 22. Sequences and Synonyms 520
22.1 Sequences 520
22.2 Synonyms 529
22.3 Metadata Views 532
Chapter 23. Security 534
23.1 Users 534
23.2 Privileges 542
23.3 Grouping Privileges Using Roles 553
23.4 Metadata Views 561
Chapter 24. Basic PL/SQL 562
24.1 What is PL/SQL? 562
24.2 Why Is PL/SQL a Programming Language? 563
24.3 Variables and Datatypes in PL/SQL 572
24.4 Retrieving Data in PL/SQL 574
24.5 Changing Data in PL/SQL 580
24.6 Dynamic SQL 581
24.7 Control Structures 584
24.8 Objects and Methods 597
24.9 Oracle-Provided Packages 598
24.10 Metadata Views 599
Appendix A. Schema Scripting 600
Appendix B. Utility Scripts 654
Appendix C. Sources of Information 656
Index 658