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