SQL Practice Dataset: the ShopKart E-commerce Database

Q: Can I use this dataset in MySQL?

Mostly, yes. The script uses standard data types, so it should load in MySQL 8 too. One difference: MySQL ignores foreign keys written inline next to a column, so they are not enforced there. A few lessons use PostgreSQL-only functions; each lesson points these out.

Q: Is the data real?

No. ShopKart is a fictional store. Names, orders and prices are made up, but they are designed to behave like real e-commerce data, including cancelled orders, missing phone numbers and customers who never ordered.

By Skillancy Editorial Team

Every example runs on the ShopKart dataset, already loaded in the free SQL Compiler. Click Try it in the SQL Compiler under a query to open it there, then press Run.

Every lesson in this SQL tutorial uses the same small database: ShopKart, a fictional Indian online store. Using one dataset throughout means you learn SQL concepts without re-learning a new table every lesson.

How to use it

ShopKart is already loaded in the free Skillancy SQL Compiler. Open it, paste any query from these lessons and run it. You'll get exactly the results shown on the page.

The ShopKart tables appear in the compiler's View Schema panel, under "ShopKart (SQL tutorial dataset)".
Click Reset Database to restore the original data, for example after the INSERT, UPDATE and DELETE lessons.
Want to practise in your own database instead? Copy the setup script at the bottom of this page.

The tables

Table	One row is…	Rows	Key columns
`customers`	a registered shopper	12	`customer_id`, `city`, `signup_date`, `is_prime`
`categories`	a product category	6	`category_id`, `category_name`
`products`	an item for sale	15	`product_id`, `category_id`, `price`, `cost_price`
`orders`	a checkout	24	`order_id`, `customer_id`, `order_date`, `status`
`order_items`	one product line in an order	38	`order_id`, `product_id`, `quantity`, `unit_price`, `discount_pct`
`employees`	a ShopKart staff member	12	`employee_id`, `manager_id`, `department`, `salary`

How the tables connect

orders.customer_id → customers.customer_id (who placed the order)
order_items.order_id → orders.order_id (which order the line belongs to)
order_items.product_id → products.product_id (which product was bought)
products.category_id → categories.category_id (what kind of product it is)
employees.manager_id → employees.employee_id (who each person reports to)

customers ──< orders ──< order_items >── products >── categories
employees ──< employees (manager_id)

──< means "one to many": one customer can have many orders, one order can have many items.

Things worth knowing about the data

These are deliberate, because real data is messy and interview questions love them:

Order status is one of Delivered, Shipped, Pending, Cancelled or Returned. Revenue questions usually count only Delivered orders.
delivery_date is NULL for orders that haven't been delivered yet (and for cancelled ones).
Some customers have no phone or email (NULL values).
Customer 12 (Divya Menon) has never placed an order, which is useful for LEFT JOIN lessons.
Product 115 (Cricket Bat) has never been sold and has no brand.
unit_price is the price at the time of purchase, and discount_pct is a percentage (10 means 10% off).
Line revenue = quantity * unit_price * (1 - discount_pct / 100).
Orders run from January 2025 to February 2026.

A quick look at each table

SELECT * FROM orders LIMIT 5;

Try it in the SQL Compiler

Result:

order_id	customer_id	order_date	status	delivery_date	payment_method
1001	1	2025-01-05	Delivered	2025-01-08	UPI
1002	2	2025-01-18	Delivered	2025-01-23	Card
1003	3	2025-02-02	Delivered	2025-02-05	UPI
1004	1	2025-02-20	Cancelled	NULL	Card
1005	4	2025-03-11	Delivered	2025-03-14	NetBanking

(5 rows)

SELECT * FROM order_items LIMIT 5;

Try it in the SQL Compiler

Result:

order_item_id	order_id	product_id	quantity	unit_price	discount_pct
1	1001	101	1	23999.00	10.00
2	1001	102	2	1299.00	0.00
3	1002	105	1	8499.00	5.00
4	1003	103	1	31990.00	15.00
5	1003	108	3	549.00	0.00

(5 rows)

SELECT employee_id, employee_name, department, manager_id, salary
FROM employees;

