Index of Oracle SQL with SQL Developer 19.4 training course
Section
Section Name
Page
Part I
One
Introduction to SQL
2
- Usage of SQL
3
- Definition of a Database
5
- What is SQL Developer ?
6
Two
The SQL Developer Interface
7
- Introduction
8
- SQL Developer Connections
10
- Connections
12
- Worksheet
13
- Toolbar and Menus
14
Three
Connections
15
- Viewing Objects
16
- Viewing Object Structure
17
- Table Structure
18
- Column Structure
20
- Mandatory Columns
24
- Data Default
25
- The Number Datatype
26
- The Varchar2 Datatype
27
- The Date Datatype
28
- Large Objects
29
- The BLOB Datatype
32
- The CLOB Datatype
31
- Object Details Dialog
32
Four
Object Details Dialog
33
- Introduction
34
- Columns Tab
35
- Columns Tab (Resizing columns)
36
- Columns Tab (Moving columns)
37
- Columns Tab (Sorting columns)
38
- Columns Tab (Filter)
39
- Columns Tab (Icons)
40
- Columns Tab (Right Click)
41
- Data Tab
42
- Data Tab (Single Record View)
43
- Data Tab (Count Rows)
45
- Data Tab (Viewing Blobs and Clobs)
46
- Data Tab (Viewing Blobs)
47
- Model Tab
49
- Constraints Tab
52
- Grants Tab
53
- Statistics Tab
54
- Triggers Tab
55
- Flashback Tab
56
- Dependencies Tab
57
- Details Tab
58
- Partitions Tab
59
- Indexes Tab
60
- SQL Tab
61
- Exercise One
62
Five
Limiting Records in the Data Tab
73
- Introduction
74
- Usage
75
- Using Varchar2 and Dates
76
- Case Sensitivity
77
- Limiting Dates
78
- Invalid Identifier
79
- Invalid Number
80
- Direct Comparison
81
- Value Comparison
83
- Column Comparison
85
- Range Comparison
86
- List Comparison
89
- Wildcard Comparison
92
- Null Values
96
- Combining Filters
97
- AND Filters
98
- OR Filters
99
- Complex Filters
100
- Exercise Two
101
Six
Ordering Records in the Data Tab
111
- Ordering Records in the Data Tab – Introduction
112
- Ordering Records in the Data Tab – Examples
113
Seven
SQL Worksheet
114
- Introduction
115
- Opening in SQL Developer
116
- Creating an SQL Statement
117
- Executing an SQL Statement
119
- Formatting the SQL Statement
120
- SQL Syntax
121
- Labelling Selected Columns (Column Aliases)
127
- Using Literals
130
- Database Variables and Sys.DUAL
128
- Saving the SQL to a File
130
- Opening and Executing a File
132
- Commenting Out Lines
133
- Identifying Amended Files
135
- SQL History
136
- Miscellaneous Commands
137
- Query Result Buttons
138
- Find / Highlight
143
- Searching the Output
144
- Exercise Three
146
Eight
Manipulating SQL Output
151
- Introduction
152
- Functions
153
- Upper and Lower
156
- Round
157
- Ceil and Floor
159
- Count
160
- Using '*' in Selections
161
- Sum
162
- To_Char
163
- To_Char (Number Examples)
164
- To_Char (Date Examples)
166
- Substr
168
- Substr Examples
169
- Decode
170
- Combining Functions
172
- Concatenating Columns
173
- Performing Mathematics
175
Nine
Snippets
177
- Introduction
178
- Topography
179
- Using Snippets with the SQL Worksheet
180
- Adding User Defined Snippets
183
- Editing User Defined Category
185
- Editing User Defined Snippets
187
- Exercise Four
188
Ten
Limiting Data in the SQL Worksheet
197
- The Where Clause
198
- Using Functions within the Where Clause
200
- Using Lower with a Where Clause
201
- Using Substr with a Where Clause
202
- Using NVL with a Where Clause
203
- Using To_Date with a Where Clause
204
- Using Trunc with a Where Clause
207
- Using Trunc with a Number
208
- Using Trunc with a Date
209
- Sub-Queries with a Where Clause
210
- Exists with a Where Clause
212
- Correlated Sub-Queries
214
- Table Aliases
215
- Advanced IN Sub-Queries
216
Eleven
Ordering Data in the SQL Worksheet
218
- Introduction
219
- Advanced Techniques
220
- Using Decode in Order By
222
- Exercise Five
224
Twelve
Aggregating Values in SQL Worksheet
229
- Introduction
230
- ORA-00937: Not a single-group function
231
- Using Group By
232
- Using Where with Group By
234
- Using Having with Group By
236
- Using Rollup to Total Grouping
238
- Exercise Six
243
Thirteen
Joining Tables in an SQL Statement
246
- Introduction
247
- Linking Tables in an SQL Statement
249
- Linking Tables using ANSI SQL/86
253
- Linking Tables in an SQL Statement
254
- Cartesian Products in an SQL Statement
255
- Problems with Joining Tables in SQL
257
- Linking Tables using ANSI SQL/99
260
- Natural Join using ANSI SQL/99
261
- Cross Join using ANSI SQL/99
263
- Join using ANSI SQL/99
264
- Join On using ANSI SQL/99
265
- Outer Joins using ANSI SQL/99
266
- Left Outer Join using ANSI SQL/99
267
- Right Outer Join using ANSI SQL/99
268
- Full Outer Join using ANSI SQL/99
269
- Multiple Joins using ANSI SQL/99
270
- Exercise Seven
271
Fourteen
Query Builder in the SQL Worksheet
274
- Introduction
275
- Invoking Query Builder
276
- Query Builder Topography
278
- Selecting Tables
282
- Checking Links
283
Fifteen
Using Views
284
- Using Views – Database Views
285
- Using Views – Materialized Views
286
Sixteen
Merging Tables in an SQL Statement
287
- Creating Table Sets in SQL
288
- Using the Merge / Set Syntax in SQL
289
- Using Union to Merge Tables in SQL
291
- Using Union All to Merge Tables in SQL
292
- Using Intersect to Merge Tables in SQL
293
- Using Intersect All to Merge Tables in SQL
294
- Using Minus to Merge Tables in SQL
295
- Using Minus All to Merge Tables in SQL
296
- Using Except to Merge Tables in SQL
297
- Using Except All to Merge Tables in SQL
298
- Using the Merge Syntax in SQL
299
Seventeen
Regular Expressions in Oracle SQL
300
- Introduction
301
- Using REGEXP_LIKE in Where Clauses
304
- Case Sensitivity
306
- Line Anchors
308
- Using the Dot (Any Character)
310
- Counting Characters {Intervals}
314
- Character Lists
318
- Excluding Character Lists
324
- Alternatives to Character Lists
325
- Class Shorthands
328
- Or Patterns (Alternatives)
329
- Or Patterns with Character Lists
330
- Using the Question Mark (Optional)
331
- Using the Plus sign (Mandatory)
334
- Using the Star sign (Optional)
337
- Backreferencing
338
- POSIX
340
- Using POSIX in Oracle - [:upper] example
342
- Using POSIX in Oracle - [:digit] example
343
- Using POSIX in Oracle – Character equivalents
344
- Using Regexp_count in SQL
345
- Using Regexp_replace in SQL
349
- Using Regexp_instr in SQL
351
- Using Regexp_substr in SQL
353
- Using Regular Expressions in Oracle
355
Eighteen
Exporting Data in SQL Developer
356
- Introduction
357
- File Types
359
- Exporting to CSV
360
- Exporting to Delimited
362
- Exporting to Excel
363
- Exporting to Excel.xml
365
- Exporting to Fixed
367
- Exporting to HTML
369
- Insert
372
- Json
374
- Insert
375
- Loader
376
- Loader (ldr)
377
- Loader (ctl)
378
- PDF
379
- Text
381
- XML
383
Nineteen
SQL Developer Help Facility
385
- Introduction
386
- Search and Table of Contents
387
- Start Page
388
- Data Miner
389
- Check for Updates
390
- About
391
- Exercise Eight
392
Part II
One
Tree Walking Tables in SQL Worksheet
2
- Introduction
3
- Syntax for Tree Walking
6
- Using LPAD with Tree Walking
11
- Using Order Siblings By in Tree Walks
13
- Using Sys_Connect_By_Path in Tree Walks
14
- Exercise Eight
16
Two
Sub-Queries in Oracle SQL
21
- Introduction
22
- Using Equals
23
- In and Not In
26
- Exists
28
- Any / Some
29
- All
30
- Correlated Queries
31
- Correlated Updates and Deletes
33
- Inline Views
34
- Inline View Example
35
- Sub-Queries in a Select Statement
36
- Sub-Query Factoring (Using With)
37
- Sub-Query Factoring Example with Aliases
39
- Sub-Query Factoring Example with Joins
40
Three
Advanced SQL Query Techniques
41
- Introduction
42
- Using First_Value/Last_Value
43
- ORA-30484: missing window specification
46
- Breakdown of Window Specification
47
- Window Specification - The Arguments
49
- Window Specification - Query Partition
50
- Window Specification - The Order By
52
- Window Specification - The Windowing
54
- Window Specification - Rows between
56
- Difference between Range and Rows
63
- Using Lead and Lag
66
- Using the Rank Function
69
- Using the Rank Function with partition by
71
- Using the Ntile Function
72
- Using Width_Bucket Function
74
- Using Width_Bucket Function in SQL Worksheet
76
- Pivot
77
- Adding a Total to a Pivot
84
- Changing the Pivot Headings of X Axis
85
- Unpivot
87
- Using Listagg Function in SQL
90
- Using Case Function
92
- Nesting the Case Function
95
- Subqueries in the Case Function
96
- Using Ratio_To_Report Function
97
- Fetch first rows of a Table (Top-N)
99
- Using Percent with Fetch
101
- Using Offset with Fetch
102
- Using With Ties with Fetch
103
Four
Using Alternative Quoting Mechanism
104
- Introduction to AQM
105
- Using Alternative Quoting Mechanism
106
- Exercise Two
108
Five
Committing Records
116
- DML Changes
118
- DML Changes in SQLcl
123
- DDL Changes
124
Six
Data Manipulation in SQL Developer
125
- General Comments
127
- Updating a record in Data
128
- ORA-00001: unique constraint violated
130
- Reverting Changes using Refresh
131
- Updating a table with a trigger in Data
132
- ORA-04088: error during execution of trigger
133
- Deleting a record in Data
134
- ORA-02292: integrity constraint violated
135
- Rules When Populating Tables
136
- Inserting a record in Data
137
Seven
Data Manipulation in SQL Worksheet
139
- Introduction
140
- SQL Syntax for Populating Tables
142
- SQL Syntax for Populating Tables (multiple insert)
147
- Inserting Data using the SQL Worksheet
150
- Saving the Insert statement
156
- Substitution Variables
157
- Updating Data in SQL Worksheet
161
- Deleting Data in SQL Worksheet
166
- Deleting Records using Truncate
169
- Exercise Three
171
Eight
SQLcl in SQL Developer
178
- Introduction
179
- Running the SQLcl product
180
- How to log into SQLcl
181
- Anatomy of SQLcl
185
- How to exit an SQLcl Session
186
- Copying and pasting in SQLcl
187
- Using the Line Editor in SQLcl
189
- Re-defining Line Editor in SQLcl using define_editor
192
- SQLcl Environment Settings
192
- Opening files in SQLcl
194
- Saving SQL in SQLcl
196
- Changing the SQLcl Prompt
198
- Clearing SQLcl screen
201
- Alias
202
- Statusbar
207
- Spool to Zip
210
- Repeat
211
- Highlighting
213
- Help Alias
214
- glogin.sql, login.sql, startup.sql
215
- Creating login.sql
216
- Saving settings within SQLcl
218
- Using SQLcl within Worksheet
221
- Help
222
- Help Set
224
- Help reserved_words
225
- The purpose of describe
226
- Using spool to write the output to files
227
- Producing DDL
230
- Info
231
- SQLFormat
232
- Load, Unload and LoadFormat
235
- Displaying all Environment Settings
244
- Set Heading off
245
- Set Pause on
246
- Set Pagesize
247
- Set Linesize
249
- Set Wrap
250
- Show All
251
- Renaming Columns for the Session
252
- Wrapping Columns
256
- Justifying Column Headings
258
- Suppressing Columns
259
- Truncate Columns
260
- Viewing Columns
261
- Viewing All Columns
262
- Temporarily Disabling Columns
263
- Clearing Columns
264
- Using Columns to deal with Nulls
265
- Using Break in the Output
266
- Using Aliases with Break
268
- Using Skip with Break
269
- Using Skip Row with Break
270
- Displaying Duplicates with Break
271
- Viewing/Clearing Breaks
272
- Using Compute with Break
273
- Using Compute Labels with Break
275
- Using Compute Sub-totaling with Break
278
- Combining Computes
279
- Setting Titles for the Output
280
- Using SQL.PNO in the Title
284
- Using Variables in the Title
286
- Using Btitle
289
- Reviewing the Title settings
291
- Suppressing the Title settings
292
- Exercise Four
293
Nine
Table Manipulation in SQL Developer
299
- Creating Columns for Tables
300
- Naming Tables and Columns
301
- Creating Tables
303
- Creating Columns
306
- Column Datatypes
307
- Creating Mandatory Columns
309
- Creating Indexes for Tables
310
- Default
312
- Table Creation DDL
314
- Creation Syntax for Tables
315
- Comments for Columns
316
- Comments for Tables
320
- User_Tab_Comments
323
- User_Col_Comments
324
- Renaming a Table
325
- Creating a Table by copying an existing one
328
- Copying Tables SQL Syntax
330
- Dropping Tables
333
- Dropping Tables (Cascade Constraints)
335
- Dropping Tables (Purge)
336
- Dropping Tables (Flashback to Before Drop)
339
- Dropping Tables SQL Syntax
341
- Restoring Tables from Recycle SQL Syntax
343
- Altering Columns
346
- Renaming Columns
348
- Renaming Columns Syntax
350
- ORA-00957: Duplicate Column Name
351
- Adding Columns
352
- ORA-00910: specified length too long for its datatype
353
- Adding Columns SQL Syntax
354
- Dropping Columns
356
- ORA-12992: Cannot drop parent key column
357
- Dropping Columns Syntax
358
- Modifying Columns Syntax
359
- Rules for Modifying Columns
360
- Multiple Column Modification
361
- Normalizing Columns
362
Ten
Table Constraints and Indexes
365
- Introduction
366
- Creating Indexes in SQL
369
- Add Primary Key
372
- ORA-02260: Table can have only one primary key
374
- Add Unique Key
375
- Add Foreign Key
377
- ORA-02267: column type incompatible with type
379
- ORA-02270: no matching unique or primary key
380
- Non-Unique Index
381
- Primary Key Creation in SQLcl
383
- Primary Key Creation in SQL
384
- Foreign Key DDL
385
- Unique Key Creation in SQL
386
- Non-Unique Index DDL
388
- Disabling Indexes in SQL Developer
389
- Disabling Indexes in SQLcl
391
- Check Constraints
392
- Add Check
393
- ORA-02293: cannot validate – check constraint violated
394
- Check Constraint Syntax in SQLcl
395
- Drop
399
Eleven
Sequences
400
- Introduction
401
- Creating Sequences
402
- Viewing Sequences
404
- Editing Sequences
405
- Using Sequences
406
- Dropping Sequences
407
- Exercise Five
408
Twelve
Views
416
- Introduction
417
- Creating Views Syntax
419
- New View
422
- Creating Views
423
- ORA-00936: Missing expression
426
- Check Syntax
427
- Query Restriction
429
- Force on Create
431
- Updating using Views
432
- ORA-42399: Cannot perform a DML on a read-only
433
- Renaming Views SQL Syntax
434
- Renaming Views
435
- Dropping a View SQL Syntax
436
- Dropping a View
437
- Compiling a View
438
Thirteen
Materialized Views
440
- Introduction
441
- Creating Simple Materialized Views
443
- New Materialized View
446
- Refresh Options
447
- Refresh Method
457
- Fast Refresh and Materialized View Logs
459
- Key Type
466
- Editing Materialized Views
467
- Viewing Materialized Views
470
- Dropping Materialized Views
471
- Synchronizing Materialized Views
473
- Exercise Six
475
Fourteen
Accessing Objects
480
- Introduction
481
- Privileges
482
- Granting Privileges
484
- Revoking Privileges
485
- Viewing Privileges
486
- ORA-01031: Insufficient Privileges
487
- Granting Privileges Syntax
488
- Revoking Privileges Syntax
490
- Accessing other Schemas
491
- Accessing other Schemas with SQL Worksheet
493
- Accessing other Schemas with Synonyms
494
- ORA-00955: Name is already used
499
- Synonym SQL Syntax
500
- Dropping Synonyms
501
- Dropping Synonyms Syntax
502
- Public Synonyms
503
Fifteen
Virtual Columns
504
- Creating Virtual Columns with new Table
506
- Adding Virtual Columns to Existing Table
507
- Using Virtual Columns
510
- Using Virtual Columns in SQLcl
511
- Using Virtual Columns with Functions
513
- ORA-54002: only pure functions can be specified
514
- ORA-54012: virtual column is referenced in a column
515
- ORA-54015: Duplicate column expression specified
516
- ORA-54013: INSERT disallowed on virtual columns
517
- ORA-54017: UPDATE disallowed on virtual columns
518
Sixteen
Invisible Columns
519
- ORA-54034: column to be dropped or modified is used
524
Seventeen
Oracle Directories
525
- Introduction
526
- Creating Directories
527
- Dropping Directories
532
Eighteen
External Tables
534
- Introduction
535
- Creating using SQL Developer
536
- Columns
538
- External Table Properties
541
- Access Parameters
545
- Access Parameters (Load When)
546
- Using Load When with Position
549
- Access Parameters (Audit Tables)
550
- Access Parameters (Logfile)
553
- Access Parameters (Badfile)
556
- Access Parameters (Discardfile)
557
- Access Parameters (Skip)
558
- Access Parameters (terminated by)
559
- Access Parameters (missing field values)
560
- Access Parameters (Reject Rows With all Null Fields)
561
- Access Parameters (Tab Separated)
562
- Access Parameters (Trimming values)
563
- Access Parameters (external file columns)
564
- Access Parameters - Defining Fields
566
- Location Specifier
567
- Multiple Flat Files
568
- Creating External Tables Syntax
569
- Access Parameters for Fixed Length Files
572
- Editing External Tables
576
- Using Alter Table with External Tables
577
- Using Oracle Datapump
579
- Creating Oracle Datapump flat files
580
- Accessing Oracle Datapump flat files
584
- Overwriting the Flat File
585
- Metadata for External Tables
586
- Exercise Seven
588
Nineteen
Importing External Data
591
- Introduction
592
- Beginning the Import
593
- Step 1 Using CSV
595
- Step 1 Using Excel
597
- Step 1
598
- Step 2
599
- Step 2 (Insert Script)
600
- Step 3 (Choose Columns)
602
- Step 4 (Choose Definitions)
603
- Step 5 (Finish)
604
- Step 2 (Staging External Table)
607
- Step 3 (Column Definition)
608
- Step 4 (Options)
609
- Step 5 (Finish)
610
- Step 2 (SQL Loader Utility)
612
- Cutting and pasting xls files
613
Twenty
Export the Schema
615
- Introduction
616
- Object by Object
617
- Exporting DDL and DML
618
- Export Wizard (Step One)
620
- Pretty Print
621
- Show Schema / Grants
622
- Byte
623
- Terminator
624
- Force to Views
625
- Drops
626
- Cascade Drops
627
- Storage
628
- Dependents
629
- Compatible
631
- DDL and/or DML
632
- Export Data
633
- Exporting to CSV
634
- Exporting to Delimited
635
- Exporting to Excel
636
- Exporting to Excel.xml
637
- Exporting to Fixed
638
- Exporting to HTML
639
- Exporting to Insert
641
- Exporting to Json
642
- Exporting to Loader
643
- Exporting to t2
644
- Exporting to Text
645
- Exporting to XML
646
- Destination
647
- Specify Data (Step 2)
649
- Export Summary (Step 3)
653
- Selection in the Connections Pane
654
Twenty One
SQL Developer Reports
656
- Introduction
657
- User Defined Reports
659
- Creating User Defined Reports
660
- New Report
661
- Master Report
662
- Table
664
- Editing / Advanced Options
667
- SQL Query
668
- Binds
669
- Refreshing
673
- Child Reports
675
- Multiple Child Reports
681
- Drill Down
683
- Styles
687
- Chart
688
- Bar
690
- Properties
692
- Pie
693
- Line
695
- Area
697
- Combination
699
- Scatter
700
- Bubble
702
- Stock (Candlestick graph)
703
- Polar
705
- Pareto
707
- Funnel
709
- 3D
710
- Gauge
711
- Code
713
- Script
716
- PL/SQL DBMS Output
718
- Child Reports with Charts
720
- Refresh
724
- New Folder
725
Twenty Two
SQL Developer Help Facility
726
- Introduction
727
- Search and Table of Contents
728
- Start Page
729
- Data Miner
730
- Check for Updates
731
- About
732
- Exercise Eight
733
