0(0)

SQL Advanced

  • Course level: Intermediate

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 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:

  1. 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.

What Will I Learn?

  • 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

Topics for this course

64 Lessons

SQL Querying: Advanced

Welcome to the course00:00:00
Installing Oracle00:00:00
Installing Java SDK00:00:00
Installing SQL Developer00:00:00
Running scripts necessary for the course00:00:00
Default values for columns00:00:00
Virtual Columns00:00:00
Arithmetic calculations on NULL Values00:00:00
Multi table Inserts00:00:00
Merge the data00:00:00
Analytical Functions Introduction00:00:00
Why Analytical Functions Example 100:00:00
Why Analytical Functions Example 200:00:00
Getting the cummulative Sum of Sales00:00:00
Displaying Sales as a percentage of Total sales00:00:00
Ranking your data00:00:00
Performing Top N Analysis00:00:00
Dividing your data into Bands00:00:00
LAG and LEAD function Examples00:00:00
Analyzing Sales growth across time00:00:00
Row level data to Column level using CASE statement00:00:00
Row level data to Column level using PIVOT00:00:00
Row level data to Column level using LISTAGG00:00:00
Column level data to Row level using UNION00:00:00
Column level data to Row level using UNPIVOT00:00:00
Hierarchical Queries Introduction00:00:00
Connect By clause00:00:00
Creating the Hierarchy Tree00:00:00
Sorting the Hierarchy Tree00:00:00
CONNECT BY ROOT unary operator00:00:00
Get me the Sales under Manager Raj00:00:00
SYS CONNECT BY PATH function00:00:00
CONNECT BY for number generation00:00:00
Extensions to Group BY00:00:00
Sub Totals using ROLLUP function00:00:00
Sub Totals using CUBE function00:00:00
GROUPING function00:00:00
GROUPING ID function00:00:00
Limiting number of sub totals using GROUPING SETS function00:00:00
Composite Columns00:00:00
Table Partitioning Introduction00:00:00
Range Partition based on range of values00:00:00
List Partition based on list of values00:00:00
Hash Partition based on the hash key00:00:00
Composite Partitioning by mixing things up00:00:00
Interval Partition for automatic partition creation00:00:00
Materialized Views Introduction00:00:00
Materialized Views creation Options00:00:00
Materialized Views with ON COMMIT option00:00:00
Materialized Views with ON DEMAND option00:00:00
Materialized Views with REFRESH FAST option00:00:00
Timing the refresh00:00:00
Query Rewrite functionality00:00:00
Regular Expressions Introduction00:00:00
Meta Characters and00:00:00
Meta Characters and00:00:00
Interval Operator to match the number of occurances00:00:00
Matching the characters in a List00:00:00
Lets combine multiple expressions using00:00:00
Check for an expression in the beginning or end of a string00:00:00
POSIX Character class operators00:00:00
Search for meta characters by placing a escape character00:00:00
Flashback operations Introduction00:00:00
Tracking changes in Data00:00:00
145 £

Enrolment validity: Lifetime

Requirements

  • Brush up necessary SQL skills so that it is easy to follow the advanced concepts.