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

User Manual: Pdf

Open the PDF directly: View PDF .
Page Count: 794 [warning: Documents this large are best viewed by clicking the View PDF Link!]

Scroll down to view the document on your mobile browser.

Contents
Acknowledgments
Preface
Introduction
1 Introduction to SQL
- The Exam: An Overview
  - "SQL Fundamentals I" Versus "SQL Expert"
  - What to Expect
- Define and Understand the Basics of the RDBMS
- Define and Understand the Basics of SQL
- Understand the Oracle RDBMS and Oracle SQL
- Understand the Unique Role of SQL in Modern Software Systems
- Confirm Appropriate Materials for Study
  - Software
  - Documentation
- Certification Summary
2 Using DDL Statements to Create and Manage Tables
- Categorize the Main Database Objects
  - What Are Database Objects?
  - Schemas
- Create a Simple Table
  - Naming a Table or Other Object
  - The SQL Statement CREATE TABLE
- Review the Table Structure
- List the Data Types That Are Available for Columns
- Explain How Constraints Are Created at the Time of Table Creation
  - Creating CONSTRAINTS in the CREATE TABLE Statement
  - The Types of CONSTRAINTS
- Certification Summary
3 Manipulating Data
- Describe Each Data Manipulation Language (DML) Statement
  - SQL Statement Overview
  - DML Statement Descriptions
- Insert Rows into a Table
  - Default Column List
  - Enumerated Column List
- Update Rows in a Table
- Delete Rows from a Table
- Control Transactions
- Certification Summary
4 Retrieving Data Using the SQL SELECT Statement
- Execute a Basic SELECT Statement
  - The SELECT Statement—An Example
  - SELECT: Minimum Requirements
- List the Capabilities of SQL SELECT Statements
- Describe How Schema Objects Work
- Certification Summary
5 Restricting and Sorting Data
- Limit the Rows That Are Retrieved by a Query
- Sort the Rows That Are Retrieved by a Query
- Certification Summary
6 Using Single-Row Functions to Customize Output
- Describe Various Types of Functions That Are Available in SQL
- Use Character, Number, and Date Functions in SELECT Statements
- Describe the Use of Conversion Functions
  - Conversion Functions
  - Automatic Datatype Conversions
- Manage Data in Different Time Zones—Use Various Datetime Functions
- Certification Summary
7 Reporting Aggregated Data Using the Group Functions
- Identify the Available Group Functions
- Describe the Use of Group Functions
- Group Data by Using the GROUP BY Clause
- Include or Exclude Grouped Rows by Using the HAVING Clause
- Certification Summary
8 Displaying Data from Multiple Tables
- 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
- Join a Table to Itself by Using a Self-Join
  - Self-Referencing Foreign Keys
  - Self-Join Syntax
- Generate a Cartesian Product of All Rows from Two or More Tables
- Certification Summary
9 Retrieving Data Using Subqueries
- Define Subqueries
- Describe the Types of Problems That Subqueries Can Solve
- List the Types of Subqueries
- Write Single-Row and Multiple-Row Subqueries
  - Single-Row Subqueries
  - Multiple-Row Subqueries
- Write a Multiple-Column Subquery
- Use Scalar Subqueries in SQL
- Solve Problems with Correlated Subqueries
- Update and Delete Rows Using Correlated Subqueries
  - UPDATE with a Correlated Subquery
  - DELETE with a Correlated Subquery
- Use the EXISTS and NOT EXISTS Operators
- Use the WITH Clause
- Certification Summary
10 Creating Other Schema Objects
- Create and Use Simple and Complex Views
- Create, Maintain, and Use Sequences
  - Creating Sequences
  - Using Sequences
- Create and Maintain Indexes
- Create Private and Public Synonyms
- Certification Summary
11 Managing Schema Objects
- Add and Modify Columns
- Drop Columns and Set Column UNUSED
  - Dropping Columns
  - UNUSED
- Add Constraints
- Create Indexes Using the CREATE TABLE Statement
  - Automatic Index Creation
  - USING INDEX
- Create Function-Based Indexes
- Perform FLASHBACK Operations
- Create and Use External Tables
  - Benefits
  - Creating External Tables
- Certification Summary
12 Using the Set Operators
- Describe Set Operators
- Use a Set Operator to Combine Multiple Queries into a Single Query
- Control the Order of Rows Returned
  - ORDER BY—By Position
  - ORDER BY—By Reference
- Certification Summary
13 Generating Reports by Grouping Related Data
- Use the ROLLUP Operation to Produce Subtotal Values
- Use the CUBE Operation to Produce Crosstabulation Values
- Use the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE
- Use GROUPING SETS to Produce a Single Result Set
- Certification Summary
14 Managing Objects with Data Dictionary Views
- Use the Data Dictionary Views to Research Data on Your Objects
- Query Various Data Dictionary Views
- Certification Summary
15 Manipulating Large Data Sets
- Manipulate Data Using Subqueries
- Describe the Features of Multitable INSERTs
- Use the Following Types of Multitable INSERTS: Unconditional, Conditional, and Pivot
- Merge Rows in a Table
- Track the Changes to Data over a Period of Time
- Certification Summary
16 Hierarchical Retrieval
- Interpret the Concept of a Hierarchical Query
- Create and Format Hierarchical Data
- Exclude Branches from the Tree Structure
- Certification Summary
17 Regular Expression Support
- Using Metacharacters
- Regular Expression Functions
- Replacing Patterns
- Regular Expressions and CHECK Constraints
- Certification Summary
18 Controlling User Access
- Differentiate System Privileges from Object Privileges
- Grant Privileges on Tables
- View Privileges in the Data Dictionary
- Grant Roles
- Distinguish Between Privileges and Roles
- Certification Summary
A: About the CD
- System Requirements
- Installing and Running MasterExam
  - MasterExam
- Electronic Book
- Help
- Removing Installation(s)
- Technical Support
  - LearnKey Technical Support
Glossary
- A
- B
- C
- D
- E
- F
- G
- H
- I
- J
- K
- L
- M
- N
- O
- P
- Q
- R
- S
- T
- U
- V
- W
Index
- A
- B
- C
- D
- E
- F
- G
- H
- I
- J
- L
- M
- N
- O
- P
- Q
- R
- S
- T
- U
- V
- W

Navigation menu