ORACLE 11G SQL AND PL/SQL

Who can learn this course:

· Anyone who want to be a application developer, technical consultants , database administrators,

PL/SQL developers, system analysts ,data warehouse administrators

Pre Requisite:

· Knowledge on RDBMS Concepts

· Basic programming knowledge will be an advantage

End Objective:

· Write SELECT statements that include queries

· Retrieve row and column data from tables

· Run data manipulation statements (DML) in Oracle Database 12c

· Create tables to store data

· Utilize views to display data

· Control database access to specific objects

· Manage schema objects

· Display data from multiple tables using JOIN syntax

· Use conditional compilation to customize the functionality

in a PL/SQL application without removing any source code

· Design PL/SQL packages to group related constructs

· Create overloaded package subprograms for more flexibility

· Design PL/SQL anonymous blocks that execute efficiently

· Leverage the functionality of the various Oracle Supplied PL/SQL packages

· Write dynamic SQL for more coding flexibility

· Identify the major structural components of the Oracle Database 12c

· Create reports of aggregated data

Course Duration:

· Total Number of Hours : 40

INTRODUCTION TO SQL

· Introducing the Structured Query Language (SQL)

· SQL Sub Languages (DDL, DML, TCL, DCL, DRL)

· Using SQL*Plus

· Connecting to SQL*Plus

· SQL*Plus Commands

DDL AND DML

· Create Table

· Alter Table

· Drop Table

· Insert

· Insert trough select

· Update

· Delete

SELECTING USING OPERATORS

· Arithmetic Operators

· Relational Operators

· Logical Operators

· Set Operators

· Other Operators

ORDER BY

· ASC

· DESC

· Multiple columns

· Expressions

· Columns not in SELECT list

SELECTING USING FUNCTIONS

· Number Functions

· Character Functions

· Date and Time Functions

· Aggregate Functions

· Other Functions

GROUP BY

· Single column

· Multiple columns

· HAVING considerations

· With ORDER BY

JOINS

· What is join

· INNER

· OUTER (LEFT, RIGHT & FULL)

· ON vs. WHERE

· Cartesian product

SUB-QUERIES

· Nested Queries

· Correlated Sub Queries

·

INTEGRITY CONSTRAINTS

· Not Null

· Unique Key

· Primary key

· Check Constraints

· Default

· Foreign Key

TRANSACTION MANAGEMENT

· What is Transaction

· Commit

· Auto commit

· Implicit Commit

· Explicit Commit

· Save point

· Rollback

DATA CONTROL LANGUAGE (DCL)

· Creating Users

· Privileges

· Roles

· Grant

· Revoke

IMPLEMENTING VIEWS

· Introduction to Views

· Creating and Managing Views

· Restriction on Views

·

INDEXES

· Requirement of index

· Advantages of index

· Create index

· Drop index

SEQUENCES

· What is Sequence

· Advantages of Sequence

· Create Sequence

· Usage of Sequence

· Pseudo Columns

· NEXTVAL

· CURRVAL

·

INTRODUCTION TO PL/SQL

· PL/SQL – Introduction

· Structure/Components of PL/SQL Block

· Advantages of PL/SQL

· Nesting of Blocks

PL/SQL LANGUAGE FEATURES

· PL/SQL block

· Lexical Units

· Variables

· Data types

· Declaring Variable

· Operators

· Built in Functions

SQL IN PL/SQL

· Data Retrieval from PL/SQL

· Data From SQL into PL/SQL

· Restrictions on SELECT in PL/SQL

· DML in PL/SQL

· TCL in PL/SQL

CONTROL STRUCTURES

· Conditional Statements

· If – Then

· If – Then – Else

· Elsif

· CASE

· Loop Constructs

· Loop – End Loop

· While

· For

· NULL Statement

EXCEPTIONS

· Introduction to Exception

· Need of Exception handling

· Types of Exceptions

· Pre-defined Exception

· User Defined Exception

CURSORS

· When and Why to use Cursors

· Difference between Implicit and Explicit Cursors

· Implicit Cursor Attributes and its Usage

· Steps involved in Usage of Cursors

· Mapping cursor variable to record type variable

· Explicit Cursor Attributes and its Usage

STORED PROCEDURES

· Advantages of Subprograms

· Types of Parameters – Formal and Actual

· Different Modes of parameters (in, out, in out)

· Creating the Procedure

· Invoking the Procedure

· Removing the Procedure

· Data Dictionary views for Stored Procedures

STORED FUNCTIONS

· Creating the Function

· Return Statement

· Invoking the Function

· Removing the Function

· Procedure Vs Function

· Data Dictionary views for Functions

PACKAGES

· Introduction to Packages

· Advantages of Packages

· Restrictions in Packages

· Package Specification(header)

· Package Body

· Referencing Package objects

· Removing the Packages

· Application of Overloading

TRIGGERS

· What Trigger is?

· Need for a Trigger

· Types of Triggers

· Database Triggers

· System Triggers

· Statement level and Row level Triggers

· Removing Triggers

Related Courses:

Using Java – for PL/SQL and Database Developers

This Java for PL/SQL and Developers training teaches you to access Oracle Database using JDBC , UCP,

Java stored procedures and SQLJ technologies. Learn to create, load, resolve and publish Java classes

in the Database and more.

Oracle Database: SQL Tuning for Developers

In the Oracle Database: SQL Tuning for Developers course, you learn about Oracle SQL tuning and how to apply tuning techniques to your SQL code. Learn the different ways in which data can be accessed efficiently.

Oracle Database 12c: Analytic SQL for Data Warehousing

This Oracle Database 12c: Analytic SQL for Data Warehousing training teaches you how to use Analytic

SQL to aggregate, analyze, report and model data. Interact with expert Oracle University instructors.