# 2.BASIC SQL COMMANDS

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

```javascript
//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.

```javascript
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.

```javascript
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

```javascript
SELECT DISTINCT city FROM students;
```

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

```javascript
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

```javascript
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

```javascript
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

```javascript
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: = , != , > , < , >= , <=

```javascript
SELECT * FROM students WHERE marks >= 80;
```

2.Logical operator: AND , OR , NOT

```javascript
//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\_

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

4.Set matching: (IN & NOT IN)

```javascript
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

```javascript
//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:**

```javascript
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;

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

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