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

Course Name: Oracle Essential SQL 9i

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:
2 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
15
8
298
None
Oracle Essential PLSQL 9i Advanced Oracle SQL 11g
Contact us for the latest pricing

Synopsis for course

SQL is the foundation of all Oracle 9i Database commands.

For end users SQL will often only be used as a simple method of retrieving records, however, Developers who master advanced statements can use it as a powerful and productive programming tool.

Course Contents

Section
Contents
Page

One
Introduction to SQL
2

 
- Definition of SQL
3

 
- Definition of a Database
4

Two
Database Objects
5

 
- Introduction
6

 
- Tables
7

 
- Views
8

 
- Materialized Views
9

 
- Indexes
10

 
- Sequences
11

 
- Packages, Functions and Procedures
12

 
- Synonyms and Schemas
13

Three
The SQL Plus Interface
14

 
- Introduction
15

 
- Logging on
17

 
- Anatomy of SQL Plus
20

 
- ORA-12154: TNS:could not resolve the connect identifier
21

 
- ORA-01017: invalid username/password
22

 
- Exiting an SQL Plus Session
23

 
- Using the Line Editor in SQL Plus
24

 
- Using the Editor in SQL Plus
25

 
- SQL Plus Environment Settings
26

 
- File Manipulation in SQL Plus
28

 
- Spooling Results in SQL Plus
29

 
- Changing Passwords / Clearing the Screen
30

Four
SQL Scripts
31

 
- Commenting SQL Scripts
35

 
- Running SQL in Batch Scripts
37

 
- Passing Parameters with SQL Scripts
39

Five
Simple Queries
41

 
- Introduction
42

 
- Oracle Metadata
43

 
- SQL Syntax
48

 
- Counting Records in an Oracle Table
51

 
- Displaying Literals in a Select statement
53

 
- Displaying Variables in a Select statement
56

 
- Exercise One
57

 
- Column Aliases
61

 
- Column Manipulation with SQL Functions
64

 
- Concatenating Columns in SQL
70

 
- Performing Calculations in SQL
71

 
- Ordering Data in SQL
72

 
- Ordering More than One Column
74

 
- Manipulating Dates in SQL
75

 
- Displaying Dates in SQL
76

 
- Exercise Two
77

Six
Limiting Data Selected with a Where Clause
82

 
- Limiting Data in SQL
83

 
- Structure of an SQL Where Clause
84

 
- Different types of SQL Where Clauses
85

 
- Handling Nulls in Where Clauses
93

 
- Case Restrictions in SQL Where Clauses
94

 
- Using Functions in Where Clauses
95

 
- Rownum in SQL Tables
98

 
- Using Rowid in SQL Tables
100

 
- Exercise Three
102

Seven
Oracle Datatypes
106

 
- Introduction
107

 
- Varchar2
108

 
- Varchar2 and Char
110

 
- Number
111

 
- Date
112

 
- Float
113

 
- Long
114

 
- Large Objects
115

 
- Bfile
119

Eight
Aggregating Values
120

 
- Introduction
121

 
- Using Group By
122

 
- Using Having with Group By
125

 
- Using Rollup to Total Grouping
127

Nine
Tree Walking Tables in SQL
128

 
- Introduction
129

 
- Syntax for Tree Walking in SQL
131

 
- Example of Tree Walking in SQL
132

 
- Advanced Ordering of Siblings in Tree Walks
133

 
- Sys_Connect_By_Path
135

 
- Exercise Four
137

Ten
Joining Tables in an SQL Statement
140

 
- Using Table Aliases in SQL
141

 
- Linking Tables in an SQL statement
143

 
- Problems with Joining Tables in SQL
148

 
- Methods of Joining Tables in SQL
150

 
- Using Ansi SQL/86 to Join Tables
151

 
- Ansi SQL/86 and Outer Joins
152

 
- Ansi SQL/99 Standards (Natural Joins)
155

 
- Ansi SQL/99 Standards (Cross Joins)
156

 
- Ansi SQL/99 Standards (Joins/Using)
157

 
- Ansi SQL/99 Standards (On)
158

 
- Ansi SQL/99 Standards (Multiple On)
159

 
- Ansi SQL/99 Standards (Left Outer Join)
160

 
- Ansi SQL/99 Standards (Right Outer Join)
161

 
- Ansi SQL/99 Standards (Full Outer Join)
162

 
- Merging Tables in SQL
163

 
- Using Union to Merge Tables in SQL
164

 
- Using Union All to Merge Tables in SQL
165

 
- Using Intersect to Merge Tables in SQL
166

 
- Using Minus to Merge Tables in SQL
167

 
- Knowledge Check
168

 
- Using the Merge Syntax in SQL
173

 
- Exercise Five
174

Eleven
Advanced SQL Query Techniques
178

 
- Introduction
179

 
- Pivot
180

 
- Using First_Value/Last_Value in SQL
182

 
- Window Specification
184

 
- Breakdown of Window Specification
185

 
- Window Specification - The Arguments
187

 
- Window Specification - Query Partition
188

 
- Window Specification - The Order By
190

 
- Window Specification - The Windowing
192

 
- Window Specification - Rows between
194

 
- Window Specification - Shortcuts
198

 
- Difference between Range and Rows
199

 
- Using Lead and Lag in SQL
202

 
- Using Rank Function in SQL
206

 
- Using Ntile Function in SQL
213

 
- Using Width_Bucket Function in SQL
215

 
- Using Case Function in SQL
218

 
- Exercise Six
222

Twelve
Creating Database Objects in SQL
226

 
- Creating Tables in SQL
227

 
- Mandatory Columns in SQL Tables
229

 
- Creation Syntax for Tables in SQL
230

 
- Commenting on Tables in SQL
232

 
- Commenting on Columns in SQL
233

 
- Creating Indexes in SQL
234

 
- Index Creation in SQL
237

 
- Disabling Indexes in SQL
239

 
- Creating Views in SQL
240

 
- Creating Sequences in SQL
242

 
- Using Sequences in SQL
243

 
- Altering Sequences in SQL
245

 
- Using Default Value in SQL Tables
246

 
- Renaming Objects in an Oracle Database
247

 
- Dropping Objects from an Oracle Database
250

 
- Granting Access in other Schemas
251

 
- Accessing other Schemas
254

 
- Creating Synonyms in Oracle
255

 
- Exercise Seven
256

Thirteen
Altering and Populating Oracle Tables
258

 
- Some Rules when Altering Tables
259

 
- Syntax for Altering Tables
260

 
- Rules When Populating Tables in SQL
261

 
- Syntax for Populating Tables
262

 
- Using Variables to Populate Tables
267

 
- General Comments
268

 
- Updating Data in Oracle Tables
269

 
- Deleting Data from Oracle Tables
271

 
- Handling Large Objects in SQL Plus
272

 
- Handling BFiles in SQL Plus
274

Fourteen
Committing Records
275

 
- The Difference Between DDL and DML
276

 
- Using Autocommit in SQL Plus
279

Fifteen
Materialized Views
280

 
- Introduction
281

 
- Syntax for Creating Materialized Views
283

 
- Refreshing Materialized Views
285

 
- Building Materialized Views
291

 
- Miscellaneous
293

 
- Exercise Eight
294