Download our latest
Android and iPhone apps ...
At a glance ...
Pages
699
Exercises
12
Sections
28
Public
On-Site
Level
2
Days
3

Course Name: Advanced Oracle SQL 11g

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
699
Oracle Essential SQL 11g
None
Contact us for the latest pricing

Synopsis for course

A supplement course for experienced SQL Developers using SQL in a 11g Database, please refer to the index for this course at the bottom of the page for full details.

Course Contents

Section
Contents
 

One
Advanced SQL
1

 
- Introduction
3

Two
Substitution in Oracle SQL 11g
4

 
- Introduction
5

 
- Using Ampersands
6

 
- Using Ampersands with Strings
7

 
- Using the same value more than once
8

 
- Persistent values (Using Undefine)
9

 
- Advanced Substitution for Advanced Users
10

 
- Preventing prompting when Ampersands are encountered
12

 
- Redefining the Prompt (Set Define)
13

 
- Removing the Verification (Verify off)
14

 
- Defining Session Variables
15

 
- Using the Accept command in SQLPlus
19

 
- Within Insert Statements
24

 
- Within Scripts
25

 
- Exercise One
28

Three
Advanced Features of SQLPlus
32

 
- Using the Help facility
33

 
- Using the Spool Command
35

 
- Using the Host Command
37

 
- Changing the Prompt
38

 
- Displaying the Time
39

 
- Displaying all Environment Settings
40

 
- Saving the Environment Settings using Store Set
41

 
- Changing Environment Settings in glogin.sql
42

 
- Creating HTML Pages
43

 
- Renaming Columns for the Session
45

 
- Formatting Columns (Varchar2)
48

 
- Formatting Columns (Number)
49

 
- Formatting Columns (Date)
50

 
- Wrapping Columns
51

 
- Justifying Column Headings
53

 
- Suppressing Columns
55

 
- Truncate Columns
56

 
- Storing Values in Columns
57

 
- Viewing Columns
58

 
- Viewing All Columns
59

 
- Temporarily Disabling Columns
60

 
- Clearing Columns
61

 
- Copying Columns with Like
62

 
- Using Columns to deal with Nulls
63

 
- Multi-Line Output
64

 
- Using Break in the Output
65

 
- Using Aliases with Break
67

 
- Using Skip with Break
68

 
- Using Skip Page with Break
69

 
- Using Skip Row with Break
70

 
- Displaying Duplicates with Break
71

 
- Viewing/Clearing Breaks
72

 
- Using Compute with Break
73

 
- Using Compute Labels with Break
75

 
- Using ComputeSub-totalling with Break
79

 
- Combining Computes
81

 
- Setting Titles for the Output
83

 
- Using Ttitle for the Output
84

 
- Using SQL.PNO in the Title
86

 
- Using Variables in the Title
88

 
- Using Btitle
90

 
- Using Repheader and Repfooter
92

 
- Reviewing the Title settings
93

 
- Suppressing the Title settings
94

Four
Using the Alternative Quoting Mechanism in SQL
95

 
- Introduction
96

 
- Using Alternative Quoting Mechanism
97

 
- Exercise Two
99

Five
Metadata in Oracle SQL 11g
108

 
- Introduction
109

 
- Selecting from Dictionary
110

 
- Metadata Synonyms
111

 
- Metadata Comments
112

Six
Regular Expressions in Oracle SQL 11g
114

 
- Introduction
115

 
- Using REGEXP_LIKE in Where Clauses
118

 
- Case Sensitivity
120

 
- Line Anchors
122

 
- Using the Dot (Any Character)
124

 
- Counting Characters {Intervals}
127

 
- Character Lists
131

 
- Excluding Character Lists
135

 
- Alternatives to Character Lists
136

 
- Class Shorthands
139

 
- Or Patterns (Alternatives)
140

 
- Or Patterns with Character Lists
141

 
- Using the Question Mark (Optional)
142

 
- Using the Plus sign (Mandatory)
145

 
- Using the Star sign (Optional)
147

 
- Backreferencing
148

 
- POSIX
150

 
- Using POSIX in Oracle - [:upper] example
152

 
- Using POSIX in Oracle - [:digit] example
153

 
- Using POSIX in Oracle - character equivalents
154

 
- Using Regexp_count in SQL
155

 
- Using Regexp_replace in SQL
159

 
- Using Regexp_instr in SQL
161

 
- Using Regexp_substr in SQL
163

 
- Using Regular Expressions in Oracle
165

