Top
Email
Bottom
Oracle SQL 19c with Apex 20
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Oracle SQL 19c with Apex 20 course at a glance

Pages
958No of slides in the Manual
Exercises
16Total No of Exercises
Sections
25No of main topics, see index below
Public
NoPublic course in the UK and Ireland
On-Site
YesWe can come to your venue
Level
11 - Beginners
2 - Intermediate
3 - Advanced
Days
4Week days but we can put on weekend courses
Duration:
Minimum skill level required:
On-Site course:
Public course:
Sections:
No of exercises:
No of pages in the manual:
Pre-Requisite course(s):
Suggested further course(s):
Price guide:
4 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
25
16
958
None
Oracle PLSQL 19c with Apex 20
Contact us for the latest pricing

Index of Apex 20 with Oracle 19c SQL training course

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
 
- Exiting the Session
13
 
- Timeout and Session Expiry
14
 
- Topography
16
 
- Logging in
21
Three
SQL Scripts
29
 
- Introduction
30
 
- Topogrpahy
33
 
- Upload Script
35
 
- Script Editor
38
 
- Run Script
41
 
- Manage Results
49
 
- Import
54
 
- Export
61
 
- Show Quotas
67
 
- Create
68
 
- Exercise One
70
Four
Database Objects
77
 
- Introduction
78
 
- Tables
79
 
- Views
80
 
- Materialized Views
81
 
- Indexes
82
 
- Sequences
83
 
- Packages, Functions and Procedures
84
 
- Synonyms and Schemas
85
 
- The Schema
86
Five
Object Browser with Tables
87
 
- Introduction
88
 
- Table
90
 
- Sample Queries
92
 
- Data
96
 
- Limitations of Query
104
 
- Insert Row
105
 
- Count Rows
107
 
- Indexes
108
 
- Model
109
 
- Constraints
110
 
- Exercise Two
112
Six
SQL Commands
120
 
- Introduction
121
 
- Find Tables
123
 
- Saving SQL
128
 
- Saved SQL
129
 
- History
130
 
- Download
131
 
- Settings
132
 
- Invalid SQL
133
 
- Number of Rows returned
135
 
- Explain
137
Seven
Simple Queries
142
 
- Introduction
143
 
- Oracle Metadata
145
 
- SQL Syntax
150
 
- Counting Records in an Oracle Table
153
 
- Displaying Literals in a Select statement
155
 
- Displaying Variables in a Select statement
158
 
- Column Aliases
159
 
- Column Manipulation with SQL Functions
162
 
- Concatenating Columns in SQL
168
 
- Performing Calculations in SQL
169
 
- Ordering Data in SQL
170
 
- Ordering More than One Column
172
 
- Ordering with nulls first / nulls last
173
 
- Manipulating Dates in SQL
174
 
- Displaying Dates in SQL
175
 
- Exercise Three
176
Eight
Limiting Data Selected with Where Clauses
183
 
- Limiting Data in SQL
184
 
- Structure of an SQL Where Clause
185
 
- Different types of SQL Where Clauses
186
 
- Handling Nulls in Where Clauses
194
 
- Case Restrictions in SQL Where Clauses
195
 
- Using Functions in Where Clauses
196
 
- Rownum in SQL Tables
199
 
- Using Rowid in SQL Tables
201
 
- Fetch first rows of a Table (Top-N)
203
 
- Using Percent with Fetch in Oracle 19c
204
 
- Using Offset with Fetch in Oracle 19c
205
 
- Using With Ties with Fetch in Oracle 19c
206
 
- Exercise Four
207
Nine
Oracle Datatypes
212
 
- Introduction
213
 
- Varchar2
214
 
- ORA-00910: specified length too long for its datatype
216
 
- Varchar2 and Char
217
 
- Number
218
 
- Date
219
 
- Float
220
 
- Long
221
 
- Large Obects
222
 
- Bfile
226
Ten
Aggregating Values
227
 
- Introduction
228
 
- Using Group By
229
 
- Using Having with Group By
232
 
- Using Rollup to Total Grouping
234
Eleven
Tree Walking Tables in SQL
235
 
- Introduction
236
 
- Syntax for Tree Walking in SQL
238
 
- Advanced Ordering of Siblings in Tree Walks
239
 
- Sys_Connect_By_Path
241
 
- Exercise Five
243
Twelve
Query Builder
247
 
- Introduction
248
 
- Topography
251
 
- The Selection Pane
252
 
- Conditions
253
 
- Conditions – Alias
254
 
