I.                 Objectives

This course develops advanced SQL methods with both database functions (views and indexes) and specific syntax (analytics, advanced grouping, hierarchical tables, etc.).

The course uses the Oracle Database relational database.

At the end of the course, the student will have sufficient knowledge to:

Use views for data retrieval and DML restrictions. Know and use the main indexes provided by the Oracle database.

Use advanced SQL queries for efficient data retrieval in specific cases such as: data analytics, complex grouping, hierarchical tables, advanced searches, pivoting and unpivoting of data sets and multiple insertion). Elements that will be useful for different areas (data integration processes, data analysis, query performance improvement).

II.Requirements

SQL knowledge (preferably in an Oracle Database environment).

III. Duration

20 hours.

IV. Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations.

The student will use the tools related to the presentation. Resolution of doubts about the concepts presented.

V.              Content

Views. Creating views. Updatable views. Online views. Using Check Option.

Deleting views.

Indexes. Utility of indexes. Types of indexes. B-TREE indexes. BITMAP indexes. Creation and maintenance of indexes.

Indexes based on functions.

Advanced SQL. Analytical SQL.RANK.DENSE_RANK.CUME_DIST.PERCENT_RANK.NTILE.RATIO_TO_REPORT.LAG/LEAD.FIRST_VALUE/LAST_VALUE.LIST_AGG.Advanced grouping.ROLLUP.CUBE.GROUPING SETS.Hierarchical data retrieval.CONNECT BY.Regular expressions.Metacharacters.Functions: REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXTP_COUNT.Pivoting and unpivoting of data (PIVOT/UNPIVOT).

Multiple data insertion.

CONTENTS:

Relational database concepts

MySQL basics
2a. Accessing the server
2b. Using MySQL Workbench

SQL language: Using SELECT
3a. Basic statements
3b. Using aliases
3c. Arithmetic expressions
3d. NULL concept

SQL language: Using single-row functions
4a. Single-row functions for characters
4b. Single-row functions for numeric values
4c. Single-row functions for dates
4d. Function nesting
4e. Type conversion functions
4f. Null handling functions
4g. Conditional functions

SQL language: Grouping data
5a. Using GROUP BY
5b. Using HAVING

SQL language: Table joins
6a. Using ANSI syntax
6b. Using “Oracle” syntax
6c. INNER / OUTER joins
6d. Using self-joins (SELF JOIN)
6e. EQUIJOIN / NONEQUIJOIN joins
6f. Joining using Cartesian product

SQL language: Subqueries
7a. Using subqueries
7b. Converting subqueries to joins

DDL language: Creating objects
8a. Creating tables
8b. Creating restrictions
8b1. Primary keys
8b2. Foreign keys
8b3. NOT NULL, CHECK and UNIQUE restrictions
8c. Creating indexes

DML language: Managing rows
9a. Inserting rows (INSERT)
9b. Updating rows (UPDATE)
9c. Deleting rows (DELETE)

Introduction
1.1 Identifying SQL to optimize
1.2 Tuning strategies

Optimizer operation
2.1 Analyzing and processing a query
2.2 Query transformer
2.3 Transformer operations
2.4 Selectivity and cardinality
2.5 Calculating the cost of a query
2.6 Adaptive query optimization
2.7 Controlling optimizer behavior

Execution plans
3.1 Displaying execution plans
3.2 Reviewing execution plans
3.3 Using EXPLAIN PLAN
3.4 EXPLAIN PLAN options
3.5 Using AUTOTRACE
3.6 Performance views
3.7 Monitoring SQL

Interpreting execution plans
4.1 How to read an execution plan
4.2 Table operations
4.3 Index operations
4.4 Join operations
4.5 Other operations

Result caching
5.1 Introduction to RESULT_CACHE
5.2 Enabling RESULT_CACHE RESULT_CACHE
5.3 Using RESULT_CACHE

Optimizer Statistics
6.1 Importance of Statistics
6.2 Calculating and Configuring Statistics Calculation
6.3 Histograms and Histogram Calculation

Using BIND Variables
7.1 Cursor Sharing Concept
7.2 Using BIND Variables for Cursor Sharing

Application Trace
8.1 Using TKPROF
8.2 Using TRCSESS

Best Practices with SQL
9.1 Examples of Good and Bad Practices
9.2 Recommended SQL
9.3 Automatic Transformations

Objective:
The course is aimed at creating queries in SQL language
in an Oracle environment (19c).
The student will learn to:

Create queries using clauses to project expressions and select rows

Create calculations using expressions and functions,
as well as create data summaries (groups and group functions).

Join tables using ANSI and Oracle syntax and the
different joining methods.

Use set operators.

Use analytical functions that allow creating advanced calculations
with group information at the row level.

Methodology:
The course is developed through the theoretical exposition of the content
accompanied by practical demonstrations.
The student will use the tools related to the exposition
in practices related to each topic of the content.
Resolution of doubts about the concepts presented.

Duration:
24 hours

Content:

Introduction
1.1 Introduction to Oracle Database
1.2 Introduction to the relational model
1.3 Introduction to the SQL language and tools for use

Data retrieval using SELECT
2.1 Data projection
2.2 Using aliases in queries
2.3 Arithmetic operations
2.4 Definition of NULL
2.5 String concatenation
2.6 Text, number and date literals
2.7 Using DISTINCT
2.8 Using DESCRIBE

Data filtering
3.1 Using the WHERE clause
3.2 Comparison operators
3.3 Searching data with LIKE
3.4 Conditions for handling null values
3.5 Evaluation operators: AND, OR and NOT
3.6 Using IN and BETWEEN

Data sorting
4.1 Sorting methods
4.2 Sorting null data

Clause 5.1 Limiting rows
5.2 Skipping rows
5.3 Outputting “tied” rows

Single row functions
6.1 Using functions
6.2 Numeric functions
6.3 Text functions
6.4 Date handling functions
6.5 Data conversion functions
6.6 Null handling functions
6.7 Complex evaluation functions (CASE)

Group functions and generating grouped data
7.1 Group functions (SUM, MAX, MIN…)
7.2 Generating grouped data
7.3 Filtering grouped data

Table joins
8.1 Join concepts in the relational model
8.2 ANSI and Oracle syntax for joins:
8.2.1 Inner joins
8.2.2 Outer joins, orphan management
8.2.3 Non-equijoins
8.2.4 Self-join and recovery hierarchical
8.2.5 Cartesian Product

Subqueries
9.1 Using Subqueries
9.2 Matched and Unmatched Subqueries
9.3 Correlated Subqueries
9.3.1 Building Correlated Queries
9.3.2 Effective Data Correlation Methods
9.4 Using IN and EXISTS in Subqueries

Set Operators
10.1 Set Operations Concepts
10.2 Using Set Operators
10.2.1 UNION and UNION ALL
10.2.2 INTERSECT
10.2.3 MINUS

Using WITH
11.1 Advantages of Using WITH
11.2 Using WITH
11.3 Recursive WITH

Analytical SQL
12.1 Analytical Function Concepts
12.2 Calculation Window
12.3 Analytical Functions:
12.3.1 RANK and DENSE_RANK
12.3.2 CUME_DIST and PERCENT_RANK
12.3.3 NTILE and RATIO_TO_REPORT
12.3.4 LAG and LEAD
12.3.5 FIRST and LAST
12.3.6 LIST_AGG (within group)