OCA Oracle Database SQL Exam Guide (Exam 1Z0-071)
1st Edition
Published on 25. August 2017
832 pages
978-1-259-58461-9 (ISBN)
Available for download
Description
A fully updated, integrated self-study system for the Oracle Database SQL ExamThis thoroughly revised Oracle Press guide offers 100% coverage of all objectives on the latest version of the Oracle Database SQL Exam. Ideal both as a study guide and on-the-job reference, OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) features detailed explanations, examples, practice questions, and chapter summaries. Certification Objectives, Exam Watch, and On the Job sections reinforce salient points throughout. You will gain access to two complete practice exams that match the tone, tenor, and format of the live test.Get complete coverage every topic on Exam 1Z0-071, including: DDL and SQL SELECT statements Manipulating, restricting, and sorting data Single-row and group functions Displaying data from multiple tables Subqueries Schema objects Set operators Grouping related data Report creation Data dictionary views Large data sets Hierarchical retrieval Regular expression support User access controlThe electronic includes: Two full practice exams Detailed answers and explanations
More details
Content
- Cover
- 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
- V 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
- V 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
- V 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
- V 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
- V 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
- V 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
- V 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
- V 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
- V 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
- V Two-Minute Drill
- Q&A Self Test
- Self Test Answers
- 11 Using the Set Operators
- Describe Set Operators
- Use a Set Operator to Combine Multiple Queries into a Single Query
- UNION
- UNION ALL
- INTERSECT
- MINUS
- Combinations
- Control the Order of Rows Returned
- ORDER BY-By Position
- ORDER BY-By Reference
- Certification Summary
- V Two-Minute Drill
- Q&A Self Test
- Self Test Answers
- 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
- V Two-Minute Drill
- Q&A Self Test
- Self Test Answers
- 13 Manipulating Large Data Sets
- Describe the Features of Multitable INSERTs
- Use the Following Types of Multitable INSERTS: Unconditional and Conditional
- Merge Rows into a Table
- Certification Summary
- V Two-Minute Drill
- Q&A Self Test
- Self Test Answers
- 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
- V Two-Minute Drill
- Q&A Self Test
- Self Test Answers
- A About the Download
- System Requirements
- Installing and Running Total Tester
- About Total Tester
- Technical Support
- Glossary
- Index
