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