The Advanced SQL Course
Level up your SQL query skills and go from intermediate to advanced level
4.58 (1778 reviews)
45,908
students
6.5 hours
content
Apr 2024
last update
$109.99
regular price
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
Why take this course?
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:
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!
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)
Screenshots
Reviews
Jason
September 29, 2023
It was quite informative however; it was assigned to me to complete and we're more of a T-SQL shop. I don't think being an Oracle focused course was prominent enough in the description, or may have been overlooked.
Carlos
September 21, 2023
Excellent teaching - thank you - I will be reviewing these lessons at least one more time. I actually can apply this knowledge right at work as I am building a complicated report and did not know how I was going to get it done. I have a better way now.
Jenny
August 30, 2023
I found not much trainings or courses with this relevant content for me and the teacher did it very well. i learn al lot
Keerthana.M
July 20, 2023
Its really useful to learning advanced syntax present in SQL. Its a great lecture . The Examples given in the lecture are beneficial for knowing the usage of the queries.
Debra
June 17, 2023
Window functions are explained like nowhere else. More depth on CTEs and query optimization would be good.
Praneet
June 5, 2023
Excellent course and pace of delivery. Trainer is very alert and knowledgeable. I am learning/refreshing my understanding of SQL, acquiring knowledge of new concepts which I had nodding acquaintances with.
Pierre
May 18, 2023
Very well structured. I learned a lot of things and it helped me reinforce some pre-existing knowledge. Thanks!
Carlos
April 24, 2023
This professor is amazing, every topic were done explained with datail using clear english words. I´m practicing my english at the same time that I´m learning SQL.
Academy
April 3, 2023
Las instrucciones son muy claras y por ahora la instalación es directa y sin errores.
Te ayuda ampliar el conocimiento sobre este lenguaje.
Christian
March 10, 2023
There are some topics related to Oracle exclusively, I expected this course was only for SQL Server.
Although these differences, I consider what we work in SQL was excellent and very well explained.
Dmitriy
March 3, 2023
This course is pointless, as it is easy to find better, shorter and FREE tutorials on each of these topics on the internet
Abraham
January 9, 2023
good explanations, plenty of examples, challenging test questions but within the scope of what was taught
Julio
December 16, 2022
It was a great course overall I just have some points for improvement
* The regex section could be removed or shrunk since is not related only to SQL.
* Like the idea of using the same dataset for different functions, In this way you can compare results and understand better what the functions do.
* When removing things you could just trim the part you write the query.
Murray
November 10, 2022
The practice problems for the windowed function section seemed to focus on date manipulation more than actually practicing how to use windowed functions.
Manuel
October 25, 2022
Excelentes herramientas SQL para el analisis, agrupacion, conteo, seleccion, regex y demas conceptos que llevados todos a la practica se puede realizar un analisis de datos de cualquier tipo de dato, gracias
Coupons
| Submit by | Date | Coupon Code | Discount | Emitted/Used | Status |
|---|---|---|---|---|---|
| benjaminsucasaire@gmail.com | 8/2/2021 | 5F78CD08807CD18E174F | 100% OFF | 40000/9906 | expired |
| Angelcrc Seven | 2/20/2022 | 508833955BB034FB2018 | 100% OFF | 1000/710 | expired |
| - | 2/20/2022 | F0ED57A4932BF85268B4 | 100% OFF | 1000/961 | expired |
| Angelcrc Seven | 3/2/2022 | D1AC507BF9A24CAB5179 | 100% OFF | 1000/964 | expired |
| Angelcrc Seven | 4/10/2022 | 5C522AE0155085FD51D8 | 100% OFF | 1000/908 | expired |
| Angelcrc Seven | 4/10/2022 | D08404B08502DBF887E5 | 100% OFF | 1000/866 | expired |
| Angelcrc Seven | 4/15/2022 | 72109D1FCDF68EB9F872 | 100% OFF | 1000/399 | expired |
| - | 5/13/2022 | 77EC5987FA92F794C18C | 100% OFF | 1000/933 | expired |
| - | 5/14/2022 | 3CE6865252AC9892A123 | 100% OFF | 1000/869 | expired |
| - | 5/14/2022 | 90262FC2CEDD4B251113 | 100% OFF | 1000/885 | expired |
| - | 6/3/2022 | 9C3E4E390C39DE026531 | 100% OFF | 1000/959 | expired |
| Ignacio Castro | 6/10/2022 | 58EE04AB77C4D50F19B8 | 100% OFF | 1000/938 | expired |
| - | 6/16/2022 | EF0A22DFEEA1A1DD9A7A | 100% OFF | 1000/603 | expired |
| - | 7/1/2022 | 07B8DC02D7B48EE5F2C0 | 100% OFF | 1000/990 | expired |
| - | 7/11/2022 | C39E102198514A078FB1 | 100% OFF | 1000/960 | expired |
| Angelcrc Seven | 7/25/2022 | 1187F2F08508B11DC6A2 | 100% OFF | 1000/985 | expired |
| - | 8/2/2022 | 760F05A42BCB3D84C1A8 | 100% OFF | 1000/917 | expired |
| - | 8/23/2022 | 97D412172D419D4BE898 | 100% OFF | 1000/999 | expired |
| - | 9/10/2022 | CB0B39DCBEA0A66529D2 | 100% OFF | 1000/187 | expired |
| - | 9/20/2022 | 40AF0703CB35942C725F | 100% OFF | 1000/972 | expired |
| - | 9/25/2022 | EF4E5F63760CDFC03847 | 100% OFF | 1000/945 | expired |
| - | 10/13/2022 | E0A29F409779096C46BF | 100% OFF | 1000/885 | expired |
Charts
Price
Rating
Enrollment distribution
Related Topics
4121154
udemy ID
6/13/2021
course created date
7/30/2021
course indexed date
Bot
course submited by