SQL Advanced

About Course

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 Structured Query Language skills. So let’s take the next step of learning these advanced concepts of Oracle SQL.

Learning these advanced Structured Query Language concepts would position you better in your working environment.

What will we learn?

Students will learn the art of breaking a big SQL statement into small pieces and rebuild it again.

Create Materialized views to replicate data across servers and improve performance by using simple Structured Query Language syntax.

Partition the tables for better management and performance optimization using Structured Query Language Partitioning.

Students will learn to use the Analytic Structured Query Language to aggregate, analyze and report, and model data using the Structured Query Language Analytic capabilities.

Students will learn to interpret the concept of a hierarchical query, create a tree-structured report, format hierarchical data, and exclude branches from the tree structure using SQL Hierarchical features.

Students will also learn to use regular expressions and subexpressions to search for, match, and replace strings using Structured Query Language built-in functions.

Group and aggregate data using the built-in Structured Query Language functions like ROLLUP and CUBE operators.

See you inside,

Who this course is for:

This SQL course is meant for students who already have familiarity with the Structured Query Language syntax and would like to learn the advanced concepts of SQL.

Understand the key advance concepts being implemented in the database world
Choose between Views and Materialized views based on the requirement
Partition the tables for better management and performance optimization
Perform complex pattern matching using Regular Expressions
Create advanced reports with sub totals at various grouping levels
Perform analysis with ease using the analytical functions

Course Content

SQL Querying: Advanced

Welcome to the course

00:00
Installing Oracle

00:00
Installing Java SDK

00:00
Installing SQL Developer

00:00
Running scripts necessary for the course

00:00
Default values for columns

00:00
Virtual Columns

00:00
Arithmetic calculations on NULL Values

00:00
Multi table Inserts

00:00
Merge the data

00:00
Analytical Functions Introduction

00:00
Why Analytical Functions Example 1

00:00
Why Analytical Functions Example 2

00:00
Getting the cummulative Sum of Sales

00:00
Displaying Sales as a percentage of Total sales

00:00
Ranking your data

00:00
Performing Top N Analysis

00:00
Dividing your data into Bands

00:00
LAG and LEAD function Examples

00:00
Analyzing Sales growth across time

00:00
Row level data to Column level using CASE statement

00:00
Row level data to Column level using PIVOT

00:00
Row level data to Column level using LISTAGG

00:00
Column level data to Row level using UNION

00:00
Column level data to Row level using UNPIVOT

00:00
Hierarchical Queries Introduction

00:00
Connect By clause

00:00
Creating the Hierarchy Tree

00:00
Sorting the Hierarchy Tree

00:00
CONNECT BY ROOT unary operator

00:00
Get me the Sales under Manager Raj

00:00
SYS CONNECT BY PATH function

00:00
CONNECT BY for number generation

00:00
Extensions to Group BY

00:00
Sub Totals using ROLLUP function

00:00
Sub Totals using CUBE function

00:00
GROUPING function

00:00
GROUPING ID function

00:00
Limiting number of sub totals using GROUPING SETS function

00:00
Composite Columns

00:00
Table Partitioning Introduction

00:00
Range Partition based on range of values

00:00
List Partition based on list of values

00:00
Hash Partition based on the hash key

00:00
Composite Partitioning by mixing things up

00:00
Interval Partition for automatic partition creation

00:00
Materialized Views Introduction

00:00
Materialized Views creation Options

00:00
Materialized Views with ON COMMIT option

00:00
Materialized Views with ON DEMAND option

00:00
Materialized Views with REFRESH FAST option

00:00
Timing the refresh

00:00
Query Rewrite functionality

00:00
Regular Expressions Introduction

00:00
Meta Characters and

00:00
Meta Characters and

00:00
Interval Operator to match the number of occurances

00:00
Matching the characters in a List

00:00
Lets combine multiple expressions using

00:00
Check for an expression in the beginning or end of a string

00:00
POSIX Character class operators

00:00
Search for meta characters by placing a escape character

00:00
Flashback operations Introduction

00:00
Tracking changes in Data

00:00

Student Ratings & Reviews

No Review Yet

About Course

Senior SQL Developers are in high demand and demand 100k+ salary in the IT industry.

Who this course is for:

What Will You Learn?

Course Content

SQL Querying: Advanced

Welcome to the course

Installing Oracle

Installing Java SDK

Installing SQL Developer

Running scripts necessary for the course

Default values for columns

Virtual Columns

Arithmetic calculations on NULL Values

Multi table Inserts

Merge the data

Analytical Functions Introduction

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

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

Column level data to Row level using UNION

Column level data to Row level using UNPIVOT

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 generation

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

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

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

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

Flashback operations Introduction

Tracking changes in Data

Student Ratings & Reviews