Oracle SQL for Administrators

• Basic understanding of databases and familiarity with general computing concepts

Audience

• Database administrators
• Developers

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 beginner-level database administrators and developers who wish to learn SQL to efficiently manage, query, and manipulate data in Oracle databases for administrative and operational tasks.

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

• Understand Oracle database architecture, including schemas, users, and sessions.
• Write and optimize SQL queries for data retrieval, filtering, and manipulation.
• Use joins, subqueries, and set operators to work with multiple tables and complex queries.
• Perform data aggregation, grouping, and analysis using SQL functions.
• Manage schema objects like views, sequences, and indexes to enhance database operations.

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 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

Regular Expressions in SQL

• Implementing regular expressions for pattern matching

Analyzing Aggregated Data

• Using grouping functions
• Applying the 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)

Managing Concurrent User Access

• Understanding transactions
• Implementing locks
• Utilizing FLASHBACK technology

Overview of Schema Objects

• Views
• Sequences
• Private and public synonyms
• Indexes

Summary and Next Steps