Download our latest
Android and iPhone apps ...
At a glance ...
Pages
192
Exercises
4
Sections
5
Public
On-Site
Level
1
Days
1

Course Name: Oracle SQL for Users III

Duration:
Minimum skill level required:
On-Site course:
Public course:
Sections:
No of exercises:
No of pages in the manual:
Pre-Requisite course(s):
Suggested further course(s):
Price guide:
1 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
5
4
192
Oracle SQL for Users I Oracle SQL for Users II
None
Contact us for the latest pricing

Synopsis for course

This course concentrates on using Oracle's advanced SQL functions to produce output suitable for Data Warehousing and Management Information.

Course Contents

Section
Section Name
Page

One
Joining Tables using Data Sets
2

 
- Creating Data Sets
3

 
- Union Syntax
4

 
- Union
5

 
- Union Example
6

 
- Join Rules
8

 
- Union All
9

 
- Intersect
12

 
- Minus
14

 
- Knowledge Check
16

 
- Tree Walks
21

 
- Tree Walking Syntax
23

 
- Use of Level
26

 
- Use of Nocycle (ORA-01436)
27

 
- Use of Connect_by_iscycle
29

 
- Advanced Ordering of Tree Walks using Siblings
30

 
- Output of Tree Walks using Sys_Connect_By_Path
31

 
- Limiting Records using Connect_by_isleaf
32

 
- Showing the Root using Connect_by_root
34

 
- Exercise One
36

Two
Advanced SQL Query Functions/Techniques
42

 
- Introduction
43

 
- Pivot
44

 
- Adding a Total to a Pivot
49

 
- Changing the Pivot Headings of the X Axis
50

 
- Rotating the Axis of the Pivot
52

 
- Unpivot
53

 
- Unpivot and Null values
56

 
- Using First_Value/Last_Value in SQL
57

 
- Ignoring Nulls with First/Last_Value
59

 
- Window Specification
60

 
- Breakdown of Window Specification
61

 
- Window Specification - The Arguments
63

 
- Window Specification - The Query Partition
64

 
- Window Specification - The Order By
66

 
- Window Specification - The Windowing
68

 
- Window Specification - Rows between
70

 
- Window Specification - Shortcuts
74

 
- Difference between Range and Rows
75

 
- Using Lead and Lag in SQL
78

 
- Using Rank Function in SQL
82

 
- Using Ntile Function in SQL
89

 
- Using Width_Bucket Function in SQL
91

 
- Using Listagg Function in SQL
94

 
- Exercise Two
96

Three
Advanced Date Datatypes
102

 
- Introduction
103

 
- Database Timezone
104

 
- Date Datatypes
106

 
- Timestamp
110

 
- Extract
115

 
- Timestamp with Time Zone
116

 
- Time Zone with Extract
117

 
- TZ_Offset
118

 
- Time Zones and UTC
119

 
- From_TZ
121

 
- Timestamp with Local Time Zone
122

 
- Intervals
123

 
- Using Intervals
126

 
- Intervals and Extract
129

 
- Exercise Three
130

Four
Aggregating Values in Oracle SQL 11g
134

 
- Introduction
135

 
- Using Distinct with other Aggregates
136

 
- Using Group By
137

 
- Using Group By with Aliases
140

 
- Using Group By with Many Columns
141

 
- Using Having with Group By
143

 
- Using Rollup to 'Sum' the Group By
145

 
- Using Partial Rollup with several non-aggregates
147

 
- Using Full Rollup with several non-aggregates
148

 
- Getting the Total to the Top
149

 
- Using Cube with Group By
150

 
- Using Grouping Sets with Group By
152

 
- Using Grouping ID with Grouping Sets
154

 
- Using Group ID with Group By
156

 
- Nesting Group Functions
158

Five
Flashback
161

 
- Introduction
162

 
- Flashback Query
163

 
- Using the Dbms_Flashback package
167

 
- Using Time Flashbacks
168

 
- Disabling Flashbacks
172

 
- Using SCN Flashbacks
173

 
- Implicit Flashback using AS OF
175

 
- Flashback using Versions Between
177

 
- Using Flashback_transaction_query View
182

 
- Exercise Four
185