- Lab
- Data
SQL Fundamentals: Writing Advanced Queries with Subqueries
In this lab, you'll learn how to write advanced SQL queries using subqueries and correlated subqueries. You'll practice selecting data based on subquery results, combining joins and subqueries, and using correlated subqueries for more complex data retrieval.
Path Info
Table of Contents
-
Challenge
Writing Advanced SQL Queries with Subqueries
SQL Guide
For each task, replace or update the existing SQL code in the top pane (
main.sql) with your answer to the task and run it to see the results in the bottom pane (SQL Viewer).
Step 1: Writing Advanced SQL Queries with Subqueries
In this step, you will learn how to write advanced SQL queries using subqueries. A subquery is a query nested within another SQL query, often used to simplify complex queries or return specific data for comparison. The tasks in this step will focus on using subqueries to filter data based on average values. You will write SQL queries that select data based on comparisons to averages, such as product prices or employee salaries, demonstrating the power of subqueries in SQL.
🫙 Database Structure
Tables You Will Be Working With:
Products:
| Column Name | Data Type | |-------------|--------------------------------| | ProductID | INT PRIMARY KEY AUTO_INCREMENT | | ProductName | VARCHAR(100) | | Category | VARCHAR(100) | | ListPrice | DECIMAL(10, 2) |
Employees:
| Column Name | Data Type | |--------------|--------------------------------| | EmployeeID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) | | DepartmentID | INT | | Salary | DECIMAL(10, 2) |
Customers:
| Column Name | Data Type | |-------------|--------------------------------| | CustomerID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) |
Orders:
| Column Name | Data Type | |--------------|--------------------------------| | OrderID | INT PRIMARY KEY AUTO_INCREMENT | | CustomerID | INT | | OrderValue | DECIMAL(10, 2) |
-
Challenge
Combining Joins and Subqueries
Step 2: Combining Joins and Subqueries
In this step, you will learn how to combine SQL joins with subqueries to retrieve complex sets of data. A
JOINcombines rows from two or more tables based on a related column, while a subquery allows you to calculate or filter additional data. By usingINNER JOIN,LEFT JOIN, andRIGHT JOIN, you will retrieve data across multiple tables and filter this data with the help of subqueries. These tasks will give you a deeper understanding of how to join and query data for more complex conditions.🫙 Database Structure
Tables You Will Be Working With:
Employees:
| Column Name | Data Type | |--------------|--------------------------------| | EmployeeID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) | | DepartmentID | INT | | Salary | DECIMAL(10, 2) |
Departments:
| Column Name | Data Type | |----------------|--------------------------------| | DepartmentID | INT PRIMARY KEY AUTO_INCREMENT | | DepartmentName | VARCHAR(100) | | Budget | DECIMAL(10, 2) | | Size | INT |
Customers:
| Column Name | Data Type | |-------------|--------------------------------| | CustomerID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) |
Orders:
| Column Name | Data Type | |--------------|--------------------------------| | OrderID | INT PRIMARY KEY AUTO_INCREMENT | | CustomerID | INT | | OrderValue | DECIMAL(10, 2) |
-
Challenge
Working with Correlated Subqueries
Step 3: Working with Correlated Subqueries
In this step, you will learn how to use correlated subqueries, which are subqueries that rely on data from the outer query. Correlated subqueries are useful for row-by-row comparisons and help solve problems that require dynamic, context-based conditions. In these tasks, you will use correlated subqueries to filter employees, products, and customers based on comparisons like salary or price within specific categories or departments.
🫙 Database Structure
Tables You Will Be Working With:
Products:
| Column Name | Data Type | |-------------|--------------------------------| | ProductID | INT PRIMARY KEY AUTO_INCREMENT | | ProductName | VARCHAR(100) | | Category | VARCHAR(100) | | ListPrice | DECIMAL(10, 2) |
Employees:
| Column Name | Data Type | |--------------|--------------------------------| | EmployeeID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) | | DepartmentID | INT | | Salary | DECIMAL(10, 2) |
Customers:
| Column Name | Data Type | |-------------|--------------------------------| | CustomerID | INT PRIMARY KEY AUTO_INCREMENT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) |
Orders:
| Column Name | Data Type | |--------------|--------------------------------| | OrderID | INT PRIMARY KEY AUTO_INCREMENT | | CustomerID | INT | | OrderValue | DECIMAL(10, 2) |
What's a lab?
Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.
Provided environment for hands-on practice
We will provide the credentials and environment necessary for you to practice right within your browser.
Guided walkthrough
Follow along with the author’s guided walkthrough and build something new in your provided environment!
Did you know?
On average, you retain 75% more of your learning if you get time for practice.