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