1.查询每个专业的学生

SELECT COUNT(*),majorid
FROM student
GROUP BY majorid;

2.参加考试的学生中，每个学生的平均分、最高分

SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

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

SELECT s.studentno,s.studentname,MIN(score)
FROM student s
JOIN reuslt r
ON s.studentno=r.studentno
WHERE s.studentname LIKE '张%'
GROUP BY s.studentno
HAVING MIN(score)>60;

4.查询生日在1988-1-1后的学生的姓名、专业名称

SELECT studentname,majorname
FROM student s
JOIN major m
ON s.majorid=m.majorid
WHERE DATEDIFF(borndate,'1988-1-1')>0; -- DATEDIFF 比较日前大小

5.查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*),sex,majorid
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;

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

SELECT majorid
FROM student
WHERE studentname='张翠山';

SELECT studentno
FROM student s
WHERE s.majorid=(
SELECT majorid
FROM student
WHERE studentname='张翠山'
);

SELECT MIN(score)
FROM result
WHERE studentno IN(
SELECT studentno
FROM student s
WHERE s.majorid=(
SELECT majorid
FROM student
WHERE studentname='张翠山'
)
);

7.查询大于60分的学生的姓名、密码、专业名 （三表连接）

SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m
ON s.majorid=m.majorid
JOIN result r
ON s.studentno=r.studentno
WHERE r.score>60;

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

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

9.查询学名、专业名、分数

SELECT studentname,majorname,score
FROM student s
JOIN major m
ON s.majorid=m.majorid
JOIN result r
ON s.studentno=r.studentno

10.查询哪个专业没有学生

SELECT m.majorid,m.majoridname,s.studentno
FROM major m
LEFT JOIN student s
ON s.majorid=m.majorid
WHERE s.studentno IS NULL;

SELECT m.majorid,m.majoridname,s.studentno
FROM student s
RIGHT JOIN major m
ON s.majorid=m.majorid
WHERE s.studentno IS NULL;

11.查询没有成绩的学生人数

SELECT COUNT(*)
FROM student s
LEFT JOIN result s
ON s.studentno=r.studentno
WHERE s.studentno IS NULL;