mysql练习题99

一、查询每个专业的学生人数
SELECT COUNT(*)
FROM student
GROUP BY majorid;

二、查询参加考试的学生中，每个学生的平均分、最高分
SELECT avg(score),MAX(score),studentno
FROM result
GROUP BY stduentno;

三、查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentid,s.studentname,MIN(score)
FROM student s
JOIN result r
on s.studentno=r.studentno
where s.studentname LIKE '张%'
GROUP BY studentno
HAVING min(score)>60;

四、查询专业生日在“1988-1-1”后的学生姓名、专业名称
SELECT studentname,majorname,borndate
FROM student s
join major m
on s.majorid=m.majorid
where DATEDIFF(borndate,'1988-1-1')>0;

#五、查询每个专业的男生人数和女生人数分别是多少
#方式一：
SELECT COUNT(*) 个数,majorid,sex
FROM student
GROUP BY sex,majorid;

方式二：
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)
FROM result
where studentno in (
SELECT studentno
FROM student
WHERE mojorid=(SELECT majorid
FROM student 
where studentname='张翠山'));

七、查询大于60分的学生的姓名、密码、专业名
SELECT studentname,loginpwd,majorname
FROM student  s
join result r
on s.studentno=r.studentno
JOIN major m
on s.majorid=m.majorid
where r.score>60;

八、按邮箱位数分组，查询每组的学生个数
SELECT COUNT(*)
FROM student
GROUP BY LENGTH(email);

九、查询学生名、专业名、分数
SELECT studentname,majorname,score
FROM student s
JOIN major m
on s.majorid=m.majorid
JOIN s.studentno=r.studentno;

十、查询哪个专业没有学生，分别用左连接和右连接实现
SELECT majorid,majorname
from major m
LEFT JOIN student s
on m.majorid=s.majorid
where s.studentno is null

十一、查询没有成绩的学生人数
SELECT COUNT(*)
FROM student s
LEFT JOIN result r on s.studentno=r.studentno
where r.id is null