03_queries.sql

-- Basic SELECT

-- Q1 Show all active customers.
select * from customers
where Status = 'active';

-- Q2 Display restaurant names, city and average rating.
select RestaurantName, city, RATING 
from Restaurants;


-- Q3 List all menu items costing more than ₹300.
select ItemName, Price
from menuitems
where price > 300;

-- Q4 Find all restaurants that are currently open.
select RestaurantName,Status
from Restaurants
where Status = 'Open';

-- Q5 Display customers who signed up after January 1, 2023.
select CustomerID, FirstName, SignupDate
from customers 
where SignupDate > '2023-01-01' ;

-- WHERE + ORDER BY + LIMIT 

-- Q6 Show the 10 most expensive menu items.
select ItemName, price 
from menuitems
order by price desc
limit 10;

-- Q7 Find all orders whose total amount is greater than ₹1000.
select OrderID, TotalAmount
from orders 
where TotalAmount > 1000;

-- Q8 Display restaurants with ratings above 4.5 sorted by rating.
select RestaurantName, Rating
from restaurants
where rating > 4.5
order by Rating asc;

-- Q9 Find customers from Jaipur.
select FirstName, city
from customers 
where city = 'jaipur';

-- Q10 Display the latest 10 orders.
select OrderID, OrderDate 
from orders
order by OrderDate desc
limit 10;

-- DISTINCT
-- Q11 Find all unique cities where customers live.
select distinct (City)
from customers;

-- Q12 List different restaurant categories.
select distinct (CategoryName)
from RestaurantCategories;

-- Q13 Find all distinct payment methods.
select distinct PaymentMethod
from Payments;


-- Q14 Display unique order statuses.
select distinct OrderStatus
from orders;


-- Q15 Find all cities where restaurants operate.
select distinct City
from restaurants;

-- ========================================================================================
-- Aggregate Functions

-- Q16 Total revenue generated.
select sum(TotalAmount) as Revenue
from orders;


-- Q17 Average menu price.
select avg(Price) 
from MenuItems;

-- Q18 Highest order amount.
select max(TotalAmount)
from orders;

-- Q19 Lowest order amount.
select min(TotalAmount)
from orders;

-- Q20 Number of registered customers.
select count(customerid)
from customers;

-- ========================================================================================
-- GROUP BY

-- Q21 Number of restaurants in each category.
select CategoryID, count(RestaurantID)
from restaurants
group by (CategoryID);

-- Q22 Total orders placed by each customer.
select CustomerID, count(OrderID)
from orders
group by (CustomerID);

-- Q23 Average menu price for each restaurant.
Select RestaurantID, avg(Price) as AvgPrice
from MenuItems
group by RestaurantID;

-- Q24 Revenue generated by each restaurant.
select restaurantid, sum(TotalAmount) as Reveneue
from orders
group by RestaurantID;

-- Q25 Number of reviews each restaurant received
select RestaurantID, count(ReviewID)
from reviews
group by RestaurantID;

-- ========================================================================================

-- HAVING CLAUSE

-- Q26 Restaurants with more than 10 menu items.

select RestaurantID, count(MenuItemID)
from menuitems
group by RestaurantID
having count(MenuItemID) > 10;



-- Q27 Customers who placed more than 5 orders.
select customerID, count(orderid) as TotalOrders
from orders
group by CustomerID
having TotalOrders > 5;




-- Q28 Restaurants earning more than ₹10,000.
select RestaurantID, sum(TotalAmount)
from orders
group by RestaurantID
having sum(TotalAmount) > 10000;



-- Q29 Categories having more than 3 restaurants.
select CategoryID ,count(RestaurantID) as TotalRestaurants 
from restaurants
group by CategoryID
having count(restaurantID) > 3;



-- Q30 Restaurants with average rating greater than 4.5.
select RestaurantID, avg(Rating) as AvgRating
from reviews
group by RestaurantID
having avg(rating) > 4.5
order by avgrating asc;

-- ===========================================================================================================
-- INNER JOIN
-- Q31 Display customer name with their orders.

select FirstName, TotalAmount, OrderStatus, OrderDate
from customers
inner join orders
on customers.CustomerID = orders.CustomerID;


-- Q32 Show restaurant names with their categories.
select restaurants.RestaurantName, RestaurantCategories.CategoryName
from restaurants
inner join restaurantcategories
on restaurants.CategoryID = restaurantcategories.CategoryID;

-- Q33 Display menu item along with restaurant name.
select menuitems.ItemName, restaurants.RestaurantName
from menuitems
inner join restaurants
on menuitems.RestaurantID = restaurants.RestaurantID;


-- Q34 Show payment details with customer name.
select customers.FirstName, orders.TotalAmount, payments.amount, payments.PaymentStatus
from customers 
inner join orders
on customers.CustomerID = orders.CustomerID
inner join payments
on orders.OrderID = payments.OrderID;

-- Q35 Display review with restaurant and customer names.
select customers.FirstName, restaurants.RestaurantName, reviews.Rating, reviews.Comment
from customers
inner join reviews
on customers.CustomerID = reviews.CustomerID
inner join restaurants
on restaurants.RestaurantID = reviews.RestaurantID;

-- Q Which customer ordered from which restaurant and what he ordered?

SELECT Customers.FirstName, Restaurants.RestaurantName, MenuItems.ItemName
FROM Orders
INNER JOIN Customers
    ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Restaurants
    ON Orders.RestaurantID = Restaurants.RestaurantID
INNER JOIN OrderItems
    ON Orders.OrderID = OrderItems.OrderID
INNER JOIN MenuItems
    ON OrderItems.MenuItemID = MenuItems.MenuItemID;
    
