练习题3——学生相关信息查询

Sql脚本文件：
链接：https://pan.baidu.com/s/1C5dF6Mg9vWDckxS-DXuptw 
提取码：tp9e

又是做练习的一天。因为是对学生的信息进行的操作 在以后的代码编写中还是很实用的 所以尽可能的写的正确一点 。当然是和老师的答案进行了比对

题目：

一、查询每个专业的学生人数
二、查询参加考试的学生中，每个学生的平均分、最高分
三、查询姓张的每个学生的最低分大于60的学号、姓名
四、查询专业生日在“1988-1-1”后的学生姓名、专业名称
五、查询每个专业的男生人数和女生人数分别是多少
六、查询专业和张翠山一样的学生的最低分
七、查询大于60分的学生的姓名、密码、专业名
八、按邮箱位数分组，查询每组的学生个数
九、查询学生名、专业名、分数
十、查询哪个专业没有学生，分别用左连接和右连接实现
十一、查询没有成绩的学生人数

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

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

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

#四、查询生日在“1988-1-1”后的学生姓名、专业名称
SELECT	studentname , majorname
FROM student s
JOIN major m
ON s.majorid = m.majorid
WHERE DATEDIFF(borndate,"1988-1-1") >0

#五、查询每个专业的男生人数和女生人数分别是多少
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 majorid
FROM student
WHERE studentname = "张翠山"
#查询专业一样的学生的最低分
SELECT majorid,MIN(score)
FROM result r
JOIN student s
ON r.studentno = s.studentno
WHERE majorid = (
	SELECT majorid
	FROM student
	WHERE studentname = "张翠山")

#七、查询大于60分的学生的姓名、密码、专业名
做法一：
#查询大于60分的学生编号
SELECT r.studentno
FROM result r
WHERE r.score>60
#查询学生编号相等的姓名、密码、专业名
SELECT s.studentname , s.loginpwd , m.majorname  ,s.studentno
FROM student s
JOIN major m
ON s.majorid = m.majorid 
WHERE s.studentno IN (SELECT r.studentno FROM result r WHERE r.score>60)

#做法二：
SELECT s.studentname , s.loginpwd , m.majorname  
FROM student s 
JOIN major m ON s.majorid = m.majorid 
JOIN result r ON s.studentno = r.studentno
WHERE r.score>60

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

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

#十、查询哪个专业没有学生，分别用左连接和右连接实现
#左连接
SELECT m.majorid , m.majorname , s.studentno
FROM  major m
LEFT JOIN student s ON m.majorid = s.majorid
WHERE s.studentno IS NULL
#右连接
SELECT m.majorid , m.majorname , s.studentno
FROM student s
RIGHT JOIN  major m ON m.majorid = s.majorid
WHERE s.studentno IS NULL

#十一、查询没有成绩的学生人数
SELECT COUNT(*)
FROM student s
JOIN result r
ON s.studentno = r.studentno
WHERE r.score IS NULL