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 selecting data using the WHERE clause
• Sorting query results
• Data types, operators, and NULL value handling
• Using 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 alternative join methods (SELF JOIN, NATURAL JOIN)
• Set operators (UNION, UNION ALL, INTERSECT, MINUS)

Subqueries

• Simple subqueries
• Correlated subqueries
• EXISTS and NOT EXISTS operators
• Other subquery types

Hierarchical Queries and 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)

• INSERT, UPDATE, and DELETE statements
• Handling 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
• 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

Moving Data

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

Automating Tasks

• Task automation using DBMS_JOB and DBMS_SCHEDULER

Summary and Next Steps