Index of Oracle SQL with SQL Developer 4.0.3 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
- Connections
12
- Worksheet
15
- Toolbar and Menus
16
Three
Connections
17
- Viewing Objects
18
- Viewing Object Structure
19
- Table Structure
20
- Column Structure
22
- Mandatory Columns
26
- 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
- Constraints Tab
51
- Grants Tab
52
- Statistics Tab
53
- Triggers Tab
54
- Flashback Tab
55
- Dependencies Tab
56
- Details Tab
57
- Partitions Tab
58
- Indexes Tab
59
- SQL Tab
60
- Exercise One
62
Five
Limiting Records in the Data Tab
73
- Introduction
74
- Usage
75
- Using Varchar2
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
- Introduction
112
- 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)
123
- Using Literals
126
- Database Variables and Sys.Dual
127
- Saving SQL to a File
129
- Opening and Executing a File
131
- Commenting Out Lines
132
- Identifying Amended Files
134
- SQL History
135
- Miscellaneous Commands
136
- Query Result Buttons
137
- Find / Highlight
142
- Searching the Output
143
- Exercise Three
145
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
- Adding 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 Cluase
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
- 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 Worksheet
246
- Introduction
247
- Linking Tables in an SQL Statement
249
- Linking Tables using ANSI SQL/86
252
- Linking Tables in an SQL Statement
253
- Cartesian Products in an SQL Statement
254
- Problems with Joining Tables in SQL
256
- Linking Tables using ANSI SQL/99
259
- Natural Join using ANSI SQL/99
260
- Cross Join using ANSI SQL/99
262
- Join using ANSI SQL/99
263
- Join on using ANSI SQL/99
264
- Outer Joins using ANSI SQL/99
265
- Left Outer Join using ANSI SQL/99
266
- Right Outer Join using ANSI SQL/99
267
- Full Outer Join using ANSI SQL/99
268
- Multiple Joins using ANSI SQL/99
269
- Exercise Seven
270
Fourteen
Query Builder in the SQL Worksheet
273
- Introduction
274
- Invoking Query Builder
275
- Query Builder Topography
277
- Selecting Tables
281
- Checking Links
282
Fifteen
Using Views
283
- Database Views
284
- Materialized Views
285
Sixteen
Merging Tables in an SQL Statement
286
- Merging Tables in SQL
287
- Using Union to Merge Tables in SQL
288
- Using Union All to Merge Tables in SQL
289
- Using Intersect to Merge Tables in SQL
290
- Using Minus to Merge Tables in SQL
291
- Knowledge Check
292
- Using the Merge Syntax in SQL
297
Seventeen
Regular Expresssions in Oracle SQL
299
- Introduction
300
- Using REGEXP_LIKE in Where Clauses
303
- Case Sensitivity
305
- Line Anchors
307
- Using the Dot (Any Character)
309
- Counting Characters {Intervals}
312
- Character Lists
316
- Excluding Character Lists
320
- Alternatives to Character Lists
321
- Class Shortcuts
324
- Or Patterns (Alternatives)
325
- Or Patterns with Character Lists
326
- Using the Question Mark (Optional)
327
- Using the Plus sign (Mandatory)
330
- Using the Star sign (Optional)
332
- Using the Question Mark (Optional)
327
- Backreferencing
333
- POSIX
335
- Using POSIX in Oracle - [:upper] example
337
- Using POSIX in Oracle - [:digit] example
337
- Using POSIX in Oracle - Character equivalents
339
- Using Regexp_count
340
- Using Regexp_replace
344
- Using Regexp_instr
346
- Using Regexp_substr
348
- Using Regular Expressions in Oracle
350
Eighteen
Exporting Data in SQL Developer
351
- Introduction
352
- File Types
354
- Exporting to a CSV
355
- Exporting to a Delimited
357
- Exporting to a Excel
358
- Exporting to a Excel.xml
360
- Exporting to Fixed
362
- Exporting to HTML
364
- Exporting to Insert
367
- Exporting to Loader
369
- Exporting to Loader (ldr)
370
- Exporting to Loader (ctl)
371
- Exporting to PDF
372
- Exporting to Text
374
- Exporting to XML
376
- Exercise Eight
378
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 Data in SQL Worksheet
37
- Deleting Records using Truncate
40
- Exercise One
41
Three
Importing External Data
51
- Introduction
52
- Beginning the Import
53
- Importing xls files
55
- Importing xls files (Step 1)
57
- Importing xls files (Step 2)
59
- Importing xls files (Step 3)
60
- Importing xls files (Step 4)
61
- Importing xls files (Step 5)
63
- Importing xls files (date problems)
65
- Importing xls files (Insert Script)
66
- Importing xls files (Targeting Columns)
67
- Importing xls files (date problems)
65
- Cutting and pasting xls files
70
- Importing csv files
71
- Exercise Two
72
Four
Table Manipulation in SQL Developer
73
- Creating Columns for Tables
74
- Naming Tables and Columns
75
- Creating Tables
77
- Creating Columns
80
- Column Datatypes
81
- Creating Mandatory Columns
83
- Creating Indexes for Tables
84
- Default
86
- Table Creation DDL
88
- Creation Syntax for Tables
89
- Comments for Columns
90
- Comments for Tables
93
- Renaming a Table
96
- Creating a Table by copying an existing one
97
- 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
- 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
131
- Introduction
132
- Creating Indexes in SQL
133
- Index Creation in SQL
136
- Disabling Indexes in SQL
138
- Check Constraints
139
- Add Check
140
- Add Primary Key
142
- Add Foreign Key
144
- Add Unique
147
- Drop
149
- Rename Single
151
- Enable Related Foreign Keys
152
- Disable Related Foreign Keys
153
- Enable All
154
- Disable All
155
- Enable Single
156
- Disable Single
157
- Advanced Table Creation
158
- Advanced Table Creation (Constraints)
160
- Advanced Table Creation (Indexes)
165
- Advanced Table Creation (Storage)
166
- Advanced Table Creation (Comment)
167
- Advanced Table Creation (DDL)
168
- Renaming a Table
169
- Exercise Three
170
Six
Accessing Objects
182
- Introduction
183
- Privileges
184
- Granting Privileges
186
- Granting Privileges Syntax
187
- Revoking Privileges
189
- Revoking Privileges Syntax
190
- Viewing Privileges
191
- Accessing other Schemas
192
- ORA-01031: Insufficient Privileges
194
- Accessing other Schemas with SQL Worksheet
195
- Accessing other Schemas with Synonyms
196
- Synonym SQL Syntax
200
- ORA-00955: Name is already used
201
- Dropping Synonyms
202
- Dropping Synonyms Syntax
204
- Public Synonyms
205
- ORA-01031: Insufficient Privileges
206
- Exercise Four
207
Seven
Virtual Columns
209
- Creating Virtual Columns with new Table
211
- Specifying Virtual Column Datatype and Size
212
- Adding Virtual Columns to Existing Table
213
- Using Virtual Columns in SQL
215
- Using Virtual Columns with Functions
217
- Limitations of Virtual Columns (ORA-54012)
218
- Limitations of Virtual Columns (ORA-54015)
219
- Limitations of Virtual Columns (ORA-54013)
220
- Limitations of Virtual Columns (ORA-54017)
221
Eight
Invisible Columns in Oracle 12c
222
- Exercise Four
227
Nine
Views
229
- Introduction
230
- Creating Views Syntax
232
- Create (simple)
235
- Creating Views
237
- Properties
241
- Force on Create
242
- Query Restriction
243
- Updating using Views
245
- ORA-42399: Cannot perform a DML operation on a read-only
246
- ORA-01732: Data manipulation operation not legal on this view
247
- Renaming Views SQL Syntax
248
- Dropping Views SQL Syntax
249
- Exercise Six
250
Ten
Materialized Views
254
- Introduction
255
- Creating Simple Materialized Views
257
- Refreshing Materialized Views
261
- New Materialized View
262
- Refresh Options
264
- Time Interval
266
- Fast Refresh and Materialized View Logs
268
- Key Type
273
- Editing Materialized Views
274
- Viewing Materialized Views
276
- Dropping Materialized Views
277
- Synchronizing Materialized Views
278
- Exercise Seven
279
Eleven
Sequences
282
- Introduction
283
- Creating Sequences
284
- Viewing Sequences
286
- Editing Sequences
287
- Using Sequences
288
- Dropping Sequences
289
Twelve
Tree Walking in SQL Worksheet
290
- Introduction
291
- Syntax for Tree Walking
294
- Example of Tree Walking
295
- Using LPAD with Tree Walking
298
- Using Order Siblings By in Tree Walks
301
- Using Sys_Connect_By_Path in Tree Walks
302
- Exercise Eight
304
Thirteen
Sub-Queries in Oracle SQL
308
- Introduction
309
- Using Equals
310
- In and Not In
313
- Exists
315
- Any
316
- Some
317
- All
318
- Correlated Queries
319
- Correlated Updates and Deletes
320
- Inline Views
321
- Inline View Example
322
- Sub-Queries in a Select Statement
323
- Sub-Query Factoring (Using With)
324
- Sub-Query Factoring Example with Aliases
326
- Sub-Query Factoring Example with Joins
327
Fourteen
Advanced SQL Query Techniques
328
- Introduction
329
- Using First_Value/Last_Value
330
- Window Specification
332
- Breakdown of Window Specification
333
- Window Specification - The Arguments
335
- Window Specification - Query Partition
336
- Window Specification - The Order By
338
- Window Specification - The Windowing
340
- Window Specification - Rows between
342
- Window Specification - Shortcuts
346
- Difference between Range and Rows
347
- Using Lead and Lag
350
- Using Case Function
352
- Nesting the Case Function
355
- Subqueries in the Case Function
356
- Using the Rank Function
357
- Using Ntile Function
360
- Using Width_Bucket Function
361
- Using Width_Bucket Function in SQL Worksheet
362
- Pivot
363
- Adding a Total to a Pivot
367
- Changing the Pivot Headings of X Axis
368
- Rotating the Axis of the Pivot
369
- Unpivot
370
- Unpivot and Null values
373
- Using Listagg Function in SQL
374
- Exercise Nine
376
Fifteen
Using Merge Into
384
- Introduction
385
- SQL Syntax
386
Sixteen
Export DDL (and Data)
389
- Introduction
390
- Object by Object
391
- Exporting DDL and DML
392
- Export Wizard (Step One)
394
- Pretty Print
395
- Show Schema
396
- Grants
397
- Byte
398
- Terminator
399
- Force to Views
401
- Drops
402
- Cascade Drops
403
- Storage
404
- Dependents
405
Seventeen
SQL Developer Reports
407
- Introduction
408
- User Defined Reports
410
- Creating User Defined Reports
411
- New Folder
412
- New Report
413
- Master Report
414
- Editing / Advanced Options
417
- SQL Query
418
- Binds
419
- Refreshing
423
- Child Reports
425
- Property
430
- Multiple Child Reports
432
- Drill Down
433
- PDF
437
- PDF (Binary Large Object)
440
- PDF (Cell Layout)
442
- PDF (Column Layout)
444
- PDF (Header and Footer)
446
- PDF (Table Layout)
449
- PDF (Security)
451
- PDF (Page Layout)
452
- Styles
453
- Chart
454
- Bar
455
- Properties
457
- Pie
463
- Child Reports with Charts
465
- Line
467
- Area
469
- Combination
470
- Scatter
471
- Bubble
472
- Stock
473
- Polar
474
- Pareto
475
- Funnel
476
- 3D
477
- Gauge
478
- Script
480
Eighteen
SQL Developer Help Facility
481
- Introduction
482
- Search and Table of Contents
483
- Start Page
484
- Data Miner
485
- Check for Updates
486
- About
487
- Exercise Ten
488
