ADVANCED SQL TUTORIAL

 Advanced SQL Tutorial

The "Advanced SQL" requirement probably hints at knowledge and possibly proficiency in several of the new concepts such as: CTEs (Common Table Expressions) UDFs (User Defined Functions) Fulltext search extensions/integration. performance tuning with new partitionning schemes, filtered indexes, sparse columns...)

Topics Cover In This Course

Welcome:

• Welcome to the course
• Scripts for our Lab Exercises

Manipulating data:

• Default values for columns
• Virtual Columns
• Arithmetic calculations on NULL Values
• Multi table Inserts

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 generation

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

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

Analytical Functions:

• 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

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

Flashback operations:

• Flashback operations Introduction
• Tracking changes in Data

Regular Expressions:

• Regular Expressions Introduction
• Meta Characters
• 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

That Is All

Subscribe Our YouTube Channel For More Courses

Brought To You Free By VJ SECURITY

Click Below To Download The Course

Thank You