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