Oracle SQL & PL/SQL

Course Duration: 35 Hours

Course Highlights:

  • Introduction to SQL
  • 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
  • Generating Reports by Grouping Related Data
  • Hierarchical Retrieval
  • SQL 12c New Features
  • 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

Required Prerequisites:

  • Basic Knowledge of SQL.

Target Audience:

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

Course Content

timeline_pre_loader
SQL

Introduction to SQL and its development environments

What is Oracle SQL Developer?

Starting SQL*Plus from Oracle SQL Developer

Creation of Table

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

Truncation of Table

Dropping Table

Inserting Data

 Updating Data

Deleting Data

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

Limiting the Rows

Rules of precedence for operators in an expression

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

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

Group Functions

Creating Groups of Data

Restricting Group Results

Introduction to JOINS

Types of Joins

Natural join

Self-join

Non equijoins

OUTER join

Introduction to Subqueries

Single Row Subqueries

Multiple Row Subqueries

Set Operators

UNION and UNION ALL operator

INTERSECT operator

MINUS operator

Matching the SELECT statements

Using ORDER BY clause in set operations

sequences

synonyms

 indexes

Views

System privileges

Creating a role

Object privileges

Revoking object privileges

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

Interpret the concept of a hierarchical query

Create a tree-structured report

Format hierarchical data

Exclude branches from the tree structure

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

Introduction to PL/SQL Development Environments

Understanding the benefits and structure of PL/SQL

Examining PL/SQL Blocks

Generating output messages in PL/SQL

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

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

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

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

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

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

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

Creation of Triggers on Tables and check the events

Creation of Packages and execution of Package.

Overview of Triggers and Packages.

Types of Triggers

Download Oracle SQL & PL/SQL Brochure

Signup today and get latest Videos and Upcoming Courses for free and be the first to get notified on new updates.

Subscribe to our mailing list