Oracle SQL 10g: Level 1: Introduction for Beginners (4 day)

Course Overview

SQL forms the cornerstone of all relational database operations, regardless of the language or tool being used to develop database applications. While SQL is often used only as a simple access method, it can also be employed as a powerful and productive programming tool.

In this course, you learn to use the full potential of SQL to write complex queries for Oracle databases. You also learn how to choose the best query method for each application and test queries to ensure accuracy and avoid common errors and pitfalls.

Audience

This course is valuable for consultants, engineers, developers, analysts and others who are developing systems using Oracle databases. Course 590, " Oracle9iIntroduction ", or Course 593, " Oracle Database 10gIntroduction ", or equivalent SQL knowledge is assumed.

Skills Gained

• Formulate complex queries with Oracle SQL and SQL*Plus
• Apply the full range of complex query types
• Use the analytic OLAP functions of Oracle
• Select the best query method for each application requirement
• Test SQL queries completely
• Select the most efficient solution to complex SQL problems
• Choose between Oracle and ANSI style joins

Course Outline

Introduction and Overview

The uses of SQL queries

• SQL's central role
• Why SQL can be both easy and difficult

Enhancing query performance

• Query optimisation
• Choosing the best query method

Using Advanced SQL Functions to Build Queries

Aggregate functions

• Grouping in several levels
• Grouping and NULLs
• CUBE and ROLLUP
• Performance and grouping

Single-row functions

• String manipulation functions
• Simulating IF...THEN...ELSE with functions
• Handling regular expressions with Oracle 10gfunctions

Performing Extensive Analysis with Analytic Functions

Calculating ranks

• RANK and DENSE_RANK
• ROW_NUMBER depending on ORDER BY
• Calculating percentiles

Extending the use of aggregates

• Partitioning in multiple levels
• Computing running totals
• Comparing row and aggregate values

Defining sliding Window boundaries

• By row number
• By value
• By time interval

Developing Complex Joins

Using inner and outer joins

• Building multiple table joins
• When to use theta joins
• Grouping and joins
• Joins and performance

How and when to use self-joins

• Joining a table to itself
• Implementing recursive self-joins with CONNECT BY
• CONNECT BY and join simultaneously
• Oracle 10genhancements to CONNECT BY

Applying the ANSI standard join syntax

• INNER JOIN
• CROSS JOIN
• LEFT, RIGHT and FULL OUTER JOIN
• Subtle differences between new ANSI and old Oracle style

Using the set operators

• UNION
• INTERSECT
• MINUS

Building Subqueries

Non-correlated subqueries

• Subqueries in several levels
• Subqueries that return NULL
• Multiple row subqueries

Using correlated subqueries

• Fetching main query values
• The EXISTS operator
• Subqueries in updates

Subqueries in the FROM clause

• Breaking up a complex problem into manageable pieces
• Factoring subqueries for reusability
• An alternative to views

Subqueries as expressions

• Subqueries in the column list
• Correlated and non-correlated subqueries in expressions

Using Views and Temporary Tables

Overcoming obstacles with views

• Multiple group levels in one query
• How views impact performance

Temporary tables as alternatives to views

• Avoiding interference from other users
• Tailoring temporary tables