Beginning Oracle SQL (eBook)

Lex de Haan studied applied mathematics at the University of Technology in Delft, the Netherlands. His experience with Oracle goes back to the mid-1980s, version 4. He worked for Oracle Corporation from 1990 to 2004 in various education-related roles, ending up in Server Technologies (product development) as senior curriculum manager for the advanced database administration curriculum. In that role, he was involved in the development of Oracle9i and Oracle Database 10g. In March 2004, he decided to go independent and founded Natural Join B.V. (http://www.naturaljoin.nl). From 1999 until his passing in 2006, he was involved in the ISO SQL language standardization process, as a member of the Dutch national body. He was also one of the founding members of the OakTable network (http://www.oaktable.net).

Titlte Page 1
Copyright Page 2
Contents at a Glance 3
Table of Contents 4
About the Authors 17
Acknowledgments 19
Introduction 21
About this Book 21
About the Chapters of this Book 22
About the Case Tables 23
Chapter 1: Relational Database Systems and Oracle 24
1.1 Information Needs and Information Systems 24
1.2 Database Design 25
Entities and Attributes 25
Generic vs. Specific 26
Redundancy 27
Consistency, Integrity, and Integrity Constraints 28
Data Modeling Approach, Methods, and Techniques 29
Semantics 30
Information Systems Terms Review 30
1.3 Database Management Systems 30
DBMS Components 31
Kernel 31
Data Dictionary 31
Query Languages 31
DBMS Tools 32
Database Applications 32
DBMS Terms Review 32
1.4 Relational Database Management Systems 33
1.5 Relational Data Structures 33
Tables, Columns, and Rows 34
The Information Principle 35
Datatypes 35
Keys 35
Missing Information and Null Values 36
Constraint Checking 37
Predicates and Propositions 37
Relational Data Structure Terms Review 37
1.6 Relational Operators 38
1.7 How Relational Is My DBMS? 39
1.8 The Oracle Software Environment 40
1.9 Case Tables 42
The ERM Diagram of the Case 42
Table Descriptions 44
Chapter 2: Introduction to SQL, AQL*Plus, and SQL Developer 48
2.1 Overview of SQL 48
Data Definition 49
Data Manipulation and Transactions 49
Retrieval 50
Security 52
Privileges and Roles 52
GRANT and REVOKE 54
2.2 Basic SQL Concepts and Terminology 55
Constants (Literals) 55
Variables 57
Operators, Operands, Conditions, and Expressions 57
Arithmetic Operators 58
The Alphanumeric Operator: Concatenation 58
Comparison Operators 58
Logical Operators 59
Expressions 59
Functions 60
Database Object Naming 61
Comments 62
Reserved Words 62
2.3 Introduction to SQL*Plus 62
Entering Commands 63
Using the SQL Buffer 64
Using an External Editor 65
Using the SQL*Plus Editor 66
Using SQL Buffer Line Numbers 69
Using the Ellipsis 71
SQL*Plus Editor Command Review 71
Saving Commands 72
Running SQL*Plus Scripts 74
Specifying Directory Path Specifications 75
Adjusting SQL*Plus Settings 76
Spooling a SQL*Plus Session 79
Describing Database Objects 80
Executing Commands from the Operating System 80
Clearing the Buffer and the Screen 80
SQL*Plus Command Review 80
2.4 Introduction to SQL Developer 81
Installing and Configuring SQL Developer 81
Connecting to a Database 84
Exploring Objects 85
Entering Commands 86
Run Statement 87
Run Script 88
Saving Commands to a Script 89
Running a Script 90
Chapter 3: Data Definition, Part I 93
3.1 Schemas and Users 93
3.2 Table Creation 94
3.3 Datatypes 95
3.4 Commands for Creating the Case Tables 97
3.5 The Data Dictionary 99
Chapter 4: Retrieval: The Basics 104
4.1 Overview of the SELECT Command 104
4.2 The SELECT Clause 106
Column Aliases 107
The DISTINCT Keyword 108
Column Expressions 108
The DUAL Table 109
Null Values in Expressions 111
4.3 The WHERE Clause 111
4.4 The ORDER BY Clause 112
4.5 AND, OR, and NOT 115
The OR Operator 115
The AND Operator and Operator Precedence Issues 116
The NOT Operator 117
4.6 BETWEEN, IN, and LIKE 119
The BETWEEN Operator 119
The IN Operator 120
The LIKE Operator 121
4.7 CASE Expressions 122
4.8 Subqueries 125
The Joining Condition 126
When a Subquery Returns Too Many Values 127
Comparison Operators in the Joining Condition 128
When a Single-Row Subquery Returns More Than One Row 129
4.9 Null Values 130
Null Value Display 130
The Nature of Null Values 130
The IS NULL Operator 132
Null Values and the Equality Operator 133
Null Value Pitfalls 134
4.10 Truth Tables 135
4.11 Exercises 137
Chapter 5: Retrieval: Functions 138
5.1 Overview of Functions 138
5.2 Arithmetic Functions 140
5.3 Text Functions 142
5.4 Regular Expressions 146
Regular Expression Operators and Metasymbols 147
Regular Expression Function Syntax 148
Influencing Matching Behavior 148
REGEXP_INSTR Return Value 149
REGEXP_LIKE 149
REGEXP_INSTR 150
REGEXP_SUBSTR 151
REGEXP_REPLACE 151
5.5 Date Functions 152
EXTRACT 153
ROUND and TRUNC 154
MONTHS_BETWEEN and ADD_MONTHS 154
NEXT_DAY and LAST_DAY 155
5.6 General Functions 155
GREATEST and LEAST 156
NVL 157
DECODE 157
5.7 Conversion Functions 158
TO_NUMBER and TO_CHAR 159
Conversion Function Formats 160
Datatype Conversion 162
CAST 162
5.8 Stored Functions 163
5.9 Exercises 164
Chapter 6: Data Manipulation 166
6.1 The INSERT Command 167
Standard INSERT Commands 167
INSERT Using Subqueries 170
6.2 The UPDATE Command 172
6.3 The DELETE Command 175
6.4 The MERGE Command 178
6.5 Transaction Processing 180
6.6 Locking and Read Consistency 181
Locking 181
Read Consistency 182
Chapter 7: Data Definition, Part II 183
7.1 The CREATE TABLE Command 183
7.2 More on Datatypes 185
Character Datatypes 186
Comparison Semantics 186
Column Data Interpretation 187
Numbers Revisited 187
7.3 The ALTER TABLE and RENAME Commands 187
7.4 Constraints 190
Out-of-Line Constraints 190
Inline Constraints 192
Constraint Definitions in the Data Dictionary 193
Case Table Definitions with Constraints 194
A Solution for Foreign Key References: CREATE SCHEMA 196
Deferrable Constraints 197
7.5 Indexes 198
Index Creation 199
Unique Indexes 200
Bitmap Indexes 200
Function-Based Indexes 200
Index Management 201
7.6 Performance Monitoring with SQL Developer AUTOTRACE 202
7.7 Sequences 205
7.8 Synonyms 206
7.9 The CURRENT_SCHEMA Setting 208
7.10 The DROP TABLE Command 209
7.11 The TRUNCATE Command 211
7.12 The COMMENT Command 211
7.13 Exercises 213
Chapter 8: Retrieval: Multiple Tables and Aggregation 214
8.1 Tuple Variables 214
8.2 Joins 216
Cartesian Products 217
Equijoins 217
Non-equijoins 218
Joins of Three or More Tables 219
Self-Joins 220
8.3 The JOIN Clause 221
Natural Joins 222
Equijoins on Columns with the Same Name 223
8.4 Outer Joins 224
Old Oracle-Specific Outer Join Syntax 225
New Outer Join Syntax 226
Outer Joins and Performance 227
8.5 The GROUP BY Component 227
Multiple-Column Grouping 229
GROUP BY and Null Values 229
8.6 Group Functions 230
Group Functions and Duplicate Values 231
Group Functions and Null Values 232
Grouping the Results of a Join 233
The COUNT(*) Function 233
Valid SELECT and GROUP BY Clause Combinations 235
8.7 The HAVING Clause 236
The Difference Between WHERE and HAVING 237
HAVING Clauses Without Group Functions 237
A Classic SQL Mistake 238
Grouping on Additional Columns 239
8.8 Advanced GROUP BY Features 241
GROUP BY ROLLUP 241
GROUP BY CUBE 242
CUBE, ROLLUP, and Null Values 243
The GROUPING Function 243
The GROUPING_ID Function 244
8.9 Partitioned Outer Joins 245
8.10 Set Operators 247
8.11 Exercises 250
Chapter 9: Retrieval: Some Advanced Features 252
9.1 Subqueries Continued 252
The ANY and ALL Operators 253
Defining ANY and ALL 254
Rewriting SQL Statements Containing ANY and ALL 255
Correlated Subqueries 256
The EXISTS Operator 257
Subqueries Following an EXISTS Operator 258
EXISTS, IN, or JOIN? 258
NULLS with NOT EXISTS and NOT IN 261
9.2 Subqueries in the SELECT Clause 262
9.3 Subqueries in the FROM Clause 263
9.4 The WITH Clause 264
9.5 Hierarchical Queries 266
START WITH and CONNECT BY 267
LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF 268
CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH 269
Hierarchical Query Result Sorting 270
9.6 Analytical Functions 271
Partitions 273
Function Processing 276
9.7 Flashback Features 278
AS OF 279
VERSIONS BETWEEN 281
FLASHBACK TABLE 281
9.8 Exercises 283
Chapter 10: Views 284
10.1 What Are Views? 284
10.2 View Creation 285
Creating a View from a Query 286
Getting Information About Views from the Data Dictionary 288
Replacing and Dropping Views 290
10.3 What Can You Do with Views? 290
Simplifying Data Retrieval 290
Maintaining Logical Data Independence 292
Implementing Data Security 293
10.4 Data Manipulation via Views 293
Updatable Join Views 295
Nonupdatable Views 296
The WITH CHECK OPTION Clause 297
Disappearing Updated Rows 297
Inserting Invisible Rows 298
Preventing These Two Scenarios 299
Constraint Checking 299
10.5 Data Manipulation via Inline Views 300
10.6 Views and Performance 301
10.7 Materialized Views 302
Properties of Materialized Views 303
Query Rewrite 303
10.8 Exercises 305
Chapter 11: Writing and Automating SQL*Plus Scripts 306
11.1 SQL*Plus Variables 307
SQL*Plus Substitution Variables 307
SQL*Plus User-Defined Variables 309
Implicit SQL*Plus User-Defined Variables 310
User-Friendly Prompting 311
SQL*Plus System Variables 312
11.2 Bind Variables 317
Bind Variable Declaration 318
Bind Variables in SQL Statements 319
11.3 SQL*Plus Scripts 320
Script Execution 320
Script Parameters 321
SQL*Plus Commands in Scripts 323
The login.sql Script 324
11.4 Report Generation with SQL*Plus 325
The SQL*Plus COLUMN Command 326
The SQL*Plus TTITLE and BTITLE Commands 330
The SQL*Plus BREAK Command 331
The SQL*Plus COMPUTE Command 334
The Finishing Touch: SPOOL 336
11.5 HTML in SQL*Plus 337
HTML in SQL*Plus 337
11.6 Building SQL*Plus Scripts for Automation 340
What Is a SQL*Plus Script? 340
Capturing and Using Input Parameter Values 341
Passing Data Values from One SQL Statement to Another 342
Mechanism 1: The NEW_VALUE Clause 342
Mechanism 2: Bind Variables 343
Handling Error Conditions 344
11.7 Exercises 345
Chapter 12: Object-Relational Features 347
12.1 More Datatypes 347
Collection Datatypes 348
Methods 348
12.2 Varrays 349
Creating the Array 349
Populating the Array with Values 351
Querying Array Columns 352
12.3 Nested Tables 354
Creating Table Types 354
Creating the Nested Table 354
Populating the Nested Table 355
Querying the Nested Table 356
12.4 User-Defined Types 357
Creating User-Defined Types 357
Showing More Information with DESCRIBE 358
12.5 Multiset Operators 359
Which SQL Multiset Operators Are Available? 359
Preparing for the Examples 360
Using IS NOT EMPTY and CARDINALITY 361
Using POWERMULTISET 362
Using MULTISET UNION 363
Converting Arrays into Nested Tables 364
12.6 Exercises 364
Appendix A: The Seven Case Tables 366
ERM Diagram 366
Table Structure Descriptions 367
Columns and Foreign Key Constraints 368
Contents of the Seven Tables 369
Hierarchical Employees Overview 374
Course Offerings Overview 374
Appendix B: Answers to the Exercises 376
Chapter 4 Exercises 376
Chapter 5 Exercises 386
Chapter 7 Exercises 391
Chapter 8 Exercises 393
Chapter 9 Exercises 403
Chapter 10 Exercises 412
Chapter 11 Exercises 414
Chapter 12 Exercises 418
Index 422