OCA Oracle Database SQL Exam Guide (Exam 1Z0-071)

Table of contents

1. Cover Page
2. Title Page
3. Copyright Page
4. Dedication
5. Contents
6. Acknowledgments
7. Preface
8. Introduction
9. Exam Readiness Checklist
10. 1 Oracle and Structured Query Language (SQL)
    1. The Exam: An Overview
    2. What to Do and What to Expect
    3. Oracle SQL vs. ANSI SQL
    4. Oracle SQL vs. Oracle SQL*Plus
    5. SQL Fundamentals I vs. SQL Certified Associate
    6. Confirm Appropriate Materials for Study
    7. Identify the Connection Between an ERD and a Relational Database
    8. Entity-Relationship Diagrams and Data Modeling
    9. Relational Databases
    10. Many-to-Many Relationships
    11. Database Normalization
    12. Explain the Relationship Between a Database and SQL
    13. Describe the Purpose of DDL
    14. Describe the Purpose of DML
    15. Transaction Control Language
    16. Build a SELECT Statement to Retrieve Data from an Oracle Database Table
    17. Certification Summary
    18. ✓ Two-Minute Drill
    19. Q&A Self Test
    20. Self Test Answers
11. 2 Using DDL Statements to Create and Manage Tables
    1. Categorize the Main Database Objects
    2. What Are Database Objects?
    3. Schemas
    4. Create a Simple Table
    5. Naming a Table or Other Object
    6. The SQL Statement CREATE TABLE
    7. Review the Table Structure
    8. List the Data Types That Are Available for Columns
    9. Character
    10. Numeric
    11. Date
    12. Large Objects
    13. Explain How Constraints Are Created at the Time of Table Creation
    14. Creating CONSTRAINTS in the CREATE TABLE Statement
    15. The Types of CONSTRAINTS
    16. Drop Columns and Set Column UNUSED
    17. Dropping Columns
    18. UNUSED
    19. Create and Use External Tables
    20. Benefits
    21. Creating External Tables
    22. Certification Summary
    23. ✓ Two-Minute Drill
    24. Q&A Self Test
    25. Self Test Answers
12. 3 Manipulating Data
    1. Truncate Data
    2. Recursively Truncate Child Tables
    3. Insert Rows into a Table
    4. Default Column List
    5. Enumerated Column List
    6. Update Rows in a Table
    7. Expressions
    8. Constraints
    9. The WHERE Clause
    10. Delete Rows from a Table
    11. Control Transactions
    12. COMMIT
    13. ROLLBACK
    14. SAVEPOINT
    15. ROLLBACK Revisited
    16. Certification Summary
    17. ✓ Two-Minute Drill
    18. Q&A Self Test
    19. Self Test Answers
13. 4 Restricting and Sorting Data
    1. Sort the Rows That Are Retrieved by a Query
    2. Reference by Name
    3. Expressions
    4. Reference by Position
    5. Combinations
    6. ORDER BY and NULL
    7. Limit the Rows That Are Retrieved by a Query
    8. The WHERE Clause
    9. Boolean Logic
    10. Additional WHERE Clause Features
    11. Additional Concepts
    12. Use Ampersand Substitution to Restrict and Sort Output at Run Time
    13. &
    14. DEFINE and UNDEFINE Commands
    15. The SET and SHOW Commands
    16. ACCEPT and PROMPT
    17. Use the SQL Row Limiting Clause
    18. FETCH
    19. WITH TIES
    20. OFFSET
    21. Certification Summary
    22. ✓ Two-Minute Drill
    23. Q&A Self Test
    24. Self Test Answers
14. 5 Using Single-Row Functions to Customize Output
    1. Use Various Types of Functions That Are Available in SQL
    2. Character Functions
    3. Number Functions
    4. Date Functions
    5. Other Functions
    6. Use Character, Number, Date, and Analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) Functions in SELECT Statements
    7. The DUAL Table
    8. Character Functions
    9. Numerical Functions
    10. Date Functions
    11. Analytical Functions
    12. Nesting Functions
    13. Certification Summary
    14. ✓ Two-Minute Drill
    15. Q&A Self Test
    16. Self Test Answers
15. 6 Using Conversion Functions and Conditional Expressions
    1. Describe Various Types of Conversion Functions
    2. Explicit and Implicit Conversion
    3. Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions
    4. Conversion Functions
    5. Additional Conversion Functions
    6. Apply General Functions and Conditional Expressions in a SELECT Statement
    7. CASE
    8. DECODE
    9. NVL
    10. NULLIF
    11. Certification Summary
    12. ✓ Two-Minute Drill
    13. Q&A Self Test
    14. Self Test Answers
16. 7 Reporting Aggregated Data Using the Group Functions
    1. Describe the Use of Group Functions
    2. COUNT
    3. SUM
    4. MIN, MAX
    5. AVG
    6. MEDIAN
    7. RANK
    8. DENSE_RANK
    9. FIRST, LAST
    10. Others
    11. Group Data by Using the GROUP BY Clause
    12. Multiple Columns
    13. ORDER BY Revisited
    14. Nesting Functions
    15. Include or Exclude Grouped Rows by Using the HAVING Clause
    16. Certification Summary
    17. ✓ Two-Minute Drill
    18. Q&A Self Test
    19. Self Test Answers
