Introduction
to Oracle Database
·
List the features of Oracle Database 12c
·
Discuss the basic design, theoretical, and physical aspects of a
relational database
·
Categorize the different types of SQL statements
·
Describe the data set used by the course
·
Log on to the database using SQL Developer environment
·
Save queries to files and use script files in SQL Developer
Retrieve
Data using the SQL SELECT Statement
·
List the capabilities of SQL SELECT statements
·
Generate a report of data from the output of a basic SELECT statement
·
Select All Columns
·
Select Specific Columns
·
Use Column Heading Defaults
·
Use Arithmetic Operators
·
Understand Operator Precedence
·
Learn the DESCRIBE command to display the table structure
Learn to
Restrict and Sort Data
·
Write queries that contain a WHERE clause to limit the output retrieved
·
List the comparison operators and logical operators that are used in a
WHERE clause
·
Describe the rules of precedence for comparison and logical operators
·
Use character string literals in the WHERE clause
·
Write queries that contain an ORDER BY clause to sort the output of a
SELECT statement
·
Sort output in descending and ascending order
Usage of
Single-Row Functions to Customize Output
·
Describe the differences between single row and multiple row functions
·
Manipulate strings with character function in the SELECT and WHERE
clauses
·
Manipulate numbers with the ROUND, TRUNC, and MOD functions
·
Perform arithmetic with date data
·
Manipulate dates with the DATE functions
·
Invoke Conversion Functions and Conditional Expressions
·
Describe implicit and explicit data type conversion
·
Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
·
Nest multiple functions
·
Apply the NVL, NULLIF, and COALESCE functions to data
·
Use conditional IF THEN ELSE logic in a SELECT statement
Aggregate
Data Using the Group Functions
·
Use the aggregation functions to produce meaningful reports
·
Divide the retrieved data in groups by using the GROUP BY clause
·
Exclude groups of data by using the HAVING clause
Display Data
From Multiple Tables Using Joins
·
Write SELECT statements to access data from more than one table
·
View data that generally does not meet a join condition by using outer
joins
·
Join a table to itself by using a self join
Use
Sub-queries to Solve Queries
·
Describe the types of problem that sub-queries can solve
·
Define sub-queries
·
List the types of sub-queries
·
Write single-row and multiple-row sub-queries
The SET
Operators
·
Describe the SET operators
·
Use a SET operator to combine multiple queries into a single query
·
Control the order of rows returned
Data
Manipulation Statements
·
Describe each DML statement
·
Insert rows into a table
·
Change rows in a table by the UPDATE statement
·
Delete rows from a table with the DELETE statement
·
Save and discard changes with the COMMIT and ROLLBACK statements
·
Explain read consistency
Use of DDL
Statements to Create and Manage Tables
·
Categorize the main database objects
·
Review the table structure
·
List the data types available for columns
·
Create a simple table
·
Decipher how constraints can be created at table creation
·
Describe how schema objects work
Other Schema
Object
·
Create a simple and complex view
·
Retrieve data from views
·
Create, maintain, and use sequences
·
Create and maintain indexes
·
Create private and public synonyms
Introduction
to PL/SQL
·
Overview of PL/SQL
·
Identify the benefits of PL/SQL Subprograms
·
Overview of the types of PL/SQL blocks
·
Create a Simple Anonymous Block
·
How to generate output from a PL/SQL Block?
Declare
PL/SQL Identifiers
·
List the different Types of Identifiers in a PL/SQL subprogram
·
Usage of the Declarative Section to Define Identifiers
·
Use variables to store data
·
Identify Scalar Data Types
·
The %TYPE Attribute
·
What are Bind Variables?
·
Sequences in PL/SQL Expressions
Write
Executable Statements
·
Describe Basic PL/SQL Block Syntax Guidelines
·
Learn to Comment the Code
·
Deployment of SQL Functions in PL/SQL
·
How to convert Data Types?
·
Describe Nested Blocks
·
Identify the Operators in PL/SQL
Interaction
with the Oracle Server
·
Invoke SELECT Statements in PL/SQL
·
Retrieve Data in PL/SQL
·
SQL Cursor concept
·
Avoid Errors by using Naming Conventions when using Retrieval and DML
Statements
·
Data Manipulation in the Server using PL/SQL
·
Understand the SQL Cursor concept
·
Use SQL Cursor Attributes to Obtain Feedback on DML
·
Save and Discard Transactions
Control
Structures
·
Conditional processing using IF Statements
·
Conditional processing using CASE Statements
·
Describe simple Loop Statement
·
Describe While Loop Statement
·
Describe For Loop Statement
·
Use the Continue Statement
Composite
Data Types
·
Use PL/SQL Records
·
The %ROWTYPE Attribute
·
Insert and Update with PL/SQL Records
·
INDEX BY Tables
·
Examine INDEX BY Table Methods
·
Use INDEX BY Table of Records
Explicit
Cursors
·
What are Explicit Cursors?
·
Declare the Cursor
·
Open the Cursor
·
Fetch data from the Cursor
·
Close the Cursor
·
Cursor FOR loop
·
The %NOTFOUND and %ROWCOUNT Attributes
·
Describe the FOR UPDATE Clause and WHERE CURRENT Clause
Exception
Handling
·
Understand Exceptions
·
Handle Exceptions with PL/SQL
·
Trap Predefined Oracle Server Errors
·
Trap Non-Predefined Oracle Server Errors
·
Trap User-Defined Exceptions
·
Propagate Exceptions
·
RAISE_APPLICATION_ERROR Procedure
Stored
Procedures
·
Create a Modularized and Layered Subprogram Design
·
Modularize Development With PL/SQL Blocks
·
Understand the PL/SQL Execution Environment
·
List the benefits of using PL/SQL Subprograms
·
List the differences between Anonymous Blocks and Subprograms
·
Create, Call, and Remove Stored Procedures
·
Implement Procedures Parameters and Parameters Modes
·
View Procedure Information
Stored
Functions and Debugging Subprograms
·
Create, Call, and Remove a Stored Function
·
Identify the advantages of using Stored Functions
·
Identify the steps to create a stored function
·
Invoke User-Defined Functions in SQL Statements
·
Restrictions when calling Functions
·
Control side effects when calling Functions
·
View Functions Information
·
How to debug functions and procedures