Food Delivery SQL Project

A relational database project simulating a food delivery platform (like Zomato/Swiggy) — built in MySQL, covering schema design, data seeding, and a growing set of practice queries from basic filters through joins, subqueries, and correlated subqueries.

Note: This project is actively being expanded. The schema and data are complete; the queries file is a work in progress — some advanced subquery questions are still marked as pending.

Overview

This project models a food delivery ecosystem with customers, restaurants, menu items, orders, delivery partners, payments, reviews, and coupons. It was built to practice SQL concepts progressively — starting from basic SELECT statements and working up through joins (INNER, LEFT, RIGHT, multi-table), aggregate functions, GROUP BY/HAVING, subqueries, and correlated subqueries.

Database Schema

The database (FoodDelivery) consists of 10 tables:

Table	Description
Customers	Customer details — name, contact, city, signup date, account status
RestaurantCategories	Cuisine/restaurant category names
Restaurants	Restaurant details — category, city, rating, operating hours, status
MenuItems	Menu items per restaurant — name, price, veg/non-veg, availability
Orders	Customer orders — restaurant, amount, status, delivery partner
OrderItems	Line items per order (menu item, quantity, price)
DeliveryPartners	Delivery partner details — vehicle type, rating, status
Payments	Payment records linked to orders
Reviews	Customer reviews and ratings per restaurant
Coupons	Discount coupons — code, percentage, expiry, minimum order value

Relationships:

• Restaurants → RestaurantCategories (many-to-one)
• MenuItems → Restaurants (many-to-one)
• Orders → Customers, Restaurants, DeliveryPartners (many-to-one each)
• OrderItems → Orders, MenuItems (many-to-one each)
• Payments → Orders (one-to-one)
• Reviews → Customers, Restaurants (many-to-one each, unique per customer-restaurant pair)

Files

File	Description
01_database_and_tables.sql	Creates the database, all 10 tables with constraints and foreign keys, plus later ALTER TABLE fixes
02_data_insertion.sql	Seeds all tables with sample data
03_queries.sql	Practice queries — basic filters through subqueries and correlated subqueries

Setup

Run the files in order in your MySQL client:

SOURCE 01_database_and_tables.sql;
SOURCE 02_data_insertion.sql;

Then run any query from 03_queries.sql individually.

Query Progression

The queries file is organized by concept, building in complexity:

• Basic SELECT / WHERE — active customers, restaurants above a rating, menu items above a price
• ORDER BY / LIMIT — most expensive menu items, latest orders
• DISTINCT — unique cities, payment methods, order statuses
• Aggregate functions — total revenue, average menu price, highest/lowest order
• GROUP BY — restaurants per category, orders per customer, revenue per restaurant
• HAVING — restaurants with more than 10 menu items, customers with more than 5 orders
• INNER / LEFT / RIGHT JOIN — customer-order-payment chains, restaurants with no reviews, delivery partners with no deliveries
• Multi-table JOIN — customer → order → payment, restaurant → menu → order items
• Subqueries & correlated subqueries — restaurants above average rating, customers who spent more than the city average (some questions in this section are still in progress — marked with TODO)

Tech Stack

• MySQL
• Standard SQL (joins, subqueries, correlated subqueries, aggregate functions, GROUP BY/HAVING)

Status

Actively in progress — the schema and core query set (basic filters through joins) are complete. The subquery and correlated subquery sections are being finished incrementally.

Author

Ritwal Chouhan — GitHub