Capability Development / Database & DataScience

Oracle SQL

This course provides a comprehensive, hands-on introduction to Oracle SQL and relational database concepts. Participants learn to retrieve, filter, sort, and summarize data, then progress to joins, subqueries, and set operators for more advanced analysis. The curriculum also covers data manipulation and transaction control, schema object management including views, sequences, synonyms, and indexes, and user access control. Learners finish with practical exposure to advanced querying techniques such as flashback queries, multitable inserts, and time zone handling, building a strong SQL foundation for development, reporting, and further database study.

Enquire Now Back to Catalogue

Oracle SQLCourse Code

Instructor-ledCourse Duration

FoundationLevel

Guided LearningSupport

← Back to Capability Development

Program Overview

Audience Profile

Aspiring database developers and report writers; IT professionals new to Oracle SQL; Business analysts who query relational data; and Developers preparing for Oracle SQL certification.

Course Syllabus

Module 1: Introduction

Topics

Relational database concepts and the entity-relationship model
Relational and object-relational database management systems
How SQL works and the SQL statements used in the course
Development environments: Oracle Live SQL, SQL Developer, and SQL*Plus

Module 2: Retrieving Data Using the SQL SELECT Statement

Topics

Basic SELECT statement and selecting specific or all columns
Arithmetic expressions and NULL values
Column aliases and the concatenation operator
Literal character strings and duplicate row handling

Module 3: Restricting and Sorting Data

Topics

The WHERE clause and comparison operators
BETWEEN, IN, LIKE, and NULL conditions
Logical operators (AND/OR/NOT) and rules of precedence
ORDER BY, the row limiting clause, and substitution variables

Module 4: Using Single-Row Functions to Customize Output

Topics

Character functions: case-conversion and manipulation
Numeric functions: ROUND, TRUNC, and MOD
Date arithmetic and date-manipulation functions
Nesting functions

Module 5: Using Conversion Functions and Conditional Expressions

Topics

Implicit and explicit data type conversion
TO_CHAR, TO_NUMBER, TO_DATE, and CAST functions
NVL, NVL2, NULLIF, and COALESCE
CASE expressions and the DECODE function

Module 6: Reporting Aggregated Data Using the Group Functions

Topics

Types of group functions: AVG, SUM, MIN, MAX, COUNT
Group functions and NULL values
Creating groups of data with GROUP BY
Using and nesting the HAVING clause

Module 7: Using Joins

Topics

Types of joins and joining tables using SQL
Natural joins and joins with the USING and ON clauses
Self joins, non-equi joins, and three-way joins
Outer joins: LEFT, RIGHT, FULL, and Cartesian joins

Module 8: Using Subqueries

Topics

Syntax, rules, and guidelines for subqueries
Single-row and multiple-row subqueries
Using group functions within a subquery
Multiple-column subqueries and the ANY/ALL operators

Module 9: Using Set Operators

Topics

Set operator rules and behavior
UNION and UNION ALL
Matching SELECT statements and ORDER BY with set operators

Module 10: Managing Tables Using DML

Topics

INSERT, UPDATE, and DELETE statements
Inserting rows with NULL and special values
Database transactions: COMMIT, ROLLBACK, and savepoints
Read consistency and the FOR UPDATE clause

Module 11: Introduction to Data Definition Language

Topics

Naming rules for tables and columns, and data types
Creating tables and defining constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK)
ALTER TABLE: adding, modifying, and dropping columns
Read-only tables and dropping tables

Module 12: Introduction to Data Dictionary Views

Topics

Purpose and structure of the data dictionary
Using USER_OBJECTS and ALL_OBJECTS
Querying USER_CONS_COLUMNS
Adding comments to a table

Module 13: Creating Sequences, Synonyms, and Indexes

Topics

Creating and modifying sequences; NEXTVAL and CURRVAL
Creating and removing synonyms
Creating indexes, including function-based indexes
Querying index information via USER_INDEXES and USER_IND_COLUMNS

Module 14: Views

Topics

What a view is and its advantages
Simple versus complex views
Creating and querying views, and the WITH CHECK OPTION
Rules for performing DML operations on a view

Module 15: Managing Schema Objects

Topics

Adding, dropping, and disabling/enabling constraints
Cascading constraints and constraint states
Renaming columns and constraints, and deferring constraints
Temporary and external tables

Module 16: Retrieving Data by Using Subqueries

Topics

Multiple-column subqueries and pairwise/non-pairwise comparisons
Scalar and correlated subqueries
Using the EXISTS clause and the WITH clause

Module 17: Manipulating Data by Using Subqueries

Topics

Using a subquery to insert data
The WITH CHECK OPTION in DML operations
Correlated UPDATE and DELETE statements

Module 18: Controlling User Access

Topics

System and object privileges
Creating users and granting system privileges
Creating and granting roles
Passing on and revoking privileges

Module 19: Manipulating Data Using Advanced Queries

Topics

Multitable INSERT statements
The MERGE statement
Flashback table, flashback query, and flashback version query

Module 20: Managing Data in Different Time Zones

Topics

The TIME_ZONE session parameter and CURRENT_DATE/CURRENT_TIMESTAMP functions
DBTIMEZONE and SESSIONTIMEZONE
TIMESTAMP and INTERVAL data types
EXTRACT, TZ_OFFSET, FROM_TZ, and related conversion functions