Seven
Ordering Records in Oracle SQL 11g
166

 
- Syntax
167

 
- Ordering by Columns
168

 
- Ordering by more Columns
170

 
- Dealing with Nulls
171

 
- Using Column position when Sorting
173

 
- Using Expressions when Sorting
174

 
- Using Aliased Columns
175

 
- Using Alternative Sorts
176

 
- Exercise Three
178

Eight
Aggregating Values in Oracle SQL 11g
187

 
- Introduction
188

 
- Using Distinct with other Aggregates
189

 
- Using Group By
190

 
- Using Group By with Aliases
193

 
- Using Group By with Many Columns
194

 
- Using Having with Group By
196

 
- Using Rollup to 'Sum' the Group By
198

 
- Using Partial Rollup with several non-aggregates
200

 
- Using Full Partial Rollup with several non-aggregates
201

 
- Getting the Total to the Top
202

 
- Using Cube with Group By
203

 
- Using Grouping Sets with Group By
205

 
- Using Grouping ID with Grouping Sets
207

 
- Using Group ID with Group By
209

 
- Nesting Group Functions
211

 
- Exercise Four
214

Nine
Joining Tables in an SQL Statement
218

 
- Ansi SQL:1999 Natural Join
219

 
- Ansi SQL:1999 Cross Join
220

 
- Ansi SQL:1999 Join Using
221

 
- Ansi SQL:1999 Join On
222

 
- Ansi SQL:1999 Join On with Multiple Tables
223

 
- Ansi SQL:1999 Preventing Ambiguous Columns
224

 
- Ansi SQL:1999 Non-equijoins
225

 
- Ansi SQL:1999 Self Joined Tables
227

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

 
- Ansi SQL:1999 Inner Joins
229

 
- Ansi SQL:1999 Outer Joins
230

 
- Ansi SQL:1999 Full Outer Joins
231

 
- Ansi SQL:1999 Left and Right Outer Joins
232

 
- Ansi SQL:1999 Alternative to Where Clause
234

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

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

 
- Ansi SQL:1986
237

 
- Ansi SQL:1986 Outer Joins
238

 
- Creating Data Sets
240

 
- Union
241

 
- Union All
244

 
- Intersect
246

 
- Minus
247

 
- Tree Walking
248

 
- Tree Walking Syntax
249

 
- Use of Level
252

 
- Use of Nocycle (ORA-01436)
253

 
- Use of Connect_by_iscycle
255

 
- Advanced Ordering of Tree Walks using Siblings
256

 
- Output of Tree Walks using Sys_connect_by_path
257

 
- Limiting Records using Connect_by_isleaf
258

 
- Showing the Root using Connect_by_root
260

Ten
Sub-Queries in Oracle SQL 11g
262

 
- Introduction
263

 
- Using Equals
264

 
- In
267

 
- Exists
269

 
- Any
271

 
- Some
272

 
- All
273

 
- Correlated Queries
274

 
- Correlated Updates and Deletes
275

 
- Inline Views
276

 
- Inline Views Example
277

 
- Sub-Queries in a Select Statement
278

 
- Sub-Query Factoring (Using With)
279

 
- Sub-Query Factoring Example with Aliases
281

 
- Sub-Query Factoring Example with Joins
282

 
- Exercise Five
283

Eleven
Advanced SQL Query Functions/Techniques
285

 
- Introduction
286

 
- Pivot
287

 
- Adding a Total to a Pivot
292

 
- Changing the Pivot Headings of the X Axis
293

 
- Rotating the Axis of the Pivot
295

 
- Unpivot
296

 
- Unpivot and Null values
299

 
- Using First_Value/Last_Value in SQL
300

 
- Ignoring Nulls with First/Last_Value
302

 
- Window Specification
303

 
- Breakdown of Window Specification
304

 
- Window Specification - The Arguments
306

 
- Window Specification - The Query Partition
307

 
- Window Specification - The Order By
309

 
- Window Specification - The Windowing
311

 
- Window Specification - Rows between
313

 
- Window Specification - Shortcuts
317

 
- Difference between Range and Rows
318

 
- Using Lead and Lag in SQL
321

 
- Using Rank Function in SQL
325

 
- Using Ntile Function in SQL
332

 
- Using Width_Bucket Function in SQL
334

 
- Using Listagg Function in SQL
337

 
- Using Case Function in SQL
339

 
- Using Nested Case Function in SQL
341

 
- Using Case Function in Order By
342

 
- Using Searched Case Function in SQL
343

 
- Using Searched Case Function with Sub-queries
345

 
- Using Sample in SQL
346

 
- Exercise Six
348

