All courses
Hamburger Icon
  • Labs icon Lab
  • Data
Labs

SQL Fundamentals: Techniques for Joining Tables

In this lab, you will learn essential SQL techniques for working with multiple tables in a database. You will practice using INNER JOIN, LEFT JOIN, RIGHT JOIN, and UNION to retrieve and combine data from different tables. Additionally, you'll explore how to retrieve distinct values and handle missing data effectively. By the end of the lab, you will have a solid understanding of how to query complex datasets using various join operations and filtering techniques.

Get started Contact sales
Labs

Path Info

Level
Clock icon Intermediate
Duration
Clock icon 1h 3m
Published
Clock icon Nov 14, 2024

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

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

    Order

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

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.
Ready to get started?
View individual plans View team plans