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!
Data Cleansing And Manipulation
Regular Expressions (RegEx)
-
9Section Overview
-
10Coalesce and NVL
-
11Trim and Pad
-
12Greatest and Least
-
13Pivoting Tables
-
14Unpivoting Tables
-
15Assignment Question 1
-
16Assignment Question 1 Solution
-
17Assignment Question 1 Solution (SQL Code)
-
18Assignment Question 2
-
19Assignment Question 2 Solution
-
20Assignment Question 2 Solution (SQL Code)
Analytical (Window) Functions
-
21Section Overview
-
22Regular Expressions And Metacharacters
-
23Pattern Matching With The '.' '+' '?' '*' Metacharacters
-
24Pattern Matching With The 'd' and 'w' Metacharacters
-
25Pattern Matching With Intervals, Lists and Groups
-
26Pattern Matching Continued
-
27Introduction To RegEx Functions In Oracle
-
28REGEXP_LIKE & Assignment Questions
-
29REGEXP_INSTR & Assignment Questions
-
30REGEXP_SUBSTR & Assignment Questions
-
31REGEXP_REPLACE & Assignment Questions
-
32Class Metacharacters
-
33Concluding Thoughts On RegEx
-
34Useful External Resources
-
35Quiz
Group By Extensions
-
36Section Overview
-
37Introduction to Analytical Functions
-
38An Introduction To The Over Clause
-
39The Over Clause - Partition By
-
40The Over Clause - Order By
-
41An Introduction To Window Frames
-
42Window Frame - Rows
-
43Window Frame - Range
-
44Window Frame - Default Behaviour
-
45Quick Note on Window Frames
-
46Main Types Of Window Functions
-
47Aggregate Functions - SUM, AVG, MAX, MIN and COUNT
-
48Analytical Functions - LAG, LEAD, NTILE and NTH_VALUE
-
49Ranking Functions - ROW_NUMBER, RANK and DENSE_RANK
-
50Distribution Functions - PERCENT_RANK and CUME_DIST
-
51Order of Execution
-
52Practice Scenario Walkthrough 1
-
53Practice Scenario Walkthrough 2
-
54Practice Scenario Walkthrough 3
-
55Practice Scenario Walkthrough 4
-
56Assignment Data Preparation
-
57Assignment Data Preparation (SQL Code)
-
58Assignment Question 1
-
59Assignment Question 1 Solution
-
60Assignment Question 1 Solution (SQL Code)
-
61Assignment Question 2
-
62Assignment Question 2 Solution
-
63Assignment Question 2 Solution (SQL Code)
-
64Assignment Question 3
-
65Assignment Question 3 Solution
-
66Note on Assignment Solution
-
67Assignment Question 3 Solution (SQL Code)
-
68BONUS (Running Totals): Assignment Question 4
-
69BONUS (Running Totals): Assignment Question 4 Solution (SQL Code)
Hierarchical Queries
-
70Section Overview
-
71Introduction To Group By Extensions
-
72ROLLUP
-
73CUBE
-
74Quick Video On Why I Am Using NVL()
-
75GROUPING_ID Function
-
76Grouping Sets
-
77Combining Columns
-
78Useful External Resources
-
79Assignment Question 1
-
80Assignment Question 1 Solution
-
81Assignment Question 1 Solution (SQL Code)
-
82Assignment Question 2
-
83Assignment Question 2 Solution
-
84Assignment Question 2 Solution (SQL Code)
-
85Assignment Question 3
-
86Assignment Question 3 Solution
-
87Assignment Question 3 Solution (SQL Code)