Subqueries_Assignment.sql

--- Create new database
CREATE DATABASE product_db;

--- Use the database
USE product_db;

--- Create Product table
CREATE TABLE Product
(
    product_id INT,
    product_name VARCHAR(255),
    quantity INT,
    salesunit INT,
    price INT,
    city VARCHAR(255)
);

select * from product 

---inserting data 

INSERT INTO Product VALUES
(1, 'Laptop', 15, 40, 6000, 'Mumbai'),
(2, 'Smartphone', 20, 50, 2500, 'Ahmedabad'),
(3, 'Smartphones', 18, 48, 2400, 'Ahmedabad'),
(4, 'Tablet', 0, 18, 1800, 'Pune'),
(5, 'Headphones', 5, 12, 3000, 'Vadodara'),
(6, 'Smartwatch', 12, 40, 1500, 'Surat'),
(7, 'Keyboard', 8, 25, 2000, 'Vadodara'),
(8, 'Mouse', 10, 38, 1500, 'Delhi'),
(9, 'Monitor', 6, 42, 12000, 'Surat'),
(10, 'Printer', 9, 56, 9000, 'Rajkot');

drop table product 
---1. Find all products that have a Quantity greater than the average quantity of all products.
Select * from Product Where Quantity
> (Select AVG(Quantity) from Product )

---2. Display the ProductName of products sold in the same city as 'Laptop'.
Select Product_name from product
where city 
= (Select city from Product
where Product_name = 'Laptop')

---3. Find the details of the products with the maximum Quantity.
Select * from Product where Quantity =
(Select Max(quantity) from product)

---4. List products whose salesUnit is higher than the salesUnit of ProductID 5.
Select * from Product where SalesUnit 
> (Select SalesUnit from Product 
where Product_ID=5)

---5. Find products that have a lower Quantity than the minimum Quantity found in 'Vadodara'.
Select * from Product where Quantity 
< (Select Min(Quantity) from Product
where City = 'Vadodara')

---6. Display products whose salesUnit is greater than the average salesUnit of products in 'Mumbai'.
Select * from product where SalesUnit 
> (Select AVG(Salesunit) from Product
where city = 'Mumbai')

---7. Find the product name with the lowest salesUnit.
Select Product_name from product
where Salesunit =
(Select Min(SalesUnit) from Product)

---8. List all products sold in cities that have more than 50 total Quantity across all their products.
select * from product 
where city in 
(Select city from product
group by city 
having sum(quantity) > 50)

---9. Show products whose Quantity is exactly equal to the salesUnit of 'Smartphone'.
Select * from product 
Where Quantity = 
(Select Salesunit from product 
where product_name = 'Smartphones')

---10. Find the city which has the product with the highest salesUnit.
Select City from Product
where Salesunit=
(Select Max(SalesUnit) from Product)

---(IN, ALL, ANY & Correlated)

---11. Find all products sold in cities where at least one product has a Quantity of zero.
select * from product 
where city in 
(select city from product 
where quantity = 0)

---12. List products whose salesUnit is greater than the salesUnit of all products in 'Surat'.
Select * from product where salesunit 
> (select sum(salesunit) from product
where city = 'surat')

---13. Find products that belong to cities where the average salesUnit is greater than 10.
select * from product
where city in 
(select city from product 
group by city 
having avg(salesunit) > 10)


---14.Display products that have a Quantity greater than any product's Quantity in 'Pune'.
Select * from product
where quantity > any 
(select quantity from product 
where city = 'Pune')

---15. Find all products whose ProductName is the same as any product sold in 'Ahmedabad'.
Select * from product
where product_name IN
(select product_name from product
where city = 'ahmedabad')

---16. Select products where the Quantity is greater than the average Quantity of their own city.
select * from product p1
where quantity >
(Select avg(quantity) from product p2
where p2.city = p1.city)

---17. Find cities where the total salesUnit is higher than the total salesUnit of 'Vadodara'.
select city from product  group by city 
having sum(salesunit) >
(select sum(salesunit) from product 
where city = 'vadodara')

---18. List products that are sold in the city that has the maximum variety (count) of products.
Select * from product where city = 
(Select top 1 city from Product 
group by city 
order by count(*) desc)

---19. Find the second highest Quantity from the Product table using a subquery.
    SELECT MAX(Quantity) FROM Product
    WHERE Quantity 
    < (SELECT MAX(Quantity) FROM Product)

---20.Display the ProductName and a calculated column showing the difference between its Quantity and the global average Quantity.
SELECT product_name,
    Quantity - (SELECT AVG(Quantity) FROM Product) AS quantity_diff
FROM Product;