- Lab
- Data
SQL Fundamentals: Advanced Data Retrieval 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
Using CASE Statements
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).To review the concepts covered in this step, please refer to the course Subqueries and Sets module of the Introduction to SQL Course.
Step 1: Using CASE Statements
In this step, you’ll learn how to use the
CASEstatement in SQL to apply conditional logic within your queries. TheCASEstatement allows you to evaluate conditions and return specific values based on those conditions, which is useful for categorizing or grouping data. You’ll be working with employee salaries and product prices, determining how data can be classified using conditional logic.Database Schema
Employees Table:
| Column | Data Type | |------------|---------------| | EmployeeID | INT (Primary Key) | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | Department | VARCHAR(50) | | Salary | DECIMAL(10, 2) | | TotalSales | DECIMAL(10, 2) |
Products Table:
| Column | Data Type | |------------|---------------| | ProductID | INT (Primary Key) | | ProductName| VARCHAR(100) | | Category | VARCHAR(50) | | ListPrice | DECIMAL(10, 2) |
Orders Table:
| Column | Data Type | |------------|---------------| | OrderID | INT (Primary Key) | | OrderDate | DATE | | CustomerID | INT | | Status | VARCHAR(20) |
-
Challenge
Using Window Functions
Step 2: Using Window Functions
In this step, you will explore window functions, which allow you to perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, window functions do not collapse rows into one. You’ll calculate running totals, ranks, and cumulative counts to gain insight into your data based on different categories, like salaries and product prices.
Database Schema
Employees Table:
| Column | Data Type | |------------|---------------| | EmployeeID | INT (Primary Key) | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | Department | VARCHAR(50) | | Salary | DECIMAL(10, 2) | | TotalSales | DECIMAL(10, 2) |
Products Table:
| Column | Data Type | |------------|---------------| | ProductID | INT (Primary Key) | | ProductName| VARCHAR(100) | | Category | VARCHAR(50) | | ListPrice | DECIMAL(10, 2) |
Orders Table:
| Column | Data Type | |------------|---------------| | OrderID | INT (Primary Key) | | OrderDate | DATE | | CustomerID | INT | | Status | VARCHAR(20) |
-
Challenge
Using Common Table Expressions (CTEs)
Step 3: Using Common Table Expressions (CTEs)
In this step, you will learn how to simplify complex queries using Common Table Expressions (CTEs). A CTE allows you to break a query into smaller, more manageable parts by defining a temporary result set. You will use CTEs to calculate total sales for salespeople, average sales by product category, and total orders per customer in the last year. This technique is invaluable for organizing and managing more complex SQL queries.
Database Schema
Employees Table:
| Column | Data Type | |------------|---------------| | EmployeeID | INT (Primary Key) | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | Department | VARCHAR(50) | | Salary | DECIMAL(10, 2) | | TotalSales | DECIMAL(10, 2) |
Products Table:
| Column | Data Type | |------------|---------------| | ProductID | INT (Primary Key) | | ProductName| VARCHAR(100) | | Category | VARCHAR(50) | | ListPrice | DECIMAL(10, 2) |
Orders Table:
| Column | Data Type | |------------|---------------| | OrderID | INT (Primary Key) | | OrderDate | DATE | | CustomerID | INT | | Status | VARCHAR(20) |
OrderDetails Table:
| Column | Data Type | |----------------|---------------| | OrderDetailID | INT (Primary Key) | | OrderID | INT | | ProductID | INT | | Quantity | INT |
Customers Table:
| Column | Data Type | |------------|---------------| | CustomerID | INT (Primary Key) | | FirstName | VARCHAR(50) | | LastName | VARCHAR(50) | | TotalOrders| INT |
Sales Table:
| Column | Data Type | |------------|---------------| | SaleID | INT (Primary Key) | | EmployeeID | INT | | SaleAmount | DECIMAL(10, 2) | | SaleDate | DATE |
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.