- Lab
- Data
SQL Fundamentals: Advanced JOIN and Subquery Techniques
In this lab, you'll dive into advanced SQL techniques, focusing on complex JOIN operations and subqueries. Learn how to use table aliases, self-joins, cross-joins, and perform nested subqueries to efficiently query relational databases. By the end, you’ll have a solid understanding of how to retrieve and manipulate data using advanced SQL queries.
Path Info
Table of Contents
-
Challenge
Table Aliases and Advanced Joins
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: Table Aliases and Advanced Joins
To review the concepts covered in this step, please refer to the How to JOIN Tables module of the SQL Fundamentals course.
In this step, you’ll learn how to use SQL joins to retrieve related data from multiple tables. You’ll also practice using table aliases, which help make your queries more concise and readable. You’ll explore various types of joins such as
INNER JOIN,LEFT JOIN,RIGHT JOIN, andCROSS JOINto manipulate and retrieve data from theCustomers,Orders,Product, andCategorytables.Database Schema
Customers Table:
| Column | Data Type | |-------------|-----------------| | CustomerID | INT (Primary Key)| | CustomerName| VARCHAR(100) |
Orders Table:
| Column | Data Type | |-------------|-----------------| | OrderID | INT (Primary Key)| | CustomerID | INT (Foreign Key)| | OrderValue | DECIMAL(10, 2) | | OrderDate | DATE |
Product Table:
| Column | Data Type | |-------------|-----------------| | ProductID | INT (Primary Key)| | ProductName | VARCHAR(100) | | ListPrice | DECIMAL(10, 2) | | CategoryID | INT (Foreign Key)|
Category Table:
| Column | Data Type | |-------------|-----------------| | CategoryID | INT (Primary Key)| | CategoryName| VARCHAR(100) |
Employee Table:
| Column | Data Type | |-------------|-----------------| | EmployeeID | INT (Primary Key)| | EmployeeName| VARCHAR(100) | | Salary | DECIMAL(10, 2) | | DepartmentID| INT (Foreign Key)|
Department Table:
| Column | Data Type | |----------------|-----------------| | DepartmentID | INT (Primary Key)| | DepartmentName | VARCHAR(100) | | DepartmentSize | INT | | Budget | DECIMAL(10, 2) | | Location | VARCHAR(100) |
-
Challenge
Subqueries
Step 2: Subqueries for Advanced Data Retrieval
In this step, you’ll dive deeper into SQL subqueries, which allow you to embed queries inside others to perform more complex data operations. You’ll learn about correlated subqueries, where the inner query depends on values from the outer query, as well as subqueries used in the
FROMclause to calculate aggregate values. These techniques will enhance your ability to retrieve data based on advanced conditions from theOrders,Customers, andEmployeetables.Database Schema
Customers Table:
| Column | Data Type | |-------------|-----------------| | CustomerID | INT (Primary Key)| | CustomerName| VARCHAR(100) |
Orders Table:
| Column | Data Type | |-------------|-----------------| | OrderID | INT (Primary Key)| | CustomerID | INT (Foreign Key)| | OrderValue | DECIMAL(10, 2) | | OrderDate | DATE |
Employee Table:
| Column | Data Type | |-------------|-----------------| | EmployeeID | INT (Primary Key)| | EmployeeName| VARCHAR(100) | | Salary | DECIMAL(10, 2) | | DepartmentID| INT (Foreign Key)|
Department Table:
| Column | Data Type | |----------------|-----------------| | DepartmentID | INT (Primary Key)| | DepartmentName | VARCHAR(100) | | DepartmentSize | INT | | Budget | DECIMAL(10, 2) | | Location | VARCHAR(100) |
-
Challenge
Combining Joins and Subqueries
Step 3: Combining Joins and Subqueries
In this step, you’ll learn how to combine SQL joins and subqueries to tackle more complex data retrieval tasks. You’ll work on filtering and aggregating data across multiple tables, retrieving information based on conditions that span multiple datasets. This step brings together your knowledge of joins and subqueries, enhancing your ability to perform advanced queries across the
Customers,Orders,Employee, andDepartmenttables.Database Schema
Customers Table:
| Column | Data Type | |-------------|-----------------| | CustomerID | INT (Primary Key)| | CustomerName| VARCHAR(100) |
Orders Table:
| Column | Data Type | |-------------|-----------------| | OrderID | INT (Primary Key)| | CustomerID | INT (Foreign Key)| | OrderValue | DECIMAL(10, 2) | | OrderDate | DATE |
Employee Table:
| Column | Data Type | |-------------|-----------------| | EmployeeID | INT (Primary Key)| | EmployeeName| VARCHAR(100) | | Salary | DECIMAL(10, 2) | | DepartmentID| INT (Foreign Key)|
Department Table:
| Column | Data Type | |----------------|-----------------| | DepartmentID | INT (Primary Key)| | DepartmentName | VARCHAR(100) | | DepartmentSize | INT | | Budget | DECIMAL(10, 2) | | Location | VARCHAR(100) |
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.