Table of Contents

1. 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) |

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 JOIN combines rows from two or more tables based on a related column, while a subquery allows you to calculate or filter additional data. By using INNER JOIN, LEFT JOIN, and RIGHT 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) |

3. 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) |