FoodOrderingSystem SQL Script with Outputs
SQL Script: FoodOrderingSystem
-- ========================
-- PART A – DATABASE & TABLE CREATION
-- ========================
CREATE DATABASE FoodOrderingSystem;
USE FoodOrderingSystem;
CREATE TABLE Customer (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(15) NOT NULL UNIQUE,
address VARCHAR(255) NOT NULL
);
CREATE TABLE Restaurant (
restaurant_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
location VARCHAR(100) NOT NULL,
contact_number VARCHAR(15) NOT NULL UNIQUE
);
CREATE TABLE FoodItem (
item_id INT PRIMARY KEY AUTO_INCREMENT,
restaurant_id INT NOT NULL,
item_name VARCHAR(100) NOT NULL,
price DECIMAL(8,2) NOT NULL,
category VARCHAR(50),
FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id)
);
CREATE TABLE DeliveryAgent (
agent_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
phone VARCHAR(15) NOT NULL UNIQUE
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);
CREATE TABLE OrderDetails (
order_id INT NOT NULL,
item_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, item_id),
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
FOREIGN KEY (item_id) REFERENCES FoodItem(item_id)
);
CREATE TABLE Review (
review_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
restaurant_id INT NOT NULL,
rating INT CHECK (rating BETWEEN 1 AND 5),
comments TEXT,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id)
);
CREATE TABLE Offer (
offer_id INT PRIMARY KEY AUTO_INCREMENT,
restaurant_id INT NOT NULL,
item_id INT,
discount_percent DECIMAL(5,2) NOT NULL,
valid_till DATE NOT NULL,
FOREIGN KEY (restaurant_id) REFERENCES Restaurant(restaurant_id),
FOREIGN KEY (item_id) REFERENCES FoodItem(item_id)
);
-- ========================
-- PART B – SAMPLE DATA INSERTS
-- ========================
INSERT INTO Customer (name, email, phone, address) VALUES
('Amit Sharma', 'amit@[Link]', '9876543210', 'Delhi, India'),
('Priya Verma', 'priya@[Link]', '9876501234', 'Mumbai, India'),
('Rajesh Gupta', 'rajesh@[Link]', '9876505678', 'Kolkata, India'),
('Sneha Iyer', 'sneha@[Link]', '9876598765', 'Chennai, India'),
('Karan Singh', 'karan@[Link]', '9876523456', 'Pune, India');
INSERT INTO Restaurant (name, location, contact_number) VALUES
('Spicy Treats', 'Delhi', '9999000001'),
('Food Paradise', 'Mumbai', '9999000002'),
('Tandoori Tales', 'Kolkata', '9999000003');
INSERT INTO FoodItem (restaurant_id, item_name, price, category) VALUES
(1, 'Paneer Butter Masala', 250.00, 'Main Course'),
(1, 'Butter Naan', 50.00, 'Bread'),
(2, 'Veg Biryani', 200.00, 'Main Course'),
(2, 'Chicken Curry', 300.00, 'Main Course'),
(3, 'Tandoori Roti', 30.00, 'Bread'),
(3, 'Chicken Tikka', 350.00, 'Starter'),
(1, 'Masala Dosa', 150.00, 'Breakfast'),
(2, 'Gulab Jamun', 80.00, 'Dessert');
INSERT INTO DeliveryAgent (name, phone) VALUES
('Ravi Kumar', '8888800001'),
('Pooja Mehta', '8888800002'),
('Sunil Joshi', '8888800003');
INSERT INTO Orders (customer_id, order_date, total_amount, status) VALUES
(1, '2025-08-01', 550.00, 'Delivered'),
(2, '2025-08-02', 300.00, 'Delivered'),
(3, '2025-08-05', 800.00, 'Delivered'),
(4, '2025-08-06', 150.00, 'Pending'),
(5, '2025-08-07', 700.00, 'Delivered');
INSERT INTO OrderDetails (order_id, item_id, quantity) VALUES
(1, 1, 2),
(1, 2, 3),
(2, 3, 1),
(3, 4, 2),
(3, 6, 1),
(4, 5, 5),
(5, 6, 2),
(5, 8, 3);
INSERT INTO Review (customer_id, restaurant_id, rating, comments) VALUES
(1, 1, 5, 'Amazing food!'),
(2, 2, 4, 'Great taste, but delivery was late.'),
(3, 3, 5, 'Excellent service!');
INSERT INTO Offer (restaurant_id, item_id, discount_percent, valid_till) VALUES
(1, 1, 10.00, '2025-08-20'),
(2, 4, 15.00, '2025-08-15');
-- ========================
-- PART C – QUERIES
-- ========================
SELECT * FROM Customer;
SELECT f.item_name, [Link], [Link] AS restaurant_name
FROM FoodItem f
JOIN Restaurant r ON f.restaurant_id = r.restaurant_id;
SELECT * FROM Orders
WHERE customer_id = (SELECT customer_id FROM Customer WHERE name = 'Amit Sharma');
SELECT [Link] AS restaurant_name, COUNT(f.item_id) AS total_items
FROM Restaurant r
JOIN FoodItem f ON r.restaurant_id = f.restaurant_id
GROUP BY r.restaurant_id;
SELECT * FROM Orders
WHERE total_amount > 500;
SELECT [Link] AS customer_name, [Link], [Link]
FROM Review r
JOIN Customer c ON r.customer_id = c.customer_id
WHERE r.restaurant_id = (SELECT restaurant_id FROM Restaurant WHERE name = 'Spicy Treats');
SELECT f.item_name, [Link], [Link] AS restaurant_name
FROM FoodItem f
JOIN Restaurant r ON f.restaurant_id = r.restaurant_id
ORDER BY [Link] DESC
LIMIT 1;
SELECT * FROM Offer
WHERE valid_till >= CURDATE();
SELECT SUM(total_amount) AS total_revenue
FROM Orders;
Sample Outputs
-- ========================
-- SAMPLE OUTPUTS FOR QUERIES
-- ========================
1. Display all customers:
+-------------+-------------+-------------------+-------------+----------------+
| customer_id | name | email | phone | address |
+-------------+-------------+-------------------+-------------+----------------+
| 1 | Amit Sharma | amit@[Link] | 9876543210 | Delhi, India |
| 2 | Priya Verma | priya@[Link] | 9876501234 | Mumbai, India |
| 3 | Rajesh Gupta| rajesh@[Link]| 9876505678 | Kolkata, India |
| 4 | Sneha Iyer | sneha@[Link] | 9876598765 | Chennai, India |
| 5 | Karan Singh | karan@[Link] | 9876523456 | Pune, India |
+-------------+-------------+-------------------+-------------+----------------+
2. List all food items along with restaurant names:
+-----------------------+--------+-----------------+
| item_name | price | restaurant_name |
+-----------------------+--------+-----------------+
| Paneer Butter Masala | 250.00 | Spicy Treats |
| Butter Naan | 50.00 | Spicy Treats |
| Veg Biryani | 200.00 | Food Paradise |
| Chicken Curry | 300.00 | Food Paradise |
| Tandoori Roti | 30.00 | Tandoori Tales |
| Chicken Tikka | 350.00 | Tandoori Tales |
| Masala Dosa | 150.00 | Spicy Treats |
| Gulab Jamun | 80.00 | Food Paradise |
+-----------------------+--------+-----------------+
3. Show all orders placed by Amit Sharma:
+----------+-------------+------------+--------------+-----------+
| order_id | customer_id | order_date | total_amount | status |
+----------+-------------+------------+--------------+-----------+
| 1 | 1 | 2025-08-01 | 550.00 | Delivered |
+----------+-------------+------------+--------------+-----------+
4. Total number of food items available in each restaurant:
+-----------------+--------------+
| restaurant_name | total_items |
+-----------------+--------------+
| Spicy Treats | 3 |
| Food Paradise | 3 |
| Tandoori Tales | 2 |
+-----------------+--------------+
5. Orders with total amount greater than ■500:
+----------+-------------+------------+--------------+-----------+
| order_id | customer_id | order_date | total_amount | status |
+----------+-------------+------------+--------------+-----------+
| 1 | 1 | 2025-08-01 | 550.00 | Delivered |
| 3 | 3 | 2025-08-05 | 800.00 | Delivered |
| 5 | 5 | 2025-08-07 | 700.00 | Delivered |
+----------+-------------+------------+--------------+-----------+
7. Reviews for Spicy Treats:
+--------------+--------+----------------+
| customer_name| rating | comments |
+--------------+--------+----------------+
| Amit Sharma | 5 | Amazing food! |
+--------------+--------+----------------+
8. Most expensive food item:
+---------------+--------+----------------+
| item_name | price | restaurant_name|
+---------------+--------+----------------+
| Chicken Tikka | 350.00 | Tandoori Tales |
+---------------+--------+----------------+
9. Current valid offers:
+----------+---------------+--------+-------------+
| offer_id | restaurant_id | item_id| valid_till |
+----------+---------------+--------+-------------+
| 1 | 1 | 1 | 2025-08-20 |
| 2 | 2 | 4 | 2025-08-15 |
+----------+---------------+--------+-------------+
10. Total revenue from all orders:
+---------------+
| total_revenue |
+---------------+
| 2500.00 |
+---------------+