group_by, Sql Server

group_by

create table emp (name varchar(25), eid int, city varchar(15), department varchar(25), salary int);
insert into emp values('vasu',100,'cheyyar','hr',15000);
insert into emp values('mani',101,'vandavasi','teacher',12000);
insert into emp values('velu',102,'kanchipuram','cricket',20000);
insert into emp values('gopi',103,'cheyyar','gove',25000);
insert into emp values('dinesh',104,'arni','shop',30000);
insert into emp values('gopi',103,'cheyyar','gove',45000);
insert into emp values('vasu',100,'cheyyar','hr',18000);
insert into emp values('gomathi',105,'mangal','executive',25000);
insert into emp values('viji',106,'arcot','sales',17000);
insert into emp values('karthi',107,'chennai','tleader',26000);
insert into emp values('dinesh',104,'arni','shop',30000);
select *from emp;
select name from emp group by name;
select name, city from emp group by name, city;
select name, city, count(*) from emp group by name, city;
select name, city, count(*) from emp where city ='cheyyar' group by name, city having count(*)>0
select name, city, count(*) from emp where city ='cheyyar' or city='arni' group by name, city order by name;
alter table emp add gender varchar(5);
update emp set gender = 'male' where eid =101;
update emp set gender = 'male' where eid =100;
update emp set gender = 'male' where eid =102;
update emp set gender = 'male' where eid =103;
update emp set gender = 'male' where eid =104;
update emp set gender = 'girl' where eid =105;
update emp set gender = 'girl' where eid =106;
update emp set gender = 'male' where eid =107;
select *from emp;
alter table emp add country varchar(15);
update emp set country = 'india' where eid in (100,101,102);
update emp set country = 'aua' where eid in (103,104,105);
update emp set country = 'paki' where eid in (106,107);
select *from emp;


run \| edit \| history \| help	0