Table of Contents

1. 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, and CROSS JOIN to manipulate and retrieve data from the Customers, Orders, Product, and Category tables.

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

2. 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 FROM clause to calculate aggregate values. These techniques will enhance your ability to retrieve data based on advanced conditions from the Orders, Customers, and Employee tables.

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

3. 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, and Department tables.

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