Try it in the SQL Compiler

Result:

employee_id	employee_name	department	manager_id	salary
1	Rajesh Khanna	Leadership	NULL	450000.00
2	Neha Joshi	Operations	1	220000.00
3	Amit Verma	Technology	1	260000.00
4	Pooja Sinha	Marketing	1	180000.00
5	Suresh Rao	Operations	2	95000.00
6	Kavya Pillai	Operations	2	88000.00
7	Manish Tiwari	Technology	3	150000.00
8	Ritika Bose	Technology	3	150000.00
9	Deepak Yadav	Technology	7	75000.00
10	Sana Qureshi	Marketing	4	70000.00
11	Harish Gowda	Operations	5	45000.00
12	Tanvi Shah	Marketing	4	82000.00

(12 rows)

Full setup script

You don't need this in the Skillancy SQL Compiler, where ShopKart is preloaded. Use it to create the same tables in your own PostgreSQL database (or MySQL 8, with the small differences noted in the FAQ). Running it again resets the data to its original state.

Show the full ShopKart SQL script

-- Skillancy SQL Tutorials: practice dataset ("ShopKart", a fictional Indian e-commerce store)
-- Dialect: PostgreSQL (runs as-is in the Skillancy SQL Compiler)

DROP TABLE IF EXISTS order_items, orders, products, categories, customers, employees CASCADE;

CREATE TABLE categories (
  category_id   INT PRIMARY KEY,
  category_name VARCHAR(50) NOT NULL
);

CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  email         VARCHAR(100) UNIQUE,
  phone         VARCHAR(15),
  city          VARCHAR(50),
  state         VARCHAR(50),
  signup_date   DATE NOT NULL,
  is_prime      BOOLEAN DEFAULT FALSE
);

CREATE TABLE products (
  product_id    INT PRIMARY KEY,
  product_name  VARCHAR(100) NOT NULL,
  category_id   INT REFERENCES categories(category_id),
  brand         VARCHAR(50),
  price         NUMERIC(10,2) NOT NULL,
  cost_price    NUMERIC(10,2) NOT NULL,
  stock_qty     INT NOT NULL,
  launch_date   DATE
);

CREATE TABLE orders (
  order_id       INT PRIMARY KEY,
  customer_id    INT REFERENCES customers(customer_id),
  order_date     DATE NOT NULL,
  status         VARCHAR(20) NOT NULL,
  delivery_date  DATE,
  payment_method VARCHAR(20) NOT NULL
);

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY,
  order_id      INT REFERENCES orders(order_id),
  product_id    INT REFERENCES products(product_id),
  quantity      INT NOT NULL,
  unit_price    NUMERIC(10,2) NOT NULL,
  discount_pct  NUMERIC(5,2) DEFAULT 0
);

CREATE TABLE employees (
  employee_id   INT PRIMARY KEY,
  employee_name VARCHAR(100) NOT NULL,
  department    VARCHAR(50) NOT NULL,
  manager_id    INT REFERENCES employees(employee_id),
  salary        NUMERIC(10,2) NOT NULL,
  hire_date     DATE NOT NULL,
  city          VARCHAR(50)
);

INSERT INTO categories VALUES
(1,'Electronics'),(2,'Fashion'),(3,'Home & Kitchen'),(4,'Beauty'),(5,'Books'),(6,'Sports');

