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