• Post category:StudyBullet-6
  • Reading time:12 mins read


Level up your SQL query skills and go from intermediate to advanced level

What you will learn

Students will master advanced SQL functions and operations

Analytical (Window) Functions

Regular Expressions (RegEx)

Materialized Views

Group By Extensions

Hierarchical Queries

Common Table Expressions (CTEs)

Advanced Subqueries

Data Cleansing

Data Manipulation

Description

If you have some experience with SQL and want to develop your query skills to the next level from intermediate to advanced then this is the perfect course for you!

No downloads or software installation required. We will be using Oracle APEX which is a web-based application – you will be set up with your own virtual database hosted on the cloud! Although we will be using Oracle APEX the course has been designed to highlight key differences between some of the main Database Management Systems such as MySQL and Microsoft SQL Server, so what you learn in this course can be applied across all platforms supporting SQL.

This course will cover


Subscribe to latest coupons on our Telegram channel.

  • Analytical (Window) Functions
  • Regular Expressions (RegEx)
  • Materialized Views
  • Extensions to Group By
  • Correlated Subqueries
  • Common Table Expressions
  • Hierarchical Queries
  • Data Cleansing
  • Data Manipulation

The lectures in this course are arranged into short, bite-sized chunks. The course is designed to be comprehensive, but also concise in order to make the learning experience as easy as possible. Each section of the course has been specifically tailored to give the optimal learning experience, sections are packed with quizzes, assignments and real world type scenarios to give you an opportunity to develop your practical skills.

As your instructor I have 10+ years of professional experience consulting and working for a range of publicly listed companies. I have worked extensively across multiple database management systems including Oracle, MySQL and Microsoft SQL Server.

I hope to see you enrolled in the course!

English
language

Content

Introduction & Course Set Up

Course Overview & Prerequisite Knowledge
Oracle APEX Account Set Up
Oracle APEX Overview
Dataset Installation (Automatic)
(Optional) Manual Data Installation
Dataset Overview
Dual Table
Understanding Explain Plans In Oracle

Data Cleansing And Manipulation

Section Overview
Coalesce and NVL
Trim and Pad
Greatest and Least
Pivoting Tables
Unpivoting Tables
Assignment Question 1
Assignment Question 1 Solution
Assignment Question 1 Solution (SQL Code)
Assignment Question 2
Assignment Question 2 Solution
Assignment Question 2 Solution (SQL Code)

Regular Expressions (RegEx)

Section Overview
Regular Expressions And Metacharacters
Pattern Matching With The ‘.’ ‘+’ ‘?’ ‘*’ Metacharacters
Pattern Matching With The ‘d’ and ‘w’ Metacharacters
Pattern Matching With Intervals, Lists and Groups
Pattern Matching Continued
Introduction To RegEx Functions In Oracle
REGEXP_LIKE & Assignment Questions
REGEXP_INSTR & Assignment Questions
REGEXP_SUBSTR & Assignment Questions
REGEXP_REPLACE & Assignment Questions
Class Metacharacters
Concluding Thoughts On RegEx
Useful External Resources
Quiz

Analytical (Window) Functions

Section Overview
Introduction to Analytical Functions
An Introduction To The Over Clause
The Over Clause – Partition By
The Over Clause – Order By
An Introduction To Window Frames
Window Frame – Rows
Window Frame – Range
Window Frame – Default Behaviour
Quick Note on Window Frames
Main Types Of Window Functions
Aggregate Functions – SUM, AVG, MAX, MIN and COUNT
Analytical Functions – LAG, LEAD, NTILE and NTH_VALUE
Ranking Functions – ROW_NUMBER, RANK and DENSE_RANK
Distribution Functions – PERCENT_RANK and CUME_DIST
Order of Execution
Practice Scenario Walkthrough 1
Practice Scenario Walkthrough 2
Practice Scenario Walkthrough 3
Practice Scenario Walkthrough 4
Assignment Data Preparation
Assignment Data Preparation (SQL Code)
Assignment Question 1
Assignment Question 1 Solution
Assignment Question 1 Solution (SQL Code)
Assignment Question 2
Assignment Question 2 Solution
Assignment Question 2 Solution (SQL Code)
Assignment Question 3
Assignment Question 3 Solution
Assignment Question 3 Solution (SQL Code)
BONUS (Running Totals): Assignment Question 4
BONUS (Running Totals): Assignment Question 4 Solution (SQL Code)

Group By Extensions

Section Overview
Introduction To Group By Extensions
ROLLUP
CUBE
Quick Video On Why I Am Using NVL()
GROUPING_ID Function
Grouping Sets
Combining Columns
Useful External Resources
Assignment Question 1
Assignment Question 1 Solution
Assignment Question 1 Solution (SQL Code)
Assignment Question 2
Assignment Question 2 Solution
Assignment Question 2 Solution (SQL Code)
Assignment Question 3
Assignment Question 3 Solution
Assignment Question 3 Solution (SQL Code)

Hierarchical Queries

Section Overview
Introduction To Hierarchical Queries
The Connect By & Start With Clauses
The Level Pseudocolumn
CONNECT_BY_ROOT & SYS_CONNECT_BY_PATH
Ordering Your Hierarchical Query Results
Formatting Your Hierarchical Query
Filtering Your Results
Hierarchical Query Processing
Assignment Question 1
Assignment Question 1 Solution
Assignment Question 1 Solution (SQL Code)
Assignment Question 2
Assignment Question 2 Solution
Assignment Question 2 Solution (SQL Code)
Assignment Question 3
Assignment Question 3 Solution
Assignment Question 3 Solution (SQL Code)
Assignment Question 4
Assignment Question 4 Solution
Assignment Question 4 Solution (SQL Code)

Correlated Subqueries And Common Table Expressions (CTEs)

Section Overview
Recap On Subqueries
Correlated Subqueries, Where Exists And Where Not Exists
Common Table Expressions (CTEs)
Recursive CTEs
Assignment Question 1
Assignment Question 1 Solution
Assignment Question 1 Solution (SQL Code)
Assignment Question 2
Assignment Question 2 Solution
Assignment Question 2 Solution (SQL Code)
Assignment Question 3
Assignment Question 3 Solution
Assignment Question 3 Solution (SQL Code)

Materialized Views

Section Overview
Quick Recap On Views
What Are Materialized Views?
Syntax
Simple And Complex Materialized Views
Refresh Types
On Commit / On Demand
Manually Refreshing Your Materialized View
Build Deferred
Query Rewrite
Converting An Existing Table Into A Materialized View
Additional Guidance
Useful Links And Resources
Quiz

Appendix: Manual Dataset Installation (Optional)

Countries Dataset Installation (Manual)
Employees (EMP/DEPT) Dataset Installation (Manual)
Customer Orders Dataset Installation (Manual)