New Oracle SQL Fundamentals Course

SQL is one of the most important and widely used query languages, used every day by developers, database administrators, business analysts, and reporting specialists to retrieve, analyze, and manage data. In the Oracle Database environment, a wide range of SQL functions and features are available that go beyond the ANSI SQL standard, making knowledge of these capabilities essential for efficient work. The aim of this intensive training is to provide participants with a practical understanding of the fundamentals of Oracle SQL. During the course, participants will become familiar with the Oracle SQL Developer environment, learn how to create simple and advanced queries, filter, sort, and group data, join multiple tables, and perform the basic operations required to modify data.

The training is highly practice-oriented: each topic is followed by hands-on lab exercises and comprehensive practical examples that simulate real-world tasks commonly encountered in corporate database environments.

This training is recommended for professionals who work with Oracle databases or plan to create Oracle SQL queries in the future. It is particularly suitable for data analysts, database developers, business analysts, BI and reporting specialists, as well as testers who create database queries as part of their work.

Upon successful completion of the training, participants will be able to:

• create Oracle SQL queries independently;
• filter, sort, and format data;
• use Oracle built-in character, numeric, date, and NULL-handling functions;
• create aggregations and groupings required for basic business reports;
• join multiple database tables using different JOIN operations;
• use simple subqueries;
• insert, update, and delete records;
• write and troubleshoot the most common Oracle SQL queries independently.

Oracle Database and SQL Developer Overview

• Introduction to Oracle Database
• Oracle SQL features and characteristics
• Overview of Oracle SQL Developer
• Connecting to a database
• Using the Worksheet
• Executing queries
• Displaying and exporting query results
• Managing SQL scripts
• Overview and interpretation of the sample training database
• Hands-on exercise: connecting to the database, using SQL Developer, and executing the first queries.

Creating SELECT Queries

• Structure of the SELECT statement
• Selecting columns
• Retrieving all columns
• Using DISTINCT
• Using expressions in the SELECT list
• Creating calculated columns
• Hands-on exercise: creating simple queries for different business data.

Filtering Data

• Using the WHERE clause
• Comparison operators
• Using BETWEEN
• Using IN
• Using LIKE
• Using IS NULL
• Using logical operators (AND, OR, NOT)
• Operator precedence
• Hands-on exercise: creating and applying complex filtering conditions.

Aliases and Formatting Results

• Column aliases
• Table aliases
• Calculated columns
• Concatenating strings

Sorting Results

• Using ORDER BY
• Sorting by multiple columns
• Using ASC and DESC
• Sorting NULL values

Using Oracle Single-Row Functions

• Using character functions (UPPER, LOWER, LENGTH, SUBSTR, TRIM, CONCAT, etc.)
• Using numeric functions (ROUND, TRUNC, CEIL, FLOOR, MOD)
• Using date functions (SYSDATE, CURRENT_DATE, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY)
• Handling NULL values (NVL, NVL2, COALESCE, NULLIF)
• Using conversion functions (TO_CHAR, TO_DATE, TO_NUMBER)
• Hands-on exercise: creating advanced reports using Oracle functions.

Using Aggregate Functions

• COUNT
• SUM
• AVG, MIN, MAX

Using GROUP BY and HAVING

• Grouping data
• GROUP BY with multiple columns
• Using HAVING
• Filtering aggregated results

Joining Tables (JOIN)

• Overview of table relationships
• INNER JOIN
• LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
• Creating correct JOIN conditions
• Joining multiple tables
• Hands-on exercise: creating complex queries using multiple joined tables.

Introduction to Subqueries

• The role of subqueries
• Single-row subqueries
• Multiple-row subqueries
• Using IN
• Using EXISTS
• Hands-on exercise: creating hierarchical and conditional queries using subqueries.

Basic computer and operating system skills (preferably Windows) are required. Participants should have a basic understanding of relational databases, including the concepts of tables, records, and fields, primary and foreign keys, table relationships, and the fundamentals of the relational data model. Previous experience working with databases, for example in Microsoft Excel or Microsoft Access, is recommended and considered an advantage.