Top
Email
Bottom
Advanced Oracle SQL 11g
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Download our latest
Android and iPhone apps ...
Click on whichever one of our Apps you are interested in

Advanced Oracle SQL 11g course at a glance

Pages
740No of slides in the Manual
Exercises
12Total No of Exercises
Sections
28No of main topics, see index below
Public
NoPublic course in the UK and Ireland
On-Site
YesWe can come to your venue
Level
21 - Beginners
2 - Intermediate
3 - Advanced
Days
3Week days but we can put on weekend courses
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:
3 days
2 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
28
12
740
Oracle Essential SQL 11g
None
Contact us for the latest pricing

Index of Advanced Oracle 11g SQL training course

Section
Contents
 

One
Advanced SQL
1

 
- Introduction
3

Two
Substitution in Oracle SQL 11g
4

 
- Introduction
5

 
- Using Ampersands to substitute values
6

 
- Using Ampersands with Strings
7

 
- Using the same value more than once
8

 
- Persistent values (Using Undefine or Undef)
9

 
- Persistent values (Using Define or Def)
10

 
- Advanced Substitution for Advanced Users
13

 
- Preventing prompting when Ampersands are encountered
15

 
- Redefining the Prompt (Set Define)
16

 
- Removing the Verification (Verify off)
17

 
- Defining Session Variables
18

 
- Using the Accept command in SQLPlus
19

 
- Within Insert Statements
24

 
- Within Scripts
25

 
- Exercise One
27

Three
Advanced Features of SQL*Plus
31

 
- Using the Help facility
32

 
- Using the Host Command
34

 
- Changing the SQL*Plus Prompt
35

 
- Displaying the Time
36

 
- Displaying all Environment Settings
37

 
- Saving the Environment Settings using Store Set
38

 
- Changing Environment Settings in glogin.sql
39

 
- Creating HTML Pages
40

 
- Renaming Columns for the Session
42

 
- Formatting Columns (Varchar2)
45

 
- Formatting Columns (Number)
46

 
- Formatting Columns (Date)
47

 
- Wrapping Columns
48

 
- Justifying Column Headings
50

 
- Suppressing Columns
52

 
- Truncate Columns
53

 
- Storing Values in Columns
54

 
- Viewing Columns
55

 
- Viewing All Columns
56

 
- Temporarily Disabling Columns
57

 
- Clearing Columns
58

 
- Copying Columns with Like
59

 
- Using Columns to deal with Nulls
60

 
- Multi-Line Output
61

 
- Using Break in the Output
62

 
- Using Aliases with Break
64

 
- Using Skip with Break
65

 
- Using Skip Page with Break
66

 
- Using Skip Row with Break
67

 
- Displaying Duplicates with Break
68

 
- Viewing/Clearing Breaks
69

 
- Using Compute with Break
70

 
- Using Compute Labels with Break
72

 
- Using Compute Sub-totalling with Break
76

 
- Combining Computes
78

 
- Setting Titles for the Output
80

 
- Using SQL.PNO in the Title
83

 
- Using Variables in the Title
85

 
- Using Btitle
87

 
- Using Repheader and Repfooter
89

 
- Reviewing the Title settings
90

 
- Suppressing the Title settings
91

 
- Exercise Two
92

Four
Using the Alternative Quoting Mechanism in SQL
97

 
- Introduction
98

 
- Using Alternative Quoting Mechanism
99

Five
Metadata in Oracle SQL 11g
101

 
- Introduction
102

 
- Selecting from Dictionary
103

 
- Metadata Synonyms
104

 
- Metadata Comments
105

Six
Regular Expressions in Oracle SQL 11g
107

 
- Introduction
108

 
- Using REGEXP_LIKE in Where Clauses
111

 
- Case Sensitivity
113

 
- Line Anchors in Regex
115

 
- Using the Dot (Any Character)
117

 
- Counting Characters {Intervals}
121

 
- Character Lists in Regex
125

 
- Excluding Character Lists
131

 
- Alternatives to Character Lists
132

 
- Class Shorthands
136

 
- Or Patterns (Alternatives)
137

 
- Or Patterns with Character Lists
138

 
- Using the Question Mark (Optional)
139

 
- Using the Plus sign (Mandatory)
142

 
- Using the Star sign (Optional)
145

 
- Backreferencing in Regex
146

 
- POSIX
148

 
- Using POSIX in Oracle – [:upper] example
150

 
- Using POSIX in Oracle – [:digit] example
151

 
- Using POSIX in Oracle – character equivalents
152

 
- Using Regexp_count in SQL
153

 
- Using Regexp_replace in SQL
157

 
- Using Regexp_instr in SQL
159

 
- Using Regexp_substr in SQL
161

 
- Using Regular Expressions in Oracle
163

 
- Exercise Three
164

Seven
Ordering Records in Oracle SQL 11g
172

 
- Syntax
173

 
- Ordering by Columns
174

 
- Ordering by more Columns
176

 
- Dealing with Nulls
177

 
- Using Column position when Sorting
179

 
- Using Expressions when Sorting
180

 
- Using Aliased Columns
181

 
- Using Alternative Sorts
182

 
- Fetch first rows of a Table in Oracle 11g (Top-N)
184

 
- Using Percent with Fetch in Oracle 11g
186

 
- Using Offset with Fetch in Oracle 11g
187

Eight
Aggregating Values in Oracle SQL 11g
188

 
- Introduction
189

 
- Using Distinct with other Aggregates
190

 
- Using Group By
191

 
- Using Group By with Aliases
194

 
- Using Group By with Many Columns
195

 
- Using Having with Group By
197

 
- Using Rollup to 'Sum' the Group By
199

 
- Using Partial Rollup with several non-aggregates
201

 
- Using Full Partial Rollup with several non-aggregates
202

 
- Getting the Total to the Top
203

 
- Using Cube with Group By
204

 
- Using Grouping Sets with Group By
206

 
- Using Grouping ID with Grouping Sets
208

 
- Using Group ID with Group By
210

 
- Nesting Group Functions
212

 
- Exercise Four
215

Nine
Joining Tables in an SQL Statement
223

 
- Ansi SQL:1999 Natural Join
224

 
- Ansi SQL:1999 Cross Join
225

 
- Ansi SQL:1999 Join Using
226

 
- Ansi SQL:1999 Join On
227

 
- Ansi SQL:1999 Join On with Multiple Tables
228

 
- Ansi SQL:1999 Preventing Ambiguous Columns
229

 
- Ansi SQL:1999 Non-equijoins
230

 
- Ansi SQL:1999 Self Joined Tables
232

 
- Ansi SQL:1999 Self Joined Tables vs Tree Walking
233

 
- Ansi SQL:1999 Inner Joins
234

 
- Ansi SQL:1999 Outer Joins
235

 
- Ansi SQL:1999 Full Outer Joins
236

 
- Ansi SQL:1999 Left and Right Outer Joins
237

 
- Ansi SQL:1999 Alternative to Where Clause
239

 
- Ansi SQL:1999 Using Or with 'And'
240

 
- Ansi SQL:1999 Using 'And' with 'Where'
241

 
- Ansi SQL:1986
242

 
- Ansi SQL:1986 Outer Joins
243

 
- Creating Data Sets
245

 
- Union
246

 
- Union All
249

 
- Intersect
251

 
- Minus
252

 
- Tree Walking
253

 
- Tree Walking Syntax
254

 
- Use of Level
257

 
- Use of Nocycle (ORA-01436)
258

 
- Use of Connect_by_iscycle
260

 
- Advanced Ordering of Tree Walks using Siblings
261

 
- Output of Tree Walks using Sys_connect_by_path
262

 
- Limiting Records using Connect_by_isleaf
263

 
- Showing the Root using Connect_by_root
265

