Oracle SQL And PL/SQL Programming

Course Duration: 30 Hours

Required Prerequisites

  • Basic Computer Knowledge

Target Audience

  • Fresher who wants to make career as Oracle SQL, PL/SQL Developer.

Course Highlights

  • Introduction to SQL
  • DDL Statements
  • Managing Tables using DML statements using TC Commands
  • Retrieving Data using the SQL SELECT Statement
  • Restricting and Sorting Data
  • Using Single-Row Functions to Customize Output
  • Using Conversion Functions and Conditional Expressions
  • Reporting Aggregated Data Using the Group Functions
  • Displaying Data from Multiple Tables Using Joins
  • Using Sub queries to Solve Queries
  • Using the SET Operators
  • Creating Sequences, Synonyms, Indexes & Views
  • Controlling User Access
  • Introduction to PL/SQL
  • Declaring PL/SQL Variables
  • Writing Executable Statements
  • Using SQL Statements within a PL/SQL Block
  • Writing Control Structures
  • Using Explicit Cursors
  • Handling Exceptions, Creating Stored Procedures and Functions, Creating Triggers and Packages

Course Content

SQL

Section 1: Introduction

Introduction to SQL

Installation of Oracle Database Software 12c

Creation of Oracle Database 12c

SQL*Plus

Oracle SQL Developer

Section 2: Introduction to Data Definition Language

Creation of Table

Alteration of Table / Column / Renaming Column or Table Name.

Truncation of Table

Dropping Table

Section 3: Managing Tables using DMLstatements using TC Commands.

Inserting Data

Updating Data

Deleting Data

Section 4: Retrieving Data using the SQL SELECT Statement

Capabilities of the SELECT statement

Arithmetic expressions and NULL values in the SELECT statement

Column aliases

Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

Use of the DESCRIBE command

Section 5: Restricting and Sorting Data

Limiting the Rows

Rules of precedence for operators in an expression

Section 6: Using 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

Section 7: Using 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

Section 8: Reporting Aggregated Data Using the Group Functions

Group Functions

Creating Groups of Data

Restricting Group Results

Section 9: Displaying Data from Multiple Tables Using Joins

Introduction to JOINS

Types of Joins

Natural join

Self-join

Non equijoins

OUTER join

Section 10: Using Sub queries to Solve Queries

Introduction to Subqueries

Single Row Subqueries

Multiple Row Subqueries

Section 11: Using the SET Operators

Set Operators

UNION and UNION ALL operator

INTERSECT operator

MINUS operator

Matching the SELECT statements

Using ORDER BY clause in set operations

Section 12: Creating Sequences, Synonyms, Indexes& Views

sequences

synonyms

indexes

Views

Section 13: Controlling User Access

System privileges

Creating a role

Object privileges

Revoking object privileges

Section 14: Generating Reports by Grouping Related Data

Use the ROLLUP operation to produce subtotal values

Use the CUBE operation to produce crosstabulation values

Use the GROUPING function to identify the row values created by ROLLUP or CUBE

Use GROUPING SETS to produce a single result set

Section 15: Hierarchical Retrieval

Interpret the concept of a hierarchical query

Create a tree-structured report

Format hierarchical data

Exclude branches from the tree structure

Section 16: SQL New Features

IDENTITY Columns

Default column value to a sequence in Oracle 12c

Support for 32K VARCHAR2

Row limit using FETCH FIRST

Invisible Columns

Truncate table cascade

PL/SQL

Section 1: Introduction

Introduction to PL/SQL Development Environments

Section 2: Introduction to PL/SQL

Understanding the benefits and structure of PL/SQL

Examining PL/SQL Blocks

Generating output messages in PL/SQL

Section 3: Declaring PL/SQL Variables

Identify valid and invalid identifiers

Declare and initialize variables

List and describe various data types

Identify the benefits of using the %TYPE attribute

Declare, use, and print bind variables

Section 4: Writing Executable Statements

Use SQL Functions in PL/SQL

Use Data Type Conversion

Use Nested Blocks as Statements

Reference an Identifier Value in a Nested Block

Use Operators in PL/SQL

Using Sequences in PL/SQL Expressions

Section 5: Using SQL Statements within a PL/SQL Block

Identify and use the SQL Statements in PL/SQL

Retrieve Data in PL/SQL with the SELECT statement

Manipulate Data in the Server Using PL/SQL

The SQL Cursor concept

Use SQL Cursor Attributes

Section 6: Writing Control Structures

Control PL/SQL Flow of Execution

Conditional processing Using IF and CASE Statements

Handle Nulls to Avoid Common Mistakes

Build Boolean Conditions with Logical Operators

Use Iterative Control with Looping Statements

Section 7: Using Explicit Cursors

Define Cursors

Explain Explicit Cursor Operations

Controlling Explicit Cursors

Use Explicit Cursors to Process Rows

Cursors and Records

Cursor FOR Loops Using Subqueries

Explicit Cursor Attributes

The %NOTFOUND and %ROWCOUNT Attributes

Section 8: Handling Exceptions

Functions that Return Information on
Encountered Exceptions

Trapping User-Defined Exceptions

Propagate Exceptions

Basic RAISE Statement

Define Exception

Handling Exceptions with PL/SQL

Predefined Exceptions

Trapping Predefined and Non-predefined Oracle Server Errors

Section 9: Creating Stored Procedures and Functions

Create Simple Procedures and Functions

Create a Simple Procedure with an IN Parameter

Execute a Procedure and a Function

Overview of Stored Procedures and Functions

Differentiate between anonymous blocks and subprograms

Show the CREATE OR REPLACE PROCEDURE | FUNCTION

Understand the Header Area of a Stored Procedure and Function

Section 10: Creating Triggers and Packages

Creation of Triggers on Tables and check the events

Creation of Packages and execution of Package.

Overview of Triggers and Packages.

Types of Triggers