Mastering Oracle SQL and SQL*Plus (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).

Contents 5
Foreword 9
About the Author 10
About the Technical Reviewers 11
Acknowledgments 12
Introduction 13
Chapter 1 Relational Database Systems and Oracle 17
1.1 Information Needs and Information Systems 17
1.2 Database Design 18
Entities and Attri 19
Generic vs. Specific 20
Redundancy 20
Consistency, Integrity, and Integrity Constraints 21
Data Modeling Approach,Methods, and Techniques 22
Semantics 23
Information Systems Terms Review 23
1.3 Database Management Systems 24
DBMS Components 25
Database Applications 26
DBMS Terms Review 26
1.4 Relational Database Management Systems 26
1.5 Relational Data Structures 27
Tables, Columns, and Rows 27
The Information Principle 28
Datatypes 28
Keys 29
Missing Information and Null Values 29
Constraint Checking 30
Predicates and Propositions 30
Relational Data Structure Terms Review 30
1.6 Relational Operators 31
1.7 How Relational Is My DBMS? 32
1.8 The Oracle Software Environment 34
1.9 Case Tables 35
The ERM Diagram of the Case 35
Chapter 2 Introduction to SQL, iSQL*Plus,and SQL*Plus 41
2.1 Overview of SQL 41
Data Definition 42
Data Manipulation and Transactions 42
Retrieval 43
Security 45
2.2 Basic SQL Concepts and Terminology 48
Constants (Literals) 48
Variables 50
Operators,Operands, Conditions, and Expressions 50
Functions 53
Database Object Naming 53
Comments 54
Reserved Words 54
2.3 Introduction to iSQL*Plus 55
2.4 Introduction to SQL*Plus 59
Entering Commands 60
Using the SQL Buffer 61
Using an External Editor 62
Using the SQL*Plus Editor 63
Saving Commands 70
Running SQL*Plus Scripts 71
Adjusting SQL*Plus Settings 73
Describing Database Objects 78
Executing Commands from the Operating System 79
Clearing the Buffer and the Screen 79
SQL*Plus Command Review 79
Chapter 3 Data Definition, Part I 81
3.1 Schemas and Users 81
3.2 Table Creation 82
3.3 Datatypes 83
3.4 Commands for Creating the Case Tables 85
3.5 The Data Dictionary 87
Chapter 4 Retrieval: The Basics 92
4.1 Overview of the SELECT Command 92
4.2 The SELECT Clause 94
Column Aliases 95
The DISTINCT Keyword 96
Column Expressions 97
4.3 The WHERE Clause 100
4.4 The ORDER BY Clause 101
4.5 AND, OR, and NOT 104
The OR Operator 104
The AND Operator and Operator Precedence Issues 105
The NOT Operator 106
4.6 BETWEEN, IN, and LIKE 108
The BETWEEN Operator 108
The IN Operator 109
The LIKE Operator 110
4.7 CASE Expressions 112
4.8 Subqueries 115
The Joining Condition 116
When a Subquery Returns Too Many Values 117
Comparison Operators in the Joining Condition 118
When a Single-Row Subquery Returns More Than One Row 119
4.9 Null Values 120
Null Value Display 120
The Nature of Null Values 121
The IS NULL Operator 122
Null Values and the Equality Operator 123
Null Value Pitfalls 124
4.10 Truth Tables 125
4.11 Exercises 126
Chapter 5 Retrieval: Functions 128
5.1 Overview of Functions 128
5.2 Arithmetic Functions 130
5.3 Text Functions 132
5.4 Regular Expressions 136
Regular Expression Operators and Metasymbols 137
Regular Expression Function Syntax 138
REGEXP_LIKE 139
REGEXP_INSTR 140
REGEXP_SUBST 141
REGEXP_REPLACE 141
5.5 Date Functions 142
EXTRACT 143
ROUND and TRUNC 143
MONTHS_BETWEEN and ADD_MONTHS 144
NEXT_DAY and LAST_DAY 144
5.6 General Functions 145
GREATEST and LEAST 146
NVL 146
DECODE 147
5.7 Conversion Functions 147
TO_NUMBER and TO_CHAR 148
Conversion Function Formats 149
Datatype Conversion 151
5.8 Stored Functions 152
5.9 Exercises 154
Chapter 6 Data Manipulation 155
6.1 The INSERT Command 155
Standard INSERT Commands 155
Multitable INSERT Commands 158
6.2 The UPDATE Command 159
6.3 The DELETE Command 161
6.4 The MERGE Command 163
6.5 Transaction Processing 165
The SQL*Plus AUTOCOMMIT Option 166
Transaction Design 167
Savepoints 167
6.6 Locking and Read Consistency 168
Locking 169
Read Consistency 169
Chapter 7 Data Definition, Part II 172
7.1 The CREATE TABLE Command 172
7.2 More on Datatypes 174
Character Datatypes 175
Numbers Revisited 176
7.3 The ALTER TABLE and RENAME Commands 176
7.4 Constraints 179
Out-of-Line Constraints 179
Inline Constraints 181
Constraint Definitions in the Data Dictionary 182
Case Table Definitions with Constraints 183
A Solution for Foreign Key References: CREATE SCHEMA 185
Deferrable Constraints 186
7.5 Indexes 187
7.6 Performance Monitoring with SQL*Plus AUTOTRACE 191
7.7 Sequences 194
7.8 Synonyms 196
7.9 The CURRENT_SCHEMA Setting 198
7.10 The DROP TABLE Command 199
7.11 The TRUNCATE Command 201
7.12 The COMMENT Command 201
7.13 Exercises 202
Chapter 8 Retrieval: Multiple Tables and Aggregation 204
8.1 Tuple Variables 205
8.2 Joins 207
Cartesian Products 207
Equijoin 208
Non-equijoins 209
Joins of Three or More Tables 210
Self-Joins 211
8.3 Alternative ANSI/ISO Standard Join Syntax 212
Natural Joins 213
Equijoins on Columns with the Same Name 214
8.4 Outer Joins 215
New Outer Join Syntax 217
Outer Joins and Performance 218
8.5 The GROUP BY Component 219
Multiple-Column Grouping 220
GROUP BY and Null Values 221
8.6 Group Functions 222
Group Functions and Duplicate Values 223
Group Functions and Null Values 223
Grouping the Results of a Join 225
The COUNT(*) Function 225
Valid SELECT and GROUP BY Clause Combinations 227
8.7 The HAVING Clause 228
HAVING Clauses Without Group Functions 229
A Classic SQL Mistake 230
Grouping on Additional Columns 231
8.8 Advanced GROUP BY Features 233
GROUP BY ROLLUP 234
GROUP BY CUBE 234
CUBE, ROLLUP, and Null Values 235
8.9 Partitioned Outer Joins 238
8.10 Set Operators 240
8.11 Exercises 244
Chapter 9 Retrieval: Some Advanced Features 245
9.1 Subqueries Continued 245
The ANY and ALL Operators 247
Correlated Subqueries 249
The EXISTS Operator 250
9.2 Subqueries in the SELECT Clause 253
9.3 Subqueries in the FROM Clause 254
9.4 The WITH Clause 256
9.5 Hierarchical Queries 257
START WITH and CONNECT BY 258
LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF 260
CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH 261
Hierarchical Query Result Sorting 262
9.6 Analytical Functions and Windows 263
Analytical Window Specification 264
Analytical Window Ordering 265
Partitioned Analytical Windows 267
9.7 Flashback Features 269
9.8 Exercises 274
Chapter 10 Views 276
10.1 What Are Views? 276
10.2 View Creation 277
Creating a View from a Query 279
Getting Information About Views from the Data Dictionary 280
Replacing and Dropping Views 282
10.3 What Can You Do with Views? 282
Simplifying Data Retrieval 282
Maintaining Logical Data Independence 285
Implementing Data Security 285
10.4 Data Manipulation via Views 285
Updatable Join Views 287
Nonupdatable Views 288
The WITH CHECK OPTION Clause 290
10.5 Data Manipulation via Inline Views 293
10.6 Views and Performance 294
10.7 Materialized Views 295
Properties of Materialized Views 296
Query Rewrite 296
10.8 Exercises 297
Chapter 11 SQL*Plus and iSQL*Plus 299
11.1 SQL*Plus Variables 300
SQL*Plus Substitution Variables 300
SQL*Plus User-Defined Variables 302
SQL*Plus System Variables 305
11.2 Bind Variables 310
Bind Variable Declaration 311
Bind Variables in SQL Statements 312
11.3 SQL*Plus Scripts 313
Script Execution 313
Script Parameters 315
SQL*Plus Commands in Scripts 316
The login.sql Script 318
11.4 Report Generation with SQL*Plus 318
The SQL*Plus COLUMN Command 319
The SQL*Plus TTITLE and BTITLE Commands 323
The SQL*Plus BREAK Command 324
The SQL*Plus COMPUTE Command 327
The Finishing Touch: SPOOL 329
11.5 HTML in SQL*Plus and iSQL*Plus 330
HTML in SQL*Plus 330
HTML in iSQL*Plus 333
11.6 Exercises 335
Chapter 12 Object-Relational Features 336
12.1 More Datatypes 336
Collection Datatypes 337
Methods 337
12.2 Varrays 338
Creating the Array 338
Populating the Array with Values 340
Querying Array Columns 341
12.3 Nested Tables 343
Creating Table Types 343
Creating the Nested Table 344
Populating the Nested Table 345
Querying the Nested Table 346
12.4 User-Defined Types 347
Creating User-Defined Types 347
Showing More Information with DESCRIBE 348
12.5 Multiset Operators 349
Which SQL Multiset Operators Are Available? 349
Preparing for the Examples 350
Using IS NOT EMPTY and CARDINALITY 352
Using POWERMULTISET 352
Using MULTISET UNION 354
Converting Arrays into Nested Tables 354
12.6 Exercises 355
APPENDIX A Quick Reference to SQL and SQL*Plus 357
Syntax Conventions Used in This Appendix 358
Starting and Stopping 359
Entering and Executing Commands 359
Working With the SQL*Plus Editor 360
Manipulating SQL*Plus Scripts 361
SQL*Plus Interactivity Commands 361
Variables and Parameters 362
Formatting Query Results 363
SQL: Data Manipulation (DML), Transactions, and Queries 366
SQL: Data Definition (DDL) 367
SQL: Other Commands 369
SQL: Operators 370
SQL: Functions 371
SQL: Regular Expressions 377
Rules for Naming Oracle Database Objects 378
SQL: Reserved Words 379
APPENDIX B Data Dictionary Overview 380
General Data Dictionary Views 381
ALL Views: Information About Accessible Objects 381
USER Views: Information About Your Own Data 382
DBA Views: Full Database Information 384
V$ Views: Dynamic Performance Views 385
APPENDIX C The Seven Case Tables 387
ERM Diagram 388
Table Structure Descriptions 389
Columns and Foreign Key Constraints 390
Contents of the Seven Tables 391
Hierarchical Employees Overview 396
Course Offerings Overview 397
APPENDIX D Answers to the Exercises 398
Chapter 4 Exercises 399
Chapter 5 Exercises 409
Chapter 7 Exercises 414
Chapter 8 Exercises 416
Chapter 9 Exercises 427
Chapter 10 Exercises 437
Chapter 11 Exercises 439
Chapter 12 Exercises 443
APPENDIX E Oracle Documentation, Web Sites, and Bibliography 448
Oracle Documentation 448
Oracle Web Sites 451
Bibliography 452
Index 453