practixeadv.sql

use practiseadvancesql 

---SECTION 1: AGGREGATE FUNCTIONS
-- Q1. Find total number of customers.
select count(*) from customers 

-- Q2. Find total sales of all orders.
select sum(sales) 
from orders 

--Q3. Find average order value.
select avg(sales) 
from orders 

-- Q4. Find highest and lowest product price.
select max(price),
min(price) 
from products 

-- Q5. Find total sales per customer.
select customerid ,
sum(sales) 
from orders 
group by customerid

--- Q6. Find total quantity sold per product.
select productid,
sum(quantity)
from orderdetails 
group by productid 

-- Q7. Find number of orders per customer.
select customerid,
count(*)
from orders 
group by customerid 

--- SECTION 2: GROUP BY + HAVING
--Q8.Find customers whose total sales > 1000.
select customerid,
sum(sales)
from orders 
group by customerid 
having sum(sales) > 1000

-- Q9. Find products sold more than 2 times.

select productid,
sum(quantity)
from orderdetails 
group by productid 
having sum(quantity) > 2

--Q10. Find orders having total quantity > 3.


--- JOINS 
--- Q11. Show customer name with their orders.
SELECT c.FirstName, o.OrderID
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;


Q12. Show order details with product names.
select od.orderid , p


Q13

Show full order report:
(Customer + Product + Quantity + Price)

Q14

Show customers who never placed any orders.
SELECT *
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

--SECTION 4: CASE STATEMENTS
--Q15. Categorize orders:
--High (>500)
--Medium (200–500)
--Low (<200)

select orderid,sales,
CASE 
when sales > 500 then 'high'
when sales >= 200 then 'medium'
else 'low'
end as category 
from orders 

--Q16. Label customers:
--VIP (total sales > 2000)
--Regular (else)

SELECT CustomerID,
CASE 
  WHEN SUM(Sales) > 2000 THEN 'VIP'
  ELSE 'Regular'
END AS Status
FROM Orders
GROUP BY CustomerID;


---Q17. Replace NULL values in LastName with 'Unknown'.
SELECT FirstName,
COALESCE(LastName, 'Unknown')
FROM Customers


🚀 🔥 SECTION 5: SUBQUERIES
Q18

Find products whose price is above average.
select * from products 
where price > (select avg(price) from products)


Q19

Find customers who made orders above average sales.

Q20

Find orders made by customers from 'India'.

Q21

Find customers who have NOT placed any orders.

Q22

Find orders where sales > (average sales).

🚀 🔥 SECTION 6: WINDOW FUNCTIONS (VERY IMPORTANT 🔥)
Q23

Show total sales along with each row.

Q24

Show total sales per customer using window function.

Q25

Rank orders by sales (highest to lowest).

Q26

Find top 1 order per customer.

Q27

Show running total of sales.

Q28

Find average sales per product (window).

Q29

Show difference between each order and max sales.

🚀 🔥 SECTION 7: RANKING FUNCTIONS
Q30

Use ROW_NUMBER() to assign unique row numbers to orders.

Q31

Use RANK() to rank customers by total sales.

Q32

Use DENSE_RANK() for product sales ranking.

🚀 🔥 SECTION 8: CTE (COMMON TABLE EXPRESSIONS)
Q33

Use CTE to calculate total sales per customer and display it.

Q34

Use CTE to rank customers based on sales.

Q35

Use recursive CTE to generate numbers from 1 to 20.

Q36

Use CTE to find customers with last order date.

🚀 🔥 SECTION 9: VIEWS
Q37

Create a view showing:
Customer + Order + Sales

Q38

Create a view for top-selling products.

Q39

Create a view for customer total sales summary.

🚀 🔥 SECTION 10: STORED PROCEDURES
Q40

Create procedure to get all customers.

Q41

Create procedure to get orders by CustomerID.

Q42

Create procedure to get total sales by country.

Q43

Create procedure with parameter:
→ get orders above given sales amount

Q44

Create procedure to insert a new order.

🚀 🔥 SECTION 11: TRIGGERS
Q45

Create trigger:
→ After inserting into Orders, log into another table

Q46

Create trigger:
→ Prevent inserting negative sales

Q47

Create trigger:
→ When order is deleted, log it

🚀 🔥 SECTION 12: ADVANCED / MIXED
Q48

Find customer who spent the most money.

Q49

Find most sold product.

Q50

Find revenue per month.

Q51

Find customers who bought more than 2 products in one order.

Q52

Find percentage contribution of each product to total sales.