Index of Oracle 18c SQL training course
Section
Contents
Page
One
Introduction to SQL
2
- What is SQL
3
- What is SQL user for / Who uses SQL
4
- What is a Database
5
Two
Database Objects
6
- Introduction to Oracle Database Objects
7
- About Oracle Tables
8
- About Oracle Views
9
- About Oracle Materialized Views
10
- About Oracle Indexes
11
- About Oracle Sequences
12
- About Packages, Functions and Procedures
13
- About Synonyms and Schemas
14
Three
The SQL*Plus Interface
15
- Introduction to SQL*Plus
16
- How to log into SQL*Plus
17
- Anatomy of SQL*Plus
21
- ORA-01017: invalid username/password; logon denied
22
- ORA-12154: TNS:could not resolve the connect identifier
23
- How to exit an SQL*Plus Session
24
- Using the DOS window with SQL*Plus
25
- Copying and Pasting in SQL*Plus
26
- Using the Function Keys in SQL*Plus
29
- Using the Line Editor in SQL*Plus
30
- SQL*Plus Environment Settings
32
- Opening files in SQL*Plus
33
- Saving SQL in SQL*Plus
34
- Using SQL*Plus to write the output to files
35
- Using the Spool Command
36
- Changing Passwords / Clearing the Screen in SQL*Plus
38
- Connecting to other Users within SQL*Plus
39
Four
SQL Scripts
40
- Commenting SQL statements
45
- Running SQL in Batch Scripts
47
- Passing Parameters with SQL Scripts
49
Five
Simple Queries
51
- What is an SQL query ?
52
- The purpose of describe in Oracle SQL
56
- SQL Query Syntax
58
- Counting Records in an Oracle table
60
- Displaying Literals when selecting in SQL
62
- Oracle Variables from sys.dual
65
- NLS_DATE_FORMAT
66
- Alternatives to SYSDATE in SQL
67
- Exercise One
69
Six
Column Manipulation in Oracle
73
- Introduction
74
- SQL Column Labels
75
- SQL Column Labels and Reserved Words
79
- Using Oracle SQL Functions
81
- Oracle Column Concatenation
88
- Using Oracle as a calculator
89
- Sorting SQL Output
90
- Ordering By More than One Column
92
- Ordering with nulls first / nulls last
93
- Ordering with Aliases
94
- Displaying Oracle Dates
95
- Displaying Oracle Dates with to_char
96
- Displaying Oracle Numbers with to_char
100
- Exercise Two
102
Seven
Filtering Oracle Data with a Where Clause
107
- Introduction to Oracle Where Clauses
108
- Where Clause syntax
109
- Examples of Oracle Where Clauses
110
- Handling Null values in a Where Clause
118
- Case restrictions in Where Clauses
119
- Using Functions in Where Clauses
120
- Where Clauses for Binary Datatypes
123
- Fetch first rows of a Table in Oracle 18c (Top–N)
125
- Using Percent with Fetch in Oracle 18c
127
- Using Offset with Fetch in Oracle 18c
128
- Using With Ties with Fetch in Oracle 18c
129
- Exercise Three
130
Eight
Oracle Datatypes
136
- Introduction
137
- Oracle Varchar2
138
- ORA–00910: specified length too long for its datatype
140
- Varchar2 and Char in Oracle
141
- Varchar2 and to_char in Oracle
142
- Varchar2 and Char in Oracle
141
- Varchar2 and Char in Oracle
142
- Oracle Numbers
143
- Dates in Oracle Databases
144
- Float Datatypes in Oracle
145
- Binary Float and Binary Double
146
- Long Datatypes in Oracle
147
- Blobs and Clobs in Oracle
148
- Migrating Long columns in Oracle
152
Nine
Aggregating Values in Oracle SQL
155
- Introduction
156
- Using Group By to aggregate values
157
- Using Having with Group By
160
- Totaling Group By with Rollup
163
- Using Partial Rollup with several non-aggregates
164
- Using Full Rollup with several non-aggregates
165
- Getting the Total to the Top
166
- Using Cube with Group By
167
- Using Grouping Sets with Group By
169
- Using Grouping ID with Grouping Sets
171
- Using Group ID with Group By
174
- Nesting Group Functions
176
- Exercise Four
181
Ten
Substitution in Oracle SQL 18c
187
- Introduction
188
- Using Ampersands to substitute values
189
- Using Ampersands with Strings
190
- Using the same value more than once …
191
- Persistent values (Using Undefine or Undef)
192
- Persistent values (Using Define or Def)
194
- Advanced Substitution for Advanced Users
197
- Preventing prompting when Ampersands are encountered
199
- Redefining the Prompt (Set Define)
200
- Removing the Verification (Verify off)
201
- Defining Session Variables
202
- Using the Accept command in SQL*Plus
203
- Within Insert statements
208
- Within Scripts
209
- Exercise Five
211
Eleven
Advanced Features of SQL*Plus
214
- Using the Help facility
215
- Using the Host command
217
- Changing the SQL*Plus Prompt
218
- Displaying the Time
219
- Displaying all Environment Settings
220
- Saving the Environment Settings using Store Set
221
- Changing Environment Settings in glogin.sql
222
- Creating HTML Pages
223
- Renaming Columns for the Session
225
- Formatting Columns (Varchar2)
228
- Formatting Columns (Number)
229
- Formatting Columns (Date)
230
- Wrapping Columns
231
- Justifying Column Headings
234
- Suppressing Columns
235
- Truncate Columns
236
- Storing Values in Columns
237
- Viewing Columns
238
- Viewing All Columns
239
- Temporarily Disabling Columns
240
- Clearing Columns
241
- Copying Columns with Like
242
- Using Columns to deal with Nulls
243
- Multi-Line Output
244
- Using Break in the Output
245
- Using Aliases with Break
247
- Using Skip with Break
248
- Using Skip Page with Break
249
- Using Skip Row with Break
250
- Displaying Duplicates with Break
251
- Viewing/Clearing Breaks
252
- Using Compute with Break
253
- Using Compute Labels with Break
255
- Using Compute Sub-totaling with Break
259
- Combining Computes
261
- Setting Titles for the Output
263
- Using SQL.PNO in the Title
266
- Using Variables in the Title
268
- Using Btitle
270
- Using Repheader and Repfooter
272
- Reviewing the Title settings
273
- Suppressing the Title settings
274
- Exercise Six
275
Twelve
Using Alternative Quoting Mechanism in SQL
280
- Introduction to AQM
281
- Using Alternative Quoting Mechanism
282
Thirteen
Joining Tables
284
- Using Table Aliases
285
- Linking tables
289
- Cartesian Products
290
- Joining Tables
292
- Ansi SQL/86
293
- Ansi SQL/86 and Outer Joins
295
- Ansi SQL/99 Standards (Natural Joins)
298
- Ansi SQL/99 Standards (Cross Joins)
299
- Ansi SQL/99 Standards (Joins/Using)
300
- Ansi SQL/99 Standards (On)
301
- Ansi SQL/92 Standards (Multiple On)
302
- Ansi SQL/92 Standards (Left Outer Joins)
303
- Ansi SQL/92 Standards (Right Outer Joins)
304
- Ansi SQL/92 Standards (Full Outer Joins)
305
- Merging Tables
306
- Union Syntax
307
- Union
308
- Union All
309
- Intersect
310
- Minus
311
- Knowledge Check
312
- Knowledge Check – Union
313
- Knowledge Check – Union All
314
- Knowledge Check – Intersect
315
- Knowledge Check – Minus
316
- Tree Walks
317
- Use of Nocycle (ORA-01436)
325
- Use of Connect_by_iscycle
327
- Limiting Records using Connect_by_isleaf
328
- Showing the Root using Connect_by_root
330
- Exercise Seven
332
Fourteen
Oracle Sub-Queries
338
- Introduction
339
- Using Equals
340
- In
343
- Exists
345
- Any
347
- Some
348
- All
349
- Correlated Sub-Queries
350
- Inline Views
352
- Sub-Queries in a Select Statement
354
- Sub-Query Factoring (Using With)
356
- Sub-Query Factoring Example with Aliases
358
- Sub-Query Factoring Example with Joins
359
- Exercise Eight
361
Fifteen
Advanced Queries
366
- Introduction
367
- Pivot
368
- Changing the Pivot Headings of X Axis
374
- Unpivot
382
- Unpivot and Null values
386
- First/Last_Value functions
387
- Lead and Lag functions
392
- Window Specification
395
- Breakdown of Window Specification
396
- Window Specification – The Arguments
398
- Window Specification – The Order By
401
- Window Specification – The Windowing
403
- Window Specification – Rows between
404
- Window Specification – current row
405
- Window Specification – unbounded preceding
408
- Window Specification – bounded preceding
409
- Window Specification – current row
410
- Window Specification – between
411
- Window Specification – Rows between
412
- Difference between Range and Rows
413
- Ranking function
414
- Using Ntile Function in SQL
423
- Using Listagg Function in SQL
425
- Using Case in Select statements
429
- Using Searched Case Function in SQL
434
- Using Searched Case Function with Sub-queries
436
- Using Sample in SQL
437
- Using Width_Bucket in SQL
439
- Exercise Nine
442
Sixteen
Creating Database Objects
450
- Creating Tables in SQL
451
- Mandatory Columns in SQL Tables
453
- Creation Syntax for Tables in SQL
454
- Rownum in SQL Tables
455
- Using Rowid in SQL Tables
457
- Creation Syntax for Tables in SQL
459
- Virtual Columns in Oracle Tables
460
- Creating Virtual Columns with new Table
461
- Specifying Virtual Column Datatype and Size
462
- Adding Virtual Columns to Existing Table
463
- Using Virtual Columns in SQL …
464
- Using Virtual Columns with Functions
466
- Limitations of Virtual Columns (ORA–54012)
468
- Limitations of Virtual Columns (ORA–54015)
469
- Limitations of Virtual Columns (ORA–54013)
470
- Viewing Virtual Columns in USER_TAB_COLS
472
- Invisible Columns in Oracle 18c
473
- Commenting on Tables in SQL
476
- Commenting on Columns in SQL
477
- Creating Indexes in SQL
478
- Index Creation Syntax in SQL
481
- Disabling Indexes in SQL
483
- Creating Views in SQL
484
- Creating Sequences in SQL
486
- Using Sequences in SQL
487
- Altering Sequences in SQL
489
- Using Default Value in SQL Tables
490
- Using Default Value in Oracle 18c
491
- Using Identity in Oracle 18c
492
- Renaming Objects in an Oracle Database
493
- Dropping Objects from an Oracle Database
496
- Granting Access to other Schemas
497
- Accessing other Schemas
500
- Creating Synonyms in Oracle
501
- Oracle Directory
502
- Directory Removing
503
- Directory Problems
504
- Directory Metadata
505
- Exercise Ten
506
Seventeen
Altering and Populating Tables
508
- Rules
509
- Syntax
510
- Unused Columns
512
- Rules for Populating tables
515
- Syntax for populating tables
516
- Database Values
518
- Using Default
519
- General Comments
520
- Updating Data in Oracle Tables
521
- Deleting Data in an Oracle Table
523
- Large Objects
524
- Bfiles
526
- Binary Float/Doubles
527
- Using the Merge technique
528
- Multiple Table Inserts
533
- Unconditional Multiple Inserts
534
- Conditional First Multiple Insert
536
- Conditional All Multiple Insert
538
- Pivoting Multiple Insert
540
Eighteen
Committing Records
542
- Autocommit
546
- Rollback
547
- Savepoints
548
- Exitcommit in SQL*Plus
550
- For Update in SQL
552
- For Update in SQL using 'where'
553
- For Update in SQL naming Columns
554
- Exercise Eleven
555
Nineteen
Flashback Query in Oracle Databases
562
- Dbms_Flashback
569
- Time Flashbacks
570
- SCN Flashbacks
575
- Flashback Example
576
- Implicit Flashback
579
- Exercise Twelve
582
Twenty
Materialized Views
584
- Introduction
585
- Syntax for MVs
587
- Refreshing MVs
589
- Building MVs
595
- Removing MVs
597
- Miscellaneous
598
- Metadata
599
- Exercise Thirteen
600
Twenty One
Regular Expressions in Oracle SQL
604
- Introduction
605
- Using REGEXP_LIKE in Where Clauses
608
- Case Sensitivity in Regex
610
- Line Anchors in Regex
612
- Using the Dot (Any Character)
614
- Counting Characters {Intervals}
618
- Character Lists in Regex
622
- Excluding Character Lists
628
- Alternatives to Character Lists
629
- Class Shorthands
633
- Or Patterns (Alternatives)
634
- Or Patterns with Character Lists
635
- Using the Question Mark (Optional)
636
- Using the Plus sign (Mandatory)
639
- Using the Star sign (Optional)
642
- Backreferencing in Regex
643
- POSIX
645
- Using POSIX in Oracle - [:upper] example
647
- Using POSIX in Oracle - [:digit] example
648
- Using POSIX in Oracle – Character equivalents
649
- Using Regexp_count in SQL
650
- Using Regexp_replace in SQL
654
- Using Regexp_instr in SQL
656
- Using Regexp_substr in SQL
658
- Using Regular Expressions in Oracle
660
- Exercise Fourteen
661
Twenty Two
SQL Loader
669
- Introduction
670
- Invoking SQLLDR
671
- Integrated Control Files
672
- Commenting in Control Files
675
- Optionally Enclosed By '"'
676
- Badfile
677
- Dealing with Dates and Time in the Control File
679
- Setting Datatypes in the Control File
683
- Using the Filler Datatype
685
- Inserting Values Not Found in the Data
686
- Trailing Nullcols
687
- Using Functions in Control Files
688
- Using Functions with Filler in Control Files
689
- Using Nullif in Control Files
690
- Using Defaultif in Control Files
691
- Variations in Data
693
- Working with Multi-Line Records Using Continueif
695
- Preserve Blanks
697
- Skipping Records
698
- Selecting Records with a 'When' Clause
699
- Using Discard Files with When Clause
701
- Working with Fixed Length Values
702
- Using When with Fixed Length Values
704
- Loading more than One Set of Data
707
- Using External Infiles
708
- Running SQLLDR
709
- SQLLDR Switches
710
- SQLLDR Switches – Specifying Files
711
- SQLLDR Switches – Specifying Load Numbers
713
- SQLLDR Switches – Long Commands in DOS
714
- SQLLDR Parameter File
715
- Exercise Fifteen
716
Twenty Three
External Tables
719
- Introduction
720
- Syntax for External Tables
721
- Create Oracle Directories
722
- Viewing Existing Directories
723
- Creating External Tables with Comma Separated Files
724
- Creating External Tables
725
- Access Parameters
727
- Access Parameters (Comma Separated)
728
- Access Parameters (Tab Separated)
729
- Access Parameters – Defining Fields
730
- Access Parameters (Missing Field Values are Null)
723
- Access Parameters (Reject Rows With all Null Fields)
733
- Access Parameters (Load When)
734
- Access Parameters (Skip)
736
- Access Parameters (Audit Tables)
737
- Access Parameters (Logfile)
738
- Access Parameters (Badfile)
741
- Access Parameters (Discardfile)
743
- Access Parameters (Reject Limit)
744
- Access Parameters for Fixed Length Files
745
- Using Load When with Position
751
- Using Alter Table with External Tables
752
- Metadata for External Tables
753
- Using Oracle Datapump
754
- Losing the External File
756
- Exercise Sixteen
757
Twenty Four
Exporting & Importing Data into Oracle
758
- Introduction
759
- Exporting
760
- EXP-00091: Exporting questionable statistics.
768
- Exporting using the Command Line
769
- Exporting using a Parameter File
770
- Naming Tables using a Parameter File
771
- Command Line Options
772
- Importing
773
- Importing using the Command Line
780
- Importing using a Parameter File
781
- Command Line Options
782
Twenty Five
Exporting with Data Pump
783
- Introduction
784
- Exporting using Expdp
785
- Replacing Existing Files
787
- Exporting Specific Tables using Expdp
788
- Using Expdp with a Parameter File
792
- Importing using Impdp
793
- Exercise Seventeen
794
Twenty Six
Advanced Date Datatypes
795
- Date Datatypes
796
- Database Timezone
798
- Timestamp
804
- Timestamp and Sysdate
805
- Extract
809
- To_Timestamp
810
- Timestamp with Time Zone
812
- Time Zone and Extract
815
- TZ_Offset
816
- Time Zones and UTC
817
- To_Timestamp_TZ
819
- From_TZ
820
- Timestamp with Local Time Zone
821
- Intervals
822
- Assigning Year Intervals
825
- Assigning values to Day Intervals
827
- Using Intervals
829
- Intervals and Extract
835
- Exercise Eighteen
836
Twenty Seven
Basic Administration
844
- Passwords
844
- Sqlprompt
845
- Creating Users/Roles
846
Twenty Eight
DBMS_METADATA
849
- Generating Database Object Scripts
850
- List of Object Types
851
- Example of Using Dbms_Metadata
852
- Exercise Nineteen
854
Twenty Nine
Object Oriented Programming
855
- Introduction
856
- Definition of Objects
857
Thirty
Row Objects
862
- Introduction
863
- Creating
864
- Metadata
866
- Data
867
- Indexes
868
- Views
870
- Removing
871
- OIDs
873
Thirty One
Column Objects
881
- Introduction
882
- Describing
883
- Inserting into
886
- Selecting from
888
- Updating
894
- Object Views
895
Thirty Two
Collections (Varying Arrays)
897
- Introduction
898
- Defining a single element
900
- Inserting
901
- Selecting
903
- Updating
905
- Deleting
906
- Defining using an Abstract datatype
907
- Inserting an Abstract datatype
908
- Selecting an Abstract datatype
909
Thirty Three
Collections (Nested Tables)
910
- Introduction
911
- Defining
912
- Inserting
917
- Selecting
920
- Updating
923
- Deleting
924
- Exercise Twenty
925
