Q1.
Display each customer's name along with the total number of orders they have placed.
Concepts:
INNER JOIN, COUNT, GROUP BY
ANS : SELECT [Link], COUNT([Link]) AS TotalOrders
FROM Customers c
INNER JOIN Orders o ON [Link] = o.
GROUP BY [Link];
Expected Output:
CustomerName TotalOrders
Amit 3
Neha 2
Rahul 1
Priya 1
Q2.
Show the total order amount for each customer.
Concepts:
SUM, GROUP BY, JOIN
SELECT [Link], SUM([Link]) AS TotalOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link];
Expected Output:
CustomerName TotalOrderAmount
Amit 16000
Neha 9000
Rahul 9000
Priya 4000
Q3.
Display the average order amount for each customer.
Concepts:
AVG, GROUP BY
SELECT [Link], AVG([Link]) AS AverageOrderAmount
FROM Customers
INNER JOIN Orders o ON [Link] = [Link]
GROUP BY [Link];
Expected Output:
CustomerName AverageOrderAmount
Amit 5333.33
Neha 4500.00
Rahul 9000.00
CustomerName AverageOrderAmount
Priya 4000.00
Q4.
Find the maximum order amount placed by each customer.
Concepts:
MAX, GROUP BY
SELECT [Link], MAX([Link]) AS MaxOrderAmount
FROM Customers c
INNER JOIN Orders o ON [Link] = [Link]
GROUP BY [Link];
Expected Output:
CustomerName MaxOrderAmount
Amit 8000
Neha 7000
Rahul 9000
Priya 4000
Q5.
List all customers and the total number of orders, including customers who have placed no orders.
Concepts:
LEFT JOIN, COUNT
SELECT [Link], COUNT([Link]) AS TotalOrders
FROM Customers c
LEFT JOIN Orders o ON [Link] = [Link]
GROUP BY [Link];
CustomerName TotalOrders
Amit 3
Neha 2
Rahul 1
Priya 1
Karan 0
Q6.
Display the total order amount for customers who live in Delhi.
Concepts:
JOIN, WHERE, SUM
SELECT [Link], SUM([Link]) AS TotalOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
WHERE [Link] = 'Delhi'
GROUP BY [Link];
CustomerName TotalOrderAmount
Amit 16000
Q7.
Show the number of orders placed in each city.
Concepts:
JOIN, COUNT, GROUP BY
SELECT [Link], COUNT([Link]) AS NumberOfOrders
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link];
City NumberOfOrders
Delhi 3
Mumbai 2
Bangalore 1
Chennai 1
Q8.
Find customers whose total order amount is greater than 10,000.
Concepts:
GROUP BY, HAVING
SELECT [Link], SUM([Link]) AS TotalOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link]
HAVING SUM([Link]) > 10000;
CustomerName TotalOrderAmount
Amit 16000
Q9.
Display the minimum order amount placed by each customer.
Concepts:
MIN, GROUP BY
SELECT [Link], MIN([Link]) AS MinOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link];
CustomerName MinOrderAmount
Amit 3000
Neha 2000
Rahul 9000
Priya 4000
Q10.
Show the total number of customers in each city.
Concepts:
COUNT, GROUP BY
SELECT City, COUNT(CustomerID) AS TotalCustomers
FROM Customers
GROUP BY City;
City TotalCustomers
Delhi 1
Mumbai 1
Bangalore 1
Chennai 1
Pune 1
Q11.
Find the customer who has placed the highest total order amount.
Concepts:
SUM, GROUP BY, ORDER BY, LIMIT
SELECT [Link], SUM([Link]) AS TotalOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link]
ORDER BY TotalOrderAmount DESC
LIMIT 1;
CustomerName TotalOrderAmount
Amit 16000
Q12.
Display the average order amount for each city.
Concepts:
JOIN, AVG, GROUP BY
SELECT [Link], AVG([Link]) AS AverageOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link];
City AverageOrderAmount
Delhi 5333.33
Mumbai 4500.00
Bangalore 9000.00
Chennai 4000.00
Pune NULL
Q13.
Show cities where the total order amount is greater than 15,000.
Concepts:
GROUP BY, HAVING
SELECT [Link], SUM([Link]) AS TotalOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link]
HAVING SUM([Link]) > 15000;
City TotalOrderAmount
Delhi 16000
Q14.
Find customers who have placed more than 2 orders.
Concepts:
COUNT, GROUP BY, HAVING
SELECT [Link], COUNT([Link]) AS OrderCount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 2;
CustomerName OrderCount
Amit 3
Q15.
Display customers who have placed orders after 15-Jan-2024 along with their total order amount.
Concepts:
JOIN, WHERE, SUM, GROUP BY
SELECT [Link], SUM([Link]) AS TotalOrderAmount
FROM Customers c
JOIN Orders o ON [Link] = [Link]
WHERE [Link] > '2024-01-15'
GROUP BY [Link], [Link];
CustomerName TotalOrderAmount
Amit 11000
Rahul 9000
Priya 4000