EssentialSQL: Subquery Magic - Master SQL Sub Queries.

Name: EssentialSQL: Subquery Magic - Master SQL Sub Queries.
Rating: 4.6 (439 reviews)

Subquery MySql, PostgreSQL, SQLite, Microsoft SQL Server | TSQL | Correlated subquery | Inner query | Nested query

Created byKris Wenzel, Essential Sql

Last updated 6/2024

English

What you'll learn

Write subqueries to select data from multiple tables.
Create subqueries to return a single value or multiple values.
Use subqueries in the SELECT, FROM, WHERE, and HAVING clauses.
Understand and use correlated subqueries, which depend on values from the outer query.
Use subqueries to solve problems such as finding maximum or minimum values and records that don't have corresponding entries in other tables.
Understand the difference between IN, EXISTS, and ANY/ALL operators in subqueries.
How to use subqueries to create more complex queries and better understand the structure and relationships in a database.

Coding Exercises

This course includes our updated coding exercises so you can practice your skills as you learn.

Course content

7 sections • 22 lectures • 2h 10m total length

Welcome to Subquery Magic1:49
Welcome to Subquery Magic. My goal is to help take the mystery out of subqueries and show you there is no magic to them at all!
By the time you have completed the course, you will have a thorough understanding of subqueries, be able to write your own, and know when it’s best to use them.
What is a Subquery?7:39
After completing this course know what subqueries are, and how to recognize them within a SQL query.

Should I use SQL Server, MySQL or PostgreSQL to Learn Subqueries?5:03
Using the Pizza DB in this Course8:43
The PizzaDB is used for all lessons and code exercises within this course. It is compatible with MySQL, PostgreSQL, and MS SQL Server. So if you wish, use the included SQL DDL (script) to create your own database.

Regardless, you want to download the Pizza DB ER Diagram so you understand what tables are used in the lessons.
Using the Pizza DB with SQL Server6:35
In this I'll walk through some highlights to get you started using the Pizza DB with SQL Server.
Using the Pizza DB with MySQL4:50
In this I'll walk through some highlights to get you started using the Pizza DB with MySQL.
Using the Pizza DB with PostgreSQL2:23
In this I'll walk through some highlights to get you started using the Pizza DB with PostgreSQL.

Place a subquery in the column list5:35
Write a subquery whose result is displayed as a column in your main query.
Subquery in a Column List Part 1
Subquery in a Column List Part 2
Subquery in a Column List Part 3
Incorporate Subqueries into Expressions7:10
Subqueries can be used any where within an expression. See how to do so from within the column list.
Review subquery concepts
Expressions and Subqueries Part 1
Expressions and Subqueries Part 2

Demystify Correlated Subqueries7:40
In a correlated subquery, values from the outer query “drive” the inner query.
Demystify Correlated Subqueries - Example6:23
Using SQL we'll walk through each step of the correlated sub query so you can see how they work.
Correlated Subquery Single Table Part 1
Correlated Subquery Single Table Part 2
Write a Correlated Subquery with Two Tables7:35
Learn to combine data from more than one table using a correlated subquery.
Correlated SubQuery with Two Tables Part 1
Correlated Subquery with Two Tables Part 2
Do you have any questions?0:24
Correlated subqueries versus Inner Joins, which is better?11:04
Subqueries get a bad wrap, is is justified?
Get some Correlated Subquery Reinforcement

Get Existential with Exists7:19
Familiarize yourself with EXISTS and how to use it to test whether a subquery returns rows.
Exist Clause Part 1
Exists Clause Part 2
Are you in? Use the IN Condition to Test for Membership5:10
Learn how to use the IN clause in combination with a subquery to test membership. Long story short, a subquery can be used to generate the membership list you used to "hard-code."
In Condition Part 1
In Condition Part 2
In Condition Part 3
Correlated Subquery in the Where Clause
Subquery in the Having Clause5:40
The power of using a subquery in the HAVING clause is now you don’t have to hard-code values within the comparisons. You can rely on the subquery’s results to do so for you
Subquery in Having Clause
Get fancy and write a correlated subquery in the HAVING clause6:13
After completing this lesson you'll understand how to use a subquery in the HAVING clause and correlate it with an outer query.
Get fancy and write a correlated subquery in the HAVING clause - Part B3:06
After completing this lesson you'll understand how to use a subquery in the HAVING clause and correlate it with an outer query.
Review Subqueries in the Where and Having Clause

Requirements

You should already be familiar with SELECT, GROUP BY, and ORDER BY clauses
If you can write a simple select statement, then you're ready for this course!
No special equipment needed, all exercises are done online.
Optional: Install a MySQL, PostgreSQL, or SQL Server learning lab on your own computer.

Description

In this course, you will learn the fundamentals of subqueries and how to use them effectively in your MySql, PostgreSQL, or MS SQL Server SQL statements. You will learn about different types of subqueries, including correlated and uncorrelated subqueries, and how to use them to solve complex problems. You will also learn about common pitfalls to avoid when working with subqueries, and how to optimize their performance in your queries.

By the end of this course, you will have a solid understanding of how to use subqueries in your SQL statements, and be able to apply this knowledge to a wide range of real-world scenarios.

In this Course you will Learn to:

Know and understand subqueries.
Identify subqueries in SQL.
Use subqueries in your select statement to make your queries more robust and less reliant on hard coded values.
Harness the power of derived tables to work around SQL limitations. Derived tables are also known as subqueries in the FROM clause.
Use subqueries in the WHERE clause with EXISTS and IN
Use subqueries with SUM, MAX and other Aggregate functions in GROUP BY and HAVING.
Write correlated subqueries so that your subquery is able work with the outer query to become more flexible and robust.

Nothing is worse than

Being excited to learn something new but not knowing where to start.
Wasting time learning the wrong features.
Being overwhelmed with options and not know which to use.

Imagine Having...

Knowledge - Knowing how to quickly use subqueries to take your SQL to the next level.
Confidence - Feeling good that you’re on the right track.
Accomplishment - Having a sense of accomplishment that you've learned something most cannot.
Fun - Actually having fun learning SQL subqueries!

By the time you’ve completed this SQL course you’ll have an appreciation of subqueries, overcome your fear, and able to write correlated queries to make your SQL more robust.

Who this course is for:

Beginner SQL query authors curious about subqueries.
If you're struck writing subqueries or baffled by correlated subqueries, then this course is for you!
Business analysts looking more ways to write queries
Recent college graduates looking to hone their SQL skills
Job applicants looking for that extra edge with their next interview!

EssentialSQL: Subquery Magic - Master SQL Sub Queries.

What you'll learn

Explore related topics

Coding Exercises

Course content

Introduction2 lectures • 9min

Setting Up Your Learning Lab5 lectures • 28min

Writing your First Subquery2 lectures • 13min

Create Correlated Subqueries5 lectures • 33min

Create Subqueries in the WHERE and HAVING Clauses5 lectures • 27min

Write Queries using Derived Tables2 lectures • 18min

Wrap-up1 lecture • 1min

Requirements

Description

Who this course is for: