Oracle 11g/12c: Advanced SQL

Course Description

 

In this course, students learn how to use the advanced features of SQL in order to query and manipulate data within the database. Advanced querying and reporting techniques including using SET operators, writing hierarchical queries and using the Flashback Query feature are explained. Schema objects such as Tables, Indexes and Constraints are discussed in detail.

 

In order to take this course you need to have taken Oracle 11g/12c: Introduction to SQL and have experience in writing Oracle SQL statements.

 

In This Course You Will Learn How To:

  • Combine SQL result sets into a single result set using SET operators
  • Access data from a previous point in time using the flashback query feature
  • Combine multiple SQL statements into one statement
  • Create various database objects

 

Prerequisites

 

You need to have equivalent experience or have taken the following courses:

  • Introduction to Programming for Non-Programmers
  • Introduction to Programming and Coding for Everyone with JavaScript
  • Oracle 10g/11g: Introduction to SQL
  • Oracle 11g/12c: Introduction to SQL

Outline

 

Module 00: Oracle 11g/12c: Advanced SQL Online Training - Course Introduction

 

Module 01: Using the Set Operators

  • Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
  • Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
  • Describe and use the INTERSECT operator
  • Explain and use the MINUS operator
  • List the SET operator guidelines
  • Order results when using the UNION operator
  • Using the ORDER BY clause with SET operators
  • Lab Exercise

 

Module 02: Using the Set Operators Demo

 

Module 03: Flashback Query

  • Prerequisites for using the Flashback Query feature
  • Uses for the Flashback Query feature
  • Limitations of the Flashback Query feature
  • Statement level vs. session level usage
  • Using statement level Flashback Query
  • The AS OF TIMESTAMP option
  • Using session level Flashback Query
  • The DBMS_FLASHBACK Oracle supplied package
  • Lab Exercise

 

Module 04: Flashback Query Demo

 

Module 05: Using Subqueries

  • Use a subquery to solve a problem
  • Identify where subqueries can be placed in a SELECT statement
  • Describe the types of subqueries (single row, multiple row)
  • The single row subquery operators
  • The multiple row subquery operators
  • Using multi-column subqueries
  • Writing a correlated subquery
  • Lab Exercise

 

Module 06: Using Subqueries Demo

 

Module 07: Hierarchical Retrieval

  • Hierarchical Queries
  • Ranking Rows with LEVEL
  • Formatting Hierarchical Reports Using LEVEL and LPAD
  • Pruning Branches with the WHERE and CONNECT BY clauses
  • Lab Exercise

 

Module 08: Hierarchical Retrieval Demo

 

Module 09: Using the Data Dictionary Views

  • Types of Data Dictionary Views
  • Privileges needed to access the Data Dictionary Views
  • Using the Data Dictionary Views to view database metadata information
  • Helpful Data Dictionary Views
  • Lab Exercise

 

Module 10: Using the Data Dictionary Views Demo

 

Module 11: Creating Database Objects I (Views)

  • Creating a view syntax
  • View options
  • Privileges needed to create a view
  • Benefits of views
  • Limitations of views
  • Using views in SQL statements
  • Retrieving View definitions from the Data Dictionary
  • Lab Exercise

 

Module 12: Creating Database Objects I (Views) Demos

 

Module 13: Creating Database Objects II (Sequences)

  • Creating a sequence syntax
  • Privileges needed to create a sequence
  • Sequence usage
  • Sequence options
  • Using sequences in SQL statements
  • Retrieving Sequence definitions from the Data Dictionary
  • Lab Exercise

 

Module 14: Creating Database Objects II (Sequences) Demo

 

Module 15: Creating Database Objects III (Synonyms)

  • Types of synonyms
  • Private vs. Public synonyms
  • Creating a synonym syntax
  • Privileges needed to create a synonym
  • Synonym usage
  • Using synonyms in SQL statements
  • Retrieving Synonym definitions from the Data Dictionary
  • Lab Exercise

 

Module 16: Creating Database Objects III (Synonyms) Demo

 

Module 17: Creating Database Objects IV (Tables)

  • Types of Tables
  • The Basic Heap Table
  • Creating a table syntax
  • Valid column datatypes
  • Privileges needed to create a table
  • Retrieving Table definitions from the Data Dictionary
  •    Lab Exercise

 

Module 18: Creating Database Objects IV (Tables) Demo

 

Module 19: Creating Database Objects V (Indexes)

  • Types of Indexes
  • Benefits of Indexes
  • The B-Tree Index
  • Creating an index syntax
  • Privileges needed to create a index
  • Retrieving Index definitions from the Data Dictionary
  • Lab Exercise

 

Module 20: Creating Database Objects V (Indexes) Demo

 

Module 21: Creating Database Objects VI (Constraints)

  • Types of Constraints
  • Primary Key, Foreign Key, Unique, NOT NULL, Check constraints
  • Review constraint purposes
  • Creating a constraint syntax
  • CREATE vs. ALTER syntax
  • Retrieving Constraint definitions from the Data Dictionary
  • Lab Exercise

 

Module 22: Creating Database Objects VI (Constraints) Demo

 

Module 23: Regular Expression Support

  • Regular Expression Support Overview
  • Describing simple patterns for searching and manipulating data
  • Describing complex patterns for searching and manipulating data
  • Lab Exercise

 

Module 24: Regular Expression Support Demo

 

Module 25: Advanced Oracle: 12c New Features

 

  • Row Pattern Matching

 

  • A Sequence Change

 

  • Invisible Columns

 

  • Data Type Changes

 

  • UNION/UNION All Changes