Skip to content
Chai Docs

SQL Practice Exercise for joins

Chapter 6: Practice Exercise for joins

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

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

Join challenges

Inner Join

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

Left Join

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

Right Join

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

FULL OUTER JOIN

List all customers and all chai varieties, with or without orders.