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