Oracle SQL for analysts - advanced

Knowledge of issues related to relational databases, free use of the SELECT statement in terms of projection, selection, joins and subqueries.
 

Level
Advanced
Form
Lecture, Presentation, Intensive workshop
Purpose of the training
The training is addressed to data analysts and users who need to efficiently and flexibly extract data from the database using SQL language commands directly. It complements the ORA-SA1 training with advanced SQL language mechanisms.
Benefits of completing the training
Learn advanced data analysis techniques using SQL language for more flexible and efficient reporting.

Regular expressions in SQL

• Searching through data
• Data transformations

Hierarchical queries

• Building a tree (CONNECT BY PRIOR clause and START WITH clause)
• SYS_CONNECT_BY_PATH function

Advanced grouping

• Operations on data strings (WITHIN GROUP)
• Operations on the first/last row of a string (KEEP)
• Aggregations in bulk (ROLLUP, CUBE, GROUPING SET)
• Pivot tables (PIVOT, UNPIVOT)

Analytical functions

• Generation of partial summaries
• Ranking functions
• Dynamic windows

Data manipulation (DML)

• Inserting, modifying, deleting data (INSERT, UPDATE, DELETE)
• Truncating tables (TRUNCATE)

Concurrent user operation

• Transactions
• Consistency of data over time
• Locks
• FLASHBACK

Appendix A - Moving and loading data

• Database links
• Exporting query results to csv file
• Importing data from a csv file, SqlLoader
• External tables

Appendix B - Miscellaneous database objects

• System dictionary
• Views
• Sequences
• Indexes
• Synonyms
• PL/SQL syntax programs

Suggested pre-training
ORA_SA1 or ORA_S1
Suggested follow-up training
ORA_SA3