Development
Databases
T-SQL
Subquery Magic: Write awesome SQL, Master T-SQL Sub Queries
4.3 (240 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,639 students enrolled

Subquery Magic: Write awesome SQL, Master T-SQL Sub Queries

Take the mystery out of subqueries & take your SQL to the next level. Use SQL Server & write data driven nested queries.
4.3 (240 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,639 students enrolled
Created by Kris Wenzel
Last updated 4/2020
English
English [Auto-generated]
Preview this course
Subquery Magic: Write awesome SQL, Master T-SQL Sub Queries
Current price: $80.99 Original price: $124.99 Discount: 35% off
1 day left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 3 hours on-demand video
  • 2 articles
  • 25 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Write subqueries and use them in various parts of a query, such as the FROM, WHERE, and HAVING clauses.
  • Gain the confidence to know when to use a sub query.
  • Solve complex problems, such as incorporating one query's results, into another's filter criteria.
  • How to write a correlated subquery (this is a big deal)
  • Solve problems, otherwise impossible to solve using SQL alone, using sub queries.
Course content
Expand all 20 lectures 02:48:27
+ – Introduction
2 lectures 12:46

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.


 

Welcome to Subquery Magic
Preview 01:59

After completing this course know what subqueries are, and how to recognize them within a SQL query.

What is a Subquery?
Preview 10:47
+ – Setup your own Learning Lab
5 lectures 17:50

Check out the download resource.

Note:  Install SQL Server 2019 if you're running Windows 109; otherwise, use the guide and for SQL Server 2017.

Download SQL Server
02:16

Install SQL Server on your computer.

Install SQL Server
04:31

Install SQL Server Management Studio so that you can connect to the database and run queries.

Download and Install SQL Server Management Studio
03:45

We'll get the Microsoft sample databases.  Many of our examples are based on these. Let's get them installed on your computer so that you can also run the queries and learn by example.

Download Sample Database Backups
04:25

The last step!  Once complete your learning lab is complete!

Restore Sample Database
02:53
+ – Writing your First Subquery
2 lectures 12:03

Write a subquery whose result is displayed as a column in your main query.

Place a subquery in the column list
06:46

Subqueries can be used any where within an expression.  See how to do so from within the column list.

Incorporate Subqueries into Expressions
05:17
Review subquery concepts
5 questions
+ – Create Correlated Subqueries
4 lectures 40:15

In a correlated subquery, values from the outer query “drive” the inner query.

Demystify Correlated Subqueries
15:39

Learn to combine data from more than one table using a correlated subquery.

Write a Correlated Subquery with Two Tables
12:58
Do you have any questions?
00:24

Subqueries get a bad wrap, is is justified?

Correlated subqueries versus Inner Joins, which is better?
11:14
Get some Correlated Subquery Reinforcement
6 questions
+ – Create Subqueries in the WHERE and HAVING Clauses
4 lectures 52:06

Familiarize yourself with EXISTS and how to use it to test whether a subquery returns rows.

Get Existential with Exists
13:45

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."

Are you in? Use the IN Condition to Test for Membership
17:29

Filter GROUP BY data more robustly using subqueries in-conjunction with HAVING

Write subqueries in the HAVING clause
08:14
Get fancy and write a correlated subquery in the HAVING clause
12:38
Review Subqueries in the Where and Having Clause
5 questions
+ – Write Queries using Derived Tables
3 lectures 33:26

Understand how to create a derived table, and that derived tables are subqueries in disguise.

What is a Derived Table?
13:57

Learn to combine derived tables to one another, and why this is a benefit to all query writers.

Joining Derived Tables
18:40
Review Derived Tables
3 questions
Bonus Lecture: Additional Resources and Course Coupons?
00:49
Requirements
  • You should already be familiar with SELECT, GROUP BY, and ORDER BY clauses
  • Access to a PC running Windows 7, 8, or 10
Description

In this Course you will Learn to:

  • Know and understand subqueries

  • Identify subqueries in SQL

  • Use subqueries in  your select statement

  • 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 (this is a big deal)

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 can not.

  • Fun - Actually having fun learning SQL subqueries!


By the time you’ve completed this 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!