Cover image for OCA/OCP : introduction to Oracle9i SQL : study guide
Title:
OCA/OCP : introduction to Oracle9i SQL : study guide
Author:
Dawes, Chip.
Personal Author:
Edition:
First edition.
Publication Information:
San Francisco, CA : Sybex, [2002]

©2002
Physical Description:
539 pages : illustrations ; 24 cm + 1 CD-ROM (4 3/4 in.)
General Note:
"Exam 1Z0-007"--Cover.
Language:
English
Title Subject:
Added Author:
ISBN:
9780782140620
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Item Holds
Searching...
QA76.3 .D34 2002 Adult Non-Fiction Central Closed Stacks
Searching...

On Order

Summary

Summary

Here's the book you need to prepare for Exam 1Z0-007: Introduction to Oracle9i:
* In-depth coverage of official exam objectives
* Practical information on using SQL in a real-world Oracle9i environment
* Hundreds of challenging review questions, in the book and on the CD
Authoritative coverage of all exam objectives, including:
* Writing basic SQL SELECT statements
* Restricting and sorting data
* Displaying data from multiple tables
* Aggregating data using group functions
* Producing readable output with iSQL*Plus
* Creating and managing tables
* Controlling user access


Author Notes

Chip Dawes is an OCP and has worked with Oracle databases since 1990. He is a consultant with D&D Technologies and frequently presents at various Oracle User Group conferences.

Biju Thomas , OCP, has over 7 years of Oracle database management and application development experience. He currently works as a Database Administrator Consultant for Burlington Resources in Fort Worth, TX. Both authors contributed to OCP: Oracle8i DBA SQL Study Guide .


Table of Contents

Introductionp. xxi
Assessment Testp. xxxi
Chapter 1 Basic SQL Select Statementsp. 1
SQL Fundamentalsp. 2
Oracle Datatypesp. 4
Operators and Literalsp. 9
Writing Simple Queriesp. 13
Using the Select Statementp. 13
Limiting Rowsp. 19
Other Operatorsp. 26
In and Not Inp. 26
Betweenp. 27
Existsp. 27
Is Null and is Not Nullp. 28
Likep. 28
Sorting Rowsp. 30
Sorting Nullsp. 33
Using Expressionsp. 36
The Case Expressionp. 36
Chapter 2 SQL * Plus Overviewp. 53
SQL * Plus Fundamentalsp. 54
Connecting to SQL * Plusp. 55
Using SQL * Plusp. 57
Using Script Filesp. 63
Customizing the SQL * Plus Environmentp. 66
Producing More Readable Outputp. 74
Setting Page and Line Sizesp. 76
Formatting Columnsp. 76
Suppressing Duplicate Valuesp. 77
Adding Headers and Footersp. 78
Clearing Formattingp. 81
Using a Script File to Create a Reportp. 82
Performing Summary Operationsp. 84
Accepting Values at Runtimep. 87
Using Substitution Variablesp. 88
Saving a Variable for a Sessionp. 89
Using Positional Notation for Variablesp. 91
Using the Accept Commandp. 92
Using iSQL * Plusp. 93
iSQL * Plus Architecturep. 93
Working with iSQL * Plusp. 94
iSQL * Plus Restrictionsp. 96
Chapter 3 Single-Row Functionsp. 111
Single-Row Function Fundamentalsp. 112
Nulls and Single-Row Functionsp. 113
Null Value Functionsp. 113
Using Single-Row Character Functionsp. 115
Character Function Overviewp. 116
Character Function Descriptionsp. 117
Using Single-Row Numeric Functionsp. 129
Numeric Function Overviewp. 129
Numeric Function Descriptionsp. 130
Using Single-Row Date Functionsp. 138
Date Format Conversionp. 138
Date Function Overviewp. 139
Date Function Descriptionsp. 140
Using Single-Row Conversion Functionsp. 149
Conversion Function Overviewp. 149
Conversion Function Descriptionsp. 150
Using Other Single-Row Functionsp. 164
Miscellaneous Function Overviewp. 164
Miscellaneous Function Descriptionsp. 165
Chapter 4 Aggregating Data And Group Functionsp. 189
Group Function Fundamentalsp. 190
Using Group Functionsp. 192
Group Function Overviewp. 192
Group Function Descriptionsp. 194
Grouping Data with Group Byp. 218
Limiting Grouped Data with Havingp. 220
Creating Superaggregates with Cube and Rollupp. 221
Nesting Functionsp. 224
Chapter 5 Joins and Subqueriesp. 241
Multiple-Table Queriesp. 242
Simple Joinsp. 243
Complex Joinsp. 244
Multi-Table Joinsp. 249
Cartesian Joinsp. 252
Outer Joinsp. 254
Other Multiple-Table Queriesp. 258
Subqueriesp. 264
Single-Row Subqueriesp. 265
Multiple-Row Subqueriesp. 266
Correlated Subqueriesp. 266
Scalar Subqueriesp. 268
Multiple-Column Subqueriesp. 271
Subqueries in Other DML Statementsp. 272
Chapter 6 Modifying Datap. 289
Using DML Statementsp. 290
Inserting Rows into a Tablep. 291
Inserting into Multiple Tablesp. 292
Updating Rows in a Tablep. 296
Merging Rows into a Tablep. 297
Deleting Rows from a Tablep. 299
Truncating a Tablep. 300
Truncate versus Deletep. 301
Truncate versus Drop Tablep. 302
Selecting Rows for Updatep. 303
Locking a Tablep. 303
Deadlocksp. 304
DML Locks in Oraclep. 305
Understanding Transaction Controlp. 309
Savepoints and Partial Rollbacksp. 311
Consistency and Transactionsp. 312
Enabling Transaction-Level or Statement-Level Consistencyp. 313
Specifying a Rollback Segment for a Transactionp. 314
Chapter 7 Managing Tables and Constraintsp. 333
Database Objects Reviewp. 334
Built-in Datatypesp. 335
Character Datatypesp. 336
Numeric Datatypep. 339
Date and Time Datatypesp. 340
Binary Datatypesp. 346
Row ID Datatypesp. 347
Creating Tablesp. 348
Naming Tables and Columnsp. 349
Specifing Default Values for Columnsp. 351
Adding Commentsp. 353
Creating a Table from Another Tablep. 354
Modifying Tablesp. 356
Adding Columnsp. 356
Modifying Columnsp. 358
Dropping Columnsp. 359
Dropping Tablesp. 361
Renaming Tablesp. 361
Managing Constraintsp. 362
Creating Constraintsp. 363
Not Null Constraintp. 363
Check Constraintsp. 364
Unique Constraintsp. 365
Primary Key Constraintsp. 366
Foreign Key Constraintsp. 367
Disabled Constraintsp. 368
Dropping Constraintsp. 369
Enabling and Disabling Constraintsp. 369
Validated Constraintsp. 370
Deferring Constraint Checksp. 371
Chapter 8 Managing Viewsp. 389
Creating and Modifying Viewsp. 390
Using Defined Column Namesp. 392
Creating Views with Errorsp. 393
Creating Read-Only Viewsp. 394
Creating Constraints on Viewsp. 395
Modifying Viewsp. 395
Dropping a Viewp. 397
Using Viewsp. 398
Using Views in Queriesp. 398
Inserting, Updating, and Deleting Data through Viewsp. 400
Using Join Viewsp. 403
Viewing Allowable DML Operationsp. 405
Using Inline Viewsp. 406
Performing Top-'N' Analysisp. 409
Chapter 9 Other Database Objectsp. 421
Creating and Managing Sequencesp. 422
Creating and Dropping Sequencesp. 423
Using Sequencesp. 424
Altering Sequencesp. 428
Creating and Managing Synonymsp. 429
Creating and Dropping Synonymsp. 430
Resolving Object Referencesp. 432
Creating and Managing Indexesp. 434
How Indexes Workp. 434
Using B-Tree Indexesp. 435
Using Bitmap Indexesp. 436
Calling Stored Programsp. 439
Using Procedures and Functionsp. 440
Using Packagesp. 441
Chapter 10 User Access and Securityp. 455
Creating and Modifying User Accountsp. 456
Configuring Account Authenticationp. 456
Assigning Attributes to Accountsp. 458
Using Account Licensing Controlsp. 461
Creating and Using Rolesp. 463
Granting and Revoking Privilegesp. 464
Object Privilegesp. 465
System Privilegesp. 468
Assigning Privilegesp. 473
Revoking Privilegesp. 479
Viewing Privileges in the Data Dictionaryp. 480
Managing User Groups with Profilesp. 482
Enabling Resource Settingsp. 482
Creating and Altering Profilesp. 483
Glossaryp. 503
Indexp. 513