Advanced SQL

Course Features

Course Details

Description
Senior SQL Developers are in high demand and demand 100k+ salary in the IT industry. If we have to advance in our career and earn a good salary, we need these Advanced SQL skills. So let's take the next step of learning these advanced concepts of Oracle SQL.
Who is the target audience?
This SQL course is meant for students who already have familiarity with the SQL syntax and would like to learn the advanced concepts of SQL.
course outline:Welcome to the course
Important - How to make best use of the program.
Software Installation
Installing Oracle
Installing Java SDK
Installing SQL Developer
Running scripts necessary for the course
Scripts used in the demonstration
Manipulating data
Default values for columns
Virtual Columns
Arithmetic calculations on NULL Values
Multi table Insert's
Merge the data...
Analytical Functions
Analytical Functions Introductionbr
Why Analytical Functions Example 1
Why Analytical Functions Example 2
Getting the cummulative Sum of Sales
Displaying Sales as a percentage of Total sales
Ranking your data
Performing Top N Analysis
Dividing your data into Bands
LAG and LEAD function Examples
Analyzing Sales growth across time
Analytical Functions Recap
Transforming the Data
Row level data to Column level using CASE statement
Row level data to Column level using PIVOT
Row level data to Column level using LISTAGG
Row level data to Column level using LISTAGG
Column level data to Row level using UNION
Column level data to Row level using UNPIVOT
Data Transformation Recap
Hierarchical Queries
Hierarchical Queries Introduction
Connect By clause
Creating the Hierarchy Tree
Sorting the Hierarchy Tree
CONNECT_BY_ROOT unary operator
Get me the Sales under Manager Raj
SYS_CONNECT_BY_PATH function
CONNECT BY for number generationv
Hierarchical Queries Recap
Exercise 2
Extensions to Group BY
Extensions to Group BY
Sub Totals using ROLLUP function
Sub Totals using CUBE function
GROUPING function
GROUPING_ID function
Limiting number of sub totals using GROUPING SETS function
Composite Columns
Group by Extensions Recap
Exercise 3
Table Partitioning
Table Partitioning Introduction
Range Partition based on range of values
List Partition based on list of values
Hash Partition based on the hash key
Composite Partitioning by mixing things up!!
Interval Partition for automatic partition creation
Adding/Droping Partitions
Table Partitioning Recap
Materialized Views
Materialized Views Introduction
Materialized Views creation Options
Materialized Views with ON COMMIT option
Materialized Views with ON DEMAND option
Materialized Views with REFRESH FAST option
Timing the refresh
Query Rewrite functionality
Materialized Views Recap
Regular Expressions
Regular Expressions Introduction
Meta Characters . and +
Meta Characters ? and *
Interval Operator to match the number of occurances
Matching the characters in a List
Lets combine multiple expressions using |
Check for an expression in the beginning or end of a string
POSIX Character class operators
Search for meta characters by placing a escape character
More examples on Regular Expressions
Flashback operations
Flashback operations Introduction
Tracking changes in Data
Flashback Operations Recap
This course does not have any sections.

More Courses by this Instructor