SELECT AVG(degree)
FROM score
WHERE cno = '3-105';
-------------------------------------------------------
AVG(degree)
81.50000
2.8 avg：查询95033班所选课程的平均分
SELECT AVG(degree)
FROM score
WHERE sno IN
(SELECT sno
FROM student
WHERE class = '95033');
-------------------------------------------------------
AVG(degree)
79.66667
2.9 avg：查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
SELECT cno, AVG(degree)
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(*) > 5;
-------------------------------------------------------
cno AVG(degree)
3-105 81.50000
2.10 count：查询95031班的学生人数
SELECT COUNT(*)
FROM STUDENT
WHERE class = '95031';
-------------------------------------------------------
COUNT(*)
3
暗含了要根据sno分组
SELECT sno
FROM score
GROUP BY sno
HAVING min(degree) > 70 AND max(degree) < 90;
-------------------------------------------------------
sno
105
108
SELECT a.sname, b.cname, c.degree
FROM student a
JOIN score c ON a.sno = c.sno
JOIN course b ON b.cno = c.cno;
-------------------------------------------------------
SELECT student.sname, course.cname, score.degree
FROM student
INNER JOIN score ON student.sno = score.sno
INNER JOIN course ON score.cno = course.cno;
-------------------------------------------------------
SELECT student.sname, course.cname, score.degree
FROM student, score, course
WHERE student.sno = score.sno
AND score.cno = course.cno;
-------------------------------------------------------
sname cname degree
曾华 计算机导论 78.0
曾华 数据电路 81.0
匡明 操作系统 75.0
匡明 计算机导论 88.0
王丽 计算机导论 91.0
李军 计算机导论 64.0
李军 数据电路 85.0
王芳 操作系统 68.0
王芳 计算机导论 76.0
陆君 操作系统 86.0
陆君 计算机导论 92.0
3.4 查询所有任课教师的Tname和Depart
SELECT tname, depart
FROM teacher a
WHERE tno IN
(SELECT DISTINCT tno
FROM course);
-------------------------------------------------------
tname depart
王萍 计算机系
李诚 计算机系
张旭 电子工程系
刘冰 电子工程系
3.5 查询至少有2名男生的班号
SELECT CLASS
FROM student
WHERE ssex = '男'
GROUP BY CLASS
HAVING count(ssex) > 1;
-------------------------------------------------------
CLASS
95031
95033
3.6 查询成绩比该课程平均成绩低的同学的成绩表
SELECT a.*
FROM score a
WHERE degree <
(SELECT AVG(degree)
FROM score b
WHERE b.cno = a.cno);
-------------------------------------------------------
SNO CNO DEGREE
105 3-245 75.0
109 3-245 68.0
109 3-105 76.0
101 3-105 64.0
108 3-105 78.0
108 6-166 81.0
3.7 查询存在有85分以上成绩的课程Cno
SELECT cno
FROM score
GROUP BY cno
HAVING MAX(degree) > 85;
-------------------------------------------------------
cno
3-105
3-245
3.8 查询“张旭“教师任课的学生成绩
SELECT *
FROM score
WHERE CNO = (
SELECT CNO
FROM course
JOIN teacher ON course.TNO = teacher.TNO
AND teacher.TNAME = '张旭'
)
-------------------------------------------------------
SNO CNO DEGREE
101 6-166 85.0
108 6-166 81.0
3.9 查询选修某课程的同学人数多于5人的教师姓名
SELECT tname
FROM teacher
WHERE tno IN
(SELECT tno
FROM course
WHERE cno IN
(SELECT cno
FROM score
GROUP BY cno
HAVING count(sno) > 5));
-------------------------------------------------------
tname
王萍
-------------------------------------------------------------------------------------------------------------
SELECT tname
FROM teacher
WHERE tno IN
(SELECT c.tno
FROM course c,score s
WHERE c.cno = s.cno
GROUP BY c.tno
HAVING COUNT(c.tno) > 5);
-------------------------------------------------------
tname
王萍
3.10 查询出“计算机系“教师所教课程的成绩表
SELECT *
FROM score
WHERE cno IN
(SELECT cno
FROM course, teacher
WHERE depart = '计算机系'
AND course.tno = teacher.tno);
-------------------------------------------------------
SNO CNO DEGREE
103 3-245 86.0
105 3-245 75.0
109 3-245 68.0
103 3-105 92.0
105 3-105 88.0
109 3-105 76.0
101 3-105 64.0
107 3-105 91.0
108 3-105 78.0