Oracle 11g Advanced SQL

Length: 2 days
Audience: Programmers, Analysts, Database Administrators, and Users who require advanced SQL techniques to access data in an Oracle database.
Prerequisites: Oracle11g SQL Fundamentals. Students are expected to be experienced in the development of single table operations.
Overview: This course is intended to provide experienced Oracle developers with the advanced SQL skills needed to effectively design and code complex queries and applications required to solve enterprise business problems. Coding examples and workshops serve to reinforce the material presented in class.
  • Multi-table Operations
    • Correlated Subqueries
    • EXISTS Keyword
    • Inline SELECT
    • Table Expressions and Subquery Factoring
    • Scalar Subqueries
  • Grouping Data
    • Aggregate Functions
    • Grouping Multiple Columns
    • HAVING Clause
    • ROLLUP Operations
    • Partial ROLLUP
    • CUBE Operations
    • GROUPING Function
    • GROUPING SETS
    • Cross-Tabulation Queries
    • PIVOT Operation
    • UNPIVOT Operation
  • Analytic Functions
    • Introduction to Analytic Functions
    • Partition Clause
    • Order-By Clause
    • Windowing Clause
    • Ranking Functions
    • Top-N Queries
    • LAG and LEAD
  • Hierarchic Queries
    • CONNECT BY Clause
    • START WITH Clause
    • LEVEL Pseudocolumn
    • Sort by LEVEL
    • SIBLINGS
    • SYS_CONNECT_BY_PATH
    • CONNECT_BY_ISLEAF
    • CONNECT_BY_ROOT
    • CONNECT_BY_ISCYCLE
    • NOCYCLE
    • Recursion through Subquery Factoring
  • Transaction Control
    • Transaction Review
    • Statement Level Read Consistency
    • Transaction Level Read Consistency
    • Oracle Isolation Levels
    • Phantom and Non-Repeatable Reads
    • Read Committed
    • Serializable
  • Table Alternatives
    • Temporary Table Concepts
    • Creating Temporary Tables
    • Temporary Table Limitations
    • Materialized View Concepts
    • Query Rewrite
  • Multiset Operators
    • Nested Table Concepts
    • MULTISET EXCEPT
    • MULTISET INTERSECT
    • MULTISET UNION
  • Regular Expressions
    • Regular Expression Concepts
    • Metacharacters
    • REGEXP_LIKE
    • REGEXP_SUBSTR
    • REGEXP_INSTR
    • REGEXP_REPLACE
    • REGEXP_COUNT
  • Expression Filter Type
    • Expression Column
    • DBMS_EXPFIL Package
    • Applying the EVALUATE Operator
    • Evaluation Semantics
  • Flashback Query
    • Flashback Technology Overview
    • Flashback Queries
    • Using Flashback Queries
    • DBMS_FLASHBACK Package
    • Flashback Version Query
    • Flashback Transaction Query
    • Flashback Limitations
    • UNDO and Flashback
  • ETL Support 
    • MERGE statement
    • External Tables - Concepts
    • Using External Tables
    • Multitable Insert
Back to Oracle 11g Courses