SQL Joins Exercise

In this exercise, we’ll explore practical sql joins using a real-world scenario. We’ll learn how to use joins to combine data from multiple tables.

Create 2 tables for a chai store

We will create 2 tables for a chai store so that we can practice joins. The tables will be:

chai table

CREATE TABLE chai (
  chai_id SERIAL PRIMARY KEY,
  chai_name VARCHAR(50),
  price DECIMAL(5, 2)
);

Let’s add some sample data to the chai table:

INSERT INTO chai (chai_name, price)
VALUES ('Masala Chai', 30.00),
       ('Green Chai', 25.00),
       ('Iced Chai', 35.00);

orders table

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  chai_id INT,
  customer_name VARCHAR(50),
  quantity INT,
  FOREIGN KEY (chai_id) REFERENCES chai(chai_id)
);

Let’s add some sample data to the orders table:

INSERT INTO orders (chai_id, customer_name, quantity)
VALUES (1, 'Alice', 2),
       (2, 'Bob', 1),
       (1, 'Charlie', 3),
       (3, 'David', 1);

Challenges

Now, let’s try to solve some challenges using joins.

1. Inner Join
   Get the list of all orders with the chai variety and customer details.

2. Left Join
   Show all customers and their orders, but also include customers who haven’t ordered anything yet (if any).

3. Right Join
   Show all chai varieties, including those that haven’t been ordered yet.

4. Full Outer Join
   List all customers and all chai varieties, with or without orders.

Start your journey with ChaiCode

All of our courses are available on chaicode.com. Feel free to check them out.