SQL Advanced: Advanced Subqueries

Name: SQL Advanced: Advanced Subqueries
Author: Eric Grose

Grow your subquery skill set with advanced data analysis and manipulation

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

In these lessons, you'll learn about the advanced types of sub queries in school. And in this lesson, you'll gain experience writing sub queries that reference values from the outer query. These are known as correlated sub queries. Let's review the generic syntax for this type of query. First, we use a select statement to define the columns we want in our results. Set. These columns will come from the table or the outer part of our query. Then we'll use the WHERE clause to set up our conditional operator or the logic we want to define. And here is where correlated sub queries differ from traditional ask. Well, with a correlated sub query we can have more complex logic because we can use an entire query to define our condition. For our first example, let's find the employees with salaries that are higher than the average salary within their department. So for this query, we'll return the employee ID as well as their salary from the employees table, which we've alias as e. We'll use our conditional statement to say where the salary is greater than and now we can use our inner query or sub query to define our more complex logic. So in order to meet the requirements of our use case, we need to find the average. So we'll use the average aggregate function. Again, we'll use the employees table, which is totally fine. But please note we don't have an alias here to distinguish between the two. And then here is where our logic really ties together. We're using the department ID, but one from the inner query as well as one from the outer query. Let's go ahead and run this query by pressing the play button up here and we can see in our output here the employee IDs and their salary that are higher than the average within their department. Next, let's use our orders table to find the latest order for each customer. So here we're going to select star or select all columns from the orders table, which is Alias as O. Our conditional statement is where the order date is equal to. And again in our inner query, we can define our more complex logic. Since we want to find the latest order for each customer, we'll use the max aggregate function around the order date column from the same table, but with no alias. And again where these two values meet on the customer ID. But distinguishing between the inner and outer parts of our query, this will essentially create a subset or sub queries that meet certain conditions. And we want to find the roads that match our set condition. So let's go ahead and run this by pressing the play button and we can see here for each customer all the columns as well as the max order date for each customer. Now, since this is an advanced course, let's dive a bit deeper here by calculating running total of orders for our products Table. So in our Select statement or a result set, we want to return the product name and price from our products table as well as the order date from our orders table. And now, since we want a running total or essentially a timeline for our orders, we're going to go ahead and use as some price as well as over a keyword to create a window function. Within our window function, we want to partition or split by the product ID. And since we want this in chronological order, we'll go ahead and order by the order date and we'll go ahead and define all this logic with an alias of running underscore total. Now we just need to reference the associated tables. So we'll use our product table, which is Alias as P join into our order tables, which is alias as O. In the link between these two tables is the product ID column. Last, we're going to order by or organize our data based on the product name and the order date. Let's go ahead and run this by pressing the play button. And if we see here, this is broken down by each product and it shows in chronological order of the purchases correlated, some queries provide us with the massive amount of flexibility and complexity with our analysis. But please be cautious because these queries run a full table scan on each row in the outer query. My recommendation would be to start with one piece here logic and slowly build up complexity of testing the performance of your queries operation. Thanks for watching. Stay tuned for the next lesson. Rebuild Scale our sub queries to return a single value.

Advanced Provider : Eric Grose 5 Lessons · 27m in Arabic, English

Course Description

These lessons will help to prepare professionals with the skills and knowledge required to navigate complex data scenarios. You’ll learn more about subqueries, and how to utilize their versatility in enhancing data extraction and manipulation. You will be able to grasp the nuances of correlated and scalar subqueries, enabling precise data retrieval. These lessons will also improve your proficiency in crafting intricate queries essential for interpreting and utilizing information from databases. This will help you gain a thorough understanding of data manipulation operations and hone your prowess in handling complex data types. These lessons serve as the cornerstone of your journey towards SQL mastery, equipping you with the tools to navigate the data landscape with confidence.

In these lessons, you’ll learn about advanced query techniques. We’ll use subqueries to perform advanced analysis. Additionally, you’ll learn to navigate precision data manipulation by creating subqueries that grab the exact data you need and nothing more. We’ll also explore a number of complex data types.

What You'll Learn

Select the right subquery for the right use case across complex data scenarios
Create correlated and scalar subqueries for precise data retrieval
Build Common Table Expressions (CTEs) as temporary result sets to reference in subsequent queries
Apply EXIST and NOT EXIST operators to negate or interpret specific datasets
Manipulate data using INSERT, UPDATE, and DELETE statements
Craft intricate nested queries that extract the exact data needed and nothing more

Key Takeaways

Subqueries are versatile tools for enhancing data extraction and manipulation in complex data scenarios.
Correlated and scalar subqueries enable precise data retrieval from databases.
Common Table Expressions create temporary result sets that can be referenced in subsequent queries.
EXIST and NOT EXIST operators use defined logic to negate or interpret specific datasets.
These lessons cover advanced query techniques, precision data manipulation, and handling complex data types as a foundation for SQL mastery.

Frequently Asked Questions

Who is this course for?

It is designed to prepare professionals with the skills and knowledge required to navigate complex data scenarios using advanced SQL subqueries.

What topics does this course cover?

It covers correlated subqueries, scalar subqueries, Common Table Expressions (CTEs), EXIST and NOT EXIST operators, and INSERT, UPDATE, and DELETE statements.

What skills will I gain?

You will gain skills in adhoc queries, data manipulation, and nested queries, along with proficiency in crafting intricate queries and handling complex data types.

What will I be able to do with subqueries after this course?

You will be able to use subqueries to perform advanced analysis and create subqueries that grab the exact data you need and nothing more.