DB FOUNDATIONS – ASSIGNMENT 2
Online Food Ordering System
Student Name: S.sakthivel
Register Number: 23131050500121038
̀ Department: B.C.A
Submission Date: 21/08/2025
Objective
Design and implement a database for an online food ordering system using SQL
commands. Practice Data Definition Language (DDL), Data Manipulation Language
(DML), and Data Query Language (DQL) statements to manage and query data
efficiently.
Recommended Tools
You can use any SQL execution platform, but the following are recommended for
easy usage and compatibility:
1. MySQL Workbench
A GUI tool for MySQL database design and execution.
Available for Windows, macOS, and Linux.
Recommended for desktop users.
2. Oracle LiveSQL
Web-based SQL execution platform (no installation needed).
Runs in browsers on PC or mobile.
Best for quick practice and portability.
Part A – Database & Table Creation (DDL)
Customer
output
Restaurant
Output
FoodItem
Output
DeliveryAgent
Output
Orders
Output
OrderDetails
Output
Review
Output
Offer
Output
Part B – Insert Sample Data (DML)
Insert
Output
Restaurant
Output
FoodItem
Output
Part C – Write Queries (DQL)
Display all customers
SELECT * FROM Customer;
List all food items along with their restaurant names
SELECT f.name AS food_item, r.name AS restaurant_name
FROM FoodItem f
JOIN Restaurant r ON f.restaurant_id = r.id;
Show all orders placed by a specific customer
SELECT *
FROM Orders
WHERE customer_id = 1;
Find the total number of food items available in each restaurant
SELECT r.name AS restaurant_name, COUNT(f.id) AS total_items
FROM Restaurant r
JOIN FoodItem f ON r.id = f.restaurant_id
GROUP BY r.name;
Display orders with total amount greater than ₹500
SELECT *
FROM Orders
WHERE total_amount > 500;
List delivery agents who have delivered more than 3 orders
SELECT d.name AS delivery_agent, COUNT(o.id) AS total_orders
FROM DeliveryAgent d
JOIN Orders o ON d.id = o.delivery_agent_id
GROUP BY d.name
HAVING COUNT(o.id) > 3;
Show all reviews for a particular restaurant
SELECT *
FROM Review
WHERE restaurant_id = 2;
Find the most expensive food item and its restaurant
SELECT f.name AS food_item, f.price, r.name AS restaurant_name
FROM FoodItem f
JOIN Restaurant r ON f.restaurant_id = r.id
ORDER BY f.price DESC
LIMIT 1;
Display all offers currently valid today
SELECT *
FROM Offer
WHERE CURRENT_DATE BETWEEN start_date AND end_date;
Calculate the total revenue generated from all orders
SELECT SUM(total_amount) AS total_revenue
FROM Orders;
