2.BASIC SQL COMMANDS

SELECT & FROM
SELECT : Tells which col you want.
FROM: Tell from which Table you want to fetch.

//In Postgres default schema is public
//In  We have databse inside it we have schema inside it we have tables
// In Postgres we directly create things using GUI but in MySql we use commands to create things.
//We can create table from GUI as well and then start inserting data into tables.

//1. INSERT Data in tables(MySQL)
INSERT INTO students (id , name, age , marks , city) VALUES
(1, "RAHUL" , 20 , 50 , 'Lucknow'),
(2, "PRIYA" , 20 , 50 , 'Lucknow'),
(3, "VANSHI" , 20 , 50 , 'Lucknow'),
(4, "DHONI" , 20 , 50 , 'Lucknow'),
(5, "THALA" , 20 , 50 , 'Lucknow');

//2. From above student table print name and age col ?
SELECT name , age FROM students;

//3. Insert data in tables PostgresSQL(inside public schema)
INSERT INTO public.students (id, name, age, marks, city) VALUES
(1, 'RAHUL', 20, 50, 'Lucknow'),
(2, 'PRIYA', 20, 50, 'Lucknow'),
(3, 'VANSHI', 20, 50, 'Lucknow'),
(4, 'DHONI', 20, 50, 'Lucknow'),
(5, 'THALA', 20, 50, 'Lucknow');

//4. From above student table print name and age col ?
SELECT name , age FROM public.students

Imp -> Why did "RAHUL" give an error in PostgreSQL?
"RAHUL" treated as col name we have to put it in single quotes '...'

Que 1: Write a query to fetch all students from students table?
ANS: SELECT * FROM students

WHERE Clause
Filter rows using condition.

SELECT * FROM students WHERE marks >= 80; // after where we wrtie some col field then operator then 80

Real world example: Find all customers who place order above 5000.

LIMIT
Show only certain number of rows.

SELECT * FROM students WHERE marks > 40 LIMIT 3;

Real world example : E commerce shows only top 5 trending products

DISTINCT
Remove duplicates and return unique col values

SELECT DISTINCT city FROM students;

ORDER BY(use like WHERE)
Sort rows in ASC(default) and DESC

SELECT * FROM students ORDER BY marks; // ASC
SELECT * FROM students ORDER BY marks DESC; // DESC
SELECT * FROM students ORDER BY marks ASC; // ASC

Real world: Sort order by latest date

BETWEEN
Check range ; return no. rows in particular range ; used along with WHERE

SELECT * FROM public.students WHERE marks BETWEEN 80 AND 90;// 80 and 90 included

Real world: Get all products price between 1000 to 5000

IN
Match multiple values; return rows ; used along with WHERE

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

Real world: -> return all users that is from delhi and mumbai

IS NULL
Find missing data; if any value of col is null it return that row; used with WHERE

SELECT * FROM students WHERE city IS NULL;
SELECT * FROM students WHERE city IS NOT NULL;

Real world: Find customers who didnt provide phoneNumber..

WHERE Clause Operators
1. Comparison operator: = , != , > , < , >= , <=

SELECT * FROM students WHERE marks >= 80;

2.Logical operator: AND , OR , NOT

//AND -> both condtion must true
SELECT name , marks , city 
FROM students
WHERE marks >= 80 AND city = 'Delhi';

//OR -> only one condition needs to be true
//NOT -> Find all students where city is not mumbai
SELECT name , marks , city 
FROM students
WHERE NOT city = "Mumbai";

3. Range Checking: BETWEEN_AND_

SELECT name , marks , city 
FROM students
WHERE marks BETWEEN 70 AND 80;

4.Set matching: (IN & NOT IN)

SELECT name , marks , city 
FROM students
WHERE city NOT IN ('Delhi' , 'Mumbai');

5.Pattern Matching: LIKE(case sensitive) and ILIKE(not case sensitive only used in postgres)
Used to search for pattern in text

//In MySQL
// this give name col whose name starts with A
SELECT name 
FROM students
WHERE name LIKE 'A%';

// name end with a 
SELECT name
FROM students
WHERE name LIKE "%a"

//name just containg a 
SELECT name 
FROM students
WHERE name LIKE "%a%";

//In postgres -> ILIKE used so that wo a and A meh fark nhi krega

6.Null Checking: IS NULL , IS NOT NULL : see above example

INTERVIEW QUESTIONS:

students

+----+--------+-----+-------+----------+
| id | name   | age | marks | city     |
+----+--------+-----+-------+----------+
| 1  | Rahul  | 20  | 85    | Delhi    |
| 2  | Priya  | 21  | 92    | Mumbai   |
| 3  | Aman   | 19  | 75    | Delhi    |
| 4  | Neha   | 22  | 88    | Jaipur   |
| 5  | Rohit  | 20  | 60    | NULL     |
| 6  | Aryan  | 21  | 95    | Mumbai   |
+----+--------+-----+-------+----------+

Write queries below:
1. Find students whose marks are less than 70.
SELECT *
FROM students
WHERE marks < 70
(ALL cols came ; only rows in which marks is less than 70)

2. Show top 2 highest scoring students
SELECT*
FROM students
ORDER BY marks DESC
LIMIT 2

3. Sort students by age then marks
SELECT* *
FROM students
ORDER BY age ASC , marks DESC

4. Find whose city is null
SELECT*
FROM students
WHERE city IS NULL (not city = null -> interview trap)

5. Find name having exaclty 5 characters
SELECT name
FROM students
WHERE name LIKE '_____'; (_ = one charcater)

****6. Find names whose second letter is 'a'.**SELECT *
FROM students
WHERE name LIKE '_a%'

****7. Find top 3 students scoring more than 80.***SELECT *
FROM students
WHERE marks > 80
ORDER BY marks DESC
LIMIT 3
-> remember : Order by always comes after where

8. Find second Highest scoring student (interview fav)
SELECT *
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 1;

9. Differnce b.w IN and OR
WHERE city IN ('Delhi','Mumbai')
same as
WHERE city='Delhi' OR city='Mumbai'
But IN is cleaner and preferred.

10. Execution Order of SQL ? (Interview fav)
SELECT *
FROM students
WHERE marks > 80
ORDER BY marks DESC
LIMIT 3;

1. FROM
2. WHERE
3. SELECT
4. ORDER BY
5. LIMIT

//OR
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT