®
OCA Oracle Database 11g:
SQL Fundamentals I
Exam Guide (Exam 1Z0-051)
ABOUT THE AUTHORS
John Watson (Oxford, UK) works for BPLC Management Consultants, teaching
and consulting throughout Europe and Africa. He was with Oracle University for
several years in South Africa, and before that worked for a number of companies,
government departments, and NGOs in England and Europe. He is OCP qualified in
both database and Application Server administration. John is the author of several
books and numerous articles on technology and has 25 years of experience in IT.
Roopesh Ramklass (South Africa), OCP, is an independent Oracle specialist with
over 10 years of experience in a wide variety of IT environments. These include
software design and development, systems analysis, courseware development, and
lecturing. He has worked for Oracle Support and taught at Oracle University in
South Africa for several years. Roopesh is experienced in managing and executing
IT development projects, including infrastructure systems provisioning, software
development, and systems integration.
About the Technical Editor
Bruce Swart (South Africa) works for 2Cana Solutions and has over 14 years of
experience in IT. Whilst maintaining a keen interest for teaching others, he has
performed several roles including developer, analyst, team leader, administrator,
project manager, consultant, and lecturer. He is OCP qualified in both database and
developer roles. He has taught at Oracle University in South Africa for several years
and has also spoken at numerous local Oracle User Group conferences. His passion
is helping others achieve greatness.
Copyright © 2008 by The McGraw-Hill Companies, Inc. Click here for terms of use.
®
OCA Oracle Database 11g:
SQL Fundamentals I
Exam Guide (Exam 1Z0-051)
John Watson
Roopesh Ramklass
This publication and CD may be used in assisting students to prepare for the OCP
Oracle Database 11g: SQL Fundamentals I exam. Neither Oracle Corporation nor
The McGraw-Hill Companies warrant that use of this publication and CD will ensure
passing the relevant exam.
New York Chicago San Francisco Lisbon London Madrid
Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2008 by The McGraw-Hill Companies, Inc. All rights reserved. Manufactured in the United States of America. Except as
permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or
by any means, or stored in a database or retrieval system, without the prior written permission of the publisher.
0-07-164380-X
The material in this eBook also appears in the print version of this title: 0-07-159786-7.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked
name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the
trademark. Where such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate
training programs. For more information, please contact George Hoare, Special Sales, at
[email protected] or (212)
904-4069.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the
work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve
one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon,
transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use
the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may
be terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS
TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK,
INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE,
AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED
WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not
warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or
error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless
of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information
accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental,
special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been
advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim
or cause arises in contract, tort or otherwise.
DOI: 10.1036/0071597867
With thanks to Silvia for making life worth living.
—John
Ameetha, you have been wonderfully supportive and I want to
thank you for every moment that you share with me.
—Roopesh
This page intentionally left blank
CONTENTS AT A GLANCE
1 Oracle Server Technologies and the Relational Paradigm .......... 1
2 Data Retrieval Using the SQL SELECT Statement ............... 51
3 Restricting and Sorting Data ................................. 103
4 Single-Row Functions ...................................... 169
5 Using Conversion Functions and Conditional Expressions ......... 227
6 Reporting Aggregated Data Using the Group Functions ........... 273
7 Displaying Data from Multiple Tables .......................... 309
8 Using Subqueries to Solve Problems ........................... 357
9 Using the Set Operators ..................................... 381
10 Manipulating Data ......................................... 403
11 Using DDL Statements to Create and Manage Tables ............. 449
12 Creating Other Schema Objects .............................. 487
Appendix ................................................ 533
Glossary ................................................. 537
Index .................................................... 555
vii
This page intentionally left blank
For more information about this title, click here
CONTENTS
Introduction ........................................ xix
1 Oracle Server Technologies and
the Relational Paradigm . . . . . . . . . . . . . . . . . . . . . . . . 1
Position the Server Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
The Oracle Server Architecture . . . . . . . . . . . . . . . . . . . . . . . 3
The Oracle Application Server . . . . . . . . . . . . . . . . . . . . . . . . 5
Oracle Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Grid Computing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Exercise 1-1: Investigate Your Database and
Application Environment . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Development Tools and Languages . . . . . . . . . . . . . . . . . . . . . 9
Understand Relational Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Rows and Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Data Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Exercise 1-2: Perform an Extended
Relational Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Summarize the SQL Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
SQL Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
A Set-oriented Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Use the Client Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
SQL Developer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Create the Demonstration Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Users and Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
The HR and OE Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Demonstration Schema Creation . . . . . . . . . . . . . . . . . . . . . . 42
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
ix
x OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
2 Data Retrieval Using the SQL SELECT Statement ... 51
List the Capabilities of SQL SELECT Statements . . . . . . . . . . . . . . . . 52
Introducing the SQL SELECT Statement . . . . . . . . . . . . . . . . 52
The DESCRIBE Table Command . . . . . . . . . . . . . . . . . . . . . . 53
Exercise 2-1: Describing the Human
Resources Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Capabilities of the SELECT Statement . . . . . . . . . . . . . . . . . . 57
Execute a Basic SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Syntax of the Primitive SELECT Statement . . . . . . . . . . . . . . 59
Rules Are Meant to be Followed . . . . . . . . . . . . . . . . . . . . . . . 64
Exercise 2-2: Answering Our First
Questions with SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
SQL Expressions and Operators . . . . . . . . . . . . . . . . . . . . . . . . 70
NULL Is Nothing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Exercise 2-3: Experimenting with
Expressions and the DUAL Table . . . . . . . . . . . . . . . . . . . . 86
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
3 Restricting and Sorting Data . . . . . . . . . . . . . . . . . . . . . 103
Limit the Rows Retrieved by a Query . . . . . . . . . . . . . . . . . . . . . . . . . . 104
The WHERE clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Exercise 3-1: Using the LIKE Operator . . . . . . . . . . . . . . . 124
Boolean Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Precedence Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Sort the Rows Retrieved by a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
The ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Exercise 3-2: Sorting Data Using
the ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Contents xi
Ampersand Substitution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Substitution Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Define and Verify . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Exercise 3-3: Using Ampersand Substitution . . . . . . . . . . . 155
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
4 Single-Row Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Describe Various Types of Functions Available in SQL . . . . . . . . . . . . 170
Defining a Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Types of Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Use Character, Number, and Date Functions
in SELECT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Using Character Case Conversion Functions . . . . . . . . . . . . . 177
Exercise 4-1: Using the Case Conversion Functions . . . . . 181
Using Character Manipulations Functions . . . . . . . . . . . . . . . 183
Exercise 4-2: Using the Case Manipulation Functions . . . 194
Using Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Working with Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
Using Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Exercise 4-3: Using the Date Functions . . . . . . . . . . . . . . . 210
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
5 Using Conversion Functions and
Conditional Expressions . . . . . . . . . . . . . . . . . . . . . . . 227
Describe Various Types of Conversion
Functions Available in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Use the TO_CHAR, TO_NUMBER,
and TO_DATE Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . 231
Using the Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . 232
xii OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)
Exercise 5-1: Converting Dates into Characters
Using the TO_CHAR Function . . . . . . . . . . . . . . . . . . . . . 239
Apply Conditional Expressions in a SELECT Statement . . . . . . . . . . . 245
Nesting Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Exercise 5-2: Using NULLIF and NVL2
for Simple Conditional Logic . . . . . . . . . . . . . . . . . . . . . . . 251
Conditional Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Exercise 5-3: Using the DECODE Function . . . . . . . . . . . 260
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
6 Reporting Aggregated Data Using
the Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Describe the Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Definition of Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . 274
Types and Syntax of Group Functions . . . . . . . . . . . . . . . . . . . 275
Identify the Available Group Functions . . . . . . . . . . . . . . . . . . . . . . . . 279
Using the Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
Exercise 6-1: Using the Group Functions . . . . . . . . . . . . . 284
Nested Group Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Group Data Using the GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . 287
Creating Groups of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
The GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Grouping by Multiple Columns . . . . . . . . . . . . . . . . . . . . . . . . 291
Exercise 6-2: Grouping Data Based
on Multiple Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Include or Exclude Grouped Rows Using the HAVING Clause . . . . . . 294
Restricting Group Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
The HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Exercise 6-3: Using the HAVING Clause . . . . . . . . . . . . . 298
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Contents xiii
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
7 Displaying Data from Multiple Tables . . . . . . . . . . . . . . 309
Write SELECT Statements to Access Data from
More Than One Table Using Equijoins and Nonequijoins . . . . . . . . 310
Types of Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Joining Tables Using SQL:1999 Syntax . . . . . . . . . . . . . . . . . . 316
Qualifying Ambiguous Column Names . . . . . . . . . . . . . . . . . . 317
The NATURAL JOIN Clause . . . . . . . . . . . . . . . . . . . . . . . . . 319
Exercise 7-1: Using the NATURAL JOIN . . . . . . . . . . . . 320
The Natural JOIN USING Clause . . . . . . . . . . . . . . . . . . . . . 321
The Natural JOIN ON Clause . . . . . . . . . . . . . . . . . . . . . . . . . 322
Exercise 7-2: Using the NATURAL
JOIN…ON Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
N-Way Joins and Additional Join Conditions . . . . . . . . . . . . . 327
Nonequijoins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Join a Table to Itself Using a Self-Join . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Joining a Table to Itself Using the JOIN…ON Clause . . . . . . 331
Exercise 7-3: Performing a Self-Join . . . . . . . . . . . . . . . . . . 332
View Data that Does Not Meet a Join Condition
by Using Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Inner versus Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
Left Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Right Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Full Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Exercise 7-4: Performing an Outer-Join . . . . . . . . . . . . . . . 340
Generate a Cartesian Product of Two or More Tables . . . . . . . . . . . . . . 342
Creating Cartesian Products Using Cross Joins . . . . . . . . . . . . 342
Exercise 7-5: Performing a Cross-Join . . . . . . . . . . . . . . . . 344
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
xiv OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)
8 Using Subqueries to Solve Problems . . . . . . . . . . . . . . 357
Define Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Exercise 8-1: Types of Subquery . . . . . . . . . . . . . . . . . . . . . 359
Describe the Types of Problems That the Subqueries Can Solve . . . . . 360
Use of a Subquery Result Set for Comparison Purposes . . . . . 360
Star Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
Generate a Table from Which to SELECT . . . . . . . . . . . . . . . 362
Generate Values for Projection . . . . . . . . . . . . . . . . . . . . . . . . 362
Generate Rows to be Passed to a DML Statement . . . . . . . . . 363
Exercise 8-2: More Complex Subqueries . . . . . . . . . . . . . . 363
List the Types of Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
Single- and Multiple-Row Subqueries . . . . . . . . . . . . . . . . . . . 365
Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Exercise 8-3: Investigate the Different
Types of Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Write Single-Row and Multiple-Row Subqueries . . . . . . . . . . . . . . . . . 369
Exercise 8-4: Write a Query That Is Reliable
and User Friendly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
9 Using the Set Operators . . . . . . . . . . . . . . . . . . . . . . . . 381
Describe the Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
Sets and Venn Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Set Operator General Principles . . . . . . . . . . . . . . . . . . . . . . . 384
Exercise 9-1: Describe the Set Operators . . . . . . . . . . . . . . 385
Use a Set Operator to Combine Multiple Queries
into a Single Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
The UNION ALL Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
The UNION Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
The INTERSECT Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . 388
The MINUS Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389
More Complex Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
Exercise 9-2: Using the Set Operators . . . . . . . . . . . . . . . . 392
Contents xv
Control the Order of Rows Returned . . . . . . . . . . . . . . . . . . . . . . . . . . 393
Exercise 9-3: Control the Order of Rows Returned ..... 394
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
10 Manipulating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
Describe Each Data Manipulation Language (DML) Statement . . . . . 404
INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
MERGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
TRUNCATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
DML Statement Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Insert Rows into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Exercise 10-1: Use the INSERT Command . . . . . . . . . . . . 417
Update Rows in a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Exercise 10-2: Use the UPDATE Command . . . . . . . . . . . 421
Delete Rows from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
Removing Rows with DELETE . . . . . . . . . . . . . . . . . . . . . . . . 423
Exercise 10-3: Use the DELETE Command . . . . . . . . . . . 424
Removing Rows with TRUNCATE . . . . . . . . . . . . . . . . . . . . . 425
MERGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Control Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
Database Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
The Transaction Control Statements . . . . . . . . . . . . . . . . . . . 431
Exercise 10-4: Use the COMMIT and
ROLLBACK Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
xvi OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)
11 Using DDL Statements to
Create and Manage Tables . . . . . . . . . . . . . . . . . . . . . 449
Categorize the Main Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . 450
Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Users and Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
Naming Schema Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Object Namespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Exercise 11-1: Determine What Objects
Are Accessible to Your Session . . . . . . . . . . . . . . . . . . . . . . 455
Review the Table Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456
Exercise 11-2: Investigate Table Structures . . . . . . . . . . . . 457
List the Data Types That Are Available for Columns . . . . . . . . . . . . . . 457
Exercise 11-3: Investigate the Data Types
in the HR schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
Create a Simple Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462
Creating Tables with Column Specifications . . . . . . . . . . . . . 462
Creating Tables from Subqueries . . . . . . . . . . . . . . . . . . . . . . . 464
Altering Table Definitions after Creation . . . . . . . . . . . . . . . . 465
Dropping and Truncating Tables . . . . . . . . . . . . . . . . . . . . . . . 466
Exercise 11-4: Create Tables . . . . . . . . . . . . . . . . . . . . . . . 467
Explain How Constraints Are Created
at the Time of Table Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469
The Types of Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Defining Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Exercise 11-5: Work with Constraints . . . . . . . . . . . . . . . . 476
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484
12 Creating Other Schema Objects . . . . . . . . . . . . . . . . . . 487
Create Simple and Complex Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488
Why Use Views at All? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489
Simple and Complex Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
CREATE VIEW, ALTER VIEW, and DROP VIEW . . . . . . . 493
Exercise 12-1: Create Views . . . . . . . . . . . . . . . . . . . . . . . . 496
Contents xvii
Retrieve Data from Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Exercise 12-2: Use Views . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Create Private and Public Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . 498
Exercise 12-3: Create and Use Synonyms . . . . . . . . . . . . . 500
Create, Maintain, and Use Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . 501
Creating Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502
Using Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504
Exercise 12-4: Create and Use Sequences . . . . . . . . . . . . . 507
Create and Maintain Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
What Indexes Are For . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510
Types of Index ..................................... 511
Creating and Using Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . 515
Modifying and Dropping Indexes . . . . . . . . . . . . . . . . . . . . . . . 517
Exercise 12-5: Creating Indexes . . . . . . . . . . . . . . . . . . . . . 518
✓ Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521
Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523
Lab Question . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
Lab Answer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
Appendix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
This page intentionally left blank
INTRODUCTION
T here is an ever-increasing demand for staff with IT industry certification. The benefits
to employers are significant—they can be certain that staff have a certain level of
competence—and the benefits to the individuals, in terms of demand for their services,
are equally great. Many employers are now requiring technical staff to have certifications, and many
IT purchasers will not buy from firms that do not have certified staff. The Oracle certifications
are among the most sought after. But apart from rewards in a business sense, knowing that you
are among a relatively small pool of elite Oracle professionals and that you have proved your
competence is a personal reward well worth attaining.
There are several Oracle certification tracks—this book is concerned with the
Oracle Database Administration certification track, specifically for release 11g of the
database. There are three levels of DBA certification: Certified Associate (OCA),
Certified Professional (OCP), and Certified Master (OCM). The OCA qualification
is based on two examinations, the first of which is covered in this book. The OCP
qualification requires passing a third examination. These examinations can be taken
at any Prometric Center and consist of 60 to 70 questions to be completed in 90
minutes. The OCM qualification requires completing a further two-day evaluation
at an Oracle testing center, involving simulations of complex environments and use
of advanced techniques.
The exam content is also likely to be the starting point if you intend to study for
the Oracle PL/SQL and Oracle Forms Developer track, though (as of the time of
writing) this is at release 10g with no announcement of an 11g release.
To prepare for the first OCA examination, you can attend an Oracle University
instructor-led training course, you can study Oracle University online learning
material, or you can read this book. In all cases, you should also refer to the Oracle
Documentation Library for details on syntax. This book will be a valuable addition
to other study methods, but it is also sufficient by itself. It has been designed
with the examination objectives in mind, though it also includes a great deal
of information that will be useful to you in the course of your work. For readers
working in development, the subject matter of this book is also the starting point for
studying Oracle Corporation’s development tools: SQL, PL/SQL, and the Internet
application development kits shipped with the Oracle Application Server.
xix
Copyright © 2008 by The McGraw-Hill Companies, Inc. Click here for terms of use.
Copyright by webtailieu.net