Oracle SQL for Developers Training Course

Introduction to Oracle Database

• Database architecture.
• Relational database model.
• Users, schemas, and sessions.
• Tools for database interaction.

Introduction to the SELECT Statement

• Filtering and selection using the WHERE clause.
• Sorting query results.
• Data types, operators, and handling NULL values.
• Utilizing built-in scalar functions.
• Date operations.
• Understanding national and regional settings in SQL.

Analyzing Aggregated Data

• Grouping functions.
• DISTINCT clause.
• GROUP BY and HAVING clauses.

Retrieving Data from Multiple Tables

• Inner and outer joins (INNER JOIN, OUTER JOIN).
• ANSI SQL syntax and other join methods (SELF JOIN, NATURAL JOIN).
• Set operators (UNION, UNION ALL, INTERSECT, MINUS).

Subqueries

• Simple subqueries.
• Correlated subqueries.
• EXISTS and NOT EXISTS operators.
• Other types of subqueries.

Hierarchical Queries and Sampling

• Constructing hierarchical queries using CONNECT BY PRIOR and START WITH clauses.
• Utilizing the SYS_CONNECT_BY_PATH function.
• Sampling data with the SAMPLE clause.

Data Manipulation (DML)

• INSERT, UPDATE, DELETE statements.
• Operations on large datasets (INSERT FIRST, INSERT ALL, MERGE).

System Dictionary

• Understanding and utilizing the Oracle data dictionary.

Concurrent User Work

• Transactions.
• Locks.
• FLASHBACK technology.

Users and Permissions

• Creating and modifying users and schemas.
• Permissions and roles management.

Managing Data Storage - Logical Layer

• Tables, temporary tables, and index-organized tables.
• Constraints.
• Indexes.
• Views, sequences, synonyms, materialized views.
• Stored PL/SQL units.

Modeling and Restoring Data Models Using Oracle SQL Modeler

• Utilizing Oracle SQL Modeler for data modeling and restoration.

Moving Data

• Logical data copy - Data Pump import and export.
• Loading data - SQL*Loader.
• External tables.
• Database links.

Automating Tasks

• Using DBMS_JOB and DBMS_SCHEDULER for task automation.

Summary and Next Steps.