×
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
699
No of slides in the Manual
Exercises
12
Total No of Exercises
Sections
28
No of main topics, see index below
Public
No
Public course in the UK and Ireland
On-Site
Yes
We can come to your venue
Level
2
1 - Beginners
2 - Intermediate
3 - Advanced
Days
3
Week days but we can put on weekend courses

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
PDF - Course IndexClick to view the PDF index for the course

PDF - Exercise ExampleClick to view the PDF sample exercise

PDF - Sample PagesClick to view the PDF sample course pages

Request for details
Click for the contact form for the Advanced Oracle SQL 11g training course

Synopsis for Advanced Oracle 11g SQL training 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.

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
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

Request for details
Click for the contact form for the Advanced Oracle SQL 11g training course


Advanced Oracle 11g SQL training course

Download our latest
Android and iOS apps ...

Papers and Periodicals App
Download A-Z Yoga app
Download SQL app
Download PHP app
Download MySQL app
Download MariaDB app
Click on whichever one of our Apps you are interested in
×
Advanced Oracle SQL 11g
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Advanced Oracle SQL 11g course at a glance ...

Duration: 3 days
Minimum skill level required: 2
(1 - Beginner, 2 - Experienced
3 - Advanced)
On-Site course:
Yes

Public course: No
No of pages in the manual: 699
Sections: 28
No of exercises: 12
Pre-Requisite course(s):
Oracle Essential SQL 11g
Suggested further course(s):
None

Price guide: Contact us for the latest pricing

enquiries@seercomputing.com
or call us on +44 (0) 800 157 7519.

PDF - Course Index

PDF - Exercise Example

PDF - Sample Pages

Request for details
Click for the contact form for the Advanced Oracle SQL 11g training 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.

Scroll through these locations and find the one nearest to you.

Click on the location of your choice and that will take you to the dates for each course

Select the location and then click on the image to view the dates
Request for details
Click for the contact form for the Advanced Oracle SQL 11g training course
×
Advanced Oracle SQL 11g
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Advanced Oracle SQL 11g course at a glance ...

Duration: 3 days
Minimum skill level required: 2
(1 - Beginner, 2 - Experienced
3 - Advanced)
On-Site course:
Yes

Public course: No
No of pages in the manual: 699
Sections: 28
No of exercises: 12
Pre-Requisite course(s):
Oracle Essential SQL 11g
Suggested further course(s):
None

Price guide: Contact us for the latest pricing

enquiries@seercomputing.com
or call us on +44 (0) 800 157 7519.

PDF - Course Index

PDF - Exercise Example

PDF - Sample Pages

Request for details
Click for the contact form for the Advanced Oracle SQL 11g training course

Scroll left/right and then click on the course to view the details