DBMS ASSGINMENT 3

create table staff(
name varchar(20),
id varchar(10) not null,
department varchar,
salary numeric(10),
loction varchar(30)

);

insert  into staff values('Amit', 'S1', 'Sales', '20000' ,'KOL');
insert  into staff values('Binay' ,'S2' ,'Sales' ,'20000' ,'DEL');
insert  into staff values('Anil', 'H1', 'HR', '30000', 'KOL');
insert  into staff values('Anil', 'H2', 'HR' ,'35000' ,'KOL');
insert  into staff values('Asmit' ,'S3', 'Sales' ,'25000' ,'KOL');
insert  into staff values('Arun' ,'A1' ,'Accounts' ,'28000' ,'DEL');
insert  into staff values('Arjun' ,'A2', 'Accounts', 40000 ,'DEL');

select *  from staff where department = 'Sales';

select name , id from staff;
;
select name, id,salary from staff where department ='Accounts' ;

select name , id from staff where loction = 'KOL' AND salary >25000;

update staff set loction ='CHN' where loction ='DEL';

select * from staff;

select loction , sum(salary) from staff group by loction;

select max(salary) from staff where loction = 'KOL';

select name ,id ,salary,loction,department from staff where name like 'A%';

select * from staff where salary = (select max(salary)from staff);

select * from staff where salary = (  select max (salary)from staff);

-- to find 2nd higest salary
select * from staff 
group by salary 
order by salary desc limit 1,1;