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

Index of Advanced Oracle 19c SQL training course

Section
Contents
 

One
Advanced SQL
1

 
- Introduction
3

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

 
- Introduction
102

 
- Selecting from Dictionary
103

 
- Metadata Synonyms
104

 
- Metadata Comments
105

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

 
- Using Percent with Fetch in Oracle 19c
186

 
- Using Offset with Fetch in Oracle 19c
187

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

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

Throughout a person's working life in IT they will never stop learning new aspects to their job, whether it’s a new piece of software, operating system or machinery but whatever comes along proper and timely training is essential to keep them up to speed with new techniques, this will enable them to keep at their peak efficiency and effectiveness. Once they have learnt the fundamentals and basics of an area they will need to move onto more complex and advanced topics within the subject.

This is the Advanced Oracle 19c SQL training course, it effectively picks up where we left off from the Oracle SQL 21c training course which is available as a scheduled / public training or on-site, this course is purely on-site, we come to you or you can come to us in Swansea or we can arrange a venue convenient to your Delegates.

At the top of the page there is a banner which will take you to the latest Essentials version of this course, this course is for experienced Oracle SQL practitioners and maybe you need to look at the starter course before booking this one, take a look at the topics on this course in our index and discuss your needs with our very experienced training advisers.

When we perform this course we bring with us all the kit necessary for the work, the Database machine, individual workstations for each Delegates and of course our high quality manuals which the Delegates keep following the course. This Oracle 21c Advanced SQL course comes with 12 different exercise sets, our mantra, is tell them, show them, let them use it … so following a couple of sections of tutorial we get the Delegates to try out the techniques ably supported by our friendly and knowledgeable training Consultant, we use the Netsupport software so that we can see the screens of our Delegates without having to walk round and disturb them, we can even type in the code if they need that little nudge to get them on the way.

Some Companies like their Delegates to sit together when working on solutions, this we can do, in a normal working environment most teams work together to solve problems so why not when on a course, just let one of our experienced and friendly Training Advisers know your requirements.

On the subject of requirements, Seer Computing pride itself on being highly flexible when providing onsite training, for example you can pick the start times, normally we start at 9am but we can start earlier or later to fit in with shift patterns, regular staff meetings or indeed personal commitments. The same applies to the start dates, this is a three day course so we can start on a Monday, Tuesday or Wednesday, in fact we have run this course over a weekend when the client requests it.

Take a look at the index for this course, it may have elements within it you don’t need, if that’s the case and you are on a budget it can be slimmed down to those you are interested in, if we save a day on the course we can lower the costs.

Payment for this course can be made a number of ways, Companies can issue a purchase order and pay either by BACS, cheque or credit card (with 4% surcharge), individuals have exactly the same options minus the purchase order of course. We always issue an invoice for the work, when we quote the price is inclusive of all costs but exclusive of vat which is added when invoicing. Quotations are honoured for six months after issue.

All quotations are free without any obligation and we know you will like the quality of the course and the professionalism with which we deal with your request.

Request for detailsClick for the contact form for the Advanced Oracle SQL 19c training course
Public Courses
Oracle Essential SQL 21c
Days
2
Pages
340
Exercises
8
Sections
15
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 21c
Days
3
Pages
384
Exercises
13
Sections
25
Public
Yes
On-Site
Yes
Public Courses
Using SQL with Oracle SQL Developer 19.2
Days
4
Pages
884
Exercises
18
Sections
37
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