Index of Oracle 11g Essential SQL training course using APEX 4.2
Section
Contents
Page
One
Introduction to SQL
2
- Definition of SQL
3
- Definition of a Database
4
- Application Express
5
Two
Using Application Express
6
- Introduction
7
- Logging in
9
- The Workspace
12
- The Schema
13
- Dashboard Topography
14
- SQL Workshop
15
- Administration
16
- Changing the Password
18
- About Administration
19
- Exiting the Session
20
- Invalid Login Credentials
21
- Timeout and Session Expiry
22
Three
SQL Scripts
23
- Introduction
24
- Tables
25
- Views
26
- Materialized Views
27
- Interface
28
- Actions
30
- Actions (Select Columns)
31
- Actions (Filter)
32
- Actions (Rows Per Page)
36
- Actions (Format)
37
- Actions (Sort)
38
- Actions (Control Break)
39
- Actions (Highlight)
40
- Actions (Compute)
41
- Actions (Aggregate)
43
- Actions (Chart)
44
- Actions (Group By)
45
- Actions (Flashback)
46
- Actions (Save Report)
48
- Actions (Reset)
49
- Actions (Help)
50
- Actions (Download)
51
- Actions (Download CSV)
52
- Actions (Download HTML)
54
- Actions (Download Email)
56
- Actions (Subscription)
58
- Alternative View of Scripts (View Icons)
59
- Running Scripts
60
- Manage Script Results
62
- Other Options
71
- Upload
72
- Create
75
- Show Quotas
76
- Exporting SQL Scripts from Application Express
77
- Importing SQL into Application Express
80
- Exercise One
82
Four
Query Builder
89
- Introduction
90
- Topography
93
- The Selection Pane
96
- The Results Pane - Conditions
99
- Results
101
- Downloading to Excel
102
- Viewing the SQL
103
- Copying the SQL
104
- Sequencing Columns
106
- Creating Aliases
107
- Conditions
109
- Some Points to Note
120
- Ordering Records
121
- Saving SQL
124
- Deleting Columns
126
- Saving SQL
124
- Exercise Two
127
- Using Functions
137
- Using Text Functions
139
- Using Date Functions
142
- Using Number Functions
144
- Using Group By
147
- Exercise Three
152
Five
SQL Commands
161
- Introduction
162
- Topography
163
- Command Window
164
- Autocommit
165
- Rows
166
- Clear Command
167
- Find Tables
168
- Find Tables (Rows)
170
- Find Tables (Views)
171
- Find Tables (Columns)
172
- Save
174
- Results
176
- Explain
178
- Describe
181
- History
182
- Commenting SQL
183
- Oracle Metadata
184
- Exercise Four
186
Six
SQL Structure
190
- Amending Query Builder SQL
192
- Column Aliases
193
- Table Aliases
194
- Displaying Literals in a Select statement
195
- Using the Sys.Dual table
196
- More SQL Functions
197
- Nesting SQL Functions
200
- Concatenating Columns
201
- Performing Calculations
202
- Performing Calculations on Dates
203
- Formatting Output
204
- Ordering Data
206
- Exercise Five
208
Seven
Developing Where Clauses
215
- Limiting Data in Query Builder
216
- Combining Conditions
219
- Truncating Values
220
- Using Functions in Where Clauses
221
- Sub-Queries
222
- Correlated Sub-Queries
224
- Case Restrictions in Where Clauses
225
- Rownum in SQL Tables
226
- Using Rowid in SQL Tables
227
- Exercise Six
228
Eight
Oracle Datatypes
235
- Introduction
236
- Varchar2
237
- Varchar2 and Char
239
- Number
240
- Date
241
- Float
242
- Long
243
- Large Objects
244
- Large Objects in Object Browser
245
- Large Objects in SQL Command
246
- Bfile
247
Nine
Developing Aggregating Values
248
- Group By in Query Builder
249
- Using '*'
250
- Using Having with Group By
251
- Using Rollup to produce a Grand Total
252
Ten
Tree Walking Tables in SQL
253
- Introduction
254
- Syntax for Tree Walking in SQL
256
- Example of Tree Walking in SQL
257
- Advanced Ordering of Siblings in Tree Walks
258
- Sys_Connect_By_Path
259
- Exercise Seven
260
Eleven
Joining Tables in SQL
264
- Introduction
265
- Linking Tables in in Query Builder
266
- Linking Multiple Tables in Query Builder
269
- Deleting the Links in Query Builder
272
- Linking Tables in Query Builder Manually
273
- Problems with Joining Tables in SQL
275
- Outer Joins in Query Builder
276
- Using Ansi SQL/99 to Join Tables
281
- Ansi SQL/99 Standards (Natural Joins)
282
- Ansi SQL/99 Standards (Cross Joins)
283
- Ansi SQL/99 Standards (Joins/Using)
284
- Ansi SQL/99 Standards (On)
285
- Ansi SQL/99 Standards (Multiple On)
286
- Ansi SQL/99 Standards (Left Outer Join)
287
- Ansi SQL/99 Standards (Right Outer Join)
288
- Ansi SQL/99 Standards (Full Outer Join)
289
- Merging Tables in SQL
290
- 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 Minus to Merge Tables in SQL
294
- Knowledge Check
295
- Using the Merge Syntax in SQL
300
- Exercise Eight
301
Twelve
Advanced SQL Query Techniques
305
- Introduction
306
- Pivot
307
- Adding a Total to a Pivot
312
- Changing the Pivot Headings of X Axis
313
- Rotating the Axis of the Pivot
314
- Unpivot
315
- Unpivot and Null values
318
- Using First_Value/Last_Value in SQL
319
- Ignoring Nulls with First/Last_Value
321
- Window Specification
322
- Breakdown of Window Specification
323
- Window Specification - The Arguments
325
- Window Specification - Query Partition
326
- Window Specification - The Order By
328
- Window Specification - The Windowing
330
- Window Specification - Rows between
332
- Window Specification - Shortcuts
336
- Difference between Range and Rows
337
- Using Lead and Lag in SQL
339
- Using Rank Function in SQL
342
- Using Ntile Function in SQL
349
- Using Width_Bucket Function in SQL
351
- Using Listagg Function in SQL
354
- Using Case Function in SQL
356
- Exercise Nine
360
Thirteen
Object Browser in Application Express
364
- Introduction
365
- Topography
366
- Table Structure
371
- Create Table
373
- Create Columns
376
- Table Indexing
378
- Primary Keys
379
- Unique Keys
381
- Foreign Keys
382
- Foreign Keys and Cascading
384
- Sequences
385
- Create Primary Key
387
- Create Foreign Key
392
- Create Constraints
397
- Check Constraints
398
- Unique Key
400
- Confirm
401
- Table SQL
403
- Amending Existing Table Structures
404
- Add Column
405
- Modify Column
407
- Rename Column
410
- Drop Column
411
- Rename (Table)
412
- Copy (Table)
413
- Drop
416
- Truncate (Table)
417
- Create Lookup Table
418
- Exercise Ten
426
- Indexes
430
- Model
436
- Constraints
438
- Grants
443
- Statistics
446
- UI Defaults and Triggers
447
- Dependencies
448
- Synonyms
449
- Synonyms
449
- Synonyms
449
- Synonyms
449
Fourteen
Advanced SQL for Tables
453
- Creation Syntax for Tables in SQL
454
- Syntax for Altering Tables
456
- Virtual Columns
458
- Creating Virtual Columns with new Table
459
- Specifying Virtual Column Datatype and Size
460
- Using Virtual Columns in SQL
462
- Using Virtual Columns with Functions
464
- Limitations of Virtual Columns (ORA-54012)
465
- Limitations of Virtual Columns (ORA-54015)
466
- Limitations of Virtual Columns (ORA-54013)
467
- Index Creation in SQL
468
- Check Constraints
471
- Creating Sequences in SQL
472
- Using Sequences in SQL
473
- Displaying Variables in a Select statement
474
- Renaming Objects in an Oracle Database
475
- Dropping Objects from an Oracle Database
478
- Granting Access to other Schemas / Users
479
- Granting Access to other Schemas
480
- Accessing other Schemas
481
- Exercise Eleven
483
Fifteen
Populating Tables with Object Browser
486
- Introduction
487
- Using Object Browser
488
- Query
490
- Editing Records
493
- Deleting Records
495
- Count Rows
496
- Insert Row
497
- Rules When Populating Tables in SQL
499
- SQL Syntax for Inserting into Tables
500
- Limitations of Virtual Columns (ORA-54013)
505
- Using Variables to Populate Tables
506
- Updating in Oracle Tables
507
- Deleting Data from Oracle Tables
509
- Handling Large Objects
510
- Handling BFiles
512
Sixteen
Committing Records
513
- The Difference Between DDL and DML
514
- Exercise Twelve
517
Seventeen
Views and Materialized Views
523
- Introduction to Views
524
- SQL Syntax for Views
526
- Object Browser
529
- View
530
- Compile
531
- Drop
534
- Code
535
- Data
536
- Query
537
- Count Rows
538
- Insert Row
539
- Grants
541
- UI Defaults
542
- Dependencies
543
- SQL
544
- Creating Views in Object Browser
545
- Materialized Views
547
- Object Browser
549
- Materialized View
550
- Drop
551
- Data
552
- Query
553
- Count Rows
554
- Details
555
- Grants
556
- Dependencies
557
- SQL
558
- Creating Materialized Views in Object Browser
559
- Syntax for Creating Materialized Views
561
- Refreshing Materialized Views
562
- Building Materialized Views
567
- Exercise Thirteen
569
Eighteen
Application Express Utilities
571
- Introduction
572
- Data Workshop
574
- Data Load
575
- Text Data
576
- Loading CSV files into New Table
578
- Loading CSV files into Existing Table
583
- Copy and Paste into a Table
589
- XML Data
595
- Spreadsheet Data
599
- Data Unload
600
- Repository
604
- Generate DDL
605
- Schema Comparison
612
- Object Reports
615
- Table Reports
618
- Exception Reports
622
- Security Reports
625
- All Object Reports
628
- Data Dictionary
633
- PLSQL Reports
636
- Recycle Bin
637
- Exercise Fourteen
639
