Advanced SQL Course | VephlaUni ®

About this course

This Advanced SQL course is designed for data professionals seeking to enhance their SQL skills. Participants will explore complex querying techniques such as subqueries and window functions, learn performance optimization strategies, and delve into database design and normalization. The course covers the creation and management of stored procedures and triggers, alongside best practices for data security and transaction management. Through practical exercises and real-world applications, learners will gain the expertise needed to tackle intricate data challenges and optimize their database solutions. A foundational knowledge of SQL is required.

By the end of the course, participants will have the skills to tackle complex data challenges, optimize SQL queries, and design robust database solutions. Whether you're a data analyst, database administrator, or developer, this course will enhance your SQL expertise and elevate your career in data management.

Requirements

No prior skill prerequisites - this is a complete beginner track
A laptop (Mac, Windows, or Linux)
A stable internet connection for live classes and downloads
Dedication and time

Course curriculum — 9 modules

Module 1

Introduction to MySQL

Overview of MySQL: History, architecture, and importance in relational database management
Installation & Setup: Setting up MySQL environment and MySQL Workbench
Basic MySQL Operations: Introduction to database interaction
Structured Query Language (SQL): An introduction to the syntax and semantics of SQL
MySQL Data Types: Understanding and using different data types in MySQL

Module 2

SQL Fundamentals

Creating Databases: Syntax for creating databases and tables
SELECT Statements: Writing basic SELECT queries to retrieve data
MySQL Comments: Single-line and multi-line comments for readability
PEMDAS in SQL: Understanding operator precedence (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction)
DISTINCT: Using DISTINCT to eliminate duplicate rows
WHERE Clause: Filtering records with conditions
Logical Operators (AND, OR, NOT): Combining multiple conditions
LIKE Statement: Pattern matching using wildcards (%, _)
GROUP BY: Aggregating data into groups for analysis
ORDER BY: Sorting data in ascending or descending order
COUNT and Other Aggregate Functions: Performing calculations on sets of rows
HAVING: Filtering groups of data, post-aggregation
WHERE vs HAVING: Differences and appropriate use cases
LIMIT: Limiting the number of rows returned
Aliases (AS): Renaming columns and tables in query results for clarity

Module 3

Advanced Joins and Set Operations

Understanding Joins: Concept and importance of combining data from multiple tables
INNER JOIN: Returning matching records from both tables
LEFT JOIN: Returning all records from the left table and matching ones from the right
RIGHT JOIN: Returning all records from the right table and matching ones from the left
SELF JOIN: Joining a table with itself for complex hierarchical data analysis
UNIONS: Merging result sets of two or more queries
UNION vs UNION ALL: Handling duplicate rows in combined datasets
Real-World Use Cases of Joins: Practical scenarios and optimizations

Module 4

String Functions in MySQL

String Manipulation: Exploring MySQL's powerful string functions
LENGTH: Determining the number of characters in a string
UPPER / LOWER: Changing string case (uppercase, lowercase)
TRIM: Removing unwanted whitespace from both sides of a string
LTRIM / RTRIM: Trimming spaces from the left or right of a string
SUBSTRING: Extracting a portion of a string
REPLACE: Replacing occurrences of a substring within a string
LOCATE: Finding the position of a substring in a string
CONCAT: Concatenating multiple strings together

Module 5

Complex Queries and Advanced Functions

CASE Statements: Conditional logic within SQL queries
Subqueries: Writing nested queries for advanced filtering
Subqueries in the WHERE Clause: Filtering data based on the result of another query
Subqueries in the SELECT Statement: Returning computed columns
Window Functions: Advanced analytical queries
PARTITION BY: Dividing result sets into partitions
ROLLING TOTALS: Calculating cumulative sums across rows
ROW_NUMBER: Assigning sequential integer values to rows within partitions
DENSE_RANK: Ranking rows without gaps in rank values

Module 6

Common Table Expressions (CTEs) and Temporary Tables

Introduction to CTEs: Why and when to use Common Table Expressions
CTE Syntax: Writing and using CTEs for better query readability and reusability
Chaining Multiple CTEs: Writing queries with more than one CTE
Temporary Tables: Creating and using temporary tables for intermediate data storage
Comparison of CTEs and Temp Tables: When to use one over the other, pros and cons

Module 7

Stored Procedures, Triggers, and Events

Stored Procedures: Encapsulating business logic within the database
Delimiters: Modifying MySQL delimiters for multi-line stored procedures
Parameters: Input and output parameters for dynamic procedure execution
Triggers: Automatically invoking actions based on table events (INSERT, UPDATE, DELETE)
Triggers: Automatically invoking actions based on table events (INSERT, UPDATE, DELETE)

Module 8

Data Cleaning and Standardization with MySQL

Introduction to Data Cleaning: Importance of data quality in databases
Standardizing Data: Formatting inconsistencies, handling duplicates, and data normalization
Handling NULL Values: Strategies for dealing with missing or undefined data
Replacing or Removing Blank Values: Using NULLIF, COALESCE, and other functions to manage missing values

Module 9

Exploratory Data Analysis (EDA) with MySQL

Introduction to EDA: Goals and strategies of exploratory data analysis in SQL
Descriptive Statistics with SQL: Calculating measures like mean, median, mode, variance, and standard deviation
Data Distributions: Understanding distributions through frequency counts, histograms, and quantiles
Outlier Detection: Identifying and managing outliers with SQL queries
Correlations and Relationships: Investigating relationships between variables using SQL joins and aggregation
Data Visualization with SQL: Exporting data for visual analysis in tools like Tableau, Power BI, or Python
Real-World EDA Use Cases: Applying EDA techniques in various industries like finance, marketing, and healthcare

Dr. Richards M.

Data Engineer · University of Derby, UK

5.0 · 980+ Students

Dr. Richards M. holds a Master's in Big Data Analytics from the University of Derby, UK, and has spent years helping teams turn raw data into decisions. He focuses on practical, production-grade SQL skills rather than textbook theory, drawing on real analytics work across several industries.

Student reviews

Adenike Idowu

4.9

Finally understand window functions!

I'd used SQL for years but always avoided window functions until this course. The way Dr. Richards breaks down PARTITION BY made it click almost immediately.

Chidi Okonkwo

4.8

Genuinely practical

This wasn't a rehash of basic SQL. The optimization and indexing modules alone were worth the price — I used what I learned the same week at work.

Advanced SQL

About this course

Requirements

Course curriculum — 9 modules

Student reviews

Checkout other courses