4-SQL-Operators

SQL Operators
   Introduction

SQL Operators are used to perform operations on data in a database.
They are mainly used in the WHERE clause to filter records.

-- 1️⃣ Arithmetic Operators
Used to perform mathematical calculations.

Operator	
🔹+	Addition
🔹-	Subtraction
🔹*	Multiplication
🔹/	Division
🔹%	Modulus (Remainder)

-- 2️⃣ Comparison Operators
Used to compare two values.

Operator	
🔹= : Equal to
🔹!= : Not equal to (<>)
🔹> :	Greater than
🔹< :	Less than
🔹>= : Greater than or equal to
🔹<= : Less than or equal to

-- 3️⃣ Logical Operators
Used to combine multiple conditions.

🔹AND 
 All conditions must be true

🔹OR 
 At least one condition must be true

🔹NOT 
 Reverses the condition.

-- 4️⃣ Special Operators
Used for specific conditions.


🔹 BETWEEN
Used to filter values within a range.

🔹 IN
Used to match multiple values.

🔹 LIKE
Used for pattern matching.

🔹 IS NULL
Used to check NULL values.

🔹 IS NOT NULL
Used to check for non-Null values.


USE market_star_schema;

SELECT * FROM cust_dimen;
SELECT * FROM manu;
SELECT * FROM market_fact_full;
SELECT * FROM orders_dimen;
SELECT * FROM prod_dimen;
SELECT * FROM shipping_dimen;

SELECT 2-6;

SELECT sales, order_quantity, 
sales/order_quantity
FROM market_fact_full;

SELECT * FROM cust_dimen
WHERE city='Chennai';

-- customers who are not from chennai
SELECT * FROM cust_dimen
WHERE city<>'Chennai';


-- find the orders with profit greater than zero
SELECT * FROM market_fact_full
WHERE profit>0;


-- orders placed on or after Jan 1st 2012
SELECT * FROM orders_dimen
WHERE order_date>='2012-01-01';

-- get the details of customers who are either from city of Cochin, Hyderabad or Mumbai
SELECT * FROM cust_dimen
WHERE city='Cochin'
OR city='Hyderabad'
OR city='Mumbai';

-- get the details of customers who are from city of Mumbabi and customer segment of Home Office
SELECT * FROM cust_dimen
WHERE city='Mumbai'
AND customer_segment='HOME OFFICE';

-- get the details of customers who are not from city of Mumbai
SELECT * FROM cust_dimen
WHERE NOT city='Mumbai';

-- orders with sales between 100 and 1000
SELECT * FROM market_fact_full
WHERE sales>=100 AND sales<=1000;

SELECT * FROM market_fact_full
WHERE sales BETWEEN 100 AND 1000;


-- orders placed between 1st Jan to 31st Jan in 2012 year/ orders placed in Jan 2012
SELECT * FROM orders_dimen
WHERE order_date BETWEEN '2012-01-01' AND '2012-01-31';

-- orders placed in year of 2010
SELECT * FROM orders_dimen
WHERE order_date BETWEEN '2010-01-01' AND '2010-12-31';

-- get the rows with manu_id as NULL
SELECT * FROM prod_dimen
WHERE manu_id IS NULL;

-- get the rows with manu_id is not a null
SELECT * FROM prod_dimen
WHERE manu_id IS NOT NULL;

-- customers whose city name ending with "i"
SELECT * FROM cust_dimen
WHERE city LIKE '%i';

-- customers whose city name starting with 'c'
SELECT * FROM cust_dimen
WHERE city LIKE 'c%';

-- customers whose city name contains letter 'b'
SELECT * FROM cust_dimen
WHERE city LIKE '%b%';

-- customer details who are either from cities of Mumbai, Delhi and Chennai
SELECT * FROM cust_dimen
WHERE city='Mumbai' OR city='Delhi' OR city='Chennai';

SELECT * FROM cust_dimen
WHERE city IN ('Mumbai','Delhi','Chennai');