Oracle 12c SQL

This Oracle 12c course gives a complete introduction to Oracle 12c’s version of SQL, covering the SQL*Plus toolkit and Oracle 12c RDBMS contextual operating environment. 

It is an essential building block for all users of Oracle ‘s full technology range, including database technology, development tools, and web technologies. 

The course comprehensively introduces the ANSI standard features of the Oracle 12c SQL implementation.

Course Content:

• 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