The Advanced SQL Course

The Advanced SQL Course

What you’ll 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

Requirements

Students should have basic level experience of SQL such as performing SQL SELECT Commands, using the Where clause, Group By clause and performing table joins

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:Analytical (Window) FunctionsRegular Expressions (RegEx)Materialized ViewsExtensions to Group ByCorrelated SubqueriesCommon Table ExpressionsHierarchical QueriesData CleansingData ManipulationThe 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!

Overview

Section 1: Introduction & Course Set Up

Lecture 1 Course Overview & Prerequisite Knowledge

Lecture 2 Oracle APEX Account Set Up

Lecture 3 Oracle APEX Overview

Lecture 4 Dataset Installation (Automatic)

Lecture 5 (Optional) Manual Data Installation

Lecture 6 Dataset Overview

Lecture 7 Dual Table

Lecture 8 Understanding Explain Plans In Oracle

Section 2: Data Cleansing And Manipulation

Lecture 9 Section Overview

Lecture 10 Coalesce and NVL

Lecture 11 Trim and Pad

Lecture 12 Greatest and Least

Lecture 13 Pivoting Tables

Lecture 14 Unpivoting Tables

Lecture 15 Assignment Question 1

Lecture 16 Assignment Question 1 Solution

Lecture 17 Assignment Question 1 Solution (SQL Code)

Lecture 18 Assignment Question 2

Lecture 19 Assignment Question 2 Solution

Lecture 20 Assignment Question 2 Solution (SQL Code)

Section 3: Regular Expressions (RegEx)

Lecture 21 Section Overview

Lecture 22 Regular Expressions And Metacharacters

Lecture 23 Pattern Matching With The ‘.’ ‘+’ ‘?’ ‘*’ Metacharacters

Lecture 24 Pattern Matching With The ‘\d’ and ‘\w’ Metacharacters

Lecture 25 Pattern Matching With Intervals, Lists and Groups

Lecture 26 Pattern Matching Continued

Lecture 27 Introduction To RegEx Functions In Oracle

Lecture 28 REGEXP_LIKE & Assignment Questions

Lecture 29 REGEXP_INSTR & Assignment Questions

Lecture 30 REGEXP_SUBSTR & Assignment Questions

Lecture 31 REGEXP_REPLACE & Assignment Questions

Lecture 32 Class Metacharacters

Lecture 33 Concluding Thoughts On RegEx

Lecture 34 Useful External Resources

Section 4: Analytical (Window) Functions

Lecture 35 Section Overview

Lecture 36 Introduction to Analytical Functions

Lecture 37 An Introduction To The Over Clause

Lecture 38 The Over Clause – Partition By

Lecture 39 The Over Clause – Order By

Lecture 40 An Introduction To Window Frames

Lecture 41 Window Frame – Rows

Lecture 42 Window Frame – Range

Lecture 43 Window Frame – Default Behaviour

Lecture 44 Quick Note on Window Frames

Lecture 45 Main Types Of Window Functions

Lecture 46 Aggregate Functions – SUM, AVG, MAX, MIN and COUNT

Lecture 47 Analytical Functions – LAG, LEAD, NTILE and NTH_VALUE

Lecture 48 Ranking Functions – ROW_NUMBER, RANK and DENSE_RANK

Lecture 49 Distribution Functions – PERCENT_RANK and CUME_DIST

Lecture 50 Order of Execution

Lecture 51 Practice Scenario Walkthrough 1

Lecture 52 Practice Scenario Walkthrough 2

Lecture 53 Practice Scenario Walkthrough 3

Lecture 54 Practice Scenario Walkthrough 4

Lecture 55 Assignment Data Preparation

Lecture 56 Assignment Data Preparation (SQL Code)

Lecture 57 Assignment Question 1

Lecture 58 Assignment Question 1 Solution

Lecture 59 Assignment Question 1 Solution (SQL Code)

Lecture 60 Assignment Question 2

Lecture 61 Assignment Question 2 Solution

Lecture 62 Assignment Question 2 Solution (SQL Code)

Lecture 63 Assignment Question 3

Lecture 64 Assignment Question 3 Solution

Lecture 65 Note on Assignment Solution

Lecture 66 Assignment Question 3 Solution (SQL Code)

Lecture 67 BONUS (Running Totals): Assignment Question 4

Lecture 68 BONUS (Running Totals): Assignment Question 4 Solution (SQL Code)

Section 5: Group By Extensions

Lecture 69 Section Overview

Lecture 70 Introduction To Group By Extensions

Lecture 71 ROLLUP

Lecture 72 CUBE

Lecture 73 Quick Video On Why I Am Using NVL()

Lecture 74 GROUPING_ID Function

Lecture 75 Grouping Sets

Lecture 76 Combining Columns

Lecture 77 Useful External Resources

Lecture 78 Assignment Question 1

Lecture 79 Assignment Question 1 Solution

Lecture 80 Assignment Question 1 Solution (SQL Code)

Lecture 81 Assignment Question 2

Lecture 82 Assignment Question 2 Solution

Lecture 83 Assignment Question 2 Solution (SQL Code)

Lecture 84 Assignment Question 3

Lecture 85 Assignment Question 3 Solution

Lecture 86 Assignment Question 3 Solution (SQL Code)

Section 6: Hierarchical Queries

Lecture 87 Section Overview

Lecture 88 Introduction To Hierarchical Queries

Lecture 89 The Connect By & Start With Clauses

Lecture 90 The Level Pseudocolumn

Lecture 91 CONNECT_BY_ROOT & SYS_CONNECT_BY_PATH

Lecture 92 Ordering Your Hierarchical Query Results

Lecture 93 Formatting Your Hierarchical Query

Lecture 94 Filtering Your Results

Lecture 95 Hierarchical Query Processing

Lecture 96 Assignment Question 1

Lecture 97 Assignment Question 1 Solution

Lecture 98 Assignment Question 1 Solution (SQL Code)

Lecture 99 Assignment Question 2

Lecture 100 Assignment Question 2 Solution

Lecture 101 Assignment Question 2 Solution (SQL Code)

Lecture 102 Assignment Question 3

Lecture 103 Assignment Question 3 Solution

Lecture 104 Assignment Question 3 Solution (SQL Code)

Lecture 105 Assignment Question 4

Lecture 106 Assignment Question 4 Solution

Lecture 107 Assignment Question 4 Solution (SQL Code)

Section 7: Correlated Subqueries And Common Table Expressions (CTEs)

Lecture 108 Section Overview

Lecture 109 Recap On Subqueries

Lecture 110 Correlated Subqueries, Where Exists And Where Not Exists

Lecture 111 Common Table Expressions (CTEs)

Lecture 112 Recursive CTEs

Lecture 113 Assignment Question 1

Lecture 114 Assignment Question 1 Solution

Lecture 115 Assignment Question 1 Solution (SQL Code)

Lecture 116 Assignment Question 2

Lecture 117 Assignment Question 2 Solution

Lecture 118 Assignment Question 2 Solution (SQL Code)

Lecture 119 Assignment Question 3

Lecture 120 Assignment Question 3 Solution

Lecture 121 Assignment Question 3 Solution (SQL Code)

Section 8: Materialized Views

Lecture 122 Section Overview

Lecture 123 Quick Recap On Views

Lecture 124 What Are Materialized Views?

Lecture 125 Syntax

Lecture 126 Simple And Complex Materialized Views

Lecture 127 Refresh Types

Lecture 128 On Commit / On Demand

Lecture 129 Manually Refreshing Your Materialized View

Lecture 130 Build Deferred

Lecture 131 Query Rewrite

Lecture 132 Converting An Existing Table Into A Materialized View

Lecture 133 Additional Guidance

Lecture 134 Useful Links And Resources

Section 9: Appendix: Manual Dataset Installation (Optional)

Lecture 135 Countries Dataset Installation (Manual)

Lecture 136 Employees (EMP/DEPT) Dataset Installation (Manual)

Lecture 137 Customer Orders Dataset Installation (Manual)

Section 10: Keep learning with me

Lecture 138 BONUS: Check out my other courses

Students aiming to take their SQL skills to the next level and master advanced SQL functions and operations,Individuals looking to pursue a career in a data related field

Course Information:

Udemy | English | 6h 18m | 2.16 GB
Created by: Malvik Vaghadia

You Can See More Courses in the Developer >> Greetings from CourseDown.com