INSERT INTO customers VALUES
(1,'Aarav Sharma','aarav.sharma@gmail.com','9876543210','Bengaluru','Karnataka','2024-03-15',TRUE),
(2,'Priya Nair','priya.nair@yahoo.com','9845012345','Kochi','Kerala','2024-05-22',FALSE),
(3,'Rohan Gupta','rohan.g@outlook.com',NULL,'Delhi','Delhi','2024-07-01',TRUE),
(4,'Sneha Reddy','sneha.reddy@gmail.com','9988776655','Hyderabad','Telangana','2024-08-10',TRUE),
(5,'Vikram Singh','vikram.singh@gmail.com','9123456780','Jaipur','Rajasthan','2024-09-05',FALSE),
(6,'Ananya Iyer','ananya.iyer@gmail.com','9001122334','Chennai','Tamil Nadu','2024-11-18',FALSE),
(7,'Karan Mehta','karan.mehta@rediffmail.com',NULL,'Mumbai','Maharashtra','2025-01-09',TRUE),
(8,'Ishita Das','ishita.das@gmail.com','9830098300','Kolkata','West Bengal','2025-02-14',FALSE),
(9,'Arjun Patel','arjun.patel@gmail.com','9727001234','Ahmedabad','Gujarat','2025-04-03',FALSE),
(10,'Meera Kulkarni','meera.k@gmail.com','9822012345','Pune','Maharashtra','2025-06-20',TRUE),
(11,'Farhan Khan','farhan.khan@gmail.com','9811223344','Lucknow','Uttar Pradesh','2025-08-11',FALSE),
(12,'Divya Menon',NULL,'9447001122','Bengaluru','Karnataka','2025-10-02',FALSE);

INSERT INTO products VALUES
(101,'Redmi Note 13 Pro',1,'Xiaomi',23999.00,19500.00,40,'2024-01-04'),
(102,'boAt Airdopes 141',1,'boAt',1299.00,700.00,250,'2023-06-15'),
(103,'Samsung 43" Smart TV',1,'Samsung',31990.00,26000.00,12,'2024-04-20'),
(104,'Men''s Cotton Kurta',2,'Fabindia',1899.00,950.00,80,'2024-02-10'),
(105,'Women''s Silk Saree',2,'Nalli',8499.00,5200.00,25,'2023-10-01'),
(106,'Running Shoes',2,'Campus',2499.00,1400.00,60,'2024-07-12'),
(107,'Prestige Pressure Cooker 5L',3,'Prestige',2199.00,1500.00,45,'2023-03-18'),
(108,'Steel Water Bottle 1L',3,'Milton',549.00,250.00,300,'2023-01-05'),
(109,'Non-stick Tawa',3,'Hawkins',899.00,500.00,70,'2024-05-25'),
(110,'Herbal Face Wash',4,'Himalaya',199.00,90.00,500,'2023-02-14'),
(111,'Kumkumadi Face Oil',4,'Kama Ayurveda',2995.00,1600.00,30,'2024-09-09'),
(112,'The Psychology of Money',5,'Jaico',299.00,150.00,120,'2023-08-01'),
(113,'Wings of Fire',5,'Universities Press',350.00,180.00,90,'2023-08-01'),
(114,'Yoga Mat 6mm',6,'Boldfit',799.00,350.00,150,'2024-03-01'),
(115,'Cricket Bat (Kashmir Willow)',6,NULL,3499.00,2100.00,20,'2025-02-01');

INSERT INTO orders VALUES
(1001,1,'2025-01-05','Delivered','2025-01-08','UPI'),
(1002,2,'2025-01-18','Delivered','2025-01-23','Card'),
(1003,3,'2025-02-02','Delivered','2025-02-05','UPI'),
(1004,1,'2025-02-20','Cancelled',NULL,'Card'),
(1005,4,'2025-03-11','Delivered','2025-03-14','NetBanking'),
(1006,5,'2025-03-29','Returned','2025-04-02','COD'),
(1007,6,'2025-04-15','Delivered','2025-04-19','UPI'),
(1008,7,'2025-05-03','Delivered','2025-05-06','Card'),
(1009,3,'2025-05-21','Delivered','2025-05-24','UPI'),
(1010,8,'2025-06-09','Delivered','2025-06-15','COD'),
(1011,1,'2025-06-30','Delivered','2025-07-02','UPI'),
(1012,9,'2025-07-14','Cancelled',NULL,'UPI'),
(1013,4,'2025-08-02','Delivered','2025-08-05','Card'),
(1014,10,'2025-08-19','Delivered','2025-08-22','UPI'),
(1015,7,'2025-09-07','Returned','2025-09-11','Card'),
(1016,2,'2025-09-25','Delivered','2025-09-30','UPI'),
(1017,5,'2025-10-12','Delivered','2025-10-16','COD'),
(1018,11,'2025-10-28','Delivered','2025-11-02','UPI'),
(1019,1,'2025-11-15','Delivered','2025-11-17','Card'),
(1020,10,'2025-12-03','Shipped',NULL,'UPI'),
(1021,3,'2025-12-20','Delivered','2025-12-23','NetBanking'),
(1022,6,'2026-01-08','Shipped',NULL,'Card'),
(1023,4,'2026-01-26','Pending',NULL,'UPI'),
(1024,9,'2026-02-10','Pending',NULL,'COD');

