Table of Contents

1. Challenge

   Inner 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: Inner Joins

   To review the concepts covered in this step, please refer to the course Summarizing Data with GROUP BY module of the Introduction to SQL Course.

   In this step, you will learn how to use the INNER JOIN clause to combine data from two or more tables. An INNER JOIN retrieves only the rows where there is a match between the tables involved. You will practice combining tables such as Employee and Department to retrieve employee and department information, as well as other related tables. This step will help you gain a solid understanding of how to join tables using matching columns.

   Database Schema

   Employee

   | Column | Data Type | |--------------|-------------------| | EmployeeID | INT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) | | Salary | DECIMAL(10, 2) | | DepartmentID | INT |

   Department

   | Column | Data Type | |----------------|------------------| | DepartmentID | INT | | DepartmentName | VARCHAR(100) |

   Customer

   | Column | Data Type | |---------------|-------------------| | CustomerID | INT | | CustomerName | VARCHAR(100) |

   Orders

   | Column | Data Type | |---------------|-------------------| | OrderID | INT | | CustomerID | INT | | OrderDate | DATE |

   Product

   | Column | Data Type | |---------------|-------------------| | ProductID | INT | | ProductName | VARCHAR(255) | | Category | VARCHAR(100) | | ListPrice | DECIMAL(10, 2) |

   Review

   | Column | Data Type | |---------------|-------------------| | ReviewID | INT | | ProductID | INT | | ReviewText | TEXT |

2. Challenge

   Outer Joins

   Step 2: Outer Joins

   In this step, you will explore LEFT JOIN and RIGHT JOIN operations, which retrieve all records from one table, along with the matched records from the second table. If there is no match, NULL values will be displayed for columns from the other table. You will work with tables such as Employee and Department to retrieve all employees (even those without a department) or all departments (even those without employees). By the end of this step, you will understand how to handle incomplete data using outer joins.

   Database Schema

   Employee

   | Column | Data Type | |--------------|-------------------| | EmployeeID | INT | | FirstName | VARCHAR(100) | | LastName | VARCHAR(100) | | Salary | DECIMAL(10, 2) | | DepartmentID | INT |

   Department

   | Column | Data Type | |----------------|------------------| | DepartmentID | INT | | DepartmentName | VARCHAR(100) |

   Customer

   | Column | Data Type | |---------------|-------------------| | CustomerID | INT | | CustomerName | VARCHAR(100) |

   Order

   | Column | Data Type | |---------------|-------------------| | OrderID | INT | | CustomerID | INT | | OrderDate | DATE |

3. Challenge

   Natural Joins

   Step 3: Natural Joins

   In this step, you will learn how to use the NATURAL JOIN to join tables based on columns that share the same name and data type in both tables. A NATURAL JOIN automatically infers the join condition, simplifying queries when working with databases that have consistent naming conventions. You will practice using this technique with tables like Product and Review to join them on their common columns. This approach is useful when tables are designed with matching column names, allowing for simpler join conditions.

   Database Schema

   Product

   | Column | Data Type | |---------------|-------------------| | ProductID | INT | | ProductName | VARCHAR(255) | | Category | VARCHAR(100) | | ListPrice | DECIMAL(10, 2) |

   Review

   | Column | Data Type | |---------------|-------------------| | ReviewID | INT | | ProductID | INT | | ReviewText | TEXT |

   Customer

   | Column | Data Type | |---------------|-------------------| | CustomerID | INT | | CustomerName | VARCHAR(100) |

   Order

   | Column | Data Type | |---------------|-------------------| | OrderID | INT | | CustomerID | INT | | OrderDate | DATE |