Twelve
Materialized Views
351

 
- Introduction
352

 
- Syntax for Creating Materialized Views
353

 
- Refreshing Materialized Views
355

 
- Building Materialized Views
361

 
- Access Keys
363

 
- Materialized View Logs
364

 
- Refresh Groups
365

 
- Creating Refresh Groups
366

 
- Deleting Refresh Groups
369

 
- Amending Refresh Groups
370

 
- Amending Refresh Group Members
371

 
- Refresh Group Metadata
372

 
- Removing Materialized Views
373

Thirteen
Advanced DDL on Tables
374

 
- Introduction
375

 
- Making Tables Read Only
376

 
- Making Tables Writeable
377

 
- Truncating Tables
378

 
- Setting Columns as Unused
379

 
- USER_UNUSED_COL_TABS
380

 
- Removing Unused Columns
381

 
- Function Based Indexes
382

 
- Referencing Other Users Tables
383

 
- Database Privileges
384

 
- Synonyms
385

Fourteen
Virtual Columns
386

 
- Introduction
387

 
- Creating with new Table
388

 
- Specifying Datatype and Size
389

 
- Additional Syntax
390

 
- Adding to Existing Table
391

 
- Using the Virtual Columns in SQL
392

 
- Using the Virtual Columns with Functions
394

 
- Limitations of Virtual Columns (ORA-54012)
395

 
- Limitations of Virtual Columns (ORA-54013)
397

 
- Viewing Virtual Columns in USER_TAB_COLS
398

 
- Dropping Virtual Columns
399

Fifteen
Populating Tables Using Merge and Multiple Inserts
400

 
- Merge
401

 
- Merge Into
403

 
- Using Where with Merge Into
405

 
- Using Delete Where with Merge Into
406

 
- Multiple Table Inserts
407

 
- Unconditional Multiple Inserts (Insert All)
408

 
- Conditional First Multiple Inserts (Insert First)
410

 
- Conditional All Multiple Inserts (Insert First)
411

 
- Pivoting Multiple Inserts
412

Sixteen
TCL - Transaction Control Language
413

 
- Introduction
414

 
- Commit
415

 
- Rollback
416

 
- Savepoints
417

 
- Autocommit in SQLPLUS
418

 
- Exitcommit in SQLPLUS
420

 
- For Update in SQL
422

 
- For Update in SQL using 'where'
423

 
- For Update in SQL naming Columns
424

 
- Exercise Seven
425

Seventeen
Flashback
429

 
- Introduction
430

 
- Flashback Query
431

 
- Using the Dbms_Flashback package
435

 
- Using Time Flashbacks
436

 
- Disabling Flashbacks
440

 
- Using SCN Flashbacks
441

 
- Implicit Flashback using AS OF
443

 
- Flashback using Versions Between
445

 
- Using the Flashback_transaction_query View
450

 
- Exercise Eight
453

Eighteen
Exporting and Importing Data into Oracle Databases
455

 
- Introduction
456

 
- Exporting
457

 
- EXP-00091: Exporting questionable statistics
465

 
- Exporting using the Command Line
466

 
- Exporting using a Parameter File
467

 
- Naming Tables using a Parameter File
468

 
- Command Line Options
469

 
- Importing
470

 
- Importing using the Command Line
477

 
- Importing using a Parameter File
478

 
- Command Line Options
479

Nineteen
Oracle Directories
480

 
- Introduction
481

 
- Creating Directories
482

Twenty
Exporting with Data Pump
484

 
- Introduction
485

 
- Exporting using Expdp
486

 
- Replacing Existing Files
488

 
- Exporting Specific Tables using Expdp
489

 
- Exporting Subsets of Data Using Expdp
492

 
- Using Expdp with a Parameter File
493

 
- Importing using Impdp
494

 
- Exercise Nine
495

