Oracle 10g SQL Training

Advanced SQL Queries for Oracle10g Databases - 14 Hour Online Class or 2 Day Classroom Workshop

Advanced SQL skills for professionals who use SQL to query Oracle 10g databases. Covers conditional logic (DECODE and CASE); set operations (UNION, INTERSECT and MINUS); subqueries including NOT EXISTS and WITH; advanced joins and GROUP BY; coding queries on partitioned data; grouping data; analytic functions; MODEL queries in Oracle 10g; hierarchical queries (CONNECT BY, LEVEL with LPAD, WHERE, SIBLINGS, sorts and paths); using regular expressions (REGEXP_LIKE, REGEXP_REPLACE) in Oracle 10g; working with Oracle 10g date and time data; Oracle XML DB and XMLType, and a review of new features and enhancements in Oracle 10g.

Online Class Schedule

Request a Class

 		 

Request More Info

    

Buy Course Book

  

Topic Summary | Print Version

Class Description:

This course will give experienced Oracle technologists the advanced SQL skills necessary to design and code complex queries against Oracle databases.

You will learn to use many advanced SQL coding techniques such as coding analytic functions for data warehouse and decision support queries, using partition outer join to "densify" data, the Oracle10g MODEL clause ("spreadsheet-like capability directly from the database"), Oracle10g regular expressions for pattern matching, using the extended aggregate functions CUBE and ROLLUP, CONNECT BY (hierarchical queries), coding SET operations such as INTERSECT, and more.

An overview Oracle10g SQL-related new features is also provided. (This overview is limited to only those features not included in the primary lessons in the course.)

As with all of our courses, this course is highly customizable to your specific training requirements. Hands-on workshops constitute approximately 50% of the class.

 

Audience: Anyone who uses SQL to query Oracle databases including developers, analysts, database administrators and decision support personnel. Persons working in a data warehouse or decision support environment will benefit greatly from the lessons on analytic functions, grouping with CUBE and ROLLUP, modeling (the Oracle10g MODEL clause) and coding queries on partitioned data.

Prerequisites: Some SQL and SQL*Plus experience is required. For example, you should be comfortable coding basic SQL SELECT statements, including the use of the WHERE clause with basic filters and simple joins. This can be obtained by attending SkillBuilders' Introduction to Oracle class.

Next Courses: Oracle10g SQL Tuning for Developers and DBAs, Oracle10g Database Administration.

Objectives: After successfully completing this course, you will be able to:

  • Design, code and test the most efficient Oracle query for a given business problem
  • Code efficient inner and outer joins, including the use of the Oracle ANSI-compatible join syntax
  • Use Oracle10g partition outer join to "densify" data (i.e. fill in the gaps)
  • Code complex subqueries including correlated subqueries, inline views, subqueries in the column-list, Oracle subquery factoring (the "WITH" clause) and NOT EXISTS queries
  • Code partition independent and partition-aware queries.
  • Use the set operators UNION , INTERSECT and MINUS to combine the results of two or more queries into a single result set.
  • Query and generally handle Oracle date and datetime (i.e. temporal) data.
  • Use the Oracle10g MODEL clause to produce spreadsheet-like results (e.g. sales forecasting) from a query result set
  • Use Oracle10g regular expressions for text pattern matching (i.e. search and optionally replace data using the POSIX-compatible regular expressions)
  • Use Oracle analytic functions to code efficient solutions to complex decision support problems such as ranking (e.g. top earners by department), percentages within a group (e.g. each employees percent of total payroll), cumulative totals (e.g. cumulative salary by department), lag and lead functions (compare to next / previous row - without a self-join), windowing functions and more.
  • Use the Oracle CUBE and ROLLUP extensions to create super-aggregate rows of output (i.e. add additional dimensions of data to the result).
  • Use the Oracle GROUPING function with CUBE and ROLLUP.
  • Use the Oracle GROUPING SETS extension to the GROUP BY clause to control which dimensions are included in the result set.
  • Store hierarchical data and display data results in hierarchy order (e.g. employees sorted by management chain) with the Oracle CONNECT BY clause
  • Use the Oracle DECODE function and Oracle CASE expression to add IF / THEN / ELSE (conditional) logic to an SQL query.
  • Use basic Oracle XML functions to search and extract XML data from the Oracle database

Topic Summary

To Top | Topic Summary