Ten
Oracle Sub-Queries
267

 
- Introduction
268

 
- Using Equals
269

 
- In
272

 
- Exists
274

 
- Any
276

 
- Some
277

 
- All
278

 
- Correlated Queries
279

 
- Inline Views
281

 
- Sub-Queries in a Select Statement
283

 
- Sub-Query Factoring (Using With)
285

 
- Sub-Query Factoring Example with Aliases
287

 
- Sub-Query Factoring Example with Joins
288

 
- Exercise Five
290

Eleven
Advanced SQL Query Functions/Techniques
295

 
- Introduction
296

 
- Pivot
297

 
- Changing the Pivot Headings of the X Axis
303

 
- Rotating the Axis of the Pivot
295

 
- Unpivot
311

 
- Unpivot and Null values
315

 
- Using First_Value/Last_Value functions
316

 
- Lead and Lag functions
321

 
- Window Specification
324

 
- Breakdown of Window Specification
325

 
- Window Specification – The Arguments
327

 
- Window Specification – The Order By
330

 
- Window Specification – The Windowing
332

 
- Window Specification – Rows between
333

 
- Window Specification – Current row
334

 
- Window Specification – Unbounded preceding
337

 
- Window Specification – Bounded preceding
338

 
- Window Specification – Between
340

 
- Difference between Range and Rows
342

 
- Ranking Function
343

 
- Using Ntile Function in SQL
352

 
- Using Listagg Function in SQL
354

 
- Using Case in Select statements
358

 
- Using Nested Case Function in SQL
341

 
- Using Case Function in Order By
342

 
- Using Searched Case Function in SQL
363

 
- Using Searched Case Function with Sub-queries
365

 
- Using Sample in SQL
366

 
- Using Width_Bucket in SQL
368

 
- Exercise Six
371

Twelve
Materialized Views
381

 
- Introduction
382

 
- Syntax for Creating Materialized Views
383

 
- Refreshing Materialized Views
385

 
- Building Materialized Views
391

 
- Access Keys
393

 
- Materialized View Logs
394

 
- Refresh Groups
395

 
- Creating Refresh Groups
396

 
- Deleting Refresh Groups
399

 
- Amending Refresh Groups
400

 
- Amending Refresh Group Members
401

 
- Refresh Group Metadata
402

 
- Removing Materialized Views
403

Thirteen
Advanced DDL on Tables
404

 
- Introduction
405

 
- Making Tables Read Only
406

 
- Making Tables Writeable
407

 
- Truncating Tables
408

 
- Setting Columns as Unused
409

 
- USER_UNUSED_COL_TABS
410

 
- Removing Unused Columns
411

 
- Function Based Indexes
412

 
- Referencing Other Users Tables
413

 
- Database Privileges
414

 
- Synonyms
415

Fourteen
Virtual Columns
416

 
- Introduction
417

 
- Creating with new Table
418

 
- Specifying Datatype and Size
419

 
- Additional Syntax
420

 
- Adding to Existing Table
421

 
- Using the Virtual Columns in SQL
422

 
- Using the Virtual Columns with Functions
424

 
- Limitations of Virtual Columns (ORA-54012)
425

 
- Limitations of Virtual Columns (ORA-54015)
426

 
- Limitations of Virtual Columns (ORA-54013)
427

 
- Viewing Virtual Columns in USER_TAB_COLS
428

 
- Dropping Virtual Columns
429

Fifteen
Populating Tables Using Merge and Multiple Inserts
430

 
- Using the Merge technique
431

 
- Multiple Table Inserts
436

 
- Unconditional Multiple Inserts
437

 
- Conditional First Multiple Inserts
439

 
- Conditional All Multiple Inserts (Insert First)
441

 
- Pivoting Multiple Inserts
443

Sixteen
TCL – Transaction Control Language
445

 
- Introduction
446

 
- Commit
447

 
- Rollback
448

 
- Savepoints
449

 
- Autocommit in SQL*PLUS
450

 
- Exitcommit in SQL*PLUS
452

 
- For Update in SQL
454

 
- For Update in SQL using 'where'
455

 
- For Update in SQL naming Columns
456

 
- Exercise Seven
457