- Conditions – Condition
255
 
- Conditions – Sorting
256
 
- Conditions – Show
257
 
- Conditions – Function / Group By
258
 
- Conditions – Sequencing Columns
262
 
- Copying the SQL
263
 
- Saving SQL
264
 
- Summation
266
 
- Exercise Six
267
Thirteen
Joining Tables in an SQL Statement
277
 
- Using Table Aliases in SQL
278
 
- Linking Tables in an SQL statement
280
 
- Using Query Builder to link Tables
283
 
- Linking Tables in Query Builder Manually
289
 
- Linking Multiple Tables in Query Builder
290
 
- Linking Tables in an SQL statement
293
 
- Problems with Joining Tables in SQL
295
 
- Using Ansi SQL/86 to Join Tables
299
 
- Ansi SQL/86 and Outer Joins
300
 
- Ansi SQL/99 Standards (Natural Joins)
303
 
- Ansi SQL/99 Standards (Cross Joins)
304
 
- Ansi SQL/99 Standards (Joins/Using)
305
 
- Ansi SQL/99 Standards (On)
306
 
- Ansi SQL/99 Standards (Multiple On)
307
 
- Ansi SQL/99 Standards (Left Outer Joins)
308
 
- Ansi SQL/99 Standards (Right Outer Joins)
309
 
- Ansi SQL/99 Standards (Full Outer Joins)
310
 
- Merging Tables in SQL
311
 
- Using Union to Merge Tables in SQL
312
 
- Using Union All to Merge Tables in SQL
313
 
- Using Intersect to Merge Tables in SQL
314
 
- Using Minus to Merge Tables in SQL
315
 
- Knowledge Check
316
 
- Knowledge Check – Union
317
 
- Knowledge Check – Union All
318
 
- Knowledge Check – Intersect
319
 
- Knowledge Check – Minus
320
 
- Using the Merge Syntax in SQL
321
 
- Exercise Seven
322
Fourteen
Oracle Sub-Queries
327
 
- Introduction
328
 
- Using Equals
330
 
- Using In
332
 
- Using Exists
334
 
- Using Any
336
 
- Using Some
337
 
- Using All
338
 
- Correlated Sub-Queries
339
 
- Inline Views
341
 
- Sub-Queries in a Select Statement
343
 
- Sub-Query Factoring (Using With)
345
 
- Sub-Query Factoring Example with Aliases
347
 
- Exercise Eight
350
Fifteen
Using Alternative Quoting Mechanism in SQL
355
 
- Introduction to AQM
356
 
- Using Alternative Quoting Mechanism
357
Sixteen
Regular Expressions in Oracle SQL
359
 
- Introduction
360
 
- Using REGEXP_LIKE in Where Clauses
363
 
- Case Sensitivity in Regex
365
 
- Line Anchors in Regex
367
 
- Using the Dot (Any Character)
369
 
- Counting Characters {Intervals}
373
 
- Character Lists in Regex
377
 
- Excluding Character Lists
383
 
- Alternatives to Character Lists
384
 
- Class Shorthands
388
 
- Or Patterns (Alternatives)
389
 
- Or Patterns with Character Lists
390
 
- Using the Question Mark (Optional)
391
 
- Using the Plus sign (Mandatory)
394
 
- Using the Star sign (Optional)
397
 
- Backreferencing in Regex
398
 
- POSIX
400
 
- Using POSIX in Oracle – [:upper] example
402
 
- Using POSIX in Oracle – [:digit] example
403
 
- Using POSIX in Oracle – Character equivalents
404
 
- Using Regexp_count in SQL
405
 
- Using Regexp_replace in SQL
409
 
- Using Regexp_instr in SQL
411
 
- Using Regexp_substr in SQL
413
 
- Using Regular Expressions in Oracle
415
 
- Exercise Nine
416
Seventeen
Advanced SQL Query Techniques
424
 
- Introduction
425
 
- Pivot
426
 
- Adding a Total to a Pivot
431
 
- Changing the Pivot Headings of X Axis
432
 
- Rotating the Axis of the Pivot
434
 
- Unpivot
435
 
- Unpivot and Null values
438
 
- Using First_Value/Last_Value in SQL
439
 
- Ignoring Nulls with First/Last_Value
441
 
- Window Specification
442
 
- Breakdown of Window Specification
443
 
- Window Specification – The Arguments
445
 
- Window Specification – Query Partition
446
 
- Window Specification – The Order By
448
 
- Window Specification – The Windowing
450
 
