Assignment 3-A

Complex Joins

Master the art of combining data from multiple tables. You'll work with a multi-table e-commerce database to write complex JOIN queries that solve real business problems.

4-5 hours
Intermediate
100 Points
What You'll Practice
  • INNER JOIN for matching records
  • LEFT/RIGHT OUTER JOINs
  • FULL OUTER JOIN operations
  • Self-joins for hierarchies
  • Multi-table complex queries
Contents
01

Assignment Overview

In this assignment, you will work with an E-Commerce Database containing customers, orders, products, categories, and order details. You'll write 15 JOIN queries ranging from simple to complex.

Skills Applied: This assignment tests your understanding of INNER JOIN (Topic 3.1), OUTER JOINs (Topic 3.2), and Advanced Techniques (Topic 3.3) from Module 3.
INNER JOIN

Matching records from both tables

OUTER JOINs

LEFT, RIGHT, FULL combinations

Self-Joins

Hierarchical relationships

02

Database Schema

You will work with the following related tables: 

-- Customers table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    city VARCHAR(50)
);

-- Categories table
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50),
    parent_category_id INT -- For self-join: hierarchical categories
);

-- Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT REFERENCES categories(category_id),
    price DECIMAL(10,2),
    stock_quantity INT
);

-- Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE,
    status VARCHAR(20)
);

-- Order Details table
CREATE TABLE order_details (
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);
03

Query Requirements

1
INNER JOIN Queries (5 queries)
  • List all orders with customer names
  • Show products with their category names
  • Get order details with product information
  • Join 3 tables: customers, orders, and order_details
  • Join 4 tables to show complete order information
2
OUTER JOIN Queries (5 queries)
  • Find customers who have never placed an order (LEFT JOIN)
  • List all products including those never ordered
  • Show categories with and without products
  • Find orders with missing customer data
  • FULL OUTER JOIN to find data mismatches
3
Advanced JOIN Queries (5 queries)
  • Self-join to show category hierarchies
  • Multi-condition join with additional filters
  • Cross join for combinations with filtering
  • Join with aggregation (preview of Module 4)
  • Complex business query using multiple join types
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-3-joins
  • Include schema.sql with table creation scripts
  • Include data.sql with sample data
  • Include queries.sql with all 15 JOIN queries
  • Add an ER diagram showing table relationships
05

Grading Rubric

Criteria Points
INNER JOIN queries (5 × 6 pts) 30
OUTER JOIN queries (5 × 6 pts) 30
Advanced JOIN queries (5 × 6 pts) 30
Code quality and documentation 10
Total 100