SQLtricks.sql

Create Database sql_practise 

use sql_practise 

---Creating table product

Create table Product
(
 Product_id INT PRIMARY KEY,
 Product_name VARCHAR(50),
 quantity INT ,
 sales_unit INT , 
 price INT ,
 city VARCHAR(30)
);

---INSERT DATA'S 
INSERT INTO PRODUCT VALUES
(1, 'Laptop', 15, 40, 60000, 'Mumbai'),
(2, 'Smartphone', 20, 50, 25000, 'Ahmedabad'),
(3, 'Tablet', 0, 18, 18000, 'Pune'),
(4, 'Headphones', 5, 12, 3000, 'Vadodara'),
(5, 'Keyboard', 8, 25, 2000, 'Vadodara'),
(6, 'Monitor', 6, 42, 12000, 'Surat'),
(7, 'Mouse', 10, 38, 1500, 'Delhi'),
(8, 'Printer', 9, 56, 9000, 'Rajkot'),
(9, 'Smartwatch', 12, 40, 15000, 'Mumbai'),
(10,'Camera', 18, 30, 45000, 'Surat');



--TASK SET 1 — SUBQUERIES (YOU PRACTICE ALL HERE)
---Use BOTTOM → TOP for every question.

--Basic Subqueries

--Quantity > average quantity

Products in same city as Laptop

Product with maximum quantity

Quantity < minimum quantity of Vadodara

Sales_unit > average sales_unit of Mumbai

🔹 IN / ANY / ALL

Products in cities where quantity = 0 exists

Sales_unit > ALL products in Surat

Quantity > ANY product in Pune

Product names same as any sold in Ahmedabad

🔹 Group + Subquery

Cities with total quantity > 20

Products from cities with avg sales_unit > 30

City having highest product count

🔹 Correlated Subquery

Products whose quantity > average of their own city

🔹 Calculation

Product name + quantity difference from global average

👉 All 14 are solvable using only Product table