17. 8 Displaying Data from Multiple Tables
    1. Describe the Different Types of Joins and Their Features
    2. Types of Joins
    3. Use SELECT Statements to Access Data from More Than One Table Using Equijoins and Non-Equijoins
    4. Inner Joins
    5. Using Table Aliases
    6. Natural Joins
    7. USING
    8. Multitable Joins
    9. Non-Equijoins
    10. Join a Table to Itself by Using a Self-Join
    11. Self-Referencing Foreign Keys
    12. Self-Join Syntax
    13. View Data That Generally Does Not Meet a Join Condition by Using Outer Joins
    14. LEFT OUTER JOIN
    15. RIGHT OUTER JOIN
    16. FULL OUTER JOIN
    17. For the Record: Oracle Outer Join Syntax: (+)
    18. Certification Summary
    19. ✓ Two-Minute Drill
    20. Q&A Self Test
    21. Self Test Answers
18. 9 Using Subqueries to Solve Queries
    1. Define Subqueries
    2. Describe the Types of Problems Subqueries Can Solve
    3. Describe the Types of Subqueries
    4. Query Data Using Correlated Subqueries
    5. Update and Delete Rows Using Correlated Subqueries
    6. UPDATE with a Correlated Subquery
    7. DELETE with a Correlated Subquery
    8. Use the EXISTS and NOT EXISTS Operators
    9. Use the WITH Clause
    10. Write Single-Row and Multiple-Row Subqueries
    11. Single-Row Subqueries
    12. Multiple-Row Subqueries
    13. Certification Summary
    14. ✓ Two-Minute Drill
    15. Q&A Self Test
    16. Self Test Answers
19. 10 Managing Schema Objects
    1. Describe How Schema Objects Work
    2. Tables
    3. Constraints
    4. Views
    5. Indexes
    6. Sequences
    7. Create Simple and Complex Views with Visible/Invisible Columns
    8. Creating Views
    9. Updatable Views
    10. Inline Views
    11. ALTER VIEW
    12. Visible/Invisible Columns
    13. Create, Maintain, and Use Sequences
    14. Creating and Dropping Sequences
    15. Using Sequences
    16. Create and Maintain Indexes Including Invisible Indexes and Multiple Indexes on the Same Columns
    17. The Oracle Database Optimizer
    18. Implicit Index Creation
    19. Single Column
    20. Composite
    21. Unique
    22. Dropping
    23. Visible and Invisible Indexes
    24. Index Alternatives on the Same Column Set
    25. Perform Flashback Operations
    26. Overview
    27. Recover Dropped Tables
    28. Recovering Data Within Existing Tables over Time
    29. Marking Time
    30. Certification Summary
    31. ✓ Two-Minute Drill
    32. Q&A Self Test
    33. Self Test Answers
20. 11 Using the Set Operators
    1. Describe Set Operators
    2. Use a Set Operator to Combine Multiple Queries into a Single Query
    3. UNION
    4. UNION ALL
    5. INTERSECT
    6. MINUS
    7. Combinations
    8. Control the Order of Rows Returned
    9. ORDER BY—By Position
    10. ORDER BY—By Reference
    11. Certification Summary
    12. ✓ Two-Minute Drill
    13. Q&A Self Test
    14. Self Test Answers
21. 12 Managing Objects with Data Dictionary Views
    1. Query Various Data Dictionary Views
    2. Structure
    3. Dynamic Performance Views
    4. Reading Comments
    5. Adding Comments
    6. DICTIONARY
    7. Identifying a User’s Owned Objects
    8. Inspecting Tables and Columns
    9. Compiling Views
    10. Checking Privileges
    11. Inspecting Constraints
    12. Finding Columns
    13. Certification Summary
    14. ✓ Two-Minute Drill
    15. Q&A Self Test
    16. Self Test Answers
22. 13 Manipulating Large Data Sets
    1. Describe the Features of Multitable INSERTs
    2. Use the Following Types of Multitable INSERTS: Unconditional and Conditional
    3. Merge Rows into a Table
    4. Certification Summary
    5. ✓ Two-Minute Drill
    6. Q&A Self Test
    7. Self Test Answers
23. 14 Controlling User Access
    1. Differentiate System Privileges from Object Privileges
    2. System Privileges
    3. Prerequisites
    4. GRANT and REVOKE
    5. ANY
    6. ADMIN OPTION
    7. ALL PRIVILEGES
    8. PUBLIC
    9. Grant Privileges on Tables and on a User
    10. Schema Prefixes
    11. WITH GRANT OPTION
    12. REVOKE
    13. ALL PRIVILEGES
    14. Dependent Privileges
    15. View Privileges in the Data Dictionary
    16. Grant Roles
    17. Distinguish Between Privileges and Roles
    18. Certification Summary
    19. ✓ Two-Minute Drill
    20. Q&A Self Test
    21. Self Test Answers
24. A About the Download
    1. System Requirements
    2. Installing and Running Total Tester
    3. About Total Tester
    4. Technical Support
25. Glossary
26. Index