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

Course Name: Oracle SQL for Users II

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
8
4
216
Oracle SQL for Users I
Oracle SQL for Users III
Contact us for the latest pricing

Synopsis for course

More SQL training concentrating on the Select statement, ideally suited to anyone who is a front end user and needs to develop their skills with selecting records from an Oracle database.

Course Contents

Section
Section Name
Page

One
Producing Formatted Output in SQLPlus
2

 
- Using the Help facility
3

 
- Using the Spool Command
5

 
- Using the Host Command
7

 
- Changing the Prompt
8

 
- Displaying the Time
9

 
- Displaying all Environment Settings
10

 
- Saving the Settings using Store Set
11

 
- Changing Settings in glogin.sql
12

 
- Renaming Columns for the Session
15

 
- Formatting Columns (Varchar2)
18

 
- Formatting Columns (Number)
19

 
- Formatting Columns (Date)
20

 
- Wrapping Columns
21

 
- Justifying Column Headings
23

 
- Suppressing Columns
25

 
- Storing Values in Columns
27

 
- Viewing Columns
28

 
- Viewing All Columns
29

 
- Temporarily Disabling Columns
30

 
- Clearing Columns
31

 
- Copying Columns with Like
32

 
- Using Columns to deal with Nulls
33

 
- Multi-Line Output
34

 
- Using Break in the Output
35

 
- Using Aliases with Break
37

 
- Using Skip with Break
38

 
- Using Skip Page with Break
39

 
- Using Skip Row with Break
40

 
- Displaying Duplicates with Break
41

 
- Viewing/Clearing Breaks
42

 
- Exercise One
43

 
- Using Compute with Break
49

 
- Using Compute Labels with Break
51

 
- Using Compute Sub-totalling with Break
55

 
- Combining Computes
57

 
- Setting Titles for the Output
59

 
- Using Ttitle for the Output
60

 
- Using SQL.PNO in the Title
62

 
- Using Variables in the Title
64

 
- Using Btitle
66

 
- Using Repheader and Repfooter
68

 
- Reviewing the Title settings
69

 
- Suppressing the Title settings
70

Two
Using Substitution in SQL
71

 
- Introduction
72

 
- Using Ampersands
73

 
- Using Ampersands with Strings
74

 
- Using the same value more than once
75

 
- Persistent values (Using Undefine)
76

 
- Advanced Substitution for Advanced Users
77

 
- Preventing prompting when Ampersands are encountered
79

 
- Redefining the Prompt (Set Define)
80

 
- Removing the Verification (Verify off)
81

 
- Defining Session Variables
82

 
- Using the Accept command in SQLPlus
86

 
- Within Insert statements
91

 
- Within Scripts
92

 
- Exercise Two
95

Three
Using the Case Function
104

 
- Introduction
105

 
- Example
106

 
- Nested Case
107

 
- Using Case Function in Order By
108

 
- Using Searched Case Function in SQL
109

 
- Searched Case Function with Sub-queries
111

Four
Using the Sample Function
112

Five
Advanced Ordering of Records
115

 
- Dealing with Nulls
116

 
- Using Column position when Sorting
118

 
- Using Expressions when Sorting
119

 
- Using Aliased Columns
120

 
- Using Alternative Sorts
121

Six
Using Alternative Quoting Mechanism in SQL
123

 
- Introduction
124

 
- Using Alternative Quoting Mechanism
125

Seven
Regular Expressions in Oracle SQL
127

 
- Introduction
128

 
- Using REGEXP_LIKE in Where Clauses
131

 
- Case Sensitivity
133

 
- Line Anchors
135

 
- Using the Dot (Any Character)
137

 
- Counting Characters {Intervals}
140

 
- Character Lists
144

 
- Excluding Character Lists
148

 
- Alternatives to Character Lists
149

 
- Class Shorthands
152

 
- Or Patterns (Alternatives)
153

 
- Or Patterns with Character Lists
154

 
- Using the Question Mark (Optional)
155

 
- Using the Plus sign (Mandatory)
158

 
- Using the Star sign (Optional)
160

 
- Backreferencing
161

 
- POSIX
152

 
- Using POSIX in Oracle [:upper:] example
165

 
- Using POSIX in Oracle [:digit:] example
165

 
- Using POSIX in Oracle - Character equivalents
167

 
- Using Regexp_count in SQL
168

 
- Using Regexp_replace in SQL
172

 
- Using Regexp_instr in SQL
174

 
- Using Regexp_substr in SQL
176

 
- Using Regular Expressions in Oracle
178

 
- Exercise Three
179

Eight
Using Sub-Queries in Oracle SQL
188

 
- Introduction
189

 
- Using Equals
190

 
- In
193

 
- Exists
195

 
- Any
197

 
- Some
198

 
- All
199

 
- Correlated Sub-Queries
200

 
- Inline Views
201

 
- Inline View Example
202

 
- Sub-Queries in a Select Statement
203

 
- Sub-Query Factoring (Using With)
204

 
- Sub-Query Factoring Example with Aliases
206

 
- Sub-Query Factoring Example with Joins
207

 
- Exercise Four
208