Oracle

[ORACLE PRESS] Oracle Database SQL

(1Z0-071.AO1) / ISBN : 978-1-64459-435-3
Lessons
Lab
TestPrep
AI Tutor (Add-on)
Get A Free Trial

About This Course

Skills You’ll Get

Download Course Outline

1

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
2

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
3

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
4

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
5

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
6

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
7

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
8

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

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
11

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
12

Managing Objects with Data Dictionary Views

  • Query Various Data Dictionary Views
  • Certification Summary
  • Two-Minute Drill
13

Manipulating Large Data Sets

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

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

1

Oracle and Structured Query Language (SQL)

  • Normalizing a Table
  • Creating a Table and Inserting Records in it
2

Using DDL Statements to Create and Manage Tables

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

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
4

Restricting and Sorting Data

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

Using Single-Row Functions to Customize Output

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

Using Conversion Functions and Conditional Expressions

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

Reporting Aggregated Data Using the Group Functions

  • Using the GROUP BY Clause
  • Using the HAVING Clause
8

Displaying Data from Multiple Tables

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

Using Subqueries to Solve Queries

  • Deleting Rows Using a Correlated Subquery
  • Using the WITH Clause
  • Using the Multiple-Row Subquery
10

Managing Schema Objects

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

Using the Set Operators

  • Using the UNION Operator
12

Managing Objects with Data Dictionary Views

  • Adding the Comment to a Table
13

Manipulating Large Data Sets

  • Using the Multitable INSERT Statement
  • Using the Conditional Multitable INSERT Statement
14

Controlling User Access

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

Related Courses

All Course
scroll to top