Oracle SQL PL/SQL Course Contents

Online Training Course Contents for Oracle SQL PLSQL

Oracle SQL PL/SQL Course Contents – Join Now

The Oracle SQL PL/SQL Course is designed for developers. The developers who wants to store the application data in Oracle database.

Creating Views in Oracle Database  – Watch Introduction Lecture

  • Introduction to Oracle SQL PL/SQL
  • Describe a view. Create, alter the definition, and drop a view.
  • Retrieve, Insert, update and delete data through a view
  • Create and use an inline view
  • Perform Top ‘N’ Analysis

Oracle SQL PL/SQL Syntax and Logic – Watch Video on Variables

  • PL/SQL Blocks and Programs
  • Conditional Statements – IF/THEN and CASE
  • Comments and Labels
  • WHILE and FOR Loops

Lab assignment : logic, statement and breaking the statement

Stored Procedures and Functions – Watch Video

  • Stored Subprograms
  • Define Procedures and Functions
  • Creating a Stored Procedure
  • Calling a Stored Procedure
  • Passing Parameters and Default Arguments
  • Parameter Modes
  • Creating a Stored Function
  • Calling a Stored Function
  • Stored Functions and SQL
  • Local Procedures and Functions

Exception Handling in Oracle SQL PL/SQL

  • SQLCODE and SQLERRM
  • Exception Handlers
  • Nesting Blocks
  • Scope and Name Resolution
  • User-Defined Exceptions
  • Compile-Time Warnings

Cursors in Oracle SQL PL/SQL

  • Multi-Row Queries
  • Declaring and Opening Cursors
  • Fetching Rows
  • Closing Cursors
  • The Cursor FOR Loop
  • FOR UPDATE Cursors
  • Cursor Parameters
  • The Implicit (SQL) Cursor

Maintaining Data Integrity 

  • Implement data integrity constraints, Maintain integrity constraints
  • Obtain constraint information from the data dictionary

Query Processing

  • Query decomposition and Query optimization
  • Code generation
  • Runtime query execution
  • Rule-Based Optimization
  • Cost-Based Optimization

When to Use Specific Constructs

  • EXISTS Is Preferable to DISTINCT
  • WHERE Versus HAVING
  • UNION Versus UNION ALL
  • LEFT Versus RIGHT OUTER JOIN

Avoid Unnecessary Parsing

  • Using Bind Variables
  • Using Table Aliases

SQL Transformations

  • Simple view merging
  • Complex view merging
  • Subquery “flattening”
  • Transitive predicate generation
  • Common subexpression elimination
  • Predicate pushdown and pullup
  • Outer-join to inner join conversion

Cost-based query transformations

  • Materialized view rewrite
  • OR-expansion
  • Star transformation
  • Predicate pushdown for outer-joined views

Access path selection

  • Join ordering
  • Bitmap indexes and Bitmap join indexes
  • Domain indexes and extensibility
  • Fast full index scans
  • Index joins
  • Index skip scans
  • Partition optimizations
  • Partition-wise joins, GROUP-BY’s and sorts
  • Sort elimination
  • OLAP optimizations
  • Parallel execution
  • Hints

Cost Model and Statistics

  • Optimizer statistics
    • Object-level statistics
    • System statistics
    • User-defined statistics
  • Statistics management
    • Automatic statistic gathering
    • Parallel sampling
    • Monitoring
    • Automatic histogram determination
    • Dynamic sampling.
  • Optimization cost modes.

Dynamic Runtime Optimizations

  • Dynamic degree of parallelism and Dynamic memory allocation.
  • Database resource manager

Ranking Functions

  • RANK, DENSE_RANK and ROW_NUMBER
  • Handling NULLs
  • Top/bottom N queries
  • FIRST/LAST
  • NTILE
  • WIDTH_BUCKET
  • CUME_DIST and PERCENT_RANK
  • Hypothetical Functions

Join Now

November 14, 2018

0 responses on "Oracle SQL PL/SQL Course Contents"

Leave a Message

Your email address will not be published. Required fields are marked *

Featured Testimonial

I would like to mention a new feature "Interactive Video". You can't simply watch the video. You...Read more

Indiana Jones

Co-Founder Director

Certificate Code

Who’s Online

There are no users currently online

Last Tweets

top
X