Syllabus of Oracle Training in Pune (Best Oracle Training Institute In Pune)

Oracle SQL Training Syllabus

Introduction to Oracle Database – 2 hours

• List the features of Oracle Database 11g
• 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 – 1 hour

• 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 – 1 hour

• 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 – 1 hour

• Describe the differences between single-row and multiple-row functions
• Manipulate strings with character functions 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

Display Data From Multiple Tables Using Joins – 1 hour

• 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 by using a self-join

Introduction to PL/SQL – 2 hours

• PL/SQL Overview
• Benefits of PL/SQL Subprograms
• Overview of the Types of PL/SQL Blocks
• Create a Simple Anonymous Block
• Generate Output from a PL/SQL Block

PL/SQL Identifiers – 1 hour

• 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 the Bind Variables?
• Sequences in PL/SQL Expressions

Write Executable Statements – 1 hour

• Describe Basic PL/SQL Block Syntax Guidelines
• Comment Code
• Deployment of SQL Functions in PL/SQL
• How to convert Data Types?
• Nested Blocks
• Identify the Operators in PL/SQL

Interaction with the Oracle Server – 1 hour

• Invoke SELECT Statements in PL/SQL to Retrieve data
• Data Manipulation in the Server Using PL/SQL
• SQL Cursor concept
• Usage of SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Control Structures – 1 hour

• Conditional Processing Using IF Statements
• Conditional Processing Using CASE Statements
• Use a simple Loop Statement
• Use While Loop Statement
• Use For Loop Statement
• Describe the Continue Statement

Composite Data Types – 1 hour

• Use PL/SQL Records
• The %ROWTYPE Attribute
• Insert and Update with PL/SQL Records
• Associative Arrays (INDEX BY Tables)
• Examine INDEX BY Table Methods
• Use INDEX BY Table of Records

Explicit Cursors – 1 hour

• What are Explicit Cursors?
• Declare the Cursor
• Open the Cursor
• Fetch data from the Cursor
• Close the Cursor
• Cursor FOR loop
• Explicit Cursor Attributes
• FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling – 1 hour

• 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

Dynamic SQL – 1 hour

• The Execution Flow of SQL
• What is Dynamic SQL?
• Declare Cursor Variables
• Dynamically executing a PL/SQL Block
• Configure Native Dynamic SQL to Compile PL/SQL Code
• Invoke DBMS_SQL Package
• Implement DBMS_SQL with a Parameterized DML Statement
• Dynamic SQL Functional Completeness

Design Considerations for PL/SQL Code – 1 hour

• Standardize Constants and Exceptions
• Understand Local Subprograms
• Write Autonomous Transactions
• Implement the NOCOPY Compiler Hint
• Invoke the PARALLEL_ENABLE Hint
• The Cross-Session PL/SQL Function Result Cache
• The DETERMINISTIC Clause with Functions
• Usage of Bulk Binding to Improve Performance

Triggers – 1 hour

• Describe Triggers
• Identify the Trigger Event Types and Body
• Business Application Scenarios for Implementing Triggers
• Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
• Identify the Trigger Event Types, Body, and Firing (Timing)
• Statement Level Triggers Versus Row Level Triggers
• Create Instead of and Disabled Triggers
• How to Manage, Test, and Remove Triggers?

Manage PL/SQL Code – 1 hour

• What Is Conditional Compilation?
• Implement Selection Directives
• Invoke Predefined and User-Defined Inquiry Directives
• The PLSQL_CCFLAGS Parameter and the Inquiry Directive
• Conditional Compilation Error Directives to Raise User-Defined Errors
• The DBMS_DB_VERSION Package
• Write DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text
• Obfuscation and Wrapping PL/SQL Code

Manage Dependencies – 1 hour

• Overview of Schema Object Dependencies
• Query Direct Object Dependencies using the USER_DEPENDENCIES View
• Query an Object’s Status
• Invalidation of Dependent Objects
• Display the Direct and Indirect Dependencies
• Fine-Grained Dependency Management in Oracle Database 11g
• Understand Remote Dependencies
• Recompile a PL/SQL Program Unit