Oracle SQL for Developers

• General knowledge of information technology

Audience

• Developers
• Database administrators

Oracle SQL (Structured Query Language) is a standard programming language used for interacting with Oracle databases. It is designed to manage, query, and manipulate data stored in relational database systems.

This instructor-led, live training (online or onsite) is aimed at intermediate-level developers and database administrators who wish to use Oracle SQL to design, manage, and optimize database applications and automate tasks efficiently.

By the end of this training, participants will be able to:

• Understand Oracle database architecture and manage users, schemas, and permissions.
• Write optimized SQL queries for data retrieval, manipulation, and analysis.
• Use advanced SQL techniques, including joins, subqueries, and hierarchical queries.
• Design and manage database objects like tables, indexes, views, and sequences.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

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