Transcription
OCA Oracle DatabaseSQL Certified ExpertExam Guide(Exam 1Z0-047)
This page intentionally left blank
OCA Oracle DatabaseSQL Certified ExpertExam Guide(Exam 1Z0-047)Steve O’HearnMcGraw-Hill is an independent entity from Oracle Corporation. This publication andCD may be used in assisting students to prepare for the Oracle Database SQL Expertexam. Neither Oracle Corporation nor The McGraw-Hill Companies warrant that useof this publication will ensure passing the relevant exam.New York Chicago San Francisco Lisbon London MadridMexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright 2010 by The McGraw-Hill Companies, Inc. All rights reserved. Except as permitted under the United States Copyright Act of1976, 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.ISBN: 978-0-07-161422-1MHID: 0-07-161422-2The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-161421-4, MHID: 0-07-161421-4.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. Wheresuch 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. To contact a representative please e-mail us at bulksales@mcgraw-hill.com.Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, Publisher, or others, Publisher does not guarantee to the accuracy, adequacy, or completeness of any informationincluded in this work and is not responsible for any errors or omissions or the results obtained from the use of such information.Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy, or completeness of any information containedin this Work, and is not responsible for any errors or omissions.TERMS OF USEThis is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Useof 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 thework, 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 THEACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANYINFORMATION 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 itslicensors 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 shallMcGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from theuse 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 shallapply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.Disclaimer:This eBook does not include the ancillary media that waspackaged with the original printed version of the book.
FREE SUBSCRIPTIONTO oracle magazineGETYOURoracle magazine is essential gear for today’s information technology professionals.Stay informed and increase your productivity with every issue of oracle magazine.Inside each free bimonthly issue you’ll get: Up-to-date information on Oracle Database, Oracle Application Server,Web development, enterprise grid computing, database technology,and business trends Third-party news and announcements Technical articles on Oracle and partner products, technologies,and operating environments Development and administration tips Real-world customer storiesIf there are other Oracle users atyour location who would like toreceive their own subscription toOracle Magazine, please photocopy this form and pass it along.Three easy ways to subscribe:1 WebVisit our Web site at oracle.com/oraclemagazineYou’ll find a subscription form there, plus much more2 FaxComplete the questionnaire on the back of this cardand fax the questionnaire side only to 1.847.763.96383 MailComplete the questionnaire on the back of this cardand mail it to P.O. Box 1263, Skokie, IL 60076-8263Copyright 2008, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Want your own FREE subscription?To receive a free subscription to Oracle Magazine, you must fill out the entire card, sign it, and dateit (incomplete cards cannot be processed or acknowledged). You can also fax your application to 1.847.763.9638. Or subscribe at our Web site at oracle.com/oraclemagazineNo.Yes, please send me a FREE subscription Oracle Magazine.From time to time, Oracle Publishing allows our partnersexclusive access to our e-mail addresses for special promotions and announcements. To be included in this program,please check this circle. If you do not wish to be included, youwill only receive notices about your subscription via e-mail.Oracle Publishing allows sharing of our postal mailing list withselected third parties. If you prefer your mailing address not tobe included in this program, please check this circle.If at any time you would like to be removed from either mailing list, please contactCustomer Service at 1.847.763.9635 or send an e-mail to oracle@halldata.com.If you opt in to the sharing of information, Oracle may also provide you withe-mail related to Oracle products, services, and events. If you want to completelyunsubscribe from any e-mail communication from Oracle, please send an e-mail to:unsubscribe@oracle-mail.com with the following in the subject line: REMOVE [youre-mail address]. For complete information on Oracle Publishing’s privacy practices,please visit oracle.com/html/privacy/htmlxsignature (required)datenametitlecompanye-mail addressstreet/p.o. boxcity/state/zip or postal codetelephonecountryfaxWould you like to receive your free subscription in digital format instead of print if it becomes available?YesNoYOU MUST ANSWER ALL 10 QUESTIONS BELOW.1080140042WHAT IS THE PRIMARY BUSINESS ACTIVITYOF YOUR FIRM AT THIS LOCATION? (checkone 111213141516171819202122232498Aerospace and Defense ManufacturingApplication Service ProviderAutomotive ManufacturingChemicalsMedia and EntertainmentConstruction/EngineeringConsumer Sector/Consumer PackagedGoodsEducationFinancial Services/InsuranceHealth CareHigh Technology Manufacturing, OEMIndustrial ManufacturingIndependent Software VendorLife Sciences (biotech, pharmaceuticals)Natural ResourcesOil and GasProfessional ServicesPublic Sector ystems Integrator, VAR/VADTelecommunicationsTravel and TransportationUtilities (electric, gas, sanitation, water)Other Business and 1011121314151698oDigital Equipment Corp UNIX/VAX/VMSHP UNIXIBM AIXIBM UNIXLinux (Red Hat)Linux (SUSE)Linux (Oracle Enterprise)Linux (other)MacintoshMVSNetwareNetwork ComputingSCO UNIXSun Solaris/SunOSWindowsOther UNIXOtherNone of the Above01020304050607oHardwareBusiness Applications (ERP, CRM, etc.)Application Development ToolsDatabase ProductsInternet or Intranet ProductsOther SoftwareMiddleware ProductsNone of the Above6HARDWAREo 15 Macintosho 16 Mainframeo 17 Massively Parallel ProcessingooooooSERVICESo 24 Consultingo 25 Education/Trainingo 26 Maintenanceo 27 Online Databaseo 28 Supporto 29 Technology-Based Trainingo 30 Other99 o None of the Aboveoo7More than 25,000 Employees10,001 to 25,000 Employees5,001 to 10,000 Employees1,001 to 5,000 Employees101 to 1,000 EmployeesFewer than 100 Employees010203040506Less than 10,000 10,000 to 49,999 50,000 to 99,999 100,000 to 499,999 500,000 to 999,999 1,000,000 and OverWHAT IS YOUR COMPANY’S YEARLY SALESREVENUE? (check one only)ooooo9010203040506DURING THE NEXT 12 MONTHS, HOW MUCHDO YOU ANTICIPATE YOUR ORGANIZATIONWILL SPEND ON COMPUTER HARDWARE,SOFTWARE, PERIPHERALS, AND SERVICES FORYOUR LOCATION? (check one only)oooooo8181920212223WHAT IS YOUR COMPANY’S SIZE?(check one only)ooooooIN YOUR JOB, DO YOU USE OR PLAN TO PURCHASE ANY OF THE FOLLOWING PRODUCTS?(check all that apply)SOFTWAREo 01 CAD/CAE/CAMo 02 Collaboration Softwareo 03 Communicationso 04 Database Managemento 05 File Managemento 06 Financeo 07 Javao 08 Multimedia Authoringo 09 Networkingo 10 Programmingo 11 Project Managemento 12 Scientific and Engineeringo 13 Systems Managemento 14 WorkflowMinicomputerIntel x86(32)Intel x86(64)Network ComputerSymmetric MultiprocessingWorkstation ServicesooooooDO YOU EVALUATE, SPECIFY, RECOMMEND,OR AUTHORIZE THE PURCHASE OF ANY OFTHE FOLLOWING? (check all that apply)oooooooWHICH OF THE FOLLOWING BEST DESCRIBESYOUR PRIMARY JOB FUNCTION?(check one only)CORPORATE MANAGEMENT/STAFFo 01 Executive Management (President, Chair,CEO, CFO, Owner, Partner, Principal)o 02 Finance/Administrative Management(VP/Director/ Manager/Controller,Purchasing, Administration)o 03 Sales/Marketing Management(VP/Director/Manager)o 04 Computer er MIS/IS/IT, Ops)IS/IT STAFFo 05 Application Development/ProgrammingManagemento 06 Application Development/ProgrammingStaffo 07 Consultingo 08 DBA/Systems Administratoro 09 Education/Trainingo 10 Technical Support Director/Managero 11 Other Technical Management/Staffo 98 OtherWHAT IS YOUR CURRENT PRIMARY OPERATINGPLATFORM (check all that apply)0102030405 500, 000, 000 and above 100, 000, 000 to 500, 000, 000 50, 000, 000 to 100, 000, 000 5, 000, 000 to 50, 000, 000 1, 000, 000 to 5, 000, 000WHAT LANGUAGES AND FRAMEWORKS DOYOU USE? (check all that apply)oooo01020304AjaxCC C#oooo13141516PythonRuby/RailsSpringStruts1005 Hibernate06 J /J#07 Java08 JSP09 .NET10 Perl11 PHP12 PL/SQLo 17 SQLo 18 Visual Basico 98 OtherWHAT ORACLE PRODUCTS ARE IN USE AT YOURSITE? (check all that apply)ORACLE DATABASEo 01 Oracle Database 11go 02 Oracle Database 10 go 03 Oracle9 i Databaseo 04 Oracle Embedded Database(Oracle Lite, Times Ten, Berkeley DB)o 05 Other Oracle Database ReleaseORACLE FUSION MIDDLEWAREo 06 Oracle Application Servero 07 Oracle Portalo 08 Oracle Enterprise Managero 09 Oracle BPEL Process Managero 10 Oracle Identity Managemento 11 Oracle SOA Suiteo 12 Oracle Data HubsORACLE DEVELOPMENT TOOLSo 13 Oracle JDevelopero 14 Oracle Formso 15 Oracle Reportso 16 Oracle Designero 17 Oracle Discoverero 18 Oracle BI Beanso 19 Oracle Warehouse Buildero 20 Oracle WebCentero 21 Oracle Application ExpressORACLE APPLICATIONSo 22 Oracle E-Business Suiteo 23 PeopleSoft Enterpriseo 24 JD Edwards EnterpriseOneo 25 JD Edwards Worldo 26 Oracle Fusiono 27 Hyperiono 28 Siebel CRMORACLE SERVICESo 28 Oracle E-Business Suite On Demando 29 Oracle Technology On Demando 30 Siebel CRM On Demando 31 Oracle Consultingo 32 Oracle Educationo 33 Oracle Supporto 98 Other99 o None of the Above
To my father Don, an outstanding professional and highly accomplished engineer,who always believed in me, and taught me the value of hard work and dedication.
This page intentionally left blank
About the AuthorSteve O’Hearn is a veteran technology consultant with over 20 years of experiencein the design, development, and administration of various Oracle systems for suchclients as the U.S. Defense Department, NASA HQ, the FAA, the World Bank, andmany others. He first became an Oracle Certified Professional (OCP) in 2001 and isa certified Oracle Database SQL Expert. He has a degree in Business Administrationwith a specialization in Information Processing from The George WashingtonUniversity, and his postgraduate work includes the completion of the Future ofe-Government Executive Education training at Harvard University’s KennedySchool of Government in 2003. He is a member of MENSA.Mr. O’Hearn has been published in a variety of publications, including the HarvardBusiness Review, and contributed to Oracle Web Applications 101 from Oracle Pressand Oracle8 Server Unleashed. He authored the critically acclaimed OCP Developer:PL/SQL Program Units Exam Guide from Oracle Press. Mr. O’Hearn is an officiallyrecognized subject matter expert on the topic of database and information technologyby the National Press Club, where he is an active member, and where he has chairedor vice-chaired the New Media, High Tech, Publications, and Online JournalismCommittees, created the Club’s original social network and blog, and won severalawards. He has been Vice President and conference coordinator for the Mid-AtlanticAssociation of Oracle Professionals, where he was also the first webmaster in 1997.Mr. O’Hearn provides Oracle technology training and tutoring online. He invitesany and all inquiries at soh@corbinian.com.About the Technical EditorAlistair Grieve started his career as a Tandem NonStop COBOL programmer. Sincethen he has worked for more than 20 years as a software developer and databaseadministrator, primarily in the financial services sector, in the UK, the U.S.A., andNew Zealand. He is also a freelance technical editor.Mr. Grieve is an Engineering Science graduate of the University of Oxford. He is aSun Certified Java Programmer (SCJP) and Web Component Developer (SCWCD),as well as an Oracle Certified Professional (OCP) database administrator.He can be contacted at techedit@gmx.com.vii
viiiOCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)About LearnKeyLearnKey provides self-paced learning content and multimedia delivery solutions toenhance personal skills and business productivity. LearnKey claims the largest libraryof rich streaming-media training content that engages learners in dynamic mediarich instruction complete with video clips, audio, full motion graphics, and animatedillustrations. LearnKey can be found on the Web at www.LearnKey.com.
Contents at A Glance1Introduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12Using DDL Statements to Create and Manage Tables . . . . . . . . . . .453Manipulating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .934Retrieving Data Using the SQL S ELECT Statement . . . . . . . . . . .1375Restricting and Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . .1696Using Single-Row Functions to C ustomize Output . . . . . . . . . . . .2097Reporting A ggregated Data Using the Group Functions . . . . . . . . . .2758Displaying Data from Multiple Tables . . . . . . . . . . . . . . . . . . . .3119Retrieving Data Using Subqueries . . . . . . . . . . . . . . . . . . . . . .34510Creating Other Schema Objects . . . . . . . . . . . . . . . . . . . . . . .38111Managing Schema Objects . . . . . . . . . . . . . . . . . . . . . . . . . .42312Using the Set O perators . . . . . . . . . . . . . . . . . . . . . . . . . . . .48713 Generating Reports by Grouping Related Data . . . . . . . . . . . . . . . .51114Managing O bjects with Data D ictionary Views . . . . . . . . . . . . . . .53315Manipulating Large Data Sets . . . . . . . . . . . . . . . . . . . . . . . .55916Hierarchical R etrieval . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61517Regular Expression Support . . . . . . . . . . . . . . . . . . . . . . . . . .63918Controlling User Access . . . . . . . . . . . . . . . . . . . . . . . . . . .673AAbout the CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .709Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .713Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .729ix
This page intentionally left blank
Contents1Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiiixxvxxixIntroduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . .1The Exam: An Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .“SQL Fundamentals I” Versus “SQL Expert” . . . . . . . . . . . . . .What to Expect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Define and Understand the Basics of the RDBMS . . . . . . . . . . . . . . . .Relational Databases and Dr. E.F. Codd . . . . . . . . . . . . . . . . .Database Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . .Database Design Considerations . . . . . . . . . . . . . . . . . . . . . .Define and Understand the Basics of SQL . . . . . . . . . . . . . . . . . . . . . .Understand the Oracle RDBMS and Oracle SQL . . . . . . . . . . . . . . . .Oracle Is the Market Leader . . . . . . . . . . . . . . . . . . . . . . . . .Certification: Oracle SQL Versus ANSI SQL . . . . . . . . . . . . .Certification: Oracle SQL Versus Oracle SQL*Plus . . . . . . . .Oracle’s Tools for Working with SQL . . . . . . . . . . . . . . . . . . .Oracle’s Documentation for SQL . . . . . . . . . . . . . . . . . . . . . .Understand the Unique Role of SQL in Modern Software Systems . . . .SQL Is a 4GL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .SQL: Gateway to the RDBMS for All Other Languages . . . . .Syntax Isn’t Enough . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Confirm Appropriate Materials for Study . . . . . . . . . . . . . . . . . . . . . .Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Certification Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Two-Minute Drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Q&A Self Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Self Test Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i
xiiOCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)23Using DDL Statements to Create andManage Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45Categorize the Main Database Objects . . . . . . . . . . . . . . . . . . . . . . . .What Are Database Objects? . . . . . . . . . . . . . . . . . . . . . . . . .Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047) Steve O’Hearn McGraw-Hill is an independent entity from Oracle Corporation. This publication and CD may be used in assisting students to prepare for the Oracle Database SQL Expert exam. Neither Oracle C
