Oracle SQL for Report Writers


Course Description

This course gives a basic introduction to Oracle`s version of SQL, focusing on the select statement for use within reports. This will be relevant for all versions of the database, from 8i onwards. Designed to introduce concepts and functionality in an accessible and friendly format, the course has been prepared to assist report writers understand what reporting tools will do behind the scenes and is aimed at those using any reporting tool that generates SQL.


Prerequisites

PC Fundamentals

Oracle Overview

Introducing Oracle
The Elements of the Oracle System
Relational Concepts
What reporting tools do

SQL*Plus and SQL

Introduction
Choosing and Describing Tables
Elements of the SQL Query
The SQL Buffer and Query Layout
Editing SQL Statements
The System Dummy Table
Selecting Columns
Duplicate Information (DISTINCT)
Sorting Information

SQL*Plus Reporting

Introducing SQL*Plus for Reporting
Using SQL*Plus

SQL Functions

The Concatenation Operator
Elements of the SQL Query: Arithmetic
Column Aliases
String Functions
Arithmetic Functions
Date Functions
Mixed Functions

Filtering Data Using Where

Where Operators
Where with Keywords
Where and Logical Operators

Retrieving Data from Multiple Tables

Joining Tables (Equi-Joins)
Aliases for Table Names
Thinking behind Creating Joins
Joining Tables (Non-Equi-Joins)
Joining Tables (Outer Joins)
Joining Tables (Inner Joins)

Group By and Group Functions

Group Function Examples
Group Function with Having

Data Warehousing and OLAP

Materialised views
Dimensions
ROLLUP functions
The ROLLUP operator
The CUBE operator
Analytical Functions
Ranking
Banding
Windowing
Moving Averages
LAG and LEAD

Advanced SQL Functions

Nesting Different Functions
Select with Minus, Union and Intersect
Connect By Prior
Hierarchies