student.sql一、查询每个专业的学生人数
select count(studentno),majorid
from student
group by majorid;
二、查询参加考试的学生中，每个学生的平均分、最高分
select studentno,avg(score),max(score)
from result
group by studentno;
三、查询姓张的每个学生的最低分大于60的学号、姓名
select s.studentno,studentname，min(score) #这里要用s.studentno或r.studentno,不能只用studentno,因为inner join后得到的表有两个名为studentno的列，studentname前面不用加：(表.studentname),因为inner join得到的表只有一个studentname列
from student s
inner join result r
on s.studentno = r.studentno
where studentname like '张%'
group by studentno,studentname
having min(score) > 60;
四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称
select studentname,majorname
from student
left outer join major #这里用左外连接是考虑到：假如student表中有个majorid=9，而major表中majorid in (1,2,3).当然此题inner join也可以
on student.majorid = major.majorid
where datediff(borndate,'1988-1-1') > 0;
五、查询每个专业的男生人数和女生人数分别是多少
select count(studentno),sex
from student
group by sex,majorid;
#方式2：
select majorid,
(select count(*) from student where sex='男' and majorid = s.majorid) 男,
(select count(*) from student where sex='女' and majorid = s.majorid) 女
from student s #必须要取别名才有效
group by majorid;
六、查询专业和张翠山一样的,学生的最低分
select min(score),majorid,studentno
from student
where majorid = (
select majorid
from student
where studentname = '张翠山'
)
group by studentno;
七、查询大于60分(result表中)的学生的姓名、密码(student表中)、专业名(major表中)
select studentname,loginpwd,majorname,tab_1.studentno,score
from
(select studentname,loginpwd,majorname,studentno
from student
inner join major
on student.majorid = major.majorid
group by studentno) as tab_1
left outer join result
on tab_1.studentno = result.studentno
where score > 60;
八、按邮箱位数分组，查询每组的学生个数
select count(*),length(email)
from student
group by length(email);
九、查询学生名(student表中)、专业名(major表中)、分数(result表中)
select studentname,majorname,score
from
(
select studentname,majorname,studentno
from student
left outer join major
on student.majorid = major.majorid
) as stu_maj
left outer join result
on result.studentno = stu_maj.studentno;
十、查询哪个专业(major表中)没有学生(student表中)，分别用左连接和右连接实现
select majorname
from student
left outer join major
on student.majorid = major.majorid
group by majorname
having count(*) = 0;
十一、查询没有成绩的学生人数
select count(*)
from student
left outer join result
on student.studentno = result.studentno
where score is null;
