Top
Email
Bottom
Advanced Oracle SQL 18c
 
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 18c 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 18c
None
Contact us for the latest pricing

Index of Advanced Oracle 18c SQL training course

Section
Contents
 

One
Advanced SQL
1

 
- Introduction
3

Two
Substitution in Oracle SQL 18c
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 18c
101

 
- Introduction
102

 
- Selecting from Dictionary
103

 
- Metadata Synonyms
104

 
- Metadata Comments
105

Six
Regular Expressions in Oracle SQL 18c
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 18c
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 18c (Top-N)
184

 
- Using Percent with Fetch in Oracle 18c
186

 
- Using Offset with Fetch in Oracle 18c
187

Eight
Aggregating Values in Oracle SQL 18c
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 18c SQL training course

Section
Contents
One
Advanced SQL
Two
Substitution in Oracle SQL 18c
Three
Advanced Features of SQL*Plus
Four
Using the Alternative Quoting Mechanism in SQL
Five
Metadata in Oracle SQL 18c
Six
Regular Expressions in Oracle SQL 18c
Seven
Ordering Records in Oracle SQL 18c
Eight
Aggregating Values in Oracle SQL 18c
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 18c SQL training course

Seer Computing's philosophy is that the main assets in an IT Department are its Data and its staff, we can help with both, our Data Consultancy team can review your current position, recommend and even manage any changes that will need to be made.

Our training Consultants are armed with over 170 different IT training courses and can come to your premises to present every single one of them, that basically means no travelling expenses, no overnight stays and a lot less stressed out staff.

We'll do the stressing for you, we'll get our Consultants to you to start the course on the date you want and at the time you request, they will bring with them the complete Oracle training laboratory. This consists of the Database server and workstations for each Delegate plus our high quality manuals which is theirs to use throughout the course and beyond. We don’t connect to your Database or Network, we just need a power socket and that’s it, oh and tables and chairs.

This is the Oracle 18c Advanced SQL three day training course, it has been designed for Delegates to further their knowledge of Oracle SQL, this means it is anticipated that Delegates will have already attended our Essential SQL course or have at least a few months using SQL in a practical environment.

At the top of this webpage is our menu system, use this to view our other available courses, alternatively talk to our Training Assistants who can guide you to choosing the correct course(s) for your staff. If required we can present a number of different scenarios for you to choose from, these are given without any obligation and we can change them according to your requirement as we build up our relationship with you. All quotations we give are accompanied by an index of all courses we are quoting for; this webpage has a downloadable PDF version of the course index as well as a searchable HTML breakdown of the course topics.

Our quotations are valid for six months and will only change if your requirement does or if the VAT rate is raised or lowered. We honour our quotations even when, for example, the Irish Ferries increase their charges, we do not pass this on once we have quoted a price, we're funny like that we have integrity.

If you need to train a number of staff in the Oracle products talk to us about grouping the Delegates together in terms of skill levels, we find staff who are at the same learning level will appreciate the pace, don’t forget this is the Advanced SQL course therefore it is expected they will have some knowledge. Seer Computing have two other SQL courses (as well as SQL Developer), they are the full five day SQL course and the Essential SQL course which is an ideal starting point for a lot of Delegates.

When you are booking the course let us know if the Delegates have particular requirements, we are able to 'flex' the course to concentrate on vital areas to your Business and are delighted to help make the course the 'best fit' for your people. Also we can start the courses later or earlier than the normal 9am, this can fit into your work practices and indeed any personal commitments the Delegates have regarding school runs etc, just let us know at the time of booking. Of course one of the advantages of on-site courses is that you can interrupt if some live emergency occurs, at which time we suspend the training until the crisis has been resolved.

A number of our courses are scheduled throughout the year, throughout the UK and Ireland, this course is not one of them however visit the page for the location and view which ones are and when they are chalked up to take place. Above all speak to our advisers, we have over 20 years of experience in IT Development, Consultancy and Training, we certainly know our stuff.

Request for detailsClick for the contact form for the Advanced Oracle SQL 18c 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