Seventeen
Flashback
463

 
- Introduction
464

 
- Flashback Query
465

 
- Using the Dbms_Flashback package
469

 
- Using Time Flashbacks
470

 
- Disabling Flashbacks
474

 
- Using SCN Flashbacks
475

 
- Implicit Flashback using AS OF
477

 
- Flashback using Versions Between
479

 
- Using the Flashback_transaction_query View
484

 
- Exercise Eight
487

Eighteen
Exporting and Importing Data into Oracle Databases
488

 
- Introduction
489

 
- Exporting
490

 
- EXP-00091: Exporting questionable statistics
498

 
- Exporting using the Command Line
499

 
- Exporting using a Parameter File
500

 
- Naming Tables using a Parameter File
501

 
- Command Line Options
502

 
- Importing
503

 
- Importing using the Command Line
510

 
- Importing using a Parameter File
511

 
- Command Line Options
512

Nineteen
Oracle Directories
513

 
- Introduction
514

 
- Creating Directories
515

Twenty
Exporting with Data Pump
517

 
- Introduction
518

 
- Exporting using Expdp
519

 
- Replacing Existing Files
521

 
- Exporting Specific Tables using Expdp
522

 
- Exporting Subsets of Data Using Expdp
525

 
- Using Expdp with a Parameter File
526

 
- Importing using Impdp
527

 
- Exercise Nine
528

Twenty One
External Tables
529

 
- Introduction
530

 
- Syntax
531

 
- Create Directories
532

 
- Viewing Existing Directories
533

 
- Creating External Tables with Comma Separated File
534

 
- Creating External Tables
535

 
- Access Parameters
537

 
- Access Parameters (Comma Separated)
538

 
- Access Parameters (Tab Separated)
539

 
- Access Parameters – Defining Fields
540

 
- Access Parameters (Missing Field Values are Null)
542

 
- Access Parameters (Reject Rows With all Null Fields)
543

 
- Access Parameters – (Load When)
544

 
- Access Parameters – (Skip)
546

 
- Access Parameters – (Audit Tables)
547

 
- Access Parameters – (Logfile)
548

 
- Access Parameters – (Badfile)
551

 
- Access Parameters – (Discardfile)
553

 
- Access Parameters – (Reject Limit)
554

 
- Access Parameters for Fixed Length Files
555

 
- Using Load When with Position
561

 
- Using Alter Table with External Tables
562

 
- Metadata for External Tables
563

 
- Using Oracle Datapump
564

 
- Losing the External File
566

Twenty Two
Advanced Date Datatypes
567

 
- Introduction
568

 
- Database Timezone
569

 
- Date Datatypes
571

 
- Timestamp
575

 
- Extract
580

 
- To_Timestamp
581

 
- Timestamp with Time Zone
583

 
- Time Zone and Extract
586

 
- TZ Offset
587

 
- Timezones and UTC
588

 
- To_Timestamp_TZ
590

 
- From_TZ
591

 
- Timestamp with Local Time Zone
592

 
- Intervals
593

 
- Assigning Year Intervals
596

 
- Assigning values to Day Intervals
598

 
- Using Intervals
600

 
- Intervals and Extract
606

 
- Exercise Ten
607

Twenty Three
Object Orientated Programming
614

 
- Introduction
615

 
- Definition of Objects
616

Twenty Four
Row Objects
620

 
- Introduction
621

 
- Creating
622

 
- Metadata
623

 
- Data
624

 
- Indexes
625

 
- Views
627

 
- Removing
628

 
- OIDs
630

Twenty Five
Column Objects
638

 
- Introduction
639

 
- Describing
640

 
- Inserting Into
643

 
- Selecting From
645

 
- Updating
650

 
- Object Views
651