INSERT INTO order_items VALUES
(1,1001,101,1,23999.00,10),
(2,1001,102,2,1299.00,0),
(3,1002,105,1,8499.00,5),
(4,1003,103,1,31990.00,15),
(5,1003,108,3,549.00,0),
(6,1004,106,1,2499.00,0),
(7,1005,111,1,2995.00,10),
(8,1005,110,4,199.00,0),
(9,1006,104,2,1899.00,0),
(10,1007,107,1,2199.00,5),
(11,1007,109,1,899.00,0),
(12,1008,102,1,1299.00,0),
(13,1008,114,2,799.00,10),
(14,1009,112,2,299.00,0),
(15,1009,113,1,350.00,0),
(16,1010,105,1,8499.00,10),
(17,1011,106,1,2499.00,20),
(18,1011,108,2,549.00,0),
(19,1012,101,1,23999.00,0),
(20,1013,104,1,1899.00,0),
(21,1013,110,2,199.00,0),
(22,1014,111,1,2995.00,0),
(23,1014,114,1,799.00,0),
(24,1015,103,1,31990.00,10),
(25,1016,109,2,899.00,0),
(26,1016,107,1,2199.00,0),
(27,1017,112,3,299.00,0),
(28,1018,101,1,23999.00,5),
(29,1018,102,1,1299.00,0),
(30,1019,110,3,199.00,0),
(31,1019,113,2,350.00,0),
(32,1020,106,1,2499.00,10),
(33,1021,102,2,1299.00,0),
(34,1021,114,1,799.00,0),
(35,1022,105,1,8499.00,0),
(36,1023,108,4,549.00,0),
(37,1024,104,1,1899.00,5),
(38,1024,109,1,899.00,0);

INSERT INTO employees VALUES
(1,'Rajesh Khanna','Leadership',NULL,450000.00,'2019-04-01','Bengaluru'),
(2,'Neha Joshi','Operations',1,220000.00,'2020-06-15','Bengaluru'),
(3,'Amit Verma','Technology',1,260000.00,'2020-01-10','Hyderabad'),
(4,'Pooja Sinha','Marketing',1,180000.00,'2021-03-22','Mumbai'),
(5,'Suresh Rao','Operations',2,95000.00,'2021-09-01','Bengaluru'),
(6,'Kavya Pillai','Operations',2,88000.00,'2022-02-14','Chennai'),
(7,'Manish Tiwari','Technology',3,150000.00,'2021-07-19','Hyderabad'),
(8,'Ritika Bose','Technology',3,150000.00,'2022-11-01','Kolkata'),
(9,'Deepak Yadav','Technology',7,75000.00,'2023-05-08','Hyderabad'),
(10,'Sana Qureshi','Marketing',4,70000.00,'2023-08-21','Mumbai'),
(11,'Harish Gowda','Operations',5,45000.00,'2024-01-15','Bengaluru'),
(12,'Tanvi Shah','Marketing',4,82000.00,'2024-06-03','Ahmedabad');

Frequently asked questions

Can I use this dataset in MySQL?

Mostly, yes. The script uses standard data types, so it should load in MySQL 8 too. One difference: MySQL ignores foreign keys written inline next to a column, so they are not enforced there. A few lessons use PostgreSQL-only functions; each lesson points these out.

Is the data real?

No. ShopKart is a fictional store. Names, orders and prices are made up, but they are designed to behave like real e-commerce data, including cancelled orders, missing phone numbers and customers who never ordered.

← PreviousWhat is SQL?Next →SQL SELECT Statement

Newsletter

Keep learning SQL

One email a week with SQL and analytics guides, new practice questions and cohort dates. No spam, unsubscribe anytime.

Prefer a one-page summary? Download the free SQL cheat sheet (PDF)