Oracle SQL for Developers Training Course

Introduction to the Oracle Database

• Database architecture
• The relational database model
• Users, schemas, and session management
• Essential tools for database interaction

Introduction to the SELECT Statement

• Data filtering and selection via the WHERE clause
• Sorting query results
• Data types, operators, and NULL value handling
• Application of built-in scalar functions
• Performing date operations
• Navigating national and regional settings in SQL

Analyzing Aggregated Data

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

Retrieving Data from Multiple Tables

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

Subqueries

• Basic subqueries
• Correlated subqueries
• Using EXISTS and NOT EXISTS operators
• Exploring other subquery variations

Hierarchical Queries and Data Sampling

• Building hierarchical queries with CONNECT BY PRIOR and START WITH clauses
• Using the SYS_CONNECT_BY_PATH function
• Sampling data via the SAMPLE clause

Data Manipulation (DML)

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

System Dictionary

• Interpreting and utilizing the Oracle data dictionary

Concurrent User Work

• Managing transactions
• Handling locks
• Implementing FLASHBACK technology

Users and Permissions

• Creating and altering users and schemas
• Administering permissions and roles

Managing Data Storage - Logical Layer

• Tables, temporary tables, and index-organized tables
• Defining constraints
• Creating and managing indexes
• Working with views, sequences, synonyms, and materialized views
• Developing stored PL/SQL units

Modeling and Restoring Data Models Using Oracle SQL Modeler

• Leveraging Oracle SQL Modeler for data modeling and restoration processes

Moving Data

• Logical data copying - Data Pump import and export
• Data loading - SQL*Loader
• External tables
• Establishing database links

Automating Tasks

• Leveraging DBMS_JOB and DBMS_SCHEDULER for task automation

Summary and Next Steps