Download our latest
Android and iPhone apps ...
At a glance ...
Pages
653
Exercises
14
Sections
18
Public
On-Site
Level
1
Days
4

Course Name: Apex SQL

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
18
14
653
None
Apex PLSQL
Contact us for the latest pricing

Synopsis for course

SQL is the foundation of all Oracle database commands. Coupled with Application Express its power in the Database is unlimited.

This course assumes Delegates have little or no experience of Oracle SQL or Application Express.

For Users of Application Express it is ideal to learn how to interrogate the Database, for Developers its the first step to learning the whole of the facilities of APEX

Course Contents

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