Index of Oracle SQL with SQL Developer 18.1 training course
Section
Section Name
Page
Part I
One
Introduction to SQL
2
- Definition
3
- Usage of SQL
4
- What is SQL used for ?
5
- Who uses SQL ?
6
- Definition of a Database
7
- What is SQL Developer ?
8
Two
The SQL Developer Interface
9
- Introduction
10
- SQL Developer Connections
12
- Connections
14
- Worksheet
15
- Toolbar and Menus
16
Three
Connections
17
- Viewing Objects
18
- Viewing Object Structure
19
- Table Structure
20
- Column Structure
22
- Data Default
27
- The Number Datatype
28
- The Varchar2 Datatype
29
- The Date Datatype
30
- Large Objects
31
- The BLOB Datatype
32
- The CLOB Datatype
33
- Object Details Dialog
34
Four
Object Details Dialog
35
- Introduction
36
- Columns Tab
37
- Columns Tab (Resizing columns)
38
- Columns Tab (Moving columns)
39
- Columns Tab (Sorting columns)
40
- Columns Tab (Filter)
41
- Columns Tab (Icons)
42
- Columns Tab (Right Click)
43
- Data Tab
44
- Data Tab (Single Record View)
45
- Data Tab (Count Rows)
47
- Data Tab (Viewing Blobs and Clobs)
48
- Data Tab (Viewing Blobs)
49
- Model Tab
51
- Constraints Tab
54
- Grants Tab
55
- Statistics Tab
56
- Triggers Tab
57
- Flashback Tab
58
- Dependencies Tab
59
- Details Tab
60
- Partitions Tab
61
- Indexes Tab
62
- SQL Tab
63
- Exercise One
65
Five
Limiting Records in the Data Tab
76
- Introduction
77
- Usage
78
- Using Varchar2 and Dates
79
- Case Sensitivity
80
- Limiting Dates
81
- Invalid Identifier
82
- Invalid Number
83
- Direct Comparison
84
- Value Comparison
86
- Column Comparison
88
- Range Comparison
89
- List Comparison
92
- Wildcard Comparison
95
- Null Values
99
- Combining Filters
100
- AND Filters
101
- OR Filters
102
- Complex Filters
103
- Exercise Two
104
Six
Ordering Records in the Data Tab
114
- Ordering Records in the Data Tab – Introduction
115
- Ordering Records in the Data Tab – Examples
116
Seven
SQL Worksheet
117
- Introduction
118
- Opening in SQL Developer
119
- Creating an SQL Statement
120
- Executing an SQL Statement
122
- Formatting the SQL Statement
123
- SQL Syntax
124
- Labelling Selected Columns (Column Aliases)
126
- Using Literals
130
- Database Variables and Sys.DUAL
131
- Saving the SQL to a File
133
- Opening and Executing a File
135
- Commenting Out Lines
136
- Identifying Amended Files
138
- SQL History
139
- Miscellaneous Commands
140
- Query Result Buttons
141
- Find / Highlight
146
- Searching the Output
147
- Searching the Output
148
- Exercise Three
149
Eight
Manipulating SQL Output
155
- Introduction
156
- Functions
157
- Upper and Lower
160
- Round
161
- Ceil and Floor
163
- Count
164
- Using '*' in Selections
165
- Sum
166
- To_Char
167
- To_Char (Number Examples)
168
- To_Char (Date Examples)
170
- Substr
172
- Substr Examples
173
- Decode
174
- Combining Functions
176
- Concatenating Columns
177
- Performing Mathematics
179
- Performing Mathematics
180
Nine
Snippets
181
- Introduction
182
- Topography
183
- Using Snippets with the SQL Worksheet
184
- Adding User Defined Snippets
187
- Editing User Defined Snippets
191
- Exercise Four
192
Ten
Limiting Data in the SQL Worksheet
201
- The Where Clause
202
- Using Functions within the Where Clause
204
- Using Lower with a Where Clause
205
- Using Substr with a Where Clause
206
- Using NVL with a Where Clause
207
- Using To_Date with a Where Clause
208
- Using Trunc with a Where Clause
211
- Using Trunc with a Number
212
- Using Trunc with a Date
213
- Sub-Queries with a Where Clause
214
- Exists with a Where Clause
216
- Correlated Sub-Queries
218
- Table Aliases
219
- Advanced IN Sub-Queries
220
Eleven
Ordering Data in the SQL Worksheet
222
- Introduction
223
- Advanced Techniques
224
- Advanced Techniques
225
- Using Decode in Order By
226
- Exercise Five
228
Twelve
Aggregating Values in SQL Worksheet
233
- Introduction
234
- Using Group By
236
- Using Where with Group By
238
- Using Having with Group By
240
- Using Rollup to Total Grouping
242
- Exercise Six
247
Thirteen
Joining Tables in an SQL Statement
250
- Introduction
251
- Linking Tables in an SQL Statement
253
- Linking Tables using ANSI SQL/86
257
- Linking Tables in an SQL Statement
258
- Cartesian Products in an SQL Statement
259
- Problems with Joining Tables in SQL
261
- Linking Tables using ANSI SQL/99
264
- Natural Join using ANSI SQL/99
265
- Cross Join using ANSI SQL/99
267
- Join using ANSI SQL/99
268
- Join On using ANSI SQL/99
269
- Outer Joins using ANSI SQL/99
270
- Left Outer Join using ANSI SQL/99
271
- Right Outer Join using ANSI SQL/99
272
- Full Outer Join using ANSI SQL/99
273
- Multiple Joins using ANSI SQL/99
274
- Exercise Seven
275
Fourteen
Query Builder in the SQL Worksheet
278
- Introduction
279
- Invoking Query Builder
280
- Query Builder Topography
282
- Selecting Tables
286
- Checking Links
287
Fifteen
Using Views
288
- Using Views – Database Views
289
- Using Views – Materialized Views
290
Sixteen
Merging Tables in an SQL Statement
291
- Merging Tables in SQL
292
- Using Union to Merge Tables in SQL
293
- Using Union All to Merge Tables in SQL
294
- Using Intersect to Merge Tables in SQL
295
- Using Minus to Merge Tables in SQL
296
- Knowledge Check
297
- Knowledge Check - Union
298
- Knowledge Check - Union All
299
- Knowledge Check - Intersect
300
- Knowledge Check - Minus
301
- Using the Merge Syntax in SQL
302
Seventeen
Regular Expressions in Oracle SQL
304
- Introduction
305
- Using REGEXP_LIKE in Where Clauses
308
- Case Sensitivity
310
- Line Anchors
312
- Using the Dot (Any Character)
314
- Counting Characters {Intervals}
318
- Character Lists
322
- Excluding Character Lists
328
- Alternatives to Character Lists
329
- Class Shorthands
332
- Or Patterns (Alternatives)
333
- Or Patterns with Character Lists
334
- Using the Question Mark (Optional)
335
- Using the Plus sign (Mandatory)
338
- Using the Star sign (Optional)
341
- Backreferencing
342
- POSIX
344
- Using POSIX in Oracle - [:upper] example
346
- Using POSIX in Oracle - [:digit] example
347
- Using POSIX in Oracle – Character equivalents
348
- Using Regexp_count in SQL
349
- Using Regexp_replace in SQL
353
- Using Regexp_instr in SQL
355
- Using Regexp_substr in SQL
357
- Using Regular Expressions in Oracle
359
Eighteen
Exporting Data in SQL Developer
360
- Introduction
361
- File Types
363
- Exporting to CSV
364
- Exporting to Delimited
366
- Exporting to Excel
367
- Exporting to Excel.xml
369
- Exporting to Fixed
371
- Exporting to HTML
373
- Insert
376
- Json
378
- Insert
379
- Loader
380
- Loader (ldr)
381
- Loader (ctl)
382
- PDF
383
- Text
385
- XML
387
Nineteen
SQL Developer Help Facility
389
- Introduction
390
- Search and Table of Contents
391
- Start Page
392
- Data Miner
393
- Check for Updates
394
- About
395
- Exercise Eight
396
Part II
One
Data Manipulation in SQL Developer
2
- Introduction
3
- Updating a record in Data
4
- Updating a Primary Key in Data
6
- Reverting Changes using Refresh
7
- Updating a table with a trigger in Data
8
- Deleting a record in Data
10
- Deleting a record which has children
11
- Inserting a record in Data
12
- Problems manipulating data
14
Two
Data Manipulation in SQL Worksheet
15
- Introduction
16
- Inserting Records using SQL Worksheet
17
- Rules When Populating Tables
18
- SQL Syntax for Populating Tables
19
- Using Sysdate To Populate Tables
24
- General Comments
25
- Inserting Data using the SQL Worksheet
26
- Saving the Insert statement
32
- Updating Data in SQL Worksheet
33
- Deleting Records using Truncate
40
- Exercise One
42
Three
Importing External Data
52
- Introduction
53
- Beginning the Import
54
- Import method
57
- Choose Columns
58
- Column Definition
60
- Finish
62
- Importing xls files (Insert Script)
64
- Importing xls files (Targeting Columns)
65
- Cutting and pasting xls files
66
- Importing csv files
67
- Exercise Two
68
Four
Table Manipulation in SQL Developer
69
- Creating Columns for Tables
70
- Naming Tables and Columns
71
- Creating Tables
73
- Creating Columns
76
- Column Datatypes
77
- Creating Mandatory Columns
79
- Creating Indexes for Tables
80
- Default
82
- Table Creation DDL
85
- Creation Syntax for Tables
86
- Comments for Columns
87
- Comments for Tables
91
- Renaming a Table
94
- Copying Tables SQL Syntax
97
- Creating a Table by copying an existing one
98
- Copying Tables SQL Syntax
99
- Dropping Tables
101
- Dropping Tables (Cascade Constraints)
103
- Dropping Tables (Purge)
104
- Dropping Tables (Flashback to Before Drop)
107
- Dropping Tables SQL Syntax
109
- Restoring Tables from Recycle SQL Syntax
110
- Altering Columns
111
- Renaming Columns
113
- Renaming Columns Syntax
115
- ORA-00957: Duplicate Column Name
116
- Adding Columns
117
- ORA-00910: specified length too long for its datetype
118
- Adding Columns SQL Syntax
119
- Dropping Columns
121
- ORA-12992: Cannot drop parent key column
122
- Dropping Columns Syntax
123
- Modifying Columns Syntax
124
- Rules for Modifying Columns
125
- Multiple Column Modification
126
- Normalizing Columns
127
Five
Table Constraints and Indexes
130
- Introduction
131
- Creating Indexes in SQL
134
- Primary Key Creation in SQL
137
- Add Primary Key
139
- ORA-02260: Table can have only one primary key
141
- Unique Key Creation in SQL
142
- Add Unique Key
144
- Add Foreign Key
146
- ORA-02270: no matching unique or primary key
149
- Non-Unique Index
150
- Disabling Indexes in SQL
153
- Check Constraints
154
- ORA-02293: cannot validate – check constraint
155
- Check Constraint Syntax
156
- Add Check
157
- Drop
158
- Other options
160
- Enable / Disable Related Foreign Keys / All / Single
161
- Rename Single
162
- Exercise Three
163
Six
Accessing Objects
175
- Introduction
176
- Privileges
177
- Granting Privileges Syntax
178
- Privileges
180
- Granting Privileges
181
- Revoking Privileges Syntax
182
- Revoking Privileges
183
- Viewing Privileges
184
- Accessing other Schemas
185
- ORA-01031: Insufficient Privileges
187
- Accessing other Schemas with SQL Worksheet
188
- Accessing other Schemas with Synonyms
189
- Synonym SQL Syntax
193
- ORA-00955: Name is already used
194
- Dropping Synonyms
195
- Dropping Synonyms Syntax
196
- Public Synonyms
197
- ORA-01031: Insufficient Privileges
198
- Exercise Four
199
Seven
Virtual Columns
201
- Introduction
202
- Creating Virtual Columns with new Table
203
- Specifying Virtual Column Datatype and Size
204
- Adding Virtual Columns to Existing Table
205
- Using Virtual Columns in SQL …
206
- Using Virtual Columns with Functions
208
- ORA-54012: virtual column is referenced in a column
209
- ORA-54015: Duplicate column expression specified
210
- ORA-54013: INSERT disallowed on virtual columns
211
- ORA-54017: UPDATE disallowed on virtual columns
212
Eight
Invisible Columns in Oracle 12c
213
- Exercise Five
218
Nine
Views
220
- Introduction
221
- Creating Views Syntax
223
- Create (simple)
226
- Creating Views
228
- ORA-00936: Missing expression
230
- Properties
231
- Force on Create
232
- Query Restriction
233
- Updating using Views
235
- ORA-42399: Cannot perform a DML on a read-only
236
- Renaming Views SQL Syntax
237
- Renaming Views
238
- Dropping a View SQL Syntax
239
- Dropping a View
240
- Exercise Six
241
Ten
Materialized Views
245
- Introduction
246
- Creating Simple Materialized Views
248
- Refreshing Materialized Views
252
- New Materialized View
253
- New Materialized View
254
- Refresh Options
255
- Materialized Refresh Options
256
- Time Interval
257
- Materialized Time Interval
258
- Fast Refresh and Materialized View Logs
259
- Key Type
264
- Editing Materialized Views
265
- Viewing Materialized Views
268
- Dropping Materialized Views
269
- Synchronizing Materialized Views
270
- Exercise Seven
271
Eleven
Sequences
274
- Introduction
275
- Creating Sequences
276
- Viewing Sequences
278
- Editing Sequences
279
- Using Sequences
280
- Dropping Sequences
281
Twelve
Tree Walking Tables in SQL Worksheet
282
- Introduction
283
- Syntax for Tree Walking
286
- Example of Tree Walking
287
- Using LPAD with Tree Walking
290
- Using Order Siblings By in Tree Walks
292
- Using Sys_Connect_By_Path in Tree Walks
293
- Exercise Eight
295
Thirteen
Sub-Queries in Oracle SQL
299
- Introduction
300
- Using Equals
301
- In and Not In
304
- Exists
306
- Any
307
- Some
308
- All
309
- Correlated Queries
310
- Correlated Updates and Deletes
311
- Inline Views
312
- Inline View Example
313
- Sub-Queries in a Select Statement
314
- Sub-Query Factoring (Using With)
315
- Sub-Query Factoring Example with Aliases
317
- Sub-Query Factoring Example with Joins
318
Fourteen
Advanced SQL Query Techniques
319
- Introduction
320
- Using First_Value/Last_Value
321
- Window Specification
323
- Breakdown of Window Specification
324
- Window Specification - The Arguments
326
- Window Specification - Query Partition
327
- Window Specification - The Order By
329
- Window Specification - The Windowing
331
- Window Specification - Rows between
333
- Window Specification - Shortcuts
337
- Difference between Range and Rows
338
- Using Lead and Lag
341
- Using Case Function
343
- Nesting the Case Function
346
- Subqueries in the Case Function
347
- Using the Rank Function
348
- Using the Ntile Function
351
- Using Width_Bucket Function
352
- Using Width_Bucket Function in SQL Worksheet
353
- Pivot
354
- Adding a Total to a Pivot
358
- Changing the Pivot Headings of X Axis
359
- Rotating the Axis of the Pivot
360
- Unpivot
361
- Unpivot and Null values
364
- Using Listagg Function in SQL
365
- Exercise Nine
367
Fifteen
Using Merge Into
375
- Introduction
376
- SQL Syntax
377
Sixteen
Export DDL (and Data)
380
- Introduction
381
- Object by Object
382
- Exporting DDL and DML
383
- Export Wizard (Step One)
385
- Pretty Print
386
- Show Schema / Grants
387
- Byte
388
- Terminator
389
- Force to Views
391
- Drops
392
- Cascade Drops
393
- Storage
394
- Dependents
395
- Compatible
397
Seventeen
SQL Developer Reports
398
- Introduction
399
- User Defined Reports
401
- Creating User Defined Reports
402
- New Folder
403
- New Report
404
- Master Report
405
- Table
407
- Editing / Advanced Options
408
- SQL Query
409
- Binds
410
- Refreshing
415
- Child Reports
416
- Multiple Child Reports
421
- Drill Down
422
- PDF
426
- PDF (Binary Large Object)
428
- PDF (Cell Layout)
430
- PDF (Column Layout)
431
- PDF (Table Layout)
432
- PDF (Header and Footer)
433
- PDF (Page Layout)
434
- Styles
435
- Chart
436
- Bar
438
- Properties
440
- Pie
444
- Line
446
- Area
448
- Combination
449
- Scatter
450
- Bubble
451
- Stock
452
- Polar
453
- Pareto
454
- Funnel
455
- 3D
456
- Gauge
457
- Script
459
- Child Reports with Charts
460
- Refresh
462
Eighteen
SQL Developer Help Facility
463
- Introduction
464
- Search and Table of Contents
465
- Start Page
466
- Data Miner
467
- Check for Updates
468
- About
469
- Exercise Ten
470
