作业讲解（student）

作业讲解（student）

一、查询每个专业的学生人数

SELECT MAJORID,COUNT(*)
FROM STUDENT 
GROUP BY MAJORID

二、查询参加考试的学生中，每个学生的平均分、最高分

SELECT REA.STUDENTNO,AVG(scORE),REM.MX
FROM RESULT AS REA
INNER JOIN (
	SELECT STUDENTNO,MAX(scORE) AS MX
	FROM RESULT
	GROUP BY STUDENTNO
)AS REM
ON REA.STUDENTNO=REM.STUDENTNO
GROUP BY STUDENTNO;

三、查询姓张的每个学生的最低分大于60的学号、姓名

SELECT S.STUDENTNO,S.STUDENTNAME,MIN(SCORE)
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
INNER JOIN MAJOR M
ON S.`majorid`=M.MAJORID
WHERE DATEDIFF(borndate,'1988-1-1')>0

五、查询专业的男生人数和女生人数分别是多少

# 方法一


SELECT MA.MAJORNAME,MSC.性别,MSC.人数 
FROM MAJOR AS MA
INNER JOIN (

	SELECT MAJORID AS M,SEX AS 性别,COUNT(*)AS 人数
	FROM STUDENT
	GROUP BY MAJORID,SEX
) AS MSC
ON MA.MAJORID=MSC.M


# 方法二


SELECT COUNT(*) 个数,sex,MAJORID
FROM STUDENT
GROUP BY MAJORID,SEX


# 方法三


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;

六、查询专业和张翠山一样的学生的最低分

#1.查询张翠山的专业编号
SELECT MAJORID 
FROM STUDENT
WHERE STUDENTNAME ='张翠山'
#2.查询编号等于1的所有学生编号
SELECT STUDENTNO
FROM STUDENT
WHERE MAJORID=(
	SELECT MAJORID 
	FROM STUDENT
	WHERE STUDENTNAME ='张翠山'
)
#3.查询最低分
SELECT MIN(SCORE)
FROM RESULT 
WHERE STUDENTNO IN(
	SELECT STUDENTNO
	FROM STUDENT
	WHERE MAJORID=(
		SELECT MAJORID 
		FROM STUDENT
		WHERE STUDENTNAME ='张翠山'
	)
)
## 七、查询大于60分的学生的姓名、密码、专业名
SELECT  STUDENTNAME,loginpwd,MAJORNAME
FROM student S
INNER JOIN RESULT R ON S.STUDENTNO=R.STUDENTNO
INNER JOIN MAJOR M ON S.MAJORID =M.MAJORID
WHERE R.SCORE>60

八、按邮箱位数分组，查询每组的学生个数

SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email)

九、查询学生名、专业名、分数

SELECT  STUDENTNAME,majorname,score
FROM student S
INNER JOIN RESULT R ON S.STUDENTNO=R.STUDENTNO
INNER JOIN MAJOR M ON S.MAJORID =M.MAJORID

十、查询哪个专业没有学生，分别用左连接和右连接实现

#左
SELECT M.*
FROM major M
LEFT JOIN STUDENT S
ON M.MAJORID=S.MAJORID
WHERE S.STUDENTNO IS NULL;
#右
SELECT M.*
FROM STUDENT S
RIGHT JOIN MAJOR M
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;