Oracle 12c Advanced SQL



In this course, you will receive a hands-on introduction to advanced SQL concepts, especially as they relate to Oracle's implementation of the SQL Language. Topics necessary to the development and administration of an Oracle 12c database are covered. This course is specifically designed for those who know generic SQL but are not familiar with SQL*Plus and more importantly, Oracle's implementation of SQL.


  • Application designers
  • Developers
  • Database administrators

Learning Objectives

  • Create and drop tables, views and indexes
  • Understand Structured Query Language
  • Format reports using SQL*Plus commands
  • Extract and organize information from the database
  • Insert, update, and delete information in database tables

  • Understanding the Difference Between SQL, SQL*Plus, and PL/SQL
  • Understanding the SQL Developer Environment and Commands

2. SQL MODEL Statements

  • Symbolic cell addressing
  • Symbolic array computation
  • Wildcard dimension specification
  • Iterative rule evaluation
  • Partition wise computation

3. Analytical Functions

  • Single Row Analytic Functions
  • Aggregating Analytic Functions
  • The MODEL clause

4. SQL*Plus Reporting

  • SQL*Plus Report Writing Commands
  • COLUMN Command / Titles, System Variables
  • Master-Detail Report with TITLE and COLUMN Commands
  • Control Breaks, Computing Aggregate Amounts
  • Using SET Variables in SQL*Plus, SQL to Generate SQL
  • Creating, Deferrable, Dropping, Disabling/Enabling Constraints / Displaying Constraint Information
  • Handling Exceptions
  • Views, Synonyms, Sequences, Indexes, Virtual Columns
  • Temporary Tables, External Tables

5. Data Manipulation Language (DML) Commands

  • MERGE Command
  • Use of Subqueries
  • Transaction Control Commands / Read Consistency / Locking
  • Flashback Queries

6. Data Definition Language (DDL) Commands

  • Views, Synonyms, Sequences, Indexes, Virtual Columns
  • Temporary Tables, External Tables

7. Security

  • Users
  • Privileges
  • Roles
  • Authentication Methods

8. Multiple Table Inserts

  • Unconditional Insert ALL
  • Conditional Insert All
  • Conditional Insert First
  • Pivoting Insert
  • With Statement