Untitled

28/02/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.
select columns from tablename where condition.

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+
use testdb;

select * from customer;
select cust_id,cust_name from customer;

insert into customer values(111,"akash","nagpur",34),
(106,"sonal","nashik",45),
(107,"komal","nashik",23),
(108,"raj","nanded",22)
,(109,"pavan","mumbai",33)
,(110,"akash","mubai",34),(105,"akash","pune",32);
select * from employee;
select distinct * from employee;
select distinct emp_id ,emp_name from employee;

select * from employee where city="pune";
select distinct(emp_name) from  employee where city="pune";
select * from employee where salary=20000;
select * from employee where salary>20000;
select * from employee where salary<20000;
select * from employee where salary>=20000;
select * from employee where salary<=20000;

select * from employee where dept != "IT";
select * from employee where dept <> "IT";

select * from customer;
select * from customer where age between 30 and 40;

select * from customer where address in ("pune","nagpur");

select * from customer where cust_name like "m%";
select * from customer where cust_name like "%h";
select * from customer where cust_name like "%a%";
select * from customer where cust_name like "r__i";
select * from customer where cust_name like "r___";

select * from customer where address="pune" and age>30;
select * from customer where address="pune" or age>30;
select * from customer where  not address="pune";
select * from customer where cust_name not like "r___";

select * from customer where address is null;
select * from customer where address is not null

===================================================================================================================
Order by clause:-

order by clause is used for the data sort in ascending and descending order.

synatx:-
select columns from tablename order by column_name asc/desc;

select * from customer;
select * from customer order by cust_name asc;

select * from employeeinfo order by name desc ,address desc;

when we short data in any filter condition we use where and order by clause;
sequence is mandatory:
1 where
2.order by

select * from employeeinfo where salary>30000 order by name desc ;

select * from customer;

select * from customer order by cust_name desc;
select * from customer where age>30;
select * from customer where age>30  order by cust_name desc;
select * from customer order by cust_name desc ,address asc;


-------------------------------------------------------------------------------------------------------------------
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’

-------------------------------------------------------------------------

Q3.From the following table, write a SQL query to locate salespeople who live in the city of 'Paris'. Return salesperson's name, city. 

Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12