OCA Oracle database 11g : SQL fundamentals I : a real world certification guide

461 Pages·2011·29.294 MB·English

by  Steve Ries

OCA Oracle Database 11g: SQL
Fundamentals I: A Real-World
Certification Guide
Ace the 1Z0-051 SQL Fundamentals I exam, and
become a successful DBA by learning how SQL
concepts work in the real world
Steve Ries
BIRMINGHAM - MUMBAI
OCA Oracle Database 11g: SQL Fundamentals I:
A Real-World Certification Guide
Copyright © 2011 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: November 2011
Production Reference: 1171111
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-364-7
www.packtpub.com
Cover Image by Sandeep Babu ([email protected])
Credits
Author Project Coordinator
Steve Ries Leena Purkait
Reviewers Proofreader
Dmitri Levin Jonathan Todd
Claire Rajan
Indexer
Monica Ajmera Mehta
Acquisition Editor
Amey Kanse
Graphics
Valentina D'silva
Development Editors
Pallavi Iyengar Manu Joseph
Meeta Rajani
Production Coordinator
Melwyn D'sa
Technical Editors
Apoorva Bolar
Arun Nadar Cover Work
Melwyn D'sa
Naheed Shaikh
Copy Editor
Brandt D'Mello
About the Author
Steve Ries has been an Oracle DBA for 15 years, specializing in all aspects of
database administration, including security, performance tuning, and backup and
recovery. He is a specialist in Oracle Real Application Clusters (RAC) and has
administered Oracle clustered environments in every version of Oracle since the
creation of Oracle Parallel Server. He holds five Oracle certifications as well as the
Security+ certification. He currently consults for the Dept of Defense, U.S. Marine
Corps, and holds a high-level security clearance. Additionally, Steve has been an
adjunct instructor of Oracle technologies at Johnson County Community College for
eight years where he teaches classes that prepare students for the Oracle certification
exams. He was also a speaker at the 2011 Oracle Open World conference. Steve is an
award-winning technical paper writer and the creator of the alt.oracle blog.
I would like to thank Gary Hayes, Carol Ross, Matt Sams, Pete
Scalzi, Angela Morten, Joe Duvall, Sandee Vandenboom, Karen
Buck, Gary Deardorff, and Chad Fletcher for their support and
technical advice during the writing of this book. I would also like
to thank Debbie Rulo, Keith Krieger, and the staff at the Center for
Business at Johnson County Community College for their support.
Finally, I would like to thank my wife Dee and daughter Faith for
their love, personal support, and patience.
About the Reviewers
Dmitri Levin has been working as a database administrator for more than 15 years.
His areas of interest include database design, database replication, and database
performance tuning. Dmitri has spoken at several national and international
conferences. He is currently Sr. Database Architect and Administrator at Broder Bros
Co. Dmitri has an MS in Mathematics from St. Petersburg University, Russia, and is
an Oracle Database 11g Certified Associate.
Claire Rajan is an Oracle instructor, author, and database consultant. She currently
instructs at the American Career Institute, MD, where she teaches Oracle Database
administration. She has over 15 years of experience managing Oracle databases and
teaching Oracle-related topics. She has created and maintains the popular website
www.oraclecoach.com. The website provides a host of articles, videos, and technical
resources for both beginners and advanced learners. She has authored the book Oracle
10g Database Administrator II: Backup/Recovery and Network Administration,
published by Cengage Learning. She holds certifications in all major Oracle releases:
7.x, 8, 8i, 9i, 10g, and 11g. She can be found on Linkedin (http://www.linkedin.com/
in/clairerajan). She can be reached at [email protected].
www.PacktPub.com
Support files, eBooks, discount offers
and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files
available? You can upgrade to the eBook version at www.PacktPub.com and as a print book
customer, you are entitled to a discount on the eBook copy. Get in touch with us at service@
packtpub.com for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range
of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library.
Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
• Fully searchable across every book published by Packt
• Copy and paste, print and bookmark content
• On demand and accessible via web browser
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib
today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise
on Twitter, or the Packt Enterprise Facebook page.
Table of Contents
Preface 1
Chapter 1: SQL and Relational Databases 7
Relational Database Management Systems 8
Flat file databases 8
Limitations of the flat file paradigm 9
Normalization 10
The relational approach 13
Bringing it into the Oracle world 16
Tables and their structure 16
Structured Query Language 18
A language for relational databases 18
Commonly-used SQL tools 20
SQL*Plus 20
TOAD 21
DBArtisan 22
SQL Worksheet (Enterprise Manager) 23
PL/SQL Developer 24
Oracle SQL Developer 24
Working with SQL 25
Introducing the Companylink database 25
An introduction to Oracle SQL Developer 27
Setting up SQL Developer 27
Getting around in SQL Developer 31
Summary 34
Test your knowledge 34
Chapter 2: SQL SELECT Statements 37
The purpose and syntax of SQL 38
The syntax of SQL 38
Case sensitivity 39
Table of Contents
The use of whitespace 40
Statement terminators 41
Retrieving data with SELECT statements 42
Projecting columns in a SELECT statement 42
Selecting a single column from a table 43
Selecting multiple columns from a table 44
Selecting all columns from a table 46
Displaying the structure of a table using DESCRIBE 48
Using aliases to format output of SELECT statements 50
Using arithmetic operators with SELECT 53
The DUAL table and the use of string literals 54
Mathematical operators with SELECT 57
The meaning of nothing 60
Using DISTINCT to display unique values 62
Concatenating values in SELECT statements 65
Summary 69
Certification objectives covered 69
Test your knowledge 70
Chapter 3: Using Conditional Statements 75
Implementing selectivity using the WHERE clause 76
Understanding the concept of selectivity 76
Understanding the syntax of the WHERE clause 76
Using conditions in WHERE clauses 79
Using equality conditions 79
Implementing non-equality conditions 82
Examining conditions with multiple values 86
Constructing range conditions using the BETWEEN clause 86
Using the IN clause to create set conditions 89
Pattern-matching conditions using the LIKE clause 91
Understanding Boolean conditions in the WHERE clause 94
Examining the Boolean OR operator 95
Understanding the Boolean AND operator 97
The Boolean NOT operator 98
Using ampersand substitution with runtime conditions 101
Sorting data 102
Understanding the concepts of sorting data 102
Sorting data using the ORDER BY clause 103
Changing sort order using DESC and ASC 104
Secondary sorts 106
Summary 110
Certification objectives covered 110
[ ii ]
Table of Contents
Test Your Knowledge 111
Chapter 4: Data Manipulation with DML 115
Persistent storage and the CRUD model 115
Understanding the principles of persistent storage 116
Understanding the CRUD model and DML 117
Creating data with INSERT 118
Examining the syntax of the INSERT statement 118
Using single table inserts 119
Inserts using positional notation 119
Inserts using named column notation 121
Inserts using NULL values 122
Multi-row inserts 124
Conditional Inserts—INSERT...WHEN 125
Modifying data with UPDATE 128
Understanding the purpose and syntax of the UPDATE statement 128
Writing single-column UPDATE statements 128
Multi-column UPDATE statements 131
Removing data with DELETE 132
The purpose and syntax of the DELETE statement 133
Deleting rows by condition 133
Deleting rows without a limiting condition 135
Removing data unconditionally with TRUNCATE 136
Transaction control 138
Transactions and the ACID test 139
Completing transactions with COMMIT 140
Undoing transactions with ROLLBACK 142
DELETE and TRUNCATE revisited 146
Recognizing errors 146
Summary 149
Certification objectives covered 149
Test your knowledge 149
Chapter 5: Combining Data from Multiple Tables 155
Understanding the principles of joining tables 155
Accessing data from multiple tables 156
The ANSI standard versus Oracle proprietary syntax 158
Using ANSI standard joins 159
Understanding the structure and syntax of ANSI join statements 159
Examining ambiguous Cartesian joins 160
Using equi joins—joins based on equivalence 162
Implementing two table joins with a table-dot notation 162
Using two table joins with alias notation 165
[ iii ]