Oracle

[ORACLE PRESS] Oracle Database SQL

(MHE-1Z0-071) / ISBN : 978-1-61691-367-0

Lessons

Lab (Add-on)

TestPrep

267 Reviews

Get A Free Trial

This course includes:

Free pre-assessment and first 2 lessons

14+ Interactive Lessons

Accessible on mobile and tablet too

Certificate of completion

$159.99

Buy Now

Are you an instructor?

Access detailed information about the course content, learning objectives, activities, and assessments before adding it to your curriculum.

Skills You’ll Get

The Oracle Certified Associate (OCA) certification is the first step toward achieving an Oracle Certified Professional. The OCA credential ensures that a candidate is equipped with fundamental skills, providing a strong foundation for supporting Oracle products. The Oracle Database SQL 1Z0-071 exam exam is a midpoint knowledge exam. It covers the basic functionality of SQL and some elements of SQL development that are not covered by the SQL Fundamentals exams. This exam was validated against 11g Release 2 version 11.2.0.1.0 and up to 12c Release 1 version 12.1.0.1.0.

Interactive Lessons

14+ Interactive Lessons | 211+ Quizzes | 208+ Flashcards | 208+ Glossary of terms

Gamified TestPrep

1+ Full Length Tests | 78+ Practice Test Questions

Hands-On Labs

60+ LiveLab | 00+ Minutes

Download Course Outline

Oracle and Structured Query Language (SQL)

The Exam: An Overview
Identify the Connection Between an ERD and a Relational Database
Explain the Relationship Between a Database and SQL
Describe the Purpose of DDL
Describe the Purpose of DML
Build a SELECT Statement to Retrieve Data from an Oracle Database Table
Certification Summary
Two-Minute Drill

Using DDL Statements to Create and Manage Tables

Categorize the Main Database Objects
Create a Simple 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
Drop Columns and Set Column UNUSED
Create and Use External Tables
Certification Summary
Two-Minute Drill

Manipulating Data

Truncate Data
Insert Rows into a Table
Update Rows in a Table
Delete Rows from a Table
Control Transactions
Certification Summary
Two-Minute Drill

Restricting and Sorting Data

Sort the Rows That Are Retrieved by a Query
Limit the Rows That Are Retrieved by a Query
Use Ampersand Substitution to Restrict and Sort Output at Run Time
Use the SQL Row Limiting Clause
Certification Summary
Two-Minute Drill

Using Single-Row Functions to Customize Output

Use Various Types of Functions That Are Available in SQL
Use Character, Number, Date, and Analytical (PER...TDDEV, LAG, LEAD) Functions in SELECT Statements
Certification Summary
Two-Minute Drill

Using Conversion Functions and Conditional Expressions

Describe Various Types of Conversion Functions
Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions
Apply General Functions and Conditional Expressions in a SELECT Statement
Certification Summary
Two-Minute Drill

Reporting Aggregated Data Using the 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
Two-Minute Drill

Displaying Data from Multiple Tables

Describe the Different Types of Joins and Their Features
Use SELECT Statements to Access Data from More Than One Table Using Equijoins and Non-Equijoins
Join a Table to Itself by Using a Self-Join
View Data That Generally Does Not Meet a Join Condition by Using Outer Joins
Certification Summary
Two-Minute Drill

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
Use the EXISTS and NOT EXISTS Operators
Use the WITH Clause
Write Single-Row and Multiple-Row Subqueries
Certification Summary
Two-Minute Drill

Managing Schema Objects

Describe How Schema Objects Work
Create Simple and Complex Views with Visible/Invisible Columns
Create, Maintain, and Use Sequences
Create and Maintain Indexes Including Invisible Indexes and Multiple Indexes on the Same Columns
Perform Flashback Operations
Certification Summary
Two-Minute Drill

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
Certification Summary
Two-Minute Drill

Managing Objects with Data Dictionary Views

Query Various Data Dictionary Views
Certification Summary
Two-Minute Drill

Manipulating Large Data Sets

Describe the Features of Multitable INSERTs
Merge Rows into a Table
Certification Summary
Two-Minute Drill

Controlling User Access

Differentiate System Privileges from Object Privileges
Grant Privileges on Tables and on a User
Distinguish Between Privileges and Roles
Certification Summary
Two-Minute Drill

Oracle and Structured Query Language (SQL)

Normalizing a Table
Creating a Table and Inserting Records in it

Using DDL Statements to Create and Manage Tables

Dropping a Primary Key and Adding a Unique Key
Adding a Foreign Key
Altering the Table
Viewing the Table Structure
Creating a Table Using the CLOB Data Type
Creating a Table Using Constraints
Dropping a Column

Manipulating Data

Truncating a Table
Inserting Records in the Existing Table
Updating a Record in the Table
Deleting a Record from the Table
Using the Commit and Rollback Statements

Restricting and Sorting Data

Sorting the Result
Using the LIKE Operator
Using the Boolean Operator
Using the ACCEPT and PROMPT Statements
Using the Fetch Statement
Using the WITH TIES Clause
Using the OFFSET-FETCH Clause

Using Single-Row Functions to Customize Output

Using the INITCAP Function
Using the RTRIM Function
Using the CONCAT Function
Using the SUBSTR Function
Using the REMAINDER Function
Using the LEAD and LAG Functions
Using the STDDEV Function

Using Conversion Functions and Conditional Expressions

Using the Implicit and Explicit Conversions
Using the TO_NUMBER Function
Using the TO_CHAR Function
Using the TO_DATE Function
Using the Case Statement
Using the NVL Function

Reporting Aggregated Data Using the Group Functions

Using the GROUP BY Clause
Using the HAVING Clause

Displaying Data from Multiple Tables

Using the INNER JOIN Operation
Using the NATURAL JOIN Operation
Creating a Table Using Records of Another Table
Using the LEFT OUTER JOIN and RIGHT OUTER JOIN Operation

Using Subqueries to Solve Queries

Deleting Rows Using a Correlated Subquery
Using the WITH Clause
Using the Multiple Select Statement to Retrieve Data

Managing Schema Objects

Creating a View
Dropping a View
Creating and Using a Sequence
Creating an Implicit Index
Creating a Unique Index
Altering a Unique Index
Dropping a Unique Index
Dropping a Table
Restoring the Table Using Flashback

Using the Set Operators

Using the UNION Operator

Managing Objects with Data Dictionary Views

Adding the Comment on a Table

Manipulating Large Data Sets

Using the Conditional Multitable INSERT Statement
Using the Multitable INSERT Statement

Controlling User Access

Creating a Database User and Granting Privileges
Revoking Privileges from a User
Dropping the Database User
Granting Public Synonym Privileges to a User

Any questions?
Check out the FAQs

Still have unanswered questions and need to get in touch?

USD 145

Pearson VUE

Mutiple Choice Questions

The exam contains 73 questions.

100 minutes

63%

Here are the retake policies:

Candidates may not retake a beta exam at any time.
Candidates may not retake a passed exam at any time.
Candidates may retake an online non-proctored exam at any time.
Candidates are only allowed 4 attempts to pass an exam in a 12-month period.
Candidates must resubmit a failed Master Assignment within 30 days of receiving the notification of failing.
Candidates must wait 14 days before retaking a failed proctored exam. (Registration will be allowed on the 14th day)

Know more about the 1Z0-071