Twenty One
External Tables
496

 
- Introduction
497

 
- Syntax
498

 
- Create Directories
499

 
- Viewing Existing Directories
500

 
- Creating External Tables with Comma Seperated File
501

 
- Creating External Tables
502

 
- Access Parameters
504

 
- Access Parameters (Comma Separated)
505

 
- Access Parameters (Tab Separated)
506

 
- Access Parameters - Defining Fields
507

 
- Access Parameters (Missing Field Values are Null)
509

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

 
- Access Parameters - (Load When)
511

 
- Access Parameters - (Skip)
513

 
- Access Parameters - (Audit Tables)
514

 
- Access Parameters - (Logfile)
515

 
- Access Parameters - (Badfile)
518

 
- Access Parameters - (Discardfile)
520

 
- Access Parameters - (Reject Limit)
521

 
- Access Parameters for Fixed Length Files
522

 
- Using Load When with Position
528

 
- Using Alter Table with External Tables
529

 
- Metadata for External Tables
530

 
- Using Oracle Datapump
531

 
- Losing the External File
533

Twenty Two
Advanced Date Datatypes
534

 
- Introduction
535

 
- Database Timezone
536

 
- Date Datatypes
538

 
- Timestamp
542

 
- Extract
547

 
- To_Timestamp
548

 
- Timestamp with Time Zone
550

 
- Time Zone and Extract
553

 
- TZ Offset
554

 
- Timezones and UTC
555

 
- To_Timestamp_TZ
557

 
- From_TZ
558

 
- Timestamp with Local Time Zone
559

 
- Intervals
560

 
- Assigning Year Intervals
563

 
- Assigning values to Day Intervals
565

 
- Using Intervals
567

 
- Intervals and Extract
573

 
- Exercise Ten
574

Twenty Three
Object Orientated Programming
577

 
- Introduction
578

 
- Definition of Objects
579

Twenty Four
Row Objects
583

 
- Introduction
584

 
- Creating
585

 
- Metadata
586

 
- Data
587

 
- Indexes
588

 
- Views
590

 
- Removing
591

 
- OIDs
593

Twenty Five
Column Objects
600

 
- Introduction
601

 
- Describing
602

 
- Inserting Into
605

 
- Selecting From
607

 
- Updating
612

 
- Object Views
613

Twenty Six
Collections
615

 
- Introduction
616

 
- Varraying Arrays
617

 
- Defining a single element
618

 
- Inserting
619

 
- Selecting
621

 
- Updating
623

 
- Deleting
624

 
- Defining using an Abstract datatype
625

 
- Inserting an Abstract datatype
626

 
- Selecting an Abstract datatype
627

 
- Nested Tables
628

 
- Defining
629

 
- Inserting
633

 
- Selecting
636

 
- Updating
639

 
- Deleting
640

 
- Exercise Eleven
641

TwentySeven
DBMS_METADATA
646

 
- Generating Database Object Scripts
647

 
- List of Object Types
648

 
- Example of Using Dbms_Metadata
649

Twenty Eight
SQL Loader
651

 
- Introduction
652

 
- Invoking SQLLDR
653

 
- Integrated Control Files
654

 
- Commenting in Control Files
657

 
- Optionally Enclosed By ""
658

 
- Badfile
659

 
- Dealing with Dates and Time in the Control File
661

 
- Setting Datatypes in the Control File
665

 
- Using the Filler Datatype
667

 
- Inserting Values Not Found in the Data
668

 
- Trailing Nullcolls
669

 
- Using Functions in Control Files
670

 
- Using Functions with Filler in Control Files
671

 
- Using Nullif in Control Files
672

 
- Using Defaultif in Control Files
673

 
- Variations in Data
675

 
- Working with Multi-Line Records Using Continueif
677

 
- Preserve Blanks
679

 
- Skipping Records
680

 
- Selecting Records with a 'When' Clause
681

 
- Using Discard Files with When Clause
683

 
- Working with Fixed Length Values
684

 
- Using When with Fixed Length Values
686

 
- Loading more than One Set of Data
689

 
- Using External Infiles
690

 
- Running SQLLDR
691

 
- SQLLDR Switches
692

 
- SQLLDR Switches - Specifying Files
693

 
- SQLLDR Switches - Specifying Load Numbers
695

 
- SQLLDR Switches - Long Commands in DOS
696

 
- SQLLDR Parameter File
697

 
- Exercise Twelve
698