OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

by Steve O'Hearn
Released November 2009
Publisher(s): McGraw Hill Computing
ISBN: 9780071614221

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

A Fully Integrated Study System for OCA Exam 1Z0-047

Prepare for the Oracle Certified Associate SQL Certified Expert exam with help from this exclusive Oracle Press guide. In each chapter, you'll find challenging exercises, practice questions, and a two-minute drill to highlight what you've learned. This authoritative guide will help you pass the test and serve as your essential on-the-job reference. Get complete coverage of all objectives for exam 1Z0-047, including:

  • DDL and SQL SELECT statements
  • Manipulating, restricting, and sorting data
  • Single-row and group functions
  • Subqueries
  • Schema objects
  • Set operators
  • Data dictionary views
  • Large data sets
  • Hierarchical retrieval
  • Regular expression support
  • User access control
  • And more

On the CD-ROM:

  • One full practice exam
  • Detailed answers and explanations
  • Score report performance assessment tool
  • Complete electronic book
  • Bonus exam available with free online registration

For a complete list of Oracle Press titles, visit www.OraclePressBooks.com

Table of contents

  1. Cover Page
  2. OCA Oracle Database SQL Certified Expert Exam Guide
  3. Copyright Page
  4. Contents
  5. Acknowledgments
  6. Preface
  7. Introduction
  8. 1 Introduction to SQL
    1. The Exam: An Overview
      1. “SQL Fundamentals I” Versus “SQL Expert”
      2. What to Expect
    2. Define and Understand the Basics of the RDBMS
      1. Relational Databases and Dr. E.F. Codd
      2. Database Normalization
      3. Database Design Considerations
    3. Define and Understand the Basics of SQL
    4. Understand the Oracle RDBMS and Oracle SQL
      1. Oracle Is the Market Leader
      2. Certification: Oracle SQL Versus ANSI SQL
      3. Certification: Oracle SQL Versus Oracle SQL*Plus
      4. Oracle’s Tools for Working with SQL
      5. Oracle’s Documentation for SQL
    5. Understand the Unique Role of SQL in Modern Software Systems
      1. SQL Is a 4GL
      2. SQL: Gateway to the RDBMS for All Other Languages
      3. Syntax Isn’t Enough
    6. Confirm Appropriate Materials for Study
      1. Software
      2. Documentation
    7. Certification Summary
    8. Two-Minute Drill
    9. Q&A Self Test
      1. Self Test Answers
  9. 2 Using DDL statements to Create and Manage tables
    1. Categorize the Main Database Objects
      1. What Are Database Objects?
      2. Schemas
    2. Create a Simple Table
      1. Naming a Table or Other Object
      2. The SQL Statement Create Table
    3. Review the Table Structure
    4. List the Data Types That Are Available for Columns
      1. Character
      2. Numeric
      3. Date
      4. Large Objects (LOBs)
    5. Explain How Constraints Are Created at the Time of Table Creation
      1. Creating Constraints in the Create Table Statement
      2. The Types of Constraints
    6. Certification Summary
    7. Two-Minute Drill
    8. Q&A Self Test
      1. Self Test Answers
  10. 3 Manipulating Data
    1. Describe Each Data Manipulation Language (DML) Statement
      1. SQL Statement Overview
      2. DML Statement Descriptions
    2. Insert Rows into a Table
      1. Default Column List
      2. Enumerated Column List
    3. Update Rows in a Table
      1. Expressions
      2. Constraints
      3. The Where Clause
    4. Delete Rows from a Table
    5. Control Transactions
      1. Commit
      2. Rollback
      3. Savepoint
      4. Rollback Revisited
    6. Certification Summary
    7. Two-Minute Drill
    8. Q&A Self Test
      1. Self Test Answers
  11. 4 Retrieving Data using the SQL Select statement
    1. Execute a Basic Select Statement
      1. The Select Statement—An Example
      2. Select: Minimum Requirements
    2. List the Capabilities of SQL Select Statements
      1. The Select Statement—An Overview
      2. Expressions
      3. Additional Select Statement Clauses
      4. The Capabilities of Select
    3. Describe How Schema Objects Work
      1. Tables
      2. Constraints
      3. Views
      4. Indexes
      5. Sequences
      6. Synonyms
    4. Certification Summary
    5. Two-Minute Drill
    6. Q&A Self Test
      1. Self Test Answers
  12. 5 Restricting and sorting Data
    1. Limit the Rows That Are Retrieved by a Query
      1. The Where Clause
      2. Boolean Logic
      3. Additional Where Clause Features
      4. Additional Concepts
    2. Sort the Rows That Are Retrieved by a Query
      1. Reference by Name
      2. Expressions
      3. Reference by Position
      4. Combinations
      5. Order By And Null
    3. Certification Summary
    4. Two-Minute Drill
    5. Q&A Self Test
      1. Self Test Answers
  13. 6 Using single-row Functions to Customize output
    1. Describe Various Types of Functions That Are Available in SQL
      1. Character Functions
      2. Number Functions
      3. Date Functions
      4. Conversion Functions
      5. Other Functions
    2. Use Character, Number, and Date Functions in Select Statements
      1. The Dual Table
      2. Character Manipulation
      3. Mathematical Processing
      4. Working with Dates
      5. Other Functions
      6. Nesting Functions
    3. Describe the Use of Conversion Functions
      1. Conversion Functions
      2. Automatic Datatype Conversions
    4. Manage Data in Different Time Zones—Use Various Datetime Functions
      1. Database Time Versus Session Time
      2. Coordinated Universal Time (UTC)
      3. Time Zone Datatypes
      4. Time Zone Functions
      5. Time Zone Conversion Functions
      6. AT TIME ZONE, AT LOCAL
    5. Certification Summary
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Self Test Answers
  14. 7 Reporting Aggregated Data using the Group Functions
    1. Identify the Available Group Functions
    2. Describe the Use of Group Functions
      1. Count
      2. Sum
      3. Min, Max
      4. Avg
      5. Median
      6. Rank
      7. First, Last
      8. Grouping
      9. Others
    3. Group Data by Using the Group By Clause
      1. Multiple Columns
      2. Order By Revisited
      3. Nesting Functions
    4. Include or Exclude Grouped Rows by Using the Having Clause
    5. Certification Summary
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Self Test Answers
  15. 8 Displaying Data from Multiple tables
    1. Write Select Statements to Access Data from More Than One Table Using Equijoins and Non-Equijoins/View Data That Generally Does Not Meet a Join Condition by Using Outer Joins
      1. Key Relationships
      2. Types of Joins
      3. Inner Joins
      4. Outer Joins
      5. Using Table Aliases
      6. Natural Joins
      7. Using
      8. Multitable Joins
      9. Non-Equijoins
    2. Join a Table to Itself by Using a Self-Join
      1. Self-Referencing Foreign Keys
      2. Self-Join Syntax
    3. Generate a Cartesian Product of All Rows from Two or More Tables
    4. Certification Summary
    5. Two-Minute Drill
    6. Q&A Self Test
      1. Self Test Answers
  16. 9 Retrieving Data using subqueries
    1. Define Subqueries
    2. Describe the Types of Problems That Subqueries Can Solve
    3. List the Types of Subqueries
    4. Write Single-Row and Multiple-Row Subqueries
      1. Single-Row Subqueries
      2. Multiple-Row Subqueries
    5. Write a Multiple-Column Subquery
    6. Use Scalar Subqueries in SQL
    7. Solve Problems with Correlated Subqueries
    8. Update and Delete Rows Using Correlated Subqueries
      1. Update with a Correlated Subquery
      2. Delete with a Correlated Subquery
    9. Use the Exists and Not Exists Operators
    10. Use the With Clause
    11. Certification Summary
    12. Two-Minute Drill
    13. Q&A Self Test
      1. Self Test Answers
  17. 10 Creating other schema objects
    1. Create and Use Simple and Complex Views
      1. Creating Views
      2. Updatable Views
      3. Inline Views
      4. Retrieving Data
      5. Alter View
    2. Create, Maintain, and Use Sequences
      1. Creating Sequences
      2. Using Sequences
    3. Create and Maintain Indexes
      1. Implicit Index Creation
      2. Single Column
      3. Composite
      4. Unique
      5. Dropping
    4. Create Private and Public Synonyms
      1. Private
      2. Public
      3. Object Privileges
      4. Name Priority
      5. Replacing
      6. Dropping
    5. Certification Summary
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Self Test Answers
  18. 11 Managing schema objects
    1. Add and Modify Columns
      1. Adding Columns
      2. Modifying Columns
      3. Renaming Columns
    2. Drop Columns and Set Column UNUSED
      1. Dropping Columns
      2. Unused
    3. Add Constraints
      1. Using Alter Table to Add Constraints
      2. Modifying Constraints
      3. Removing Constraints
      4. Disabling and Enabling Constraints
      5. Drop Table and Cascade Constraints
      6. Delete and on Delete
      7. Deferrable and Deferred
      8. Renaming Constraints
    4. Create Indexes Using the Create Table Statement
      1. Automatic Index Creation
      2. Using Index
    5. Create Function-Based Indexes
    6. Perform Flashback Operations
      1. Overview
      2. Recover Dropped Tables
      3. Recovering Tables in Time
      4. Marking Time
    7. Create and Use External Tables
      1. Benefits
      2. Creating External Tables
    8. Certification Summary
    9. Two-Minute Drill
    10. Q&A Self Test
      1. Self Test Answers
  19. 12 Using the set operators
    1. Describe Set Operators
    2. Use a Set Operator to Combine Multiple Queries into a Single Query
      1. Union
      2. Union All
      3. Intersect
      4. Minus
      5. Combinations
    3. Control the Order of Rows Returned
      1. Order By—By Position
      2. Order By—By Reference
    4. Certification Summary
    5. Two-Minute Drill
    6. Q&A Self Test
      1. Self Test Answers
  20. 13 Generating reports by Grouping related Data
    1. Use the Rollup Operation to Produce Subtotal Values
    2. Use the Cube Operation to Produce Crosstabulation Values
    3. Use the Grouping Function to Identify the Row Values Created by Rollup or Cube
    4. Use Grouping Sets to Produce a Single Result Set
    5. Certification Summary
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Self Test Answers
  21. 14 Managing objects with Data Dictionary Views
    1. Use the Data Dictionary Views to Research Data on Your Objects
      1. Structure
      2. Dynamic Performance Views
      3. Reading Comments
      4. Adding Comments
    2. Query Various Data Dictionary Views
      1. Dictionary
      2. Identifying a User’s Owned Objects
      3. Inspecting Tables and Columns
      4. Compiling Views
      5. Checking Privileges
      6. Inspecting Constraints
      7. Finding Columns
    3. Certification Summary
    4. Two-Minute Drill
    5. Q&A Self Test
      1. Self Test Answers
  22. 15 Manipulating Large Data sets
    1. Manipulate Data Using Subqueries
      1. Create Table and Subqueries
      2. Insert and Subqueries
      3. Update and Correlated Subqueries
    2. Describe the Features of Multitable INSERTs
    3. Use the Following Types of Multitable Inserts: Unconditional, Conditional, and Pivot
      1. Unconditional
      2. Conditional
      3. Pivot
    4. Merge Rows in a Table
    5. Track the Changes to Data over a Period of Time
      1. Flashback Query
      2. Flashback Version Query
      3. Flashback Transaction Query
    6. Certification Summary
    7. Two-Minute Drill
    8. Q&A Self Test
      1. Self Test Answers
  23. 16 Hierarchical retrieval
    1. Interpret the Concept of a Hierarchical Query
    2. Create and Format Hierarchical Data
      1. Create a Tree-Structured Report
      2. Choosing Direction
      3. Order Siblings By
      4. Sys_Connect_By_Path
      5. Connect_By_Root
    3. Exclude Branches from the Tree Structure
    4. Certification Summary
    5. Two-Minute Drill
    6. Q&A Self Test
      1. Self Test Answers
  24. 17 Regular Expression Support
    1. Using Metacharacters
    2. Regular Expression Functions
    3. Replacing Patterns
    4. Regular Expressions and CHECK Constraints
    5. Certification Summary
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Self Test Answers
  25. 18 Controlling user Access
    1. Differentiate System Privileges from Object Privileges
      1. System Privileges
      2. Prerequisites
      3. Grant and Revoke
      4. Any
      5. Admin Option
      6. All Privileges
      7. Public
    2. Grant Privileges on Tables
      1. Schema Prefixes
      2. With Grant Option
      3. Revoke
      4. All Privileges
      5. Dependent Privileges
    3. View Privileges in the Data Dictionary
    4. Grant Roles
    5. Distinguish Between Privileges and Roles
    6. Certification Summary
    7. Two-Minute Drill
    8. Q&A Self Test
      1. Self Test Answers
  26. A About the CD
    1. System Requirements
    2. Installing and Running MasterExam
      1. MasterExam
    3. Electronic Book
    4. Help
    5. Removing Installation(s)
    6. Technical Support
      1. LearnKey Technical Support
  27. Glossary
  28. Index

Product information

  • Title: OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)
  • Author(s): Steve O'Hearn
  • Release date: November 2009
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071614221