intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE

OCA Oracle Database 11g SQL Fundamentals I Exam Guide P1

Chia sẻ: Vong Phat | Ngày: | Loại File: PDF | Số trang:40

288
lượt xem
89
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

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

Chủ đề:
Lưu

Nội dung Text: OCA Oracle Database 11g SQL Fundamentals I Exam Guide P1

  1. ® OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)
  2. 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.
  3. ® 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
  4. 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 george_hoare@mcgraw-hill.com 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
  5. 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
  6. This page intentionally left blank
  7. 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
  8. This page intentionally left blank
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. This page intentionally left blank
  19. 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.
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

 

Đồng bộ tài khoản
2=>2