Untitled

29/01/2024:

--where clause:
where clause is used for filter the record.
It is used for fetch only those records that satsfy the specific condition.
syntax:
select * from tablename where condition.

exa:-- select * from employeeinfo;
-- where
-- select * from employeeinfo where address="pune"
-- select * from employeeinfo where salary=30000
-- select * from employeeinfo where salary>30000
-- select * from employeeinfo where address !="pune";
-- select * from employeeinfo where address <> "pune";
-- select * from employeeinfo where salary between 30000 and 50000;
select * from employeeinfo where address in ("nagpur" ,"nashik")
-----------------------------------------------------------------------------------------
operators used in where clause:
1 equal to(=)
2 less than (<)
3,greater than(>)
4. greater than equal to (>=)
4. less than equal to (<=)
5. not equal to (!=,<>)
6.between (fetch records in certain range)
7.in (fetch specific multiple value)
8. like:- like operator is used to serch specific pattern.
9. and :- when both condition is true
10. or :- when  both or any one condition is true.
11. not : reverse 

exa: -- select * from employeeinfo where name like "ni%";
-- select * from employeeinfo where address like "%ded"
-- select * from employeeinfo where address like "%u%"
-- select * from employeeinfo where address like "pun_"
select * from employeeinfo where address like "pu__"

-- select * from employeeinfo where address ="nanded" and salary ="50000";
-- select * from employeeinfo where address ="nanded" or salary ="30000"
select * from employeeinfo where not name ="ram";

---------------------------------------------------------------------------------------
use first;
-- create table student(rno int primary key auto_increment,name varchar(30) not null, 
-- adresss varchar(30) default 'pune' ,age int check (age>18),marks int)

-- insert into student values(1,'ragini','satara',20,67);
-- insert into student(name,adresss,age, marks) value("jayan","nagpur",45,89);
-- insert into student(name,age, marks) value("gita",45,89);
-- insert into student(name,adresss,age, marks) value("jayan","nagpur",16,89);
-- insert into student(name,adresss,age) value("jayan","nagpur",34);
-- select * from student;employeeinfo

select * from employeeinfo;
-- select * from employeeinfo where address="pune";
-- select * from employeeinfo where salary>30000;
-- select * from employeeinfo where salary<30000;
-- select * from employeeinfo where salary>=30000;
-- select * from employeeinfo where salary<=30000;
-- select * from employeeinfo where address <> "pune";
-- select * from employeeinfo where address != "pune";

-- select * from employeeinfo where salary between 30000 and 70000;
-- select * from employeeinfo where address in ("nagpur","nashik")
-- select * from employeeinfo where address="pune" and salary = 30000;
-- select * from employeeinfo where address="nashik" or salary=30000;

-- select * from employeeinfo where not salary = 30000;
-- select * from employeeinfo where not salary in (30000,20000);
-- select * from employeeinfo where name like '%m'
-- select * from employeeinfo where name like 'ro%';
 -- select * from employeeinfo where address like '%a%' ;
-- select * from employeeinfo where address like '%a%' and name like '%s%' ;
-- select * from employeeinfo where address like '%a%' and name like '%a%' ;
-- select * from employeeinfo where name like '_a_';
-- select * from employeeinfo where name like '___a__';






-----------------------------------------------------------------------------------------------
assignment:-

table name:EmployeeInfo 
EmpID	EmpFname	EmpLname	Department	Address	     Gender	Salary
1	Sanjay	         Mehra	          HR	        Hyderaba	M	500000
2	Ananya	         Mishra	         Admin	          Delhi	         	75000
3	Rohan	         Diwan	        Account	          Mumbai	M	90000
4	Sonia	        Kulkarni	  HR	         Hyderabad	F	85000
5	Ankit	         Kapoor	         Admin	          Delhi	        M	300000


Q1.first create table then insert values

Q2. Write a query to find the names of employees that begin with ‘S’