Practice & Assessment
Test your understanding of Joins — Combining Tables
Multiple Choice Questions
5An orders table has 100 rows. A customers table has 80 rows. 10 orders have a customer_id that doesn't exist in customers. How many rows does INNER JOIN return?
You want to find all products that have never appeared in any order. Which query pattern is correct?
What is the result of joining two tables with no ON condition (a cartesian product) when table A has 5 rows and table B has 4 rows?
You have employees and managers in the same table with a manager_id column. A new employee has no manager yet (manager_id is NULL). Which join returns this employee?
In a query joining orders, customers, and products, you write SELECT name. Which table's name column does the database use?
Coding Challenges
1E-commerce Order Detail Report
Given four tables — customers (id, name, email, country), orders (id, customer_id, created_at, status), order_items (id, order_id, product_id, quantity, unit_price), products (id, name, category, cost_price) — write a query that returns: order_id, customer name, customer country, list of product names (you may concatenate or pick one), total order value (sum of quantity * unit_price), total cost (sum of quantity * cost_price), and gross margin percentage ((revenue - cost) / revenue * 100). Include only completed orders. Show only orders with margin above 30%. Sort by margin descending. Expected output: 6 columns with alias names. Time estimate: 30 minutes.
Mini Project
Complete Order Analytics Schema
Design and populate five related tables: customers, products, categories, orders, order_items. Define proper primary keys and foreign keys. Insert at least 20 customers, 30 products across 4 categories, and 50 orders with 1-3 items each. Then write: (1) Full order history per customer using multi-table JOIN. (2) Products never ordered using LEFT JOIN and NULL filter. (3) Customer lifetime value ranking using JOIN to aggregated subquery and RANK window function. (4) Category performance report showing revenue, unit sales, and average order value. (5) Self-join query on orders table finding customers who placed two orders within 7 days of each other using a non-equi join condition on dates.