DAY 66 — SQL JOINs Part 1
Practical Assignment: INNER JOIN & LEFT JOIN in PostgreSQL
Course: Data Science & Data Analytics
Subject: Structured Query Language (SQL)
Database: PostgreSQL
Level: Beginner to Intermediate
Topic: SQL JOINs Part 1
Total Practice Questions: 10
1. Introduction
Welcome to Day 66 of our SQL learning journey!
Today, we learned one of the most important concepts in SQL: JOINs.
In real-world databases, information is usually stored in multiple related tables. For example, customer details are stored in one table, while their order details are stored in another table.
But what if we want to retrieve customer names along with their order details?
This is where SQL JOINs become useful.
SQL JOINs allow us to combine records from multiple tables using a related column.
In this practical assignment, you will work with two related tables:
- customers — Stores customer information.
- orders — Stores customer order information.
You will solve 10 practical SQL questions using PostgreSQL.
2. Learning Objectives
After completing this assignment, you should be able to:
- Understand relationships between multiple tables.
- Identify Primary Keys and Foreign Keys.
- Write INNER JOIN queries.
- Write LEFT JOIN queries.
- Use the ON condition correctly.
- Use table aliases in JOIN queries.
- Select columns from multiple tables.
- Identify unmatched records using NULL.
- Find customers who have never placed an order.
- Apply JOINs to real-world business problems.
3. Database Structure
We will create a simple Customer Management Database.
Table 1: customers
Stores information about customers.
| Column | Data Type | Description |
| customer_id | INT | Primary Key |
| name | VARCHAR(100) | Customer name |
| city | VARCHAR(100) | Customer city |
| VARCHAR(150) | Customer email |
Table 2: orders
Stores information about customer orders.
| Column | Data Type | Description |
| order_id | INT | Primary Key |
| customer_id | INT | Foreign Key |
| amount | NUMERIC(10,2) | Order amount |
| order_date | DATE | Order date |
Relationship:
- customers.customer_id → Primary Key
- orders.customer_id → Foreign Key
- One customer can place multiple orders.
- A customer can also have zero orders.
4. Create Database Tables
First, create the customers table.
SQL Code — Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(100),
email VARCHAR(150)
);
SQL Code — Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount NUMERIC(10,2),
order_date DATE,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
5. Insert Sample Records
Now insert sample records into both tables.
Insert Data into Customers
INSERT INTO customers (customer_id, name, city, email) VALUES (1, 'Rahul', 'Ranchi', '[email protected]'), (2, 'Amit', 'Delhi', '[email protected]'), (3, 'Priya', 'Patna', '[email protected]'), (4, 'Sneha', 'Kolkata', '[email protected]'), (5, 'Rohan', 'Mumbai', '[email protected]'), (6, 'Neha', 'Ranchi', '[email protected]'), (7, 'Karan', 'Bangalore', '[email protected]'), (8, 'Anjali', 'Pune', '[email protected]');
Insert Data into Orders
INSERT INTO orders (order_id, customer_id, amount, order_date) VALUES (101, 1, 2500, '2026-01-10'), (102, 2, 1500, '2026-01-12'), (103, 1, 3000, '2026-01-15'), (104, 3, 4000, '2026-01-18'), (105, 5, 2200, '2026-01-20'), (106, 2, 1800, '2026-01-22'), (107, 6, 5000, '2026-01-25'), (108, 5, 3500, '2026-01-28'), (109, 7, 1200, '2026-02-01'), (110, 3, 2700, '2026-02-05');
6. Verify Your Tables
Run these queries to check whether your records were inserted successfully.
SELECT * FROM customers; SELECT * FROM orders;
Expected Dataset Summary:
| Table | Total Records |
| customers | 8 |
| orders | 10 |
Important Observation:
Some customers have multiple orders, while some customers have never placed an order.
This will help you understand the difference between INNER JOIN and LEFT JOIN.
7. Practical Questions — Solve These 10 Problems
Try to solve all questions independently before asking for help.
Question 1 — Basic INNER JOIN
Write an SQL query to display:
- Customer name
- Customer city
- Order ID
Use INNER JOIN to combine the customers and orders tables.
Concept: INNER JOIN, SELECT, ON.
Question 2 — Customer Name and Order Amount
Display the following information:
- Customer name
- Order ID
- Order amount
Only display customers who have placed orders.
Concept: INNER JOIN.
Question 3 — Use Table Aliases
Write a query to display:
- Customer name
- Customer email
- Order amount
Use table aliases:
- c for customers
- o for orders
Concept: Table Aliases, INNER JOIN.
Question 4 — Select Columns from Multiple Tables
Display the following columns:
- Customer ID
- Customer name
- Customer city
- Order ID
- Order date
- Order amount
Use INNER JOIN.
Make sure you qualify column names using table names or aliases.
Concept: Table name with column name, INNER JOIN.
Question 5 — LEFT JOIN Practice
Write a query to display all customers, including customers who have never placed an order.
Display:
- Customer ID
- Customer name
- City
- Order ID
- Order amount
Use LEFT JOIN.
Concept: LEFT JOIN, NULL values.
Question 6 — Find Customers Without Orders
The company wants to identify customers who have never purchased anything.
Write a query to display:
- Customer ID
- Customer name
- Customer city
Only show customers who do not have matching orders.
Hint: Use LEFT JOIN and IS NULL.
Concept: LEFT JOIN, WHERE, IS NULL.
Question 7 — Filter Orders by Amount
Display customer names, order IDs, and order amounts.
Only display orders where the order amount is greater than ₹2,500.
Concept: INNER JOIN, WHERE, comparison operator.
Question 8 — Filter Customers by City
Display:
- Customer name
- Customer city
- Order ID
- Order amount
Only display customers from Ranchi who have placed orders.
Concept: INNER JOIN, WHERE.
Question 9 — Sort Joined Records
Write a query to display:
- Customer name
- Order ID
- Order amount
Display all matching customer orders and sort the results by order amount in descending order.
Concept: INNER JOIN, ORDER BY, DESC.
Question 10 — Business-Level Challenge
A company wants to generate a report of all its customers and their order details.
Write a query to display:
- Customer ID
- Customer name
- Customer city
- Order ID
- Order amount
Requirements:
- Display all customers.
- Include their matching orders.
- Customers without orders must also appear.
- Show NULL when no matching order exists.
- Sort the results by customer ID in ascending order.
Concept: LEFT JOIN, Aliases, ORDER BY.
8. Important SQL Concepts to Remember
| SQL Concept | Purpose |
| INNER JOIN | Returns only matching records |
| LEFT JOIN | Returns all left-table records and matching right-table records |
| ON | Defines the joining condition |
| AS | Creates a table alias |
| WHERE | Filters records |
| IS NULL | Checks for NULL values |
| ORDER BY | Sorts the result |
9. Assignment Instructions
- Create both tables in PostgreSQL.
- Insert all sample records.
- Verify the records using SELECT queries.
- Solve all 10 questions.
- Execute each query in pgAdmin 4.
- Check your output carefully.
- Save your SQL queries for future revision.
Important: Do not copy solutions directly. First understand the question, identify the required JOIN type, and then write your SQL query.
10. Final Challenge
Think like a Data Analyst!
A business wants to understand customer purchasing behaviour.
Using the customers and orders tables, think about these questions:
- Who are the customers who purchase frequently?
- Who has never purchased anything?
- Which customers have placed high-value orders?
- How can we combine customer information with transaction records?
These are the types of problems SQL JOINs help solve in real-world companies.
Keep Practising. Keep Learning. Keep Building!



