The Advanced SQL Course

The Advanced SQL Course

The Advanced SQL Course

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

Language: english

Note: 4.6/5 (206 notes) 19,026 students

Instructor(s): Malvik Vaghadia

Last update: 2022-01-26

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) 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!

 

Who this course is for

  • 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 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
    • Note on Assignment 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)

 

The Advanced SQL Course

IBM Cybersecurity Analyst [Coursera with IBM]

Time remaining or 876 enrolls left

 

Don’t miss any coupons by joining our Telegram group 

Udemy Coupon Code 100% off | Udemy Free Course | Udemy offer | Course with certificate