- Window Specification – Rows between
452
 
- Window Specification – Shortcuts
456
 
- Difference between Range and Rows
457
 
- Using Lead and Lag in SQL
460
 
- Using Rank Function in SQL
464
 
- Using Ntile Function in SQL
471
 
- Using Width_Bucket Function in SQL
473
 
- Using Listagg Function in SQL
476
 
- Using Case Function in SQL
478
 
- Exercise Ten
482
Eighteen
Creating SQL Database Objects
488
 
- Creating Tables in SQL
489
 
- Mandatory Columns in SQL Tables
491
 
- Using Application Express
492
 
- Create Table
496
 
- Create Columns
500
 
- Table Indexing
506
 
- Primary Keys
507
 
- Sequences
509
 
- Identity
511
 
- Sys_guid
512
 
- Primary Keys in Apex
513
 
- Primary Keys – Populated from a new sequence
514
 
- Primary Keys – Populated from an existing sequence
515
 
- Primary Keys – Not populated
516
 
- Primary Keys – Populated by Identity column
517
 
- Foreign Keys
518
 
- Foreign Keys and Cascading
521
 
- Constraints
523
 
- Unique Keys
526
 
- Confirm
528
 
- Creation Syntax for Tables in SQL
529
 
- Virtual Columns
532
 
- Creating Virtual Columns with new Table
533
 
- Specifying Virtual Column Datatype and Size
534
 
- Adding Virtual Columns to Existing Table
535
 
- Using Virtual Columns in SQL
536
 
- Using Virtual Columns with Functions
537
 
- Limitations of Virtual Columns (ORA-54012)
538
 
- Limitations of Virtual Columns (ORA-54015)
539
 
- Limitations of Virtual Columns (ORA-54013)
540
 
- Viewing Virtual Columns in USER_TAB_COLS
541
 
- Invisible Columns in Oracle 19c
542
 
- Commenting on Tables in SQL
545
 
- Commenting on Columns in SQL
546
 
- Using Default Value in SQL Tables
547
 
- Using Default Value in Oracle 19c
548
 
- Using Identity in Oracle 19c
549
 
- Using Sys_guid in Oracle 19c
551
 
- Index Creation in SQL
552
 
- Disabling Indexes in SQL
554
 
- Views
555
 
- SQL Syntax for Views
557
 
- Views in Object Browser
558
 
- Dropping Views in Object Browser
563
 
- Views Code in Object Browser
564
 
- Views Data in Object Browser
565
 
- Views Query in Object Browser
566
 
- Views Count Rows in Object Browser
567
 
- Views Insert Rows in Object Browser
568
 
- Views Grants in Object Browser
570
 
- Views UI Defaults in Object Browser
571
 
- Views Dependencies in Object Browser
572
 
- Views SQL in Object Browser
573
 
- Creating Sequences in SQL
574
 
- Using Sequences in SQL
575
 
- Altering Sequences in SQL
577
 
- Renaming Objects in an Oracle Database
578
 
- Dropping Objects from an Oracle Database
581
 
- Granting Access to other Schemas
582
 
- Granting Access to other Schemas with Query Builder
583
 
- Granting Access to other Schemas
586
 
- Accessing other Schemas
588
 
- Creating Synonyms in Object Browser
589
 
- Exercise Eleven
593
Nineteen
Altering the structure of Oracle Tables/Views
597
 
- Introduction
598
 
- Some Rules When Altering Tables
599
 
- Syntax for Altering Tables
600
 
- Using Object Browser
601
 
- Using Object Browser – Add Column
602
 
- Using Object Browser – Modify Column
604
 
- Using Object Browser – Rename Column
606
 
- Using Object Browser – Drop Column
607
 
- Using Object Browser – Rename (Table)
608
 
- Using Object Browser – Copy (Table)
609
 
- Using Object Browser – Drop
613
 
- Using Object Browser – Truncate (Table)
614
 
- Using Object Browser – Create Lookup Table
615
Twenty
Populating Tables with Object Browser
622
 
- Introduction
623
 
- Rules When Populating Tables in SQL
624
 
- Using Object Browser
625
 
- Syntax for Populating Tables
633
 
- Using Variables To Populate Tables
638
 
- General Comments
639
 
- Updating Data in Oracle Tables
640
 
- Deleting Data from Oracle Tables
642
 
- Handling Large Objects in SQL Plus
643
 
- Handling BFiles in SQL Plus
645
Twenty One
Committing Records
646
 
- The Difference Between DDL & DML
647
 
- Exercise Twelve
650
Twenty Two
Materialized Views
663
 
- Introduction
664
 
- Object Browser
666
 
- Drop
667
 
- Data
668
 
- Query
669
 
- Count Rows
670
 
- Details
671
 
- Grants
672
 
- Dependencies
673
 
- SQL
674
 
- Creating in Object Browser
675
 
- Syntax for Creating Materialized Views
677
 
- Refreshing Materialized Views
680
 
- Building Materialized Views
686
 
- Dropping Materialized Views
688
 
- Exercise Thirteen
689
Twenty Three
Advanced Date Datatypes
692
 
- Introduction
693
 
- Database Timezone
694
 
- Date Datatypes
696
 
- Timestamp
700
 
- Extract
705
 
- To_Timestamp
706
 
- Timestamp with Time Zone
708
 
- Time Zone and Extract
711
 
- TZ_Offset
712
 
- Time Zones and UTC
713
 
- To_Timestamp_TZ
715
 
- From_TZ
716
 
- Timestamp with Local Time Zone
717
 
- Intervals
718
 
- Assigning Year Intervals
721
 
- Assigning values to Day Intervals
723
 
- Using Intervals
725
 
- Intervals and Extract
731
 
- Exercise Fourteen
732
Twenty Four
Flashback
738
 
- Introduction
739
 
- Flashback Query
740
 
- Using the Dbms_Flashback package
744
 
- Using Time Flashbacks
745
 
- Disabling Flashbacks
749
 
- Using SCN Flashbacks
750
 
- Implicit Flashback using AS OF
752
 
- Flashback using Versions Between
754
 
- Using the Flashback_transaction_query View
759
Twenty Five
Application Express Utilities
762
 
- Introduction
763
 
- Data Workshop
765
 
- Data Load
766
 
- Loading CSV files
767
 
- Loading CSV into a new Table
769
 
- Loading CSV into existing Table
777
 
- Loading XLSX
784
 
- Loading XLSX into a new Table
785
 
- Loading XLSX into existing Table
793
 
- XML Data
800
 
- Loading XML into a new Table
802
 
- Loading XML into existing Table
808
 
- JSON Data
814
 
- Loading JSON into a new Table
815
 
- Loading JSON into existing Table
821
 
- Data Unload
827
 
- Unload to Text
828
 
- Unload to XML
835
 
- Sample Datasets
841
 
- Generate DDL
848
 
- Exercise Fifteen
855
 
- User Interface Defaults
864
 
- Schema Comparison
874
 
- Recycle Bin
878
 
- Object Reports
883
 
- General Comments
884
 
- Table Reports
929
 
- Security Reports
935
 
- PL/SQL Reports
940
 
- All Object Reports
943
 
- Exception Reports
950
 
- Exercise Sixteen
954

Sections of Apex 20 with Oracle 19c SQL

One
Introduction to SQL
Two
Using Application Express
Three
SQL Scripts
Four
Database Objects
Five
Object Browser with Tables
Six
SQL Commands
Seven
Simple Queries
Eight
Limiting Data Selected with Where Clauses
Nine
Oracle Datatypes
Ten
Aggregating Values
Eleven
Tree Walking Tables in SQL
Twelve
Query Builder
Thirteen
Joining Tables in an SQL Statement
Fourteen
Oracle Sub-Queries
Fifteen
Using Alternative Quoting Mechanism in SQL
Sixteen
Regular Expressions in Oracle SQL
Seventeen
Advanced SQL Query Techniques
Eighteen
Creating SQL Database Objects
Nineteen
Altering the structure of Oracle Tables/Views
Twenty
Populating Tables with Object Browser
Twenty One
Committing Records
Twenty Two
Materialized Views
Twenty Three
Advanced Date Datatypes
Twenty Four
Flashback
Twenty Five
Application Express Utilities

Synopsis for Apex 20 with Oracle 19c SQL training course

When you need to learn SQL to work with an Oracle Database, Seer Computing is the best place to come in the UK and Ireland, we've been giving SQL courses for over 20 years and have a variety of different front-ends available to interface with the Oracle Database.

Traditionally Developers would use the interface provided by Oracle in the 1980s which was SQL*Plus and we still have a training course in SQL using SQL*Plus, this is a command line interface, it's not changed much over the years and still gives the Developer the quickest route into the Database, is it User friendly?, well no not really, every command needs to be typed in or copied and pasted in and that includes finding out the structure of tables using the 'describe' command.

Over the years Oracle and other software manufacturers have come out with a number of front end GUI products to use SQL against the Oracle Database. Most Oracle Developers will have used the Toad product created by Quest software but for Users of the Database it will prove too expensive to roll out to occasional Users.

In the 1990s a lot of Companies used the Discoverer software to roll out an Oracle Database to Users, this involved limiting the access of the Database using an End User Layer, regardless of the knowledge a person may have in SQL they could not influence the values stored in the Database as it the data was read only, Seer Computing still offer Discoverer courses if your legacy system still uses it.

Around the beginning of the 21st century, Oracle brought out two more products other than SQL*Plus and Discoverer for running SQL against the Database; SQL Developer and Application Express.

SQL Developer is a free to download product from the Oracle website and we feel is comparable to the Toad product already mentioned. SQL Developer gives the User access to all schemas they have access to, listing all Objects and their structures. SQL Users would be most interested in Tables, Views and Columns within them for example. SQL Developer also gives Users a number of other facilities such as creating Objects, inserting values and of course downloading data into other formats for other products such as Microsoft Excel. Seer Computing offer a number of SQL Developer courses.

Let's turn our attention to Application Express. One of the main advantages of using Apex for Users of SQL is that the SQL*Plus software does not need to be installed on an individual's machine, instead it is reached via a URL of the server. Apex like all front ends to an Oracle Database can never by-pass the Database's security features, therefore a DBA can dictate what access the User has for a particular Schema, in addition Apex has an Administration of its own which can remove a lot of the front end options from specific Users.

This course is designed primarily for Delegates who have little or no experience of SQL or Apex, not only does it teach them the main areas of SQL but also how to use the commands and techniques within the Apex environment. At this point the Delegates can stop if all they wish to learn is how to use SQL in Apex, however we also provide a four day course in developing PLSQL within Application Express, as well as a course on the main feature of Application Express; Creating web applications.

We recommend anyone needing to learn how to create Application Express applications to attend both the SQL and PLSQL courses first so that they will have an iron clad understanding of how Databases work before they tackle the front ends to go onto the Database. Delegates already with SQL and PLSQL knowledge and training can go directly to learn creating Apps with Seer Computing.

This course as well as every other course that Seer Computing performs is available on-site at your premises, we come to you bringing our complete workshop, which consists of networked workstations, and at no point do we need access to your networks or Databases. We can perform on-site training for one or more delegates up to twelve delegates at a time and in fact the more on a course the more cost effective the charges are.

Contact our training advisers, they will help you decide on the correct training strategy, give options for all courses you are interested in, both dates and prices and ultimately support you through the whole process. If you need more than one quotation, ask away we can cater for any scenario you want to discuss.

Payment for courses and indeed any of our other services is via invoice, all our charges are subject to vat at the current UK rate of 20%, this can be settled by Bacs, cheque or credit card (4% surcharge).
Request for detailsClick for the contact form for the Oracle SQL 19c with Apex 20 training course
Public Courses
Oracle Essential SQL 21c
Days
2
Pages
340
Exercises
8
Sections
15
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 21c
Days
3
Pages
384
Exercises
13
Sections
25
Public
Yes
On-Site
Yes
Public Courses
Using SQL with Oracle SQL Developer 19.2
Days
4
Pages
884
Exercises
18
Sections
37
Public
Yes
On-Site
Yes
Public Courses
Essential JavaScript
Days
4
Pages
660
Exercises
16
Sections
31
Public
Yes
On-Site
Yes
Public Courses
Essential CSS 3
Days
4
Pages
800
Exercises
15
Sections
30
Public
Yes
On-Site
Yes
Public Courses
Essential SQLite 3
Days
3
Pages
550
Exercises
12
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential MySQL 8
Days
4
Pages
533
Exercises
16
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential Python 3
Days
5
Pages
955
Exercises
17
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Oracle Application Express 20
Days
0
Pages
0
Exercises
0
Sections
0
Public
Yes
On-Site
Yes
Public Courses
PHP 8 and MySQL 8
Days
5
Pages
870
Exercises
23
Sections
38
Public
Yes
On-Site
Yes
Public Courses
Java 17 Programming with Eclipse 2021-12
Days
5
Pages
788
Exercises
23
Sections
44
Public
Yes
On-Site
Yes
Public Courses
Java 17 Swing with Eclipse 2021-12
Days
5
Pages
756
Exercises
19
Sections
35
Public
Yes
On-Site
Yes
Click on the course name to see full details, left and right to view other courses