SQL (Oracle)

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)