Advanced SQL

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.