Oracle SQL for Developers Training Course

Introduction to the Oracle Database

• Database architecture
• The relational database model
• Users, schemas, and sessions
• Tools for database interaction

Getting Started with the SELECT Statement

• Filtering and selection via the WHERE clause
• Sorting query results
• Data types, operators, and NULL value handling
• Leveraging built-in scalar functions
• Date manipulation
• Comprehending national and regional settings in SQL

Analyzing Aggregated Data

• Grouping functions
• Use of the DISTINCT clause
• GROUP BY and HAVING clauses

Fetching Data from Multiple Tables

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

Working with Subqueries

• Basic subqueries
• Correlated subqueries
• EXISTS and NOT EXISTS operators
• Additional subquery types

Hierarchical Queries and Data Sampling

• Building hierarchical queries using CONNECT BY PRIOR and START WITH clauses
• Employing the SYS_CONNECT_BY_PATH function
• Sampling data using the SAMPLE clause

Data Manipulation (DML)

• INSERT, UPDATE, and DELETE statements
• Handling large datasets (INSERT FIRST, INSERT ALL, MERGE)

The System Dictionary

• Understanding and leveraging the Oracle data dictionary

Concurrent User Operations

• Transactions
• Locking mechanisms
• FLASHBACK technology

User Management and Permissions

• Creating and modifying users and schemas
• Managing permissions and roles

Managing Data Storage - Logical Layer

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

Modeling and Restoring Data Models with Oracle SQL Modeler

• Using Oracle SQL Modeler for data modeling and restoration

Data Movement

• Logical data copying via Data Pump import and export
• Data loading with SQL*Loader
• External tables
• Database links

Task Automation

• Leveraging DBMS_JOB and DBMS_SCHEDULER for task automation

Summary and Next Steps