Oracle SQL: From Beginning to Advance Level (2025)

Introduction of Oracle Database

• What is a Database

• Introduction Oracle Database

• History of Oracle Database

Installation of Oracle Database

• Downloading and Installation of and 21C Oracle Database

• Downloading and setup of SQL Developer-23

• Logging on to the Oracle Database using SQL*Plus and introduction of SQL Developer (tool to develop of SQL statements)

• Setup the Test / work environment

Introduction of SQL and Creating Tables in Oracle Database

• Introduction to SQL

• Types the types of SQL statements – DQL, DML, DDL, DCL and TCL

• Data types in Oracle database

• Retrieving data using SELECT statement i.e. displaying data from a table for all Columns and Specific Columns

• Introduction of dummy table

• Types of Operators in Oracle database – Arithmetic, Relational, Logical, Special Operators

DDL commands

• Introduction to DDL commands in Oracle database

• Creating Tables in Oracle Database using CREATE statement

• Displaying the table structure (DESCRIBE) in command SQL*Plus and in SQL Developer

• Altering / modifying table i.e. adding a new column in the table

• Modifying the column data type in the table

• Renaming and deleting the column of the table

• Renaming the table

• Dropping the table using DROP command

• Recyclebin and Flashback to recover dropped table

DML statements (Data retrieving)

• Introduction to DML statements in Oracle database

• Inserting data in the table

• Updating the data using UPDATE in the table - updating records in certain criteria or updating the whole table

• Deleting the data using DELETE in the table - single record or deleting whole table data

• Merge command

• Difference in Delete and truncate statements

SQL Operators

• What is Dual table?

• What is SQL Operator?

• Arithmetic Operators

• Comparison Operators / Relational Operators

• Logical Operator - AND, OR etc.

• What is NULL?

• Special Operator - IN / NOT IN, BETWEEN / NOT BETWEEN, IS NULL / IS NOT NULL, LIKE / NOT LIKE, ANY / ALL

Introduction of Joins i.e. retrieving data from multiple tables

• Table alias and column alias

• Introduction of joins

• Oracle 8i joins - Equi or Inner, Non-Equi, Self, Outer Joins

• Oracle 9i / ANSI joins
  o Inner join
  o Left Outer join
  o Right Outer join
  o Full outer join
  o Natural join
  o Cross join

Introduction of Built-in Functions

• Introduction of Functions

• Types of single row functions

  o String functions
  o Numeric functions
  o Date functions

• Conversion functions
  o Convert date type string to Date value
  o Convert number type to character / string type
  o Convert date type to character / string type

Aggregate Functions (Multiple Rows Functions) in SQL

• DISTINCT keyword in SELECT statement

• Introduction of Aggregate Functions

• Types of Aggregate Functions
  o Count()
  o Maximum – MAX()
  o Minimum – MIN()
  o Average – AVG()
  o Sum()

• Introduction of Group By clause

• Introduction of Having clause

• Rollup, Cube and ListAgg Functions

Sub-Queries

• ROWNUM

• ROWID

• Types of Sub-Queries

• Exists and Not Exists Operator

Analytical Functions

• Types of Analytical Functions
  o Rank(), Dense_Rank() and Row_number()
  o Lag() and Lead()
  o First_Value() and Last_Value()
  o Grouping() and Grouping_id()

Set Operators

• Types of Set operators – UNION, UNION ALL, INTERSECT, MINUS

Oracle Data Dictionary and other topics

• Introduction of Oracle Data Dictionary

• Introduction of Index in Oracle Database

• Introduction of Sequence in Oracle Database

• Introduction of View in Oracle Database

• Introduction of Materialized View in Oracle Database

• Introduction of Synonym in Oracle Database

• Decode and Case statement

• Merge statement

• Virtual Column

• Pivoting

• Hierarchical Query

• Set System Variables
  o LINESIZE
  o NUMWIDTH
  o NUMFORMAT
  o PAGESIZE
  o ECHO ON/OFF
  o FEEDBACK ON/OFF
  o HEADING ON/OFF
  o PAUSE ON / OFF
  o VERIFY ON / OFF
  o Date Format

Constraints

• Introduction of Constraints in Oracle Database
  o NOT NULL
  o DEFAULT
  o UNIQUE
  o CHECK
  o PRIMARY KEY
  o FOREIGN KEY (Referential Integrity)