Twenty Six
Collections
653

 
- Introduction
654

 
- Varying Arrays
655

 
- Defining a single element
656

 
- Inserting
657

 
- Selecting
659

 
- Updating
661

 
- Deleting
662

 
- Defining using an Abstract datatype
663

 
- Inserting an Abstract datatype
664

 
- Selecting an Abstract datatype
665

 
- Nested Tables
666

 
- Defining
667

 
- Inserting
671

 
- Selecting
674

 
- Updating
677

 
- Deleting
678

 
- Exercise Eleven
679

Twenty Seven
DBMS_METADATA
687

 
- Generating Database Object Scripts
688

 
- List of Object Types
689

 
- Example of Using Dbms_Metadata
690

Twenty Eight
SQL Loader
692

 
- Introduction
693

 
- Invoking SQLLDR
694

 
- Integrated Control Files
695

 
- Commenting in Control Files
698

 
- Optionally Enclosed By ""
699

 
- Badfile
700

 
- Dealing with Dates and Time in the Control File
702

 
- Setting Datatypes in the Control File
706

 
- Using the Filler Datatype
708

 
- Inserting Values Not Found in the Data
709

 
- Trailing Nullcolls
710

 
- Using Functions in Control Files
711

 
- Using Functions with Filler in Control Files
712

 
- Using Nullif in Control Files
713

 
- Using Defaultif in Control Files
714

 
- Variations in Data
716

 
- Working with Multi-Line Records Using Continueif
718

 
- Preserve Blanks
720

 
- Skipping Records
721

 
- Selecting Records with a 'When' Clause
722

 
- Using Discard Files with When Clause
724

 
- Working with Fixed Length Values
725

 
- Using When with Fixed Length Values
727

 
- Loading more than One Set of Data
730

 
- Using External Infiles
731

 
- Running SQLLDR
732

 
- SQLLDR Switches
733

 
- SQLLDR Switches – Specifying Files
734

 
- SQLLDR Switches – Specifying Load Numbers
736

 
- SQLLDR Switches – Long Commands in DOS
737

 
- SQLLDR Parameter File
738

 
- Exercise Twelve
739

Index of Advanced Oracle 11g SQL training course

Section
Contents
One
Advanced SQL
Two
Substitution in Oracle SQL 11g
Three
Advanced Features of SQL*Plus
Four
Using the Alternative Quoting Mechanism in SQL
Five
Metadata in Oracle SQL 11g
Six
Regular Expressions in Oracle SQL 11g
Seven
Ordering Records in Oracle SQL 11g
Eight
Aggregating Values in Oracle SQL 11g
Nine
Joining Tables in an SQL Statement
Ten
Oracle Sub-Queries
Eleven
Advanced SQL Query Functions/Techniques
Twelve
Materialized Views
Thirteen
Advanced DDL on Tables
Fourteen
Virtual Columns
Fifteen
Populating Tables Using Merge and Multiple Inserts
Sixteen
TCL – Transaction Control Language
Seventeen
Flashback
Eighteen
Exporting and Importing Data into Oracle Databases
Nineteen
Oracle Directories
Twenty
Exporting with Data Pump
Twenty One
External Tables
Twenty Two
Advanced Date Datatypes
Twenty Three
Object Orientated Programming
Twenty Four
Row Objects
Twenty Five
Column Objects
Twenty Six
Collections
Twenty Seven
DBMS_METADATA
Twenty Eight
SQL Loader

Synopsis for Advanced Oracle 11g SQL training course

One of the most pleasant aspects of giving training to our regular Clients is watching Delegates from years ago whom we trained, attending more intensive courses we give, furthering their knowledge, indeed in some cases they started as student Developers and have progressed to being Project Leaders, do you know how old that makes us feel !! well, we have been doing this since 2000 so a few we have trained have even retired !

Training Companies which have lasted over 20 years must be doing something right, when we give training our Consultants are punctual, professionally attired and polite, they are also well versed in presenting the course you have booked and can cope with any numbers you may have attending from 1 to a dozen (although we recommend two classes of 6 to maximise the course effectiveness).

