SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK ORACLE SQL JUMPSTART WITH EXAMPLES

SUMMARY

431

1 Introduction to Oracle SQL 1
2 New Features of Oracle SQL 39
3 Oracle Database Architecture 51
4 The SELECT Statement 73
5 Filtering Rows 97
6 Sorting Rows 109
7 Operators, Conditions, and Pseudocolumns 123
8 Using SQL*Plus 137
9 Single-Row Functions 175
10 Joining Tables 205
11 Grouping and Summarizing Data 235
12 Subqueries 267
13 Unusual Query Types 285
14 Expressions 301
15 Data Manipulation Language (DML) 315
16 Datatypes and Collections 339
17 XML in Oracle 357
18 Tables 383
19 Views 425
20 Constraints 447
21 Indexes and Clusters 471
22 Sequences and Synonyms 489
23 Security 503
24 Basic PL/SQL 531
Appendix A 569
Appendix B 623
Appendix C 625
Index 627
vii
1 Introduction to Oracle SQL 1
1.1 A Little History 1
1.1.1 The Evolution of Database Modeling 1
1.1.2 The History of Relational Databases 5
1.1.3 The Evolution of Oracle Database 6
1.2 The Basics of Relational Data Modeling 8
1.2.1 Normalization 8
1.2.1.1 Referential Integrity 10
1.2.2 Denormalization 11
1.2.3 Different Forms of the Relational Data Model 13
1.3 Structured Query Language (SQL) 14
1.3.1 The Humble Origins of SQL 14
1.3.2 What Is Oracle SQL? 15
1.3.2.1 ANSI Standards and Oracle 15
1.4 Software Useful for Reading this Book 16
1.5 Syntax Conventions Used in This Book 17
1.6 SQL Tools 19
1.6.1 SQL*Plus in Command-Line Mode 19
1.6.2 SQL*Plus in Windows Mode 24
1.6.3 SQL*Plus Worksheet 28
1.6.4 iSQL*Plus 31
1.7 The MUSIC Schema 34
1.7.1 The MUSIC Schema Sales Data Warehouse 36
2 New Features of Oracle SQL 39
2.1 New Features in Oracle Database 10g 39
2.1.1 Oracle SQL Improvements in Oracle Database 10g 39
2.1.2 PL/SQL Improvements in Oracle Database 10g 44
2.1.2.1 Java Improvements in Oracle Database 10g 45
2.1.3 XML Improvements in Oracle Database 10g 45
2.1.4 Some Utility Improvements in Oracle
Database 10g 45
2.1.5 Database Object Improvements in Oracle 10g 46
2.2 New Features in Oracle Database 9i 46
2.2.1 Oracle SQL Improvements in Oracle Database 9i 47
2.2.2 New PL/SQL Features in Oracle Database 9i 48
3 Oracle Database Architecture 51
3.1 The Basic Concepts 51
3.2 The Oracle Instance 58
3.3 Oracle Database Physical Architecture 60
3.3.1 Datafiles, Tablespaces, and Objects 60
3.3.2 Controlfiles, Logging, and Archiving 61
3.3.3 Rollback and Undo 62
3.3.4 Temporary Sort Space 64
3.4 Database Startup and Shutdown 65
3.5 Enhancing the Physical Architecture 67
3.5.1 Oracle Managed Files 67
3.5.2 Partitioning 67
3.5.3 Replication 68
3.5.4 Standby Databases 69
3.5.5 Clustering and Oracle RAC 70
4 The SELECT Statement 73
4.1 The Basic SELECT Statement 73
4.1.1 Uses of the SELECT Statement 73
4.1.2 Syntax Conventions 74
4.1.3 Some Simple Example SELECT Statements 76
4.2 Types of SELECT Queries 81
4.2.1 Simple Query 82
4.2.2 Filtered Query 82
4.2.3 Sorted Query 83
4.2.4 Grouping or Aggregated Query 83
4.2.5 Join Query 84
4.2.6 Subquery 85
4.2.7 Table or View Creation Query 85
4.2.8 Hierarchical Query 86
4.2.9 Composite Queries 88
4.3 Other Aspects of the SELECT Statement 88
4.3.1 The DUAL Table 89
4.3.2 Using Functions 90
4.3.3 Arithmetic Operations 91
4.3.4 Using DISTINCT 92
4.3.5 Null Values 93
4.3.6 Using Pseudocolumns 94
5 Filtering Rows 97
5.1 WHERE Clause Syntax 97
5.1.1 Some Simple WHERE Clause Examples 98
5.2 WHERE Clause Expression Conditions 101
5.3 Logical Operators in the WHERE Clause 105
5.4 Top-N Queries 105
6 Sorting Rows 109
6.1 ORDER BY Clause Syntax 109
6.1.1 Some Simple ORDER BY Clause Examples 111
6.2 Sorting and Null Values 113
6.3 Sorting Methods 117
6.3.1 Sorting by Position 117
6.3.2 Sorting by Expression 119
7 Operators, Conditions, and Pseudocolumns 123
7.1 Precedence 124
7.2 Operators 124
7.2.1 Arithmetic Operators 125
7.2.2 Logical Operators 126
7.2.3 The Concatenation Operator 128
7.2.4 Hierarchical Query Operators 128
7.2.5 Set Operators 129
7.2.6 Multiset Operators 129
7.2.7 User-Defined Operators 131
7.3 Conditions 131
7.4 Pseudocolumns 134
8 Using SQL*Plus 137
8.1 Environmental Settings 137
8.2 Using Scripts and Variables 148
8.3 Formatting Query Output in SQL*Plus 153
8.3.1 Column Formatting and Headings 154
8.3.1.1 Formatting Dates 158
8.3.2 Lines, Pages, and Breaks 160
8.4 Using iSQL*Plus 165
8.4.1 Embedding Scripts in HTML 168
8.4.2 iSQL*Plus versus SQL*Plus 171
8.4.3 Troubleshooting iSQL*Plus 171
8.4.4 Customizing iSQL*Plus Display 172
9 Single-Row Functions 175
9.1 Types of Functions 176
9.2 Single-Row Functions 176
9.2.1 String Functions 180
9.2.2 Number Functions 182
9.2.2.1 Binary Floating-Point Number Functions 185
9.2.3 Date Functions 186
9.2.4 Datatype Conversion Functions 190
9.2.4.1 Number Conversion Function Formats 190
9.2.4.2 Date Conversion Function Formats 191
9.2.5 Miscellaneous Functions 194
9.3 Combining Functions 196
10 Joining Tables 205
10.1 Join Formats 206
10.1.1 Oracle’s Proprietary Format 206
10.1.2 ANSI Format 206
10.2 Types of Joins 207
10.3 Examining Different Types of Joins 210
10.3.1 Cross-Join or Cartesian Product 210
10.3.2 Natural or Inner Join 212
10.3.2.1 The USING clause 213
10.3.2.2 The ON clause 215
10.3.3 Outer Join 217
10.3.3.1 Left Outer Join 218
10.3.3.2 Right Outer Join 223
10.3.3.3 Full Outer Join 224
10.3.4 Self-Join 225
10.3.4.1 Grouping Self-Join 226
10.3.4.2 Hierarchical (Fishhook) Self-Join 228
10.3.5 Equi-Joins, Anti-Joins, and Range Joins 230
10.3.6 Mutable and Complex Joins 230
11 Grouping and Summarizing Data 235
11.1 GROUP BY Clause Syntax 235
11.2 Types of Group Functions 237
11.2.1 Aggregate Functions 237
11.2.1.1 Simple Summary Functions 238
11.2.1.2 Statistical Function Calculators 238
11.2.1.3 Statistical Distribution Functions 240
11.2.1.4 Ranking Functions 240
11.2.1.5 Grouping Functions 240
11.2.2 Enhancing Grouping Functions for Analysis 241
11.2.2.1 The OVER Clause 242
11.3 Special Grouping Function Behavior 245
11.3.1 Group Functions and Null Values 245
11.3.2 Selecting DISTINCT or ALL in Group Functions 246
11.4 Using the GROUP BY Clause 249
11.4.1 Grouping Rows 250
11.4.2 Filtering Grouped Results with the Having Clause 253
11.4.3 Extending the GROUP BY Clause Further 255
11.4.3.1 The ROLLUP Clause 256
11.4.3.2 The CUBE Clause 257
11.4.3.3 The GROUPING SETS Clause 258
11.5 The SPREADSHEET (MODEL) Clause 260
11.5.1 SPREADSHEET (MODEL) Clause Syntax 261
11.5.2 SPREADSHEET (MODEL) Clause Functions 261
11.5.3 Using the SPREADSHEET (MODEL) Clause 262
12 Subqueries 267
12.1 Types of Subqueries 267
12.2 Where Can Subqueries Be Used? 269
12.3 Comparison Conditions and Subqueries 269
12.4 Demonstrating Subqueries 270
12.4.1 Single-Row Subqueries 270
12.4.2 Multiple-Row Subqueries 272
12.4.3 Multiple-Column Subqueries 276
12.4.4 Regular versus Correlated Subqueries 279
12.4.5 Nested Subqueries 280
12.4.6 Inline Views 281
12.4.7 Other Uses for Subqueries 282
13 Unusual Query Types 285
13.1 Composite Queries 285
13.1.1 Set Operators 285
13.1.2 Using Composite Queries 286
13.2 Hierarchical Queries 289
13.2.1 Hierarchical Query Operators 290
13.2.2 Hierarchical Query Pseudocolumns 290
13.2.3 Using Hierarchical Queries 290
13.3 Flashback and Versions Queries 292
13.3.1 Flashback Query Syntax 293
13.3.2 Versions Query Pseudocolumns 294
13.3.3 Using Flashback Queries 294
13.4 Parallel Queries 297
14 Expressions 301
14.1 Types of Expressions 302
14.2 Regular Expressions 305
14.2.1 Regular Expression Functions 305
14.2.2 Regular Expression Patterns 306
14.2.3 Using Regular Expressions 307
14.3 Oracle Expression Filter 309
15 Data Manipulation Language (DML) 315
15.1 What Is DML? 315
15.2 Transaction Control 317
15.2.1 Locks 318
15.2.2 The SET TRANSACTION Command 319
15.2.3 The SAVEPOINT Command 322
15.3 Adding Data (INSERT) 324
15.3.1 Inserting One Row 325
15.3.2 Inserting with a Subquery 326
15.3.3 The Multiple-Table INSERT Command 327
15.4 Changing Data (UPDATE) 330
15.4.1 Updating One Row 331
15.4.2 Updating Many Rows 331
15.5 Deleting Data (DELETE) 334
15.5.1 Deleting One Row 334
15.5.2 Deleting Many Rows 334
15.5.3 Deleting All Rows 336
15.6 Merging New and Old Data (MERGE) 336
15.6.1 How To Use MERGE 337
16 Datatypes and Collections 339
16.1 Simple Datatypes 339
16.2 Complex and Object Datatypes 342
16.2.1 Binary Object Datatypes 342
16.2.2 Reference Pointer Datatypes 343
16.2.2.1 Using the REF Datatype 344
16.2.2.2 Using the BFILE Datatype 345
16.2.3 User-Defined Datatypes 347
16.2.4 Object Collection Datatypes 348
16.2.4.1 Using VARRAY Collections 349
16.2.4.2 Using Nested Table Collections 350
16.2.5 Object Collection Functions 352
16.2.6 Metadata Views 354
16.3 Special Datatypes 355
17 XML in Oracle 357
17.1 What Is XML? 357
17.1.1 What Is XSL? 360
17.2 Using XML in Oracle 360
17.2.1 Creating XML Documents 361
17.2.1.1 The XMLType Datatype 361
17.2.1.2 Generating XML from Tables 362
The SQL/XML Standard 363
The SYS_XMLGEN Function 372
17.2.2 XML and the Database 373
17.2.2.1 New XML Documents 373
17.2.2.2 Retrieving from XML Documents 374
17.2.2.3 Changing and Removing XML Document Content 378
17.3 Metadata Views 380
18 Tables 383
18.1 What Is a Table? 383
18.1.1 Types of Tables 383
18.1.2 Methods of Creating Tables 384
18.1.2.1 Scripted Method 385
18.1.2.2 CREATE TABLE AS Subquery 386
18.1.2.3 Tools 386
18.2 CREATE TABLE Syntax 387
18.3 Creating Different Table Types 388
18.3.1 Creating Relational Tables 388
18.3.2 Creating Object Tables 390
18.3.3 Creating Temporary Tables 393
18.3.4 Creating Index-Organized Tables (IOTs) 397
18.3.5 Creating External Tables 398
18.3.6 Creating Partitioned Tables 402
18.3.6.1 What Are the Types of Partitions? 402
Partition Indexing 402
18.3.6.2 CREATE TABLE Partition Syntax 403
CREATE TABLE Range Partition Syntax 403
CREATE TABLE List Partition Syntax 403
CREATE TABLE Hash Partition Syntax 404
CREATE TABLE Range-Hash Partition Syntax 405
CREATE TABLE Range-List Partition Syntax 405
18.4 Changing Table Structure 407
18.4.1 Adding, Modifying, and Removing Columns 408
18.4.2 Rebuilding a Table 412
18.4.3 Renaming a Table 413
18.5 Dropping a Table 414
18.5.1 Truncating Instead of Dropping Tables 415
18.6 Adding Comments to Tables 416
18.6.1 Adding Comments to Schema Objects 416
18.6.2 Scripting and SQL Code Comments 419
18.7 The Recycle Bin 420
18.8 Metadata Views 421
19 Views 425
19.1 What Is a View? 425
19.2 Types and Uses of Views 426
19.3 CREATE VIEW Syntax 427
19.3.1 Creating Simple Views 428
19.3.2 Creating Constraint Views 429
19.3.3 Creating Complex Views 430
19.3.3.1 Views with Joins 430
19.3.3.2 Inline Subquery Views 432
19.4 Changing and Dropping Views 433
19.5 Working with Views 435
19.5.1 Querying a View 435
19.5.2 Views and DML Commands 437
19.5.2.1 DML and Views with Joins 440
19.6 Metadata Views 441
19.7 Data Dictionary Views (Metadata) 442
20 Constraints 447
20.1 What Are Constraints? 448
20.1.1 Types and Uses of Constraints 448
20.2 Managing Constraints 449
20.2.1 CREATE TABLE Syntax 450
20.2.1.1 Primary Key and Unique Constraints 451
20.2.1.2 Foreign Key Constraints 452
Out-of-Line Primary and Foreign Keys 453
20.2.1.3 Check Constraints 456
20.2.1.4 REF Constraints 459
20.2.2 CREATE VIEW Syntax 460
20.3 Adding, Modifying, and Dropping Constraints 460
20.3.1 ALTER TABLE Syntax 460
20.3.2 ALTER VIEW Syntax 461
20.3.3 Working with Constraints and ALTER TABLE 461
20.3.3.1 Adding a Constraint to an Existing Table 462
20.3.3.2 Modifying Constraints on Existing Tables 463
20.3.3.3 Constraint States 463
20.3.4 Renaming a Constraint 464
20.3.5 Dropping Constraints 465
20.3.5.1 Dropping Constraints with CASCADE 466
20.4 Metadata Views 469
21 Indexes and Clusters 471
21.1 Indexes 471
21.1.1 What Is an Index? 471
21.1.2 Types of Indexes 474
21.1.2.1 Index Attributes 476
21.1.3 Creating Indexes 477
21.1.4 Changing and Dropping Indexes 482
21.1.5 More Indexing Refinements 483
21.2 Clusters 484
21.2.1 What is a Cluster? 484
21.2.2 Types of Clusters 485
21.2.3 Creating Clusters 485
21.3 Metadata Views 487
22 Sequences and Synonyms 489
22.1 Sequences 489
22.1.1 Creating Sequences 490
22.1.2 Changing and Dropping Sequences 493
22.1.3 Using Sequences 494
22.1.3.1 Using the CURRVAL and NEXTVAL Pseudocolumns 495
22.1.3.2 Using Sequences in an INSERT Statement 496
22.1.3.3 Other Uses of Sequences 497
22.2 Synonyms 498
22.2.1 Creating Public Synonyms 499
22.2.2 Creating Private Synonyms 500
22.2.3 Using Synonyms 500
22.3 Metadata Views 501
23 Security 503
23.1 Users 503
23.1.1 Users Provided by Oracle 504
23.1.2 Creating Users 505
23.1.3 Modifying User Passwords 508
23.1.4 Dropping Users 510
23.2 Privileges 511
23.2.1 Granting Privileges 511
23.2.2 Revoking Privileges 518
23.2.2.1 Revoked System Privileges DO NOT Cascade 520
23.2.2.2 Revoked Object Privileges DO Cascade 521
23.3 Grouping Privileges Using Roles 522
23.3.1 Creating and Altering Roles 523
23.3.2 Granting and Revoking Privileges on Roles 524
23.3.3 Setting User Roles 527
23.3.4 Dropping Roles 529
23.4 Metadata Views 530
24 Basic PL/SQL 531
24.1 What is PL/SQL? 531
24.2 Why Is PL/SQL a Programming Language? 532
24.2.1 Blocks and Exception Trapping 533
24.2.2 Procedures, Functions, Triggers, and Packages 534
24.2.2.1 Using Named Procedures 535
24.2.2.2 Using Functions 535
24.2.2.3 Using Triggers 537
24.2.2.4 Using Packages 539
24.3 Variables and Datatypes in PL/SQL 541
24.4 Retrieving Data in PL/SQL 543
24.4.1 Explicit Cursors 543
24.4.2 Implicit Cursors 544
24.4.2.1 The Internal SQL Implicit Cursor 545
24.4.2.2 Single-Row SELECT Implicit Cursor 547
24.4.2.3 Cursor FOR Loop Implicit Cursor 547
24.5 Changing Data in PL/SQL 549
24.6 Dynamic SQL 550
24.6.1 Building Cursors Dynamically 552
24.7 Control Structures 553
24.7.1 Selection 554
24.7.1.1 The IF Statement 554
24.7.1.2 The CASE Statement 556
CASE Statement Search Condition 557
CASE Statement Selector and Expression 558
24.7.2 Iteration or Repetition 560
24.7.2.1 The FOR Loop 561
24.7.2.2 The WHILE Loop 564
24.7.2.3 The LOOP .END LOOP Construct 564
24.7.2.4 The FORALL Command 565
24.7.3 Sequence Controls 565
24.7.3.1 The GOTO Statement 566
24.7.3.2 The NULL Command 566
24.8 Objects and Methods 567
24.9 Oracle-Provided Packages 567
24.10 Metadata Views 568
Appendix A
Schema Scripting 569
Appendix B
Utility Scripts 623
Appendix C
Sources of Information 625
Index 627