Workshop
Digicomp Code OSQLEI
Oracle SQL for beginners (and advanced users) («OSQLEI»)
Course facts
- 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.
In this workshop, we will teach you the basics 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.
Oracle SQL Workshop Basics
1 SQL – History
2 NULL
3 Syntax of the SELECT statement
- SELECT FROM
- SELECT DISTINCT FROM
- WHERE and comparison operators
- Arithmetic and string operators
- AND, OR and NOT
- ORDER BY
- The table DUAL
- ROWNUM
4 SQL functions
- Numeric, string, converter, date and time
- Comparison functions, LOBs and collections, hierarchical functions
- Data Mining, XML and JSON
- Encoding & Decoding
- Environment - USER
- DECODE and CASE
5 GROUP BY, group functions and HAVING
6 Set operations - UNION, UNION ALL, INTERSECT and MINUS
7 DML (INSERT, UPDATE and DELETE)
8 Transactions - COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION and read consistency
9 The Data Dictionary
10 Constraints - Primary, Unique and Foreign Key Constraints
11 ROWIDs and Indexes - Index Types, Administration and Reorganization
12 Joins
- Old Oracle Join Syntax
- INNER, CROSS, OUTER and NATURAL Joins
- Self-Joins
13 Subqueries
- Synchronous and Asynchronous Queries
- Inlay Views
- EXISTS and NOT EXISTS, ANY and ALL
- WITH
Oracle SQL Workshop for Advanced 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
Consists of the following modules
The workshop is designed for 5 days and consists of two parts:
- Oracle SQL Workshop Basics (3 days)
- Oracle SQL Workshop for Advanced Users (2 days)
Prospective Oracle database administrators, application administrators, Oracle developers or architects, generally anyone who comes into contact with SQL.
- This course is suitable for newcomers and does not require any previous knowledge.
- Programming skills are not required, but are helpful.
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.