Production Relational SchemaBeginner → Advanced

E-commerce

Customers, products, orders, payments and order items.

6 Tables10K+ RecordsJoinsAggregationWindow Functions

Interactive SQL Playground & Query Runner

In-Browser Sandbox

Run realistic queries against the E-commerce database. Write custom SQL or solve high-yield interview problems.

Interview Challenge:

easy

Top Spenders with Total Order Volume

Find the total amount spent and number of orders placed by each customer. Return display_name, city, order_count, and total_spent in descending order of total_spent.

JOINGROUP BYSUM

query.sql

Press Ctrl + Enter to execute

Entity Relationship (ER) & Foreign Key Architecture

Referential integrity mappings and table cardinalities

5 Key Relations

1:NPlaces Orders

customers

shopper_id

orders

shopper_id

1:NContains Items

orders

purchase_id

order_items

purchase_id

1:NOrdered In

products

item_id

order_items

item_id

1:1Paid Via

orders

purchase_id

payments

purchase_id

1:NLocated In Zone

zones

city

customers

city

Relational Tables & Column Data Types

Field definitions, types, and primary keys for query construction

6 tables total

customers

3 cols

PKshopper_id

INT

display_name

VARCHAR(80)

city

VARCHAR(60)

products

4 cols

PKitem_id

INT

item_name

VARCHAR(100)

Runnable DDL Setup & Seed Script

Run directly in MySQL Workbench, pgAdmin, DBeaver, or terminal

CREATE DATABASE IF NOT EXISTS studyflow_ecommerce;
USE studyflow_ecommerce;

CREATE TABLE customers (
  shopper_id INT PRIMARY KEY,
  display_name VARCHAR(80) NOT NULL,
  city VARCHAR(60)
);

CREATE TABLE products (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(100) NOT NULL,
  category VARCHAR(60),
  unit_price DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
  purchase_id INT PRIMARY KEY,
  shopper_id INT,
  purchased_on DATE,
  order_total DECIMAL(10,2),
  FOREIGN KEY (shopper_id) REFERENCES customers(shopper_id)
);

CREATE TABLE order_items (
  line_id INT PRIMARY KEY,
  purchase_id INT,
  item_id INT,
  quantity INT,
  FOREIGN KEY (purchase_id) REFERENCES orders(purchase_id),
  FOREIGN KEY (item_id) REFERENCES products(item_id)
);

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  purchase_id INT,
  method VARCHAR(30),
  payment_status VARCHAR(20),
  FOREIGN KEY (purchase_id) REFERENCES orders(purchase_id)
);

CREATE TABLE zones (
  zone_id INT PRIMARY KEY,
  city VARCHAR(60),
  zone_name VARCHAR(80)
);

INSERT INTO customers VALUES
(1,'Ira','Indore'),(2,'Dev','Kochi'),(3,'Mira','Surat'),(4,'Kabir','Jaipur'),(5,'Tara','Pune');

INSERT INTO products VALUES
(1,'Orbit Keyboard','Accessories',1800),(2,'Pebble Mouse','Accessories',700),
(3,'Northstar Monitor','Displays',12000),(4,'Nova Headset','Audio',2400),
(5,'Atlas Stand','Accessories',1500);

INSERT INTO orders VALUES
(101,1,'2026-01-10',2500),(102,1,'2026-02-02',12000),(103,2,'2026-02-08',1800),
(104,3,'2026-02-14',3600),(105,4,'2026-03-01',14400),(106,1,'2026-03-18',3900),
(107,5,'2026-04-04',1500);

INSERT INTO order_items VALUES
(1,101,1,1),(2,101,2,1),(3,102,3,1),(4,103,1,1),
(5,104,4,1),(6,104,2,2),(7,105,3,1),(8,105,2,2),
(9,106,4,1),(10,106,2,1),(11,107,5,1);

INSERT INTO payments VALUES
(1,101,'UPI','completed'),(2,102,'Card','completed'),(3,103,'UPI','completed'),
(4,104,'Card','failed'),(5,105,'Card','completed'),(6,106,'UPI','completed');

INSERT INTO zones VALUES
(1,'Indore','Central'),(2,'Kochi','South'),(3,'Surat','West'),(4,'Jaipur','North'),(5,'Pune','West');