SQL Advanced

Lead yourself to enhance your SQL skills and transform into Senior SQL Developer. Learn SQL by doing with me.
4.5 (109 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
1,382 students enrolled
Instructed by Amarnath Reddy IT & Software / Other
$150
Take This Course
  • Lectures 80
  • Contents Video: 4.5 hours
    Other: 42 mins
  • Skill Level Expert Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 11/2015 English

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

Learning these advanced SQL 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 SQL syntax.

Partition the tables for better management and performance optimization using SQL Partitioning.

Students will learn to use Analytic SQL to aggregate, analyze and report, and model data using the SQL 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 SQL built in functions.

Group and aggregate data using the built in SQL functions like ROLLUP and CUBE operators.

See you inside,

Amarnath Panyam


What are the requirements?

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

What am I going to get from this course?

  • 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

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

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Welcome to the course!!!
Preview
01:34
How to make best use of the program.
1 page
Section 1: Software Installation
Installing Oracle
04:07
Installing Java SDK
02:20
Installing SQL Developer
03:21
Running scripts necessary for the course
02:12
Scripts used in the demonstration
20 pages
Section 2: Manipulating data
Default values for columns
05:43
Virtual Columns
05:26
Arithmetic calculations on NULL Values
05:38
Multi table Insert's
05:38
Merge the data...
09:42
Article

Lab Setup

Exercise 1
Article
Section 3: Analytical Functions
Analytical Functions Introduction
04:29
Why Analytical Functions Example 1
Preview
06:06
Why Analytical Functions Example 2
02:39
Getting the cummulative Sum of Sales
01:40
Displaying Sales as a percentage of Total sales
03:04
Ranking your data
03:55
Performing Top N Analysis
Preview
03:25
Dividing your data into Bands
04:02
LAG and LEAD function Examples
04:22
Analyzing Sales growth across time
03:17
Analytical Functions Recap
3 pages
Section 4: Transforming the Data
Row level data to Column level using CASE statement
Preview
04:32
Row level data to Column level using PIVOT
Preview
03:14
Row level data to Column level using LISTAGG
03:43
Column level data to Row level using UNION
04:04
Column level data to Row level using UNPIVOT
03:32
Data Transformation Recap
Article
Section 5: Hierarchical Queries
Hierarchical Queries Introduction
01:51
Connect By clause
05:08
Creating the Hierarchy Tree
Preview
03:21
Sorting the Hierarchy Tree
02:08
CONNECT_BY_ROOT unary operator
02:34
Get me the Sales under Manager Raj
03:42
SYS_CONNECT_BY_PATH function
01:48
CONNECT BY for number generation
01:17
Hierarchical Queries Recap
2 pages
Exercise 2
Article
Section 6: Extensions to Group BY
Extensions to Group BY
00:47
Sub Totals using ROLLUP function
Preview
04:32
Sub Totals using CUBE function
02:09
GROUPING function
03:55
GROUPING_ID function
03:17
Limiting number of sub totals using GROUPING SETS function
04:41
Composite Columns
04:44
Group by Extensions Recap
5 pages
Exercise 3
Article
Section 7: Table Partitioning
Read This!!!!
1 page
Table Partitioning Introduction
Preview
04:46
Range Partition based on range of values
09:38
List Partition based on list of values
06:09
Hash Partition based on the hash key
05:40
Composite Partitioning by mixing things up!!
04:38
Interval Partition for automatic partition creation
07:12
Adding/Droping Partitions
1 page
Table Partitioning Recap
2 pages
Section 8: Materialized Views
Materialized Views Introduction
Preview
03:26
Materialized Views creation Options
03:33
Materialized Views with ON COMMIT option
05:38
Materialized Views with ON DEMAND option
04:06
Materialized Views with REFRESH FAST option
06:16
Timing the refresh
03:50
Query Rewrite functionality
05:40
Materialized Views Recap
5 pages
Section 9: Regular Expressions
Regular Expressions Introduction
Preview
03:06
Meta Characters . and +
04:16
Meta Characters ? and *
04:22
Interval Operator to match the number of occurances
03:55
Matching the characters in a List
04:55
Lets combine multiple expressions using |
02:02
Check for an expression in the beginning or end of a string
02:18
POSIX Character class operators
03:26
Search for meta characters by placing a escape character
02:07
More examples on Regular Expressions
1 page
Section 10: Flashback operations
Flashback operations Introduction
04:06
Tracking changes in Data
04:05
Flashback Operations Recap
1 page

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Amarnath Reddy, Oracle Architect

Amarnath has a Masters Degree in computer science with around 15 years of experience in the IT industry. He has successfully designed and implemented projects for clients in South Africa, India and USA. Currently his main focus is on Datawarehouse Architecture.

Amarnath is currently working for a fortune 500 company in USA and has pioneered the data architecture discipline. His Innovative and simple architectural designs have won accolades in the company.

Today, he brings leadership and technical expertise to enable customers to optimize and modernize their IT environments leveraging Business Intelligence tools, Big Data, social, mobile, and the Internet of Things.

Ready to start learning?
Take This Course