ANSI SQL Advanced

Length: 2 Days
Audience: Programmers, Analysts, Managers, and Database Administrators requiring an understanding of SQL to work with a relation database system such as Oracle, DB2, or SQL Server. Emphasis is placed on writing queries, i.e., data retrieval operations.
Prerequisites: Introduction to SQL
Overview: This course provides a hands-on investigation of SQL according to the ANSI SQL:1999 Standard. It is a continuation of the ANSI SQL Introduction, using examples and workshops to expose students to the more complex query operations available in SQL. This course examines multi-table operations in greater detail. After considering the join operations, table expressions, subqueries, and set operations are discussed. Additional advanced topics are also presented, including the use of analytic functions and data manipulation operations.
Examples and exercises are provided for the following ANSI-compliant systems:
  • Oracle11g
  • Microsoft SQL Server 2005
  • DB2 UDB 8
Topics discussed include:
  • Multi-table Operations
    • Joins
    • Subqueries
    • Scalar Subqueries
    • Correlated Subqueries
    • Set Operations: UNION, UNION ALL, INTERSECT
    • Table Expressions
    • Common Table Expressions
    • Recursive Queries
  • Grouping Data
    • Aggregate Functions
    • HAVING Clause
    • ROLLUP Operations
    • CUBE Operations
    • GROUPING Function
  • Analytic Functions
    • Introduction to Analytic Functions
    • RANK, DENSE_RANK, ROW_NUMBER
    • Partition Clause
    • Order-By Clause
    • Windowing Clause
    • Ranking Functions
    • Top-N Queries
  • Transaction Processing
    • Data Manipulation
    • INSERT, UPDATE, DELETE
    • Transaction Concept
    • Completing a Transaction
    • COMMIT, ROLLBACK, SAVEPOINT
    • Isolation Levels
    • Phantom and Non-Repeatable Reads
  • Table Alternatives
    • Working with Views
    • Creating a View
    • Using Views
    • Restriction on Views
    • Queries Against Views
    • Materialized View Concepts
Back to Other Courses