group_by, Sql Server

group_by

Language: Layout:

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;

View schema
Execution time: 0,05 sec, rows selected: 62, rows affected: 33, absolute service time: 0,22 sec, absolute service time: 0,22 sec

	name	eid	city	department	salary
1	vasu	100	cheyyar	hr	15000
2	mani	101	vandavasi	teacher	12000
3	velu	102	kanchipuram	cricket	20000
4	gopi	103	cheyyar	gove	25000
5	dinesh	104	arni	shop	30000
6	gopi	103	cheyyar	gove	45000
7	vasu	100	cheyyar	hr	18000
8	gomathi	105	mangal	executive	25000
9	viji	106	arcot	sales	17000
10	karthi	107	chennai	tleader	26000
11	dinesh	104	arni	shop	30000

	name
1	dinesh
2	gomathi
3	gopi
4	karthi
5	mani
6	vasu
7	velu
8	viji

	name	city
1	dinesh	arni
2	gomathi	mangal
3	gopi	cheyyar
4	karthi	chennai
5	mani	vandavasi
6	vasu	cheyyar
7	velu	kanchipuram
8	viji	arcot

	name	city	(No column name)
1	viji	arcot	1
2	dinesh	arni	2
3	karthi	chennai	1
4	gopi	cheyyar	2
5	vasu	cheyyar	2
6	velu	kanchipuram	1
7	gomathi	mangal	1
8	mani	vandavasi	1

	name	city	(No column name)
1	gopi	cheyyar	2
2	vasu	cheyyar	2

	name	city	(No column name)
1	dinesh	arni	2
2	gopi	cheyyar	2
3	vasu	cheyyar	2

	name	eid	city	department	salary	gender
1	vasu	100	cheyyar	hr	15000	male
2	mani	101	vandavasi	teacher	12000	male
3	velu	102	kanchipuram	cricket	20000	male
4	gopi	103	cheyyar	gove	25000	male
5	dinesh	104	arni	shop	30000	male
6	gopi	103	cheyyar	gove	45000	male
7	vasu	100	cheyyar	hr	18000	male
8	gomathi	105	mangal	executive	25000	girl
9	viji	106	arcot	sales	17000	girl
10	karthi	107	chennai	tleader	26000	male
11	dinesh	104	arni	shop	30000	male

	name	eid	city	department	salary	gender	country
1	vasu	100	cheyyar	hr	15000	male	india
2	mani	101	vandavasi	teacher	12000	male	india
3	velu	102	kanchipuram	cricket	20000	male	india
4	gopi	103	cheyyar	gove	25000	male	aua
5	dinesh	104	arni	shop	30000	male	aua
6	gopi	103	cheyyar	gove	45000	male	aua
7	vasu	100	cheyyar	hr	18000	male	india
8	gomathi	105	mangal	executive	25000	girl	aua
9	viji	106	arcot	sales	17000	girl	paki
10	karthi	107	chennai	tleader	26000	male	paki
11	dinesh	104	arni	shop	30000	male	aua