ORACLE SQL/PLSQL Online Training Course Content

Duration:3-4 Weeks

PL/SQL Stands for Procedural Language extension of SQL. PL/SQL is a combination of sql along with the procedural features of Programming languages.With PL/SQL, You can use SQL Statements to Manipulate ORACLE data and flow of Control Statements to process the data. By using of these we can create Data,update data and retrieve the data.

 

Oracle  SQL / PL SQL
  •     Intro to the class
  •     History of SQL
  •     Evolution of SQL
  •     Exercise – declarative SQL
Review of pubs database
  •      SQL constructs:
  •      Select
  •      Project
  •     Join
  •     Describing Oracle tables
  •     Exercise – investigate the pubs database
  •     Restricting row returns
  •     Where clause
  •     Exercise – write a sample query in the pubs database
  •     Complex Boolean logic in SQL
  •     ROWID restrictions
Entity/relation modeling
  •     Types of data relationships
  •     Data normalization
  •     One-to-many relationships
  •     One-to-many relationships
  •     Many-to-many relationships
  •     Recursive many-to-many relationships
Optimizing Oracle SQL
  •     Steps in SQL optimization
  •     Parsing a SQL statement
  •     First_rows vs all_rows optimization
  •     Oracle optimizer overview
  •     Management issues with system-wide optimization
  •     Different modes of SQL optimization
  •     Bi-modal databases
  •     Rule-based optimization
  •     Cost-based optimization
  •     All rows optimization
  •     First_rows optimization
  •     Exercise – display and change optimizer_mode
  •     SQL Semantic Analysis
  •     Generating the execution plan
  •     Using optimizer plan stability
  •     Using the v$sql view
  •     Using the v$sql_plan view
  •     Exercise – Query the library cache
 Using SQL*Plus
  •     Creating basic reports
  •     Exercise – format a simple query in the pubs database
  •     Using the set commands
  •     Adjusting line output
  •     Setting page size and line size
  •     Echo
  •     Feedback
  •     Verify
  •     term out
  •     Exercise – set the SQL*plus environment
  •     Column wrapping
  •     Exercise – add column statements
  •     Creating breaks and summaries
  •     Exercise – add breaks and summaries
  •     Adding prompts to queries
  •     Exercise – parameterize a SQL*Plus script
Explain plan and reading execution plans
  •      Explain plan
  •     Oracle auto trace
  •     How to read an execution plan
 Altering SQL execution plans
  •     Using hints
 Table joining internals
  •      Sort-merge joins
  •     Nested Loop joins
  •     Hash joins
  •     STAR joins
  •     Bitmap joins
  •     Exercise – Change table join techniques & evaluate  performance
  •     Equi -join ( Exercise – write an equi -join)
  •     Outer join ( Exercise – write an outer join)
  •     Hiding joins by creating views (Exercise – create a view of a join)
Sub queries
  •     Using IN, NOT IN, EXISTS and NOT EXISTS
  •     Sub queries
  •     Exercise – write a sub query
  •     Correlated sub query
  •     Non-correlated sub queries
Advanced SQL operators
  •     Between operator
  •     IN and NOT In operators
  •     Sub-queries
  •     EXISTS clause
  •     Using wild cards in queries (LIKE operator)
SQL access methods
  •     Review of Basic joining methods
  •     Merge join
  •     Hash Join
  •     Nested Loop join
  •     Advanced SQL operators
  •     Between operator
 DML and SQL Tuning
  •     Writing and optimizing INSERT statements
  •     Writing and optimizing DELETE statements
  •     Writing and optimizing UPDATE statements
 Optimizer Statistics
  •      Purpose of statistics
  •     Types of statistics (table, column, system)
  •     Histogram statistics
  •     Dynamic sampling
  •     using dbms_stats
  •     Exporting/importing statistics
  •     Statistics management
  •     Exercise – gather system stats
Optimizer Statistics
  •      Purpose of statistics
  •     Types of statistics (table, column, system)
  •     Histogram statistics
  •     Dynamic sampling
  •     using dbms_stats
  •     Exporting/importing statistics
  •     Statistics management
  •     Exercise – gather system stats
 SQL Tuning and full-table scans
  •      Basics of file I/O
  •     Sequential reads vs. scattered reads
  •     When full scans are best
  •     RAM caching in the SGA
  •     Automating table caching
  •     Solid State Disks
  •     Tracking full-scans over time with AWR
  •     Exercise – Query v$sql
 Aggregation IN sql
  •      Aggregation in SQL
  •     Count(*)
  •     Sum
  •     Avg
  •     Min and max
  •     Using the group by clause
PL/SQL Section
Basics of PL/SQL
  •     PL/SQL architecture
  •     PL/SQL and SQL*Plus
  •     PL/SQL Basics
  •     Introduction to PL/SQL
  •     PL/SQL as a 4thgeneration language
  •     Compiling vs. Interpreting
  •     Declare statement
  •     Exercise: Write “hello” world PL/SQL program
  •     Using PL/SQL Variables PL/SQL Constants PL/SQL Data types Error messages – user_errors and show errors PL/SQL wrapper utility
    PL/SQL structures
  •     Simple blocks
  •     Control structures
  •     PL/SQL records
  •     Recognizing the Basic PL/SQL Block and Its Sections
  •     Describing the Significance of Variables in PL/SQL
  •     Distinguishing Between PL/SQL and Non-PL/SQL Variables
  •     Declaring Variables and Constants
  •     Executing a PL/SQL Block
Error checking – exception handling
  •     Defining exceptions
  •     Using the when others clause
  •     Ensuring complete error checking
  •     Passing error messages to calling routine
Boolean logic in PL/SQL
  •     Identifying the Uses and Types of Control Structures
  •     Constructing an IF Statement
  •     Constructing and Identifying Different Loop Statements
  •     Controlling Block Flow Using Nested Loops and Labels
  •     Using Logic Tables
  •     If-then-else structure
  •     Testing for numbers characters and Booleans
    Cursors in PL/SQL
  •     Cursor basics
  •     Using a cursor for a multi-row SQL query
    Iteration in PL/SQL
  •     For loop
  •     While loop
     PL/SQL tables
  •     Defining PL/SQL tables
  •     Reasons to use PL/SQL tables
  •     Populating a PL/SQL table
  •     Retrieving from a PL/SQL table
    Dynamic SQL in PL/SQL
  •     Introduction to the dbms_sql package
  •     Creating a dynamic SQL statement
Nested blocks in PL/SQL
  •     Creating nested blocks
  •     Understanding scope in nested blocks
Triggers in PL/SQL
  •     Triggers and database events
  •     Defining a trigger
  •     Timing a trigger
  •     Enabling and disabling a trigger
Stored procedures, functions and packages
  •     Basics of stored procedures
  •     Basics of functions
  •     Basics of packages
  •     Defining stored procedures & functions
  •     Function and stored procedures prototypes
  •     Passing arguments to functions and stored procedures
  •     Recompiling functions and stored procedures
  •     Pinning packages in the SGA with dbms_shared_pool.keep
  •     Package forward declaration
  •     Package dependency
  •     Package overloading
  •     Listing package information
Bulking in PL/SQL
  •     Bulk queries
  •     Bulk DML (for all statement)
  •     Using cursor attributes
  •     Analyzing impact of bilk operations

 

  1. Send us which course you want to join.
Have a Query?