Workshop

Oracle SQL Workshop for advanced users (Advanced SQL) («OSQLAD»)

Oracle SQL is the database query language for Oracle databases. Although SQL has been standardized for a long time, there are some special features in the Oracle environment, both functionally and syntactically.
Duration 2 days
Price 2'150.–

Course facts

Key Learnings
  • Knowing the basic SQL concepts: the syntax of SELECT statements (e.g. `WHERE`, `ORDER BY`, `DUAL`, `ROWNUM`), SQL functions (numeric, string, date/time, `DECODE`, `CASE`), `GROUP BY`, `HAVING` and set operations (`UNION`, `INTERSECT`, `MINUS`)
  • Data Manipulation Language (DML) operations (INSERT, UPDATE, DELETE) and transaction control (COMMIT, ROLLBACK, SAVEPOINT)
  • Understanding the data dictionary, the constraints (Primary, Unique, Foreign Key) and the ROWIDs/indexes
  • Knowledge of different types of joins (INNER, CROSS, OUTER, NATURAL, self-joins) and subqueries (synchronous, asynchronous, inlay views, EXISTS, ANY, ALL, WITH)
  • Advanced SQL topics such as detailed data types, `GROUP BY` extensions (ROLLUP, CUBE, GROUPING) and analytical functions (RANK, NTILE, LISTAGG, LEAD, LAG, PARTITION BY)
  • Further advanced areas: Top-N queries, Data Definition Language (DDL) for tables, sequences and views, hierarchical queries, advanced DML (INSERT ALL/FIRST/APPEND, MERGE) and regular expressionsAnswering Oracle database administrators, application administrators, Oracle developers or architects, generally anyone who comes into contact with SQL.
Content

In this workshop, we will teach you advanced techniques and concepts of Oracle SQL. The workshop offers sufficient space for exercises in which the newly acquired knowledge can be implemented or existing knowledge can be deepened. The topics range from the syntax of simple statements to the various joins and subqueries to more specialized topics such as analytical functions, hierarchical queries and regular expressions in SQL.

1 Data Types in Detail

2 GROUP BY – Extensions ROLLUP, CUBE and GROUPING

3 Analytical Functions

  • RANK, DENSE_RANK
  • PERCENT_RANK, NTILE
  • LISTAGG, LEAD and LAG
  • FIRST and LAST VALUE
  • PARTITION BY

4 Top-N and the SQL ROW LIMIT Clause

5 Data Definition Language (DDL): Tables, Sequences, Views

  • Column DEFAULT
  • INVISIBLE Columns
  • Virtual Columns
  • Deferred Segment Creation
  • ALTER, TRUNCATE, DROP, RENAME
  • CREATE OR REPLACE

6 Hierarchical Queries – START WITH and CONNECT BY, LEVEL and PRIOR

7 Advanced DML

  • INSERT ALL, INSERT FIRST
  • INSERT APPEND
  • MERGE

8 Regular Expressions

  • REGEXP_LIKE
  • REGEXP_SUBSTR
  • REGEXP_REPLACE
  • REGEXP_COUNT

Component of the following courses

Target audience
Prospective Oracle database administrators, application administrators, Oracle developers or architects, generally anyone who comes into contact with SQL.
Requirements
Basic knowledge of SQL (regardless of the database)
Additional information

The course does not require a specific Oracle version. For newer features (from 12.2), the version from which they are available is indicated separately.

Download

Questions

Any questions?
First name
Last name
Company optional
Email
Phone
I would like to book this course as a company course
First name
Last name
Company
Email
Phone
Number of participants
Desired course location
Start date (DD.MM.YYYY)
End date (DD.MM.YYYY)
We currently do not have any dates scheduled.

We currently do not have any dates scheduled.

But we can arrange one for you. We will be happy to advise you individually on your course planning.

Contact us
We currently do not have any dates scheduled.