-- ===========================================================================================================
-- LEFT JOIN 

-- Q36 Find customers who never placed an order.
select customers.CustomerID, customers.FirstName, orders.OrderID
from customers 
left join orders
on customers.CustomerID = orders.CustomerID
WHERE Orders.OrderID IS NULL;


-- Q37 Show all restaurants including those with no reviews.
select restaurants.RestaurantID, restaurants.RestaurantName, reviews.Comment, reviews.ReviewID
from restaurants
left join reviews
on restaurants.RestaurantID = reviews.RestaurantID;

-- Q37.A) Find restaurants that have no reviews.
select restaurants.RestaurantID, restaurants.RestaurantName, reviews.Comment, reviews.ReviewID
from restaurants
left join reviews
on restaurants.RestaurantID = reviews.RestaurantID
where reviews.RestaurantID is null;


-- Q38 Show all restaurants even if they have no menu items.
select restaurants.RestaurantName, menuitems.ItemName 
from restaurants
left join menuitems
on restaurants.RestaurantID = menuitems.RestaurantID;

-- Q38.A) Find restaurants that have no menu items.
select restaurants.RestaurantName, menuitems.ItemName 
from restaurants
left join menuitems
on restaurants.RestaurantID = menuitems.RestaurantID
WHERE MenuItems.MenuItemID IS null;


-- Q39 List delivery partners including those with no deliveries.
select deliverypartners.PartnerID, deliverypartners.PartnerName, orders.PartnerID
from deliverypartners
left join orders
on deliverypartners.PartnerID = orders.PartnerID;


-- ===========================================================================================================
-- RIGHT JOIN 

-- Q41 Show all menu items with restaurants.
SELECT menuitems.ItemName, restaurants.RestaurantName
from restaurants
right join menuitems
on menuitems.RestaurantID = restaurants.RestaurantID;

-- Q42 Display every payment even if order details are missing.
select payments.amount, payments.PaymentStatus, orders.OrderStatus
from orders
right join payments
on payments.OrderID = orders.OrderID;

-- Q43 Show every review with restaurant details.
select restaurants.RestaurantName, reviews.Comment
from restaurants
right join reviews
on restaurants.RestaurantID = reviews.RestaurantID;

-- Q44 List every order item with menu information.
select menuitems.ItemName, orderitems.Price, orderitems.Quantity
from menuitems
right outer join orderitems
on menuitems.MenuItemID =  orderitems.MenuItemID;

-- Q45 Display all categories even if no restaurants belong to them.
select restaurantcategories.CategoryName, restaurants.RestaurantName
from restaurants
right join restaurantcategories
on restaurantcategories.CategoryID = restaurants.CategoryID;

-- ===========================================================================================================
-- MULTIPLE JOIN

-- Q46 Customer → Order → Payment details.

select customers.FirstName, orders.OrderStatus, payments.amount
from customers
inner join orders
on customers.CustomerID = orders.CustomerID
inner join payments
on orders.OrderID = payments.OrderID;

-- Q47 Restaurant → Menu → OrderItems.
select restaurants.RestaurantName, menuitems.ItemName, orderitems.Price
from restaurants
inner join menuitems
on restaurants.RestaurantID = menuitems.RestaurantID
inner join orderitems
on orderitems.MenuItemID = menuitems.MenuItemID;

-- Q48 Customer → Review → Restaurant.
select customers.FirstName, restaurants.RestaurantName, reviews.Comment
from customers
inner join reviews
on customers.CustomerID = reviews.CustomerID
inner join restaurants
on restaurants.RestaurantID = reviews.RestaurantID;

-- Q49 Order → Delivery Partner → Payment.
select orders.OrderID, deliverypartners.PartnerName, payments.PaymentStatus
from orders
inner join payments
on orders.OrderID = payments.OrderID
inner join deliverypartners
on deliverypartners.PartnerID = orders.PartnerID;

-- Q50 Restaurant → Category → Reviews.
select restaurants.RestaurantName, restaurantcategories.CategoryName, reviews.Rating, reviews.Comment
from restaurants
inner join restaurantcategories
on restaurants.CategoryID = restaurantcategories.CategoryID
inner join reviews
on reviews.RestaurantID = restaurants.RestaurantID;



-- ===========================================================================================================
-- Subqueries

-- TODO: pending
-- Q51 Find customers who spent more than the average customer.


-- Q52 Find restaurants with rating above average.

select RestaurantName 
from restaurants 
where Rating > (select avg(Rating) from restaurants);

-- Q53 Display menu items priced above average.

select ItemName 
from menuitems 
where Price > (select avg(price) from menuitems);

-- Q54 Find customers who placed the highest value order.
select FirstName, TotalAmount 
from customers 
inner join orders
on customers.CustomerID = orders.CustomerID
where orders.TotalAmount = (select max(TotalAmount) from orders);

-- TODO: pending
-- Q55 Find restaurants earning more than average revenue.



-- ===========================================================================================================
-- Correlated Subqueries

-- Q56 Find restaurants whose average menu price is higher than overall average.
select RestaurantName, avg(Price)
from restaurants
inner join menuitems
on restaurants.RestaurantID = menuitems.RestaurantID
group by restaurants.RestaurantID
having avg(price) > 
(
	select avg(price) from menuitems as Restaurant_AVG
);


-- TODO: pending
-- Q57 Find customers who spent more than every customer in their city.

-- TODO: pending
-- Q58 Find restaurants whose ratings exceed category average.

-- TODO: pending
-- Q59 Find orders containing the highest priced item.

-- TODO: pending
-- Q60 Find restaurants whose menu has more items than average.