We bring all the necessary machines and don’t need to use your Network, our training laboratory is completely isolated from your System.

This is the Advanced Oracle 11g SQL course and anticipates that your Delegates have already attended our Essential SQL course or have had six months of SQL experience in work, it effectively picks up where we left off, so please review the index for the course to make sure it is the correct one, our training advisers can advise you if you are in any doubt.

At the top of this page is a banner linking to the most up to date version of the course plus our menu system contains all the different versions we cover.

Seer Computing has 3 distinct courses for Oracle SQL, Essential SQL is a two day event which covers the main techniques a regular User/Developer will need when interfacing with an Oracle Database, the SQL five day event covers everything in Essential SQL plus more advanced techniques such as exporting, Data Pump etc etc … This Advanced SQL course is effectively the SQL five day course minus the Essential SQL course, ironically we cover using the union and minus clauses in relation to SQL commands in the Essential SQL course. Check out the indexes for each.

This course is purely Oracle SQL, if MySQL or SQLite is required we do those too and although they share the same characters their commands are similar but by no means identical, our advice is, if you need and use MySQL then book that training course.

All our courses are available for onsite presentation at your venue, we can also arrange courses at conference centres close to you, this is ideal if you want your Delegates to be close to the office but not far enough that they will incur travelling and overnight costs, it also means that lunch can be provided with ample parking etc …

Each delegate is supplied with their own workstation, we also bring the Database machine and various bits of stationary etc. Then of course each Delegate gets a copy of our high quality comprehensive manual, this is theirs to work with both during the course and after as it is theirs to keep as a reference for years ahead.

Booking couldn’t be simpler, contact our training aide, who will ask a small number of questions such as number of Delegates, location and preferred timescale. Yes we will fit in with dates suitable for you, then we will provide a quotation for the course, if you need a variety of scenarios we will provide those too.

Our quotations are given without any implied obligation and you have the full costings from the beginning, no added costs at a later date, what we quote is what you pay.

Paying is by invoice and you can do it by BACS, cheque or credit card (4% surcharge) and once the start date is confirmed leave it to us, we check in a week before the training to finalise things, that’s so we can get into the meeting room before the Delegates arrive. Check out the quotations from our clients over the last twenty years, you are in safe hands.

Request for detailsClick for the contact form for the Advanced Oracle SQL 11g training course
Public Courses
Oracle Essential SQL 23ai
Days
2
Pages
384
Exercises
8
Sections
17
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 23ai
Days
3
Pages
388
Exercises
13
Sections
25
Public
Yes
On-Site
Yes
Public Courses
Using SQL with Oracle SQL Developer 23.1
Days
5
Pages
1144
Exercises
16
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Essential JavaScript
Days
4
Pages
660
Exercises
16
Sections
31
Public
Yes
On-Site
Yes
Public Courses
Essential CSS 3
Days
4
Pages
800
Exercises
15
Sections
30
Public
Yes
On-Site
Yes
Public Courses
Essential SQLite 3
Days
3
Pages
550
Exercises
12
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential MySQL 8
Days
4
Pages
533
Exercises
16
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential Python 3
Days
5
Pages
955
Exercises
17
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Oracle Application Express 20
Days
0
Pages
0
Exercises
0
Sections
0
Public
Yes
On-Site
Yes
Public Courses
PHP 8 and MySQL 8
Days
5
Pages
870
Exercises
23
Sections
38
Public
Yes
On-Site
Yes
Public Courses
Java 17 Programming with Eclipse 2021-12
Days
5
Pages
788
Exercises
23
Sections
44
Public
Yes
On-Site
Yes
Public Courses
Java 17 Swing with Eclipse 2021-12
Days
5
Pages
756
Exercises
19
Sections
35
Public
Yes
On-Site
Yes
Click on the course name to see full details, left and right to view other courses