sql having group by练习

create table qwe(id int not null primary key auto_increment,
name char(10),
course char(10),age int,gender char,college char(10),year int);

insert into qwe values(null,'华华','python',25,'女','清华',2007);
insert into qwe values(null,'涛哥','java',30,'男','清华',2007);

insert into qwe values(null,'土豆','全程班',24,'女','清华',2008);

insert into qwe values(null,'happy','java',22,'男','哈工大',2007);

insert into qwe values(null,'歪歪','java',28,'男','哈工大',2008);

insert into qwe values(null,'小简','python',25,'女','哈工大',2008);

insert into qwe values(null,'亚蒙','全程班',26,'男','哈工大',2008);

insert into qwe values(null,'可可','测试开发',27,'女','哈工大',2009);

insert into qwe values(null,'雨泽','python',21,'男','哈工大',2009);

insert into qwe values(null,'木森','python',18,'男','北大',2007);

insert into qwe values(null,'可有','python',26,'男','北大',2007);

insert into qwe values(null,'罗杰','java',22,'男','北大',2007);

 

-- 1.求每个课程的老师数量
select course,count(*) from qwe group by course;

-- 2.求老师数量大于3的课程
select course,count(*) as a from qwe group by course having a>3 ;

 

 

-- where 列条件 group by 列having统计条件
-- 3.分别求男女老师的数量
select gender,count(*) from qwe group by gender;

 

-- 4.分别求男女老师的平均年龄
select gender,count(*),sum(age) from qwe group by gender;

 

-- 5.分别求最小男女老师的年龄
select min(age),gender from qwe group by gender;

 

-- 6.分别求男女老师年龄总和
select sum(age),gender from qwe group by gender;

 

-- 7.求每个学校的老师数量
select count(*),college from qwe group by college;

 

-- 8.求每个学校每届的老师数量
select count(*),college,year from qwe group by college,year;

 

-- 9.求各个学校每届的老师数量>=3
select count(*),college,year from qwe group by college,year having count(1)>=3;

 

-- 10.求不同学校每届的java老师数量>2
select count(*),course,year,college from qwe  group by course
select college,year,count(1) from qwe where count(1)>2 group by course
select college,year,count(1) from qwe where course='Java' group by college,year having count(1)>=1

=====================未完待续============================================