OCA Oracle Database SQL Exam Guide (Exam 1Z0-071) (Oracle Press) by O'Hearn, Steve -- Read -- Imperial Library of Trantor

Index

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

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 ✓ 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 ✓ 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 ✓ 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 ✓ 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

← Prev
Back
Next →

← Prev
Back
Next →