Oracle 12c SQL

Oracle Overview
Introducing Oracle
The Elements of the Oracle System
Relational Concepts

SQL*Plus and SQL
Introduction
Logging on to SQL*Plus and Leaving SQL*Plus
Choosing and Describing Tables
Elements of the SQL Query
The SQL Buffer and Query Layout
Editing SQL Statements
The System Dummy Table
Selecting Columns
Duplicate Information (DISTINCT)
Sorting Information

SQL Functions
The Concatenation Operator
Elements of the SQL Query: Arithmetic
Column Aliases
String Functions
Arithmetic Functions
Date Functions
Mixed Functions

Advanced SQL Functions
Nesting Different Functions

SQL Bar Charts
Decode Crosstab
Decode with “>”, “<” & “=”
Select with Minus Union and Intersect
Connect By Prior

Filtering Data Using Where
Where Operators
Where with Keywords
Where and Logical Operators
Where and Soundex

Retrieving Multiple Table Data
Joining Tables (Equi-Joins)
Aliases for Table Names
Thinking behind creating Joins
Joining Tables (Non-Equi-Joins)
Joining Tables (Outer Joins)
Joining Tables (Inner Joins)

Group By / Group By Functions
Group Function Examples
Group Function with Having
Object Support

Sub-Queries
Basic Subqueries
Multiple Column Subqueries
Subqueries with Having
Correlated Subqueries
Subquery Rules and Suggestions

Data Definition Language (DDL)
Create, Drop Alter Keywords
Tables/Partitioning/Column
Views/Synonyms/Sequences

Integrity Constraints
Types of Constraint
Referential Integrity
Defining Constraints
Integrity Constraints and Data Dictionary
Alter table

Indexes
Create Index
Unique Option
When and What to Index
Drop Index
Validate Index
Index Type Overview

Data Manipulation Lang (DML)
Insert /Update/Delete
OPS Commands (Commit, Rollback and Savepoints)

Data Control Language (DCL)
Data Security
Grant and Revoke

Datawarehousing and OLAP
Materialised views
Dimensions
ROLLUP functions
The ROLLUP operator
The CUBE operator
Client side caching

Oracle 9i/10g/11g SQL Features
Drop column
Function based indexes
Temporary tables
Default Column Value enhancements
Overview of Constraint enhancements
For update wait Clause
Constraints on views
External Tables
Multiple Insert Statements
Conditional Insert
Pivoting Insert
Merge Statement
Grouping Sets
WITH Clause
Analytical Functions
The Pivot command (11g only)

10g/11g SQL Improvements
Improvements review
Regular Expressions
Native Floating Point Datatypes

SQL*Plus Reporting
Introducing SQL*Plus for Reporting
Using SQL*Plus
Using the System Editor for SQL*Plus
SQL*Plus Command Variables
Building SQL*Plus Reports
Titles and Headings
Using NEW_VALUE and OLD_VALUE

ANSI Standardisation
Oracle (since 9ir2) Joins improvements
Case Expressions
SQL NULLIF and COALESCE
Scalar Subqueries
Date Datatype
TIMESTAMP Datatype
INTERVAL Datatypes
CURRENT_DATE

Oracle 12c SQL New Features
Generating a default value from a sequence
Create a default value for a null column.
Extended data types
ROW limiting for Top-N result queries
Invisible columns
Multiple indexes on the same column
Table partition maintenance enhancements
Truncate table CASCADE
Miscellaneous SQL*Plus enhancements
Display invisible columns
Session level sequences