learnSQL/2_filtering_data.md at main · kishankumar07/learnSQL

Using WHERE :

        SELECT * FROM employee WHERE dob > '1992-01-01' ORDER BY name ASC;

company=# select * from employee;

id | name | email | dob | gender | place ----+-------+-----------------+------------+--------+----------- 1 | John | john@gmail.com | 1990-04-04 | M | Kolkata 2 | Doe | doe@gmail.com | 1992-05-03 | M | Punjab 3 | Divya | divya@gmail.com | 1997-05-05 | F | Jharkhand 4 | Priya | priya@gmail.com | 1999-12-25 | F | Noida 5 | Raja | raj@gmail.com | 1993-11-12 | M | Rajastan 6 | Rahul | rahul@gmail.com | 1992-05-03 | M | Assam (6 rows)

                SELECT * FROM employee WHERE dob = '1992-05-03';

                SELECT * FROM employee WHERE name = 'John';

                SELECT dob,gender,place,id,name FROM employee WHERE place ='Assam';

        SELECT * FROM employee WHERE name = 'John' AND dob <='1990-04-04';

        SELECT * FROM names WHERE first_name = 'Vishnu' AND last_name = 'G R';

company=# select * from names where first_name = 'Vishnu' and last_name = 'G R'; first_name | last_name | email ------------+-----------+-------------------- Vishnu | G R | grvishnu@gmail.com (1 row)

            SELECT * FROM names WHERE first_name = 'Vishnu' OR last_name = 'Ravi';

            SELECT * FROM names WHERE first_name IN ('Vishnu','Sahal','Aparna');

        SELECT * FROM employee WHERE email LIKE '%gmail%';

              SELECT * FROM employee WHERE name iLIKE 'd%';

            SELECT name,LENGTH(name) len FROM employee WHERE name LIKE 'D%' AND LENGTH(name) BETWEEN 3 AND 5;

company=# SELECT name,LENGTH(name) len FROM employee WHERE name LIKE 'D%' AND LENGTH(name) BETWEEN 2 AND 5 ; name | len -------+----- Doe | 3 Divya | 5 (2 rows)

                        SELECT name FROM employee WHERE email LIKE '%gmail%' AND gender <> 'M'';

company=# select name from employee where email like '%gmail%' AND gender <> 'M'; name

Divya Priya (2 rows)

                  SELECT * FROM employee WHERE name IN ('Sangham','Raj','Rahul','Divya');

Using IN: to find any row using a string or number or anything just search this thing in the table

compared to LIKE , it check for a particular content in the table rows.

== The above query usign IN is replacable using OR :

              SELECT * FROM employee WHERE name = 'Divya' OR name = 'John' OR name = 'Daniel';

            SELECT * FROM employee WHERE name NOT IN ('John','Divya');

            (NOT IN)

                SELECT * FROM employee WHERE dob BETWEEN '1990-01-02' AND '1995-04-05' ORDER BY name;

Usage : BETWEEN

                      SELECT * FROM employee WHERE dob NOT BETWEEN '1990-01-01' AND '1992-01-01' ORDER BY name ASC;

Usage : NOT BETWEEN

                GROUP BY

                SELECT place COUNT(*) AS employee_count FROM employee GROUP BY place;

            SELECT name,email,place FROM employee GROUP BY name,email,place;

        SELECT place,COUNT(*) FROM employee GROUP BY place ORDER BY place;

                SELECT place,count(*) FROM employee GROUP BY place HAVING count(*) > 1;

company=# select place,count() as place_count from employee group by place having count() >1; place | place_count --------+------------- Assam | 2 Punjab | 2 (2 rows)

                      SELECT sum(salary) FROM employee;

company=# select max(salary) from employee; max

65490 (1 row)

company=# select min(salary) from employee; min

15000 (1 row)

company=# select avg(salary) from employee; avg

36622.500000000000 (1 row)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

company=# select name from employee where email like '%gmail%' AND gender <> 'M'; name

company=# select max(salary) from employee; max

company=# select min(salary) from employee; min

company=# select avg(salary) from employee; avg

FilesExpand file tree

2_filtering_data.md

Latest commit

History

2_filtering_data.md

File metadata and controls

company=# select name from employee where email like '%gmail%' AND gender <> 'M'; name

company=# select max(salary) from employee; max

company=# select min(salary) from employee; min

company=# select avg(salary) from employee; avg