Course · 3 days

SQL Advanced Techniques

For people who already write SQL and have started running into its edges. This course goes past querying into building: custom functions and tables on the database, insert, update and delete operations, PL/SQL, tuning, and explain plans. Guided exercises run throughout, in both SQL Developer and Argos.

Ask about scheduling this course

All training options

At a glance

Length

3 days

Maximum class size

SQL knowledge required

Strong novice, with a good understanding of basic SELECT FROM WHERE and joins

Argos prerequisite

Completed DataBlock Designer training or similar familiarity, and able to create DataBlocks with simple SQL independently

Database and software

Argos Client, SQL Developer, and Oracle Banner with a test database recommended. Attendees need an Oracle user with full permissions.

Recommended for

People with a good grounding in basic SQL who want to optimize it and use Oracle functionality to create custom functions and tables

Note: This course does not cover SELECT, FROM, or WHERE. If that is where your team is, start with SQL for Argos.

Overview

Where querying the database becomes building on it

Every reporting team hits the same wall eventually. The answer to the request is not a cleverer query. It is a function, or a view, or a table that should have existed three years ago.

That is where this course starts. It also covers what happens afterwards, once the thing is in production rather than in a workshop. Tuning. Explain plans. Why the object you wrote in a hurry is now the one people complain takes ninety seconds.

Curriculum

What the course covers

Foundations revisited

Database concepts
Alias and Data Dictionary
Field security in Visual Designer

Functions and conditional logic

Calling an Oracle function in the SELECT and WHERE clause: upper, to_date, to_char, string, sysdate, between
Calling a Banner function in the SELECT and WHERE clause
Using the Object Source DataBlock to find a function and use it correctly
Identifying when to use a case statement
Case and decode, including use in SELECT, WHERE, and ORDER BY, and the differences between them
Case and decode order in a drop-down box, and required expressions in the group band

Aggregation and shaping

Using a column number in the ORDER BY clause
DISTINCT, and when to use it
MAX, MIN, AVERAGE, COUNT, SUM
GROUP BY
Creating SQL for a chart
The PIVOT function
Unions: union, union all, intersect, and minus, and when each applies
HAVING
Datasets in banded and extract reports

Joins and subqueries

Joins: inner, outer right, outer left, defined and created in the Visual Designer, added to the Data Dictionary, and written freehand
Subqueries: scalar, noncorrelated, correlated, and in-line view
Subqueries with in, not in, exist, not exist
The WITH clause

Writing to the database

Insert, update, and delete
How to write them in Argos using SQL variables
Using them as a Banner letter generation replacement option

PL/SQL and performance

SQL tuning and SQL best practices
Changing table order in Argos
Explain plan
What PL/SQL is
Creating functions on the database
Calling PL/SQL functions within Argos
Creating tables and views
Creating cursors in anonymous blocks in Argos SQL variables
Concepts of Oracle collections

Audience

Who this is for

Experienced DataBlock designers

You write SQL daily and have started hitting problems that a SELECT statement cannot solve.

Anyone maintaining objects other people depend on

Once a dozen offices run your DataBlock every morning, somebody notices how long it takes.

Teams replacing Banner letter generation

The insert, update, and delete material covers this directly.

SQL for Argos

The prerequisite course, if SELECT, FROM, and WHERE are not yet second nature.

See the course

Argos 6-Day Advanced Training Package

Advanced modules, OLAP, charts, and the API, across two 3-day sessions.

See the course

Professional Services

When the answer is that somebody should build it rather than train for it.

See Professional Services

Next step

Tell us where your team currently stops

The query they cannot write, or the performance problem they cannot solve. It is the fastest way to tell whether this course or the foundation one is the right booking.