CERTIFICATION PROGRAM SOL Certified Expert Exam Guide (Exam 120-047) Complete Exam Preparation y 200+ Barger ES TATA McGRAW-HILL Steve O’Heam EDITION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, oF others, Publisher does not guarantee to the accuracy, adequacy. or completeness of any information included in this work and is not responsible for any errors or omissions or the results obtained from the use of such information Tata McGraw-Hill Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy. ot completeness of any information contained in this work, and is not responsible for any errors ‘or omissions OCA Oracle Database SQL Certified Expert Exam Guide (Exam 120-047) Copyright © 2010 by The McGraw-Hill Companies, Inc. All rights reserved. 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 Publisher, with the ‘exception that the program listings may be entered. stored, and exceuted in a computer system, but they may not be reproduced for publication Oracle is registered trademark of Oracle Corporation andior its affiliates. AI other trademarks are the property of their respective owners, and McGraw-Hill makes no claim of ownership by the mention of products that contain these marks Screen display’ of copyrighted Oracle software programs have been reproduced herein with the permission of Oracle Corporation and/or its affiliates ‘Tata McGraw-Hill Edition 2010 RZAYCRLZDXLXY [Reprinted in India by arrangement with The McGraw-Hill Companies, Inc., New York. Sales territories: India, Pakistan, Nepal, Bangladesh, Sri Lanka and Bhutan cs taloging-in-Publication Data is on file with the Library of Congress ISBN-13: 978-0-07-070141-0 ISBN-10; 0-07-070141-5 Published by Tata McGraw Hill Education Private Limited, 7 West Patel Nagar, New Delhi 110 008, and printed at Pashupati Printers Pvt. Ltd., Dethi 110 095CONTENTS AT A GLANCE 1__ Introduction to SQL 1 2__Using DDL Statements w Create and Manage Tables 45 3 Manipulating Data. 93 4 Retrieving Data Using the SQL SELECT Statement 137 3 Restricting and Sorting Daca 169 6 Using Single-Row Functions to Customize Ourput 209 7_Reporting Aggregated Data Using the Group Funetions 275 $__Displaying Data from Multiple Tab 31 9 Retrieving Data Using Subgueries 345 10 Creating Other Schema Objects 381 L1__ Managing Schema Objects 423 12__Using the Set Operators 487 13__ Generating Reports by Grouping Relared Data. su 14 Managing Objects with Data Dictionary Views 533 15__ Manipulating Large Data Sets 559 16 Hierarchical Retrieval 615 17_ Regular Expression Support 639 18 Controlling User Access 673 A About the CD... 709 Glossary 713 Index we m9CONTENTS Acknowledgments xxi Prefwce x Enmroduction sauix L_Inntrodnetion 60 SOM. o.oo ooo nn one ssc r esses ees 1 ‘The Exam: An Overview Z “SQL Fundamentals I” Versus “SQL Expert” 4 Whar to Expect 10 erst > Basics of the RDBMS 1 Design Con 17 Define and Understand the Basics of SOL 13 Und i the Oracle RDBMS and Oras le SQL 21 js the N 2 Certification: Ormcie SOL Versus ANSI SOL 2B Certification: Oracle SQL Versus Oracle SQL*Plus 2B Oracle's Toots for Working with SQL 2B he Unique Role of SQL in Modem Software Systems 26 SOL Is 3 4GL 26 SOL: Gateway to the RDBMS for All Orher Languages Syntax Is Confirm Approptia 3 . 2 Documentarion 32 Cenification Summary 4 X i 5 Self Test 38Xi OCA Oracle Database SQL Certified Expert Exam Guide (Exam |Z0-047) 2__Using DDL Statements to Create and Manage Tables . 4S Categorize the Main Database Obj / Tw Q&A Self Tese Self Test Answers Manipulating Data... se eeeessseeeeeeeeeerees 93° Describe Each Data Manipulation Language (DML) Statement. ement Overview Insert Rows i a a Tbe. Defaule a Update nares ina Table epressions = z « WHERE Clase Delete Rows from a Table 1 Copyrighted materialConan Summary O&A STs cee i eed ier ‘Self Test Anse ? Restricting and Sorting Data .....++++ Limit the Rows That Are Retrieved by a Qui Sore the Rows That Are Retrieved by Ques Reference byName os ce vnsessecereesessesenssooren 165 189 Copyrighted materialiV OCA Oracle Database SQL Certified Expert Exam Guide (Exam 10.047) Certification Summary 194 Two-Minute Drill 196 QGA Self Test 525s i oe 198 | Self Tese Answers _. ‘ii a .. 104 6 Using Single-Row Functions to Customize Output ... 209 Describe Various Types of Functions That Are Available in SOL. 210 Chanierer Functions yi 22 pee 212, : —-- - zs aera ‘Coordinared Universal Tame (UTC) Time Zone Datatype: Time Zone Fu a Zone Conversion Functions AT LOCAL QGA SelfTest SelfTest Answers Copyrighted materialFunctions Identify the Available Group Functions cribe the Use of Group Functions 7__ Reporting Aggregated Data Using the Group ibeladdo cor Epchide Croupedl flows by ng the HAVING Clause 296 Certification Summary. Sta bacon ee 298 ¥_Two-Minure Drill 299 DSA. 8__ Displaying Data from Multiple Tables .............. 341 Copyrighted materialXVI OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047) Certification Summary. Two-Minute Drill QSA SelfTese Self Test Answers. 336 . 342 9 Retrieving Data Using Subqueries .....-...+.+++++ 345. ‘Define Subqueries 346 ‘Describe the Types of Problems That Subgueries Can Solve 347 List the Types of Subqueries 348 Write Single ‘Row and Multiple Row Subqueri 349 ‘Write a Multiple Column Subquery 356 ‘Use Scalar Subqueries in SQL 358 Solve Problems with Correlated Subqueries. . 360 correlated Subyverics 362 ‘UepaTe witha Coctelated| Sub ery. .. 362 Ise the EXISTS and NOT EXISTS Operavors Use the WITH Clause: Certification Summary bpraievs sam smnonss au Two-Minute Dail oa 369 QS&A SelfTest .... ; : 372 Self Test Answers 378 10 Creating Other Schema Objects ................. 381 Jue Seale and Complex Views beeas aren, “SO Copyrighted materialComents xvii Composite . z - a 402 Unique 403 Dropping 404 Greate Private and Public Sony 404 Private : 405 Public 406 Object Privileges . veveeees 406 Name Priority a - 408 Dropping se 410 ion Summary 410 fo-Minute Drill 2 QUA Self Testo... senee wee reveencennes M1G Managing Schema Objects .. Add and Modify: Renaming Columns Drop Columns and Set Column UNUSED Dropping Columns UNUSED Add Constraints Using ALTER TABLE to Add Constraints Modifying Constraints Removing Constraints Disabling and Enabling Constraints... sae. : SE Neal DELETE and ON DELETE .. DEFERRABLE and DEFERRED Renaming Constraints aoe 4 Create Indexes Using the € ATE TABLE Statement Tanke, AGN Create Function-Based Indexes 457 Perform FLASHBACK Operations Overview RecXViiE OCA Oracle Database SQL Certified Expert Exam Guide (Exam 120-047) Recovering Tables in Time sus 463 Marking Time 465 Create and Use External Tables 468 Benefits eo iia 469 al Tables. » sesees 69 Creating Exte Use a Set Operator to Combine Multinle Quert INION. ree oe * 4 soe * 90 UNION ALL MINUS ORDER BY-—B: 497 ORDER BY—By Reference 499 Certification Summary 500 ¥ Two-Minute Drill 501 Q&A Self Test 502 Self Test Answers 508 13 Generating Reports by Grouping Related Data .....- 1 ‘Use the ROLLU 2 ‘Use the CUBE Operation to Prox 515 Use the GROUPING Function to Mentify the Row Values Created by ROLLUP or CUBE... . Use GROUPING SETS to. Produc Self Test AnswersContents ix 14 Managing Objects with Data Dictionary Views ...... 533 Use the Data Dictionary Views to Research Data on Your Objects 534 ‘Structure, ‘ Zi 55 Dynamic Performance Views i i - 8 Reading Comments 540 Adding Comments 541 Data Dictionary View Ghecking Privileges Inspecting Constraints Finding Columns Certification Summary 549 ¥ Two-Minute Dail 55) Q&A Self Test Self Fest 15 Manipulating Large Data Sets Manipulate Dat Ans Subqueries it “lt UPDATE and Correlated Subqueries Describe the Features of Multitable INSERTS Use the Following Types of Mulritable INSERTS: Unconditional, Conditional, and Pivor a Unconditional ...... wean oe OT Merge Rows ina 582 Tak the Changes Data ssera Pv of Tine -. 586 Flashback Query wk 588 Flashback Version Query 594 Flashback Transaction ¢ Copyrighted material