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

Course Name: Oracle SQL 10g

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:
5 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
20
12
449
None
Oracle Essential PLSQL 10g
Contact us for the latest pricing

Synopsis for course

An intense five day course designed to prepare Delegates for programming in an Oracle 10g Database, when combined with the Oracle 10g PLSQL course.

This course will give Delegates a complete knowledge of SQL in Oracle 10g databases.

Course Contents

Section
Contents
Page

One
Introduction to SQL
2

 
- Definition
3

 
- Usage
4

 
- Used For/Used by
5

 
- Definition of a Database
6

Two
Database Objects
7

 
- Introduction
8

 
- Tables
9

 
- Views
10

 
- Indexes
11

 
- Sequences
12

 
- Packages, Functions and Procedures
13

 
- Synonyms and Schemas
14

Three
The SQL Interface
15

 
- Introduction
16

 
- Anatomy
18

 
- Unsuccessful Logon
19

 
- Exiting
21

 
- Line Editor
22

 
- Environment Settings
23

 
- File Manipulation
24

Four
SQL Scripts
26

 
- Commenting
30

Five
Simple Queries
32

 
- Introduction
33

 
- Metadata
34

 
- Syntax
38

 
- Counting Records
40

 
- Displaying Literals
42

 
- Displaying Variables
45

 
- Exercise One
46

 
- Column Labels
48

 
- Column Manipulation
51

 
- Column Concatenation
56

 
- Column Calculation
57

 
- Sorting Columns
58

 
- Displaying Dates
60

 
- Exercise Two
62

 
- Where Clauses
68

 
- Null Values
80

 
- Case Restrictions
81

 
- Exercise Three
82

Six
Oracle Datatypes
89

 
- Introduction
90

 
- Varchar2
91

 
- Varchar2 and Char
93

 
- Number
94

 
- Date
95

 
- Float
96

 
- Binary Float and Binary Double
97

 
- Long
98

 
- Large Objects
99

 
- Migrating Long columns
103

 
- Bfile
105

Seven
Aggregating Values
106

 
- Introduction
107

 
- Group By
108

 
- Having
111

 
- Rollup
113

Eight
Joining Tables
114

 
- Table Aliases
115

 
- Linking Tables
117

 
- Cartesian Products
118

 
- Joining Tables
120

 
- Ansi SQL/86
121

 
- Ansi SQL/86 and Outer Joins
123

 
- Ansi SQL/86 Outer Join Example
125

 
- Ansi SQL/92 Standards (Natural Joins)
126

 
- Ansi SQL/92 Standards (Cross Joins)
127

 
- Ansi SQL/92 Standards (Joins/Using)
128

 
- Ansi SQL/92 Standards (On)
129

 
- Ansi SQL/92 Standards (Multiple On)
130

 
- Ansi SQL/92 Standards (Left Outer Joins)
131

 
- Ansi SQL/92 Standards (Right Outer Joins)
132

 
- Ansi SQL/92 Standards (Full Outer Joins)
133

 
- Merging Tables
134

 
- Union syntax
135

 
- Union
136

 
- Union All
137

 
- Intersect
138

 
- Minus
139

 
- Knowledge Check
140

 
- Tree Walks
145

 
- Exercise Four
153

Nine
Advanced Queries
158

 
- First/Last_Value
159

 
- Lead and Lag
161

 
- Case
164

 
- Ranking
168

 
- Width Bucket
177

 
- With
180

 
- Exercise Five
182

Ten
Creating Database Objects
186

 
- Tables
187

 
- Table Nullable and Mandatory Columns
189

 
- Table Creation Syntax
190

 
- Table Default Value
191

 
- Table Pseudo Columns
192

 
- Table Deletion Syntax
195

 
- Table Comments
196

 
- Index Creation
198

 
- Primary Key Index Creation Syntax
201

 
- Unique Index Creation Syntax
202

 
- Non Unique Index Creation Syntax
203

 
- Foreign Key Index Creation Syntax
204

 
- Disabling Indexes
205

 
- Index Deletion Syntax
206

 
- Index Metadata
207

 
- View Creation Syntax
208

 
- View Types
209

 
- View Deletion Syntax
210

 
- Sequence Creation Syntax
211

 
- Sequence Usage
212

 
- Sequence Alter Syntax
214

 
- Renaming Objects
215

 
- Directory
218

 
- Directory Removing
219

 
- Directory Problems
220

 
- Directory Metadata
221

 
- Granting Access to other Schemas
222

 
- Accessing other Schemas
225

 
- Synonyms
226

 
- Exercise Six
228

Eleven
Altering and Populating Tables
230

 
- Altering Tables - Rules
231

 
- Altering Tables - Syntax
232

 
- Altering Tables - Unused Columns
233

 
- Populating Tables - Rules
236

 
- Populating Tables - Syntax
237

 
- Populating Tables - Database Values
239

 
- Populating Tables - Default
240

 
- Populating Tables - General Comments
241

 
- Populating Tables - Updating Data
242

 
- Populating Tables - Deleting Data
243

 
- Populating Tables - Large Objects
244

 
- Populating Tables - Bfiles
246

 
- Populating Tables - Binary Float/Doubles
247

 
- Populating Tables - Merge
248

 
- Populating Tables - Multiple Table Inserts
252

 
- Populating Tables - Unconditional Multiple Inserts
253

 
- Populating Tables - Conditional First Multiple Insert
255

 
- Populating Tables - Conditional All Multiple Insert
257

 
- Populating Tables - Pivoting Multiple Insert
259

 
- Populating Tables - Merge
261

Twelve
Committing Records
256

 
- Autocommit
269

 
- Savepoints
270

 
- Exercise Seven
271

 
- Flashback Query
277

 
- DBMS Flashback
283

 
- Time Flashbacks
284

 
- SCN Flashbacks
289

 
- Flashback Example
290

 
- Implicit Flashback
293

 
- Exercise Eight
296

Thirteen
Materialized Views
298

 
- Introduction
299

 
- Syntax
301

 
- Refreshing
303

 
- Building
309

 
- Removing
311

 
- Miscellaneous
312

 
- Metadata
313

 
- Exercise Nine
314

Fourteen
External Tables
316

 
- Introduction
317

 
- Using
324

 
- Exercise Ten
326

Fifteen
Advanced Date Datatypes
327

 
- Date Datatypes
328

 
- Database Timezone
330

 
- Timestamp
336

 
- Timestamp and Sysdate
337

 
- Extract
341

 
- To_Timestamp
342

 
- Timestamp with Time Zone
344

 
- Time Zone and Extract
347

 
- TZ Offset
348

 
- Timezones and UTC
349

 
- To_Timestamp_TZ
351

 
- From_TZ
352

 
- Timestamp with Local Time Zone
353

 
- Intervals
354

 
- Assigning Year Intervals
357

 
- Assigning values to Day Intervals
359

 
- Using Intervals
361

 
- Intervals and Extract
367

 
- Exercise Eleven
368

Sixteen
Basic Administration
375

 
- Passwords
376

 
- Sqlprompt
377

 
- Creating Users/Roles
378

Seventeen
Object Orientated Programming
381

 
- Introduction
382

 
- Definition of Objects
383

Eighteen
Row Objects
387

 
- Introduction
388

 
- Creating
389

 
- Metadata
391

 
- Data
392

 
- Indexes
393

 
- Views
395

 
- Removing
396

 
- OIDs
398

Nineteen
Column Objects
405

 
- Introduction
406

 
- Describing
407

 
- Inserting Into
410

 
- Selecting From
412

 
- Updating
417

 
- Object Views
418

Twenty
Collections
420

 
- Introduction
421

 
- Varraying Arrays
422

 
- Defining a single element
423

 
- Inserting
424

 
- Selecting
426

 
- Updating
428

 
- Deleting
429

 
- Defining using an Abstract datatype
430

 
- Inserting an Abstract datatype
431

 
- Selecting an Abstract datatype
432

 
- Nested Tables
433

 
- Defining
434

 
- Inserting
438

 
- Selecting
441

 
- Updating
444

 
- Deleting
445

 
- Exercise Twelve
446