Date Functions 60
Conversion Functions 63
Chapter Summary 67
Two-Minute Drill 67
Fill-in-the-Blank Questions 69
Chapter Questions 69
Fill-in-the-Blank Answers 73
Answers to Chapter Questions 73
2 Advanced Data Selection in Oracle 77
Displaying Data from Multiple Tables 78
select Statements That Join Data from More Than One Table 79
Creating Outer Joins 85
Joining a Table to Itself 88
Group Functions and Their Uses 92
Identifying and Using Group Functions 92
Using the group by Clause 96
Excluding group Data with having 102
Using Subqueries 104
Understanding and Defining Subqueries 105
Listing and Writing Different Types of Subqueries 109
Multiple-Column Subqueries 113
Writing Multiple-Column Subqueries 114
NULL Values and Subqueries 116
Subqueries in a from Clause 119
Producing Readable Output with SQL*Plus 122
Entering Variables 123
Customizing SQL*Plus Environments 129
Producing Readable Output 137
Creating and Executing Scripts 142
Saving Customizations 144
Chapter Summary 146
Two-Minute Drill 146
Fill-in-the-Blank Questions 150
Chapter Questions 150
Answers to Chapter Questions 155
Fill-in-the-Blank Answers 155
3 Creating Oracle Database Objects 159
Creating the Tables of an Oracle Database 160
Describing Tables 161
Creating Tables 163
Datatypes and Column Definitions 169
Altering Table Definitions 175
Dropping, Renaming, and Truncating Tables 180
Including Constraints 183
Describing Constraints 184
Creating and Maintaining Constraints 187
The Oracle Data Dictionary 202
Available Dictionary Views 202
Querying the Data Dictionary 209
Manipulating Oracle Data 213
Adding New Rows to a Table 213
Making Changes to Existing Row Data 217
Deleting Data from the Oracle Database 219
The Importance of Transaction Control 220
Chapter Summary 226
Two-Minute Drill 227
Fill-in-the-Blank Questions 230
Chapter Questions 230
Fill-in-the-Blank Answers 235
Answers to Chapter Questions 235
4 Creating Other Database Objects in Oracle 239
Creating Views 240
Creating Simple Views 241
Creating Views That Enforce Constraints 248
Creating Complex Views 252
Modifying and Removing Views 257
Other Database Objects 260
Overview of Other Database Objects 261
Using Sequences 262
Using Indexes 268
Using Public and Private Synonyms 277
User Access Control 280
Creating Users 281
Granting and Revoking Object Privileges 289
Using Roles to Manage Database Access 296
Chapter Summary 302
Two-Minute Drill 303
Fill-in-the-Blank Questions 306
Chapter Questions 306
Answers to Chapter Questions 310
Fill-in-the-Blank Answers 310
5 Introducing PL/SQL 313
Overview of PL/SQL 314
Benefits of PL/SQL 315
Parts of a PL/SQL Block 319
Declaring and Using Variables 327
Executing PL/SQL Code 334
Writing Executable Statements 339
Writing Executable PL/SQL Statements 339
Nesting PL/SQl Blocks 342
Executing and Testing PL/SQL 345
Using Code Conventions 350
Writing Control Structures 352
Constructing an if-then Statement 353
Using Loops 355
Using Logic Tables 361
Using Nested Loops and Labels 364
Two-Minute Drill 368
Chapter Summary 368
Fill-in-the-Blanks 370
Chapter Questions 370
Fill-in-the-Blank Answers 371
Answers to Chapter Questions 371
6 Database Programming with PL/SQL 373
Interacting with the Oracle Database 374
Using select in PL/SQL Code 375
Declaring PL/SQL Variable Types Dynamically 378
Writing DML Statements in PL/SQL 382
Transaction Processing in PL/SQL 385
Determining SQL Statement Outcome 388
Working with Composite Datatypes 391
Creating PL/SQL Records 392
Using %rowtype to Create Records 397
Creating PL/SQL Tables 399
Creating PL/SQL Tables of Records 405
Reviewing PL/SQL Records, Tables, and Tables of Records 408
Using Explicit Cursors 409
Explicit Cursor Handling 409
Using PL/SQL Records to Fetch Data from Cursors 414
Writing cursor for Loops 417
Understanding Advanced Explicit Cursor Concepts 420
Passing Parameters to Explicit Cursors 421
Using the for update and where current of Clauses 423
Using Subqueries in Cursors 426
Error Handling 427
Defining a PL/SQL Exception 428
Trapping Unhandled and Unanticipated Errors 433
Customizing PL/SQL Error Messages 436
Understanding Exception Propagation in Nested Blocks 440
Chapter Summary 444
Two-Minute Drill 444
Fill-in-the-Blanks 446
Chapter Questions 447
Answers to Chapter Questions 451
Fill-in-the-Blank Answers 451
PARTⅡOCP Oracle8i DBA Practice Exams 457
7 OCP Exam I: Introduction to SQL and PL/SQL 457
Practice Exam 1 458
Practice Exam 2 477
Practice Exam 3 500
Answers to Practice Exam 1 517
Answers to Practice Exam 2 530
Answers to Practice Exam 3 544
Index 557