We may earn an affiliate commission when you visit our partners.
Course image
Travis Cuzick

Do you already know the basics of SQL, but sometimes get frustrated when you encounter situations where the basics just aren't enough?

Are you a junior analyst who wants to level up to advanced SQL so you can take the next step in your career?

Read more

Do you already know the basics of SQL, but sometimes get frustrated when you encounter situations where the basics just aren't enough?

Are you a junior analyst who wants to level up to advanced SQL so you can take the next step in your career?

Or maybe you're a data scientist who knows enough SQL to get by, but want to take your skills further so you can spend less time wrangling data and more time building models.

If any of these sound like your situation, then you're in the right place. This course on advanced SQL for data analysis  has everything that isn't covered in introductory SQL courses.

The curriculum goes DEEP, spanning all the advanced techniques you'll ever need to wrangle and analyze data in the fastest and most efficient way possible.

And these concepts aren't presented in isolation; everything is taught in the context of real-world analytics scenarios, meaning you'll be ready to apply these techniques on the job from day one.

Here's a look at just some of the things you'll get out of this course:

  • Make the leap to Senior Analyst by mastering advanced data wrangling techniques with SQL

  • Become the resident SQL expert on your team

  • Perform nuanced analysis of large datasets with Window Functions

  • Use subqueries, CTEs and temporary tables to handle complex, multi-stage queries and data transformations

  • Write efficient, optimized SQL

  • Leverage indexes to speed up your SQL queries

  • Supercharge your SQL knowledge with procedural programming techniques like variables and IF statements

  • Program database objects like user defined functions and stored procedures that will make life easier for you AND your teammates

  • Master useful tips and tricks not found in most database courses, like Dynamic SQL

  • Gain an intuition for what technique to apply and when

  • Train your brain with tons of hands-on exercises that reflect real-world business scenarios

What makes this course different

There are three things that really set this course apart.

First is its scope. We'll dig deep into the advanced toolbox that SQL has to offer, uncovering techniques to solve problems that leave even senior analysts scratching their heads.

Second is a relentless focus on that practical, real-world applications. Techniques are taught not as abstract concepts, but rather as solutions to common data wrangling and analytics problems. And whenever possible, these techniques are presented, not in isolation, but in combination with other things that have already been covered. This "spiral" approach reinforces concepts you've learned so they stay with you long after taking the course.

Finally, and most importantly, the course provides a treasure trove of coding exercises that give you ample opportunity for hands-on practice. And these exercises are distributed throughout the course - not clustered together after hours of videos. This means you'll have an opportunity to practice every concept you learn, right after you learn it.

So if you want to go from SQL apprentice to SQL master, enroll today. I look forward to seeing you in the course.

Enroll now

What's inside

Learning objectives

  • Installing sql server and sql server management studio
  • Window functions
  • Correlated subqueries
  • Advanced filtering with exists and not exists
  • Flattening data with pivot
  • Generating data series with recursive ctes
  • Leveraging ctes and temporary tables to break complex processes into manageable steps
  • Defining and manipulating tables with ddl and dml commands
  • Designing lookup tables to simplify redundant analysis
  • Sql optimization techniques, including indexes
  • Procedural programming techniques like variables and if statements
  • Defining your own sql functions
  • Creating stored procedures for flexible, repeatable analysis
  • Supercharge your sql knowledge with procedural programming techniques like variables and if statements
  • Writing code that writes code, with dynamic sql
  • Show more
  • Show less

Syllabus

Before we start coding...
Introduction
IMPORTANT NOTE: PLEASE READ
Installing SQL Server 2019 Express
Read more
Installing SQL Server Management Studio
Downloading the AdventureWorks Sample Database
Course Environment Walkthrough
Advanced Analysis With Window Functions
Introducing Window Functions With OVER
Introducing Window Functions With OVER - Exercises
PARTITION BY
PARTITION BY - Exercises
ROW_NUMBER
ROW_NUMBER - Exercises
RANK and DENSE_RANK
RANK and DENSE_RANK - Exercises
LEAD and LAG
LEAD and LAG - Exercises
FIRST_VALUE
FIRST_VALUE - Exercises
Introducing Subqueries
Introducing Subqueries - Exercises
ROWS BETWEEN
ROWS BETWEEN - Exercises
Subqueries
Scalar Subqueries
Scalar Subqueries - Exercises
Correlated Subqueries
Correlated Subqueries - Exercises
EXISTS
EXISTS - Exercises
FOR XML PATH With STUFF
FOR XML PATH With STUFF - Exercises
PIVOT - Part 1
PIVOT - Part 2
PIVOT - Exercises
Common Table Expressions (CTEs)
CTEs - Part 1
CTEs - Part 2
CTEs - Exercise
Recursive CTEs
Recursive CTEs - Exercises
Temp Tables
Temp Tables - Part 1
Temp Tables - Part 2
Temp Tables - Exercises
CREATE and INSERT
CREATE and INSERT - Exercise
TRUNCATE - Part 1
TRUNCATE - Part 2
TRUNCATE - Exercise
UPDATE - Part 1
UPDATE - Part 2
UPDATE - Exercise
DELETE
Optimization
Optimizing With UPDATE - Part 1
Optimizing With UPDATE - Part 2
Optimizing With UPDATE - Exercise
An Improved EXISTS With UPDATE
An Improved EXISTS With UPDATE - Exercise
Introducing Indexes
Optimizing With Indexes - Example
Optimizing With Indexes - Exercise
Lookup Tables - Part 1
Lookup Tables - Part 2
Lookup Tables - Exercises
Views
Views - Exercises
Programming SQL
Variables - Part 1
Variables - Exercise 1
Variables - Part 2
Variables - Exercise 2
Introducing User Defined Functions
NOTE TO STUDENTS
Making Functions Flexible With Parameters
User Defined Functions - Exercises
Table-Valued Functions
Table-Valued Functions - Exercise
Stored Procedures
Stored Procedures - Exercise
Control Flow With IF Statements
Control Flow With IF Statements - Exercise
Using Multiple IF Statements
Using Multiple IF Statements - Exercise
Dynamic SQL - Part 1
Dynamic SQL - Part 2
Dynamic SQL - Exercises

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Explores advanced SQL techniques, helping learners navigate complex data analysis
Suitable for intermediate SQL learners who want to advance
Provides hands-on exercises for practical application
Taught by industry experts with a strong reputation
Covers topics relevant to real-world data analysis scenarios
Requires prior SQL knowledge and may require additional prerequisites

Save this course

Save The Advanced SQL Server Masterclass For Data Analysis to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in The Advanced SQL Server Masterclass For Data Analysis with these activities:
Review Basic SQL Concepts
Refreshing your knowledge of basic SQL concepts will help you prepare for the more advanced topics covered in the course.
Show steps
  • Review the course materials on basic SQL concepts.
  • Complete the practice exercises provided in the course.
Create a Database and Populate it with Data
Creating a database and populating it with data will give you hands-on experience with the fundamental concepts of SQL.
Browse courses on Inserting Data
Show steps
  • Choose a database management system (e.g., MySQL, PostgreSQL).
  • Create a new database.
  • Create tables and define their columns.
  • Insert data into the tables.
  • Verify that the data was inserted correctly.
Attend a SQL Meetup or Conference
Connecting with other SQL professionals can provide you with valuable insights and networking opportunities.
Show steps
  • Find a local SQL Meetup or conference.
  • Register for the event.
  • Attend the event and participate in discussions.
  • Connect with other SQL professionals.
Six other activities
Expand to see all activities and additional details
Show all nine activities
Follow tutorials on advanced SQL techniques
Enhance your SQL skills by following online tutorials that cover advanced techniques such as window functions, CTEs, and stored procedures.
Show steps
  • Search for tutorials on specific SQL techniques
  • Watch the tutorials and take notes
  • Practice the techniques in the tutorials
Practice database queries
Practice writing and executing SQL queries to reinforce your understanding of the syntax and concepts covered in the course.
Browse courses on SQL Queries
Show steps
  • Set up a database environment
  • Write queries to retrieve data, filter data, and perform aggregations
  • Use subqueries, joins, and other advanced techniques
Complete the Exercises in the Course
Practicing SQL queries in the exercises provided in the course will help you solidify your understanding of the concepts and techniques taught.
Show steps
  • Follow the instructions in each exercise.
  • Execute the queries and verify the results.
  • Review the solutions provided in the course.
Build a data analysis dashboard
Create a data analysis dashboard that visualizes and analyzes data using the techniques covered in the course.
Browse courses on Dashboard
Show steps
  • Gather and clean the data
  • Design the dashboard layout and visualizations
  • Implement the dashboard using a data visualization tool
Attend an Advanced SQL Workshop
Attending an advanced SQL workshop can give you the opportunity to learn from experts and ask specific questions related to the topics covered in the course.
Show steps
  • Find an advanced SQL workshop that fits your needs.
  • Register for the workshop.
  • Attend the workshop and participate in discussions.
  • Apply the concepts learned in the workshop to your work.
Contribute to an Open-Source SQL Project
Contributing to an open-source SQL project will allow you to gain practical experience and connect with the SQL community.
Show steps
  • Find an open source SQL project to contribute to.
  • Read the project's documentation.
  • Identify a feature or bug that you can work on.
  • Implement the feature or fix the bug.
  • Submit a pull request.

Career center

Learners who complete The Advanced SQL Server Masterclass For Data Analysis will develop knowledge and skills that may be useful to these careers:

Reading list

We haven't picked any books for this reading list yet.

Share

Help others find this course page by sharing it with your friends and followers:
Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2024 OpenCourser