以上是表信息 ⬆
1.查询SCORE表中最高分学生的学号和课程号。
1) 查询分数为XXX的学生的学号和课程
SELECT SNO,CNO
FROM SCORES
WHERE SCORE = XXX;
2)查询最高成绩XXX
SELECT MAX(SCORE) FROM SCORES;
3) 合并
SELECT SNO,CNO
FROM SCORES
WHERE SCORE = (SELECT MAX(SCORE) FROM SCORES);
--表连接
SELECT *
FROM SCORES A
JOIN (SELECT MAX(SCORE) M FROM SCORES) B
ON A.SCORE = B.M;
2.查询"3-105"号课程的平均分。
SELECT AVG(SCORE)
FROM SCORES
WHERE CNO = '3-105';
3.查询每个班级的平均分。
SELECT A.CLASS,AVG(B.SCORE)
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
GROUP BY A.CLASS;
SELECT * FROM STUDENTS FOR UPDATE;
SELECT A.CLASS,SUM(B.SCORE)/COUNT(*)
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
GROUP BY A.CLASS;
--建议:将每个字段的表名前缀都加上
SELECT F_RANDOM(82) FROM DUAL;
4.查询"95033"班所选课程的平均分。
1)95033 限制的成绩
SELECT B.CNO,AVG(B.SCORE)
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
WHERE A.CLASS = '95033'
GROUP BY B.CNO;
SELECT *
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO ;
2)95033限制的课程
1)查询某些课程的各科平均分
SELECT CNO,AVG(SCORE)
FROM SCORES
WHERE CNO IN ()
GROUP BY CNO;
2)95033班级所选的课程
SELECT DISTINCT B.CNO
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
WHERE A.CLASS = '95033';
3)合并
SELECT CNO,AVG(SCORE)
FROM SCORES
WHERE CNO IN (SELECT DISTINCT B.CNO
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
WHERE A.CLASS = '95033')
GROUP BY CNO;
4)表连接
SELECT T1.CNO,AVG(T1.SCORE)
FROM SCORES T1
JOIN (SELECT DISTINCT B.CNO
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
WHERE A.CLASS = '95033') T2
ON T1.CNO = T2.CNO
GROUP BY T1.CNO;
5.查询SCORE表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(SCORE),CNO
FROM SCORES
WHERE CNO LIKE '3%'
GROUP BY CNO --按课程号分组
HAVING COUNT(*) >=5;
6.查询所有学生的SNAME、CNAME、SCORE和RANK列。
SELECT A.SNAME,C.CNAME,B.SCORE,D.RANK
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
LEFT JOIN COURSES C
ON B.CNO = C.CNO
LEFT JOIN GRADES D
ON B.SCORE BETWEEN D.LOW AND D.UPP;
7.查询最低分大于70,最高分小于90的SNAME列。
SELECT A.SNAME
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO
GROUP BY A.SNO,A.SNAME
HAVING MIN(B.SCORE) >70 AND MAX(B.SCORE) < 90;
SELECT *
FROM STUDENTS A
LEFT JOIN SCORES B
ON A.SNO = B.SNO;
110 匡明 99
110 匡明 66
8.查询成绩表中,选学了多门课程的同学中,每个人的非最高分成绩。
SELECT *
FROM SCORES A
LEFT JOIN (SELECT SNO,MAX(SCORE) M,COUNT(1) C FROM SCORES GROUP BY SNO) B
ON A.SNO = B.SNO
WHERE A.SCORE <> B.M
AND B.C >=2;
--考虑到只选了一门课程的,即最高分即分数本身,通过A.SCORE <> B.M 可以直接过滤掉这类学生
SELECT SCORE
FROM SCORES A
JOIN (SELECT SNO,MAX(SCORE) M,COUNT(1) C FROM SCORES GROUP BY SNO HAVING COUNT(*)>=2) B
ON A.SNO = B.SNO
WHERE SCORE <> M
SELECT * FROM SCORES A
LEFT JOIN (SELECT MAX(SCORE) M,COUNT(1) C FROM SCORES GROUP BY CNO) B
ON A.SNO=B.SNO WHERE A.SCORE<>B.M AND B.C>=1;
9.查询选修了"3-105"课程,且成绩高于"109"号同学该科成绩的同学的所有成绩信息。
SELECT A.*
FROM SCORES A
JOIN (SELECT CNO,SCORE FROM SCORES WHERE SNO ='109' AND CNO = '3-105')B
ON A.CNO = B.CNO
AND A.SCORE > B.SCORE;
10.查询"张旭"教师所教学生的成绩信息。
SELECT A.*
FROM SCORES A
LEFT JOIN COURSES B
ON A.CNO = B.CNO
LEFT JOIN TEACHERS C
ON B.TNO =C.TNO
WHERE C.TNAME = '张旭';
11.查询出"计算机系"教师所教课程的成绩表。
SELECT A.*
FROM SCORES A
LEFT JOIN COURSES B
ON A.CNO = B.CNO
LEFT JOIN TEACHERS C
ON B.TNO =C.TNO
WHERE C.DEPART = '计算机系';
12.查询选修人数超过5人的课程所对应的教师姓名。
SELECT C.TNAME
FROM SCORES A
LEFT JOIN COURSES B
ON A.CNO = B.CNO
LEFT JOIN TEACHERS C
ON B.TNO = C.TNO
GROUP BY C.TNAME,B.CNAME
HAVING COUNT(1) > 5;
--删除一个3-105的成绩,考虑不加B.CNAME 是否可以
SELECT C.TNAME
FROM (SELECT CNO FROM SCORES GROUP BY CNO HAVING COUNT(*) >5) A
JOIN COURSES B
ON A.CNO = B.CNO
JOIN TEACHERS C
ON B.TNO = C.TNO
13.查询成绩比该课程平均成绩低的成绩信息。
SELECT A.*
FROM SCORES A
LEFT JOIN (SELECT CNO,AVG(SCORE) S FROM SCORES GROUP BY CNO) B
ON A.CNO = B.CNO
WHERE A.SCORE < B.S;
SELECT A.*
FROM SCORES A
JOIN (SELECT CNO,AVG(SCORE) S FROM SCORES GROUP BY CNO) B
ON A.CNO = B.CNO
WHERE A.SCORE < B.S;
SELECT T1.*
FROM SCORES T1
JOIN (SELECT CNO,AVG(SCORE) B FROM SCORES GROUP BY CNO) T2
ON T1.SCORE<T2.B
14.查询所有未讲课的教师的TNAME和DEPART。
SELECT A.TNAME,A.DEPART
FROM TEACHERS A
LEFT JOIN COURSES B --1.压根没排课
ON A.TNO = B.TNO
LEFT JOIN SCORES C --2.排课了,但是没人选他的课
ON B.CNO = C.CNO
WHERE C.CNO IS NULL;
--如果要查询的数据来自于表连接中关联上的数据,那内连接和外连接都可以
--如果要查询的数据来自于表连接中关联不上的数据,那仅能选外连接
15.查询选修了"计算机导论"课程的、"男"同学的成绩表
SELECT A.*
FROM SCORES A
LEFT JOIN STUDENTS B
ON A.SNO = B.SNO
LEFT JOIN COURSES C
ON A.CNO = C.CNO
WHERE C.CNAME = '计算机导论'
AND B.SSEX = '男' ;
--选了计算机导论的男同学 的 所有成绩
SELECT *
FROM SCORES
WHERE SNO IN(SELECT A.SNO
FROM SCORES A
LEFT JOIN STUDENTS B
ON A.SNO = B.SNO
LEFT JOIN COURSES C
ON A.CNO = C.CNO
WHERE C.CNAME = '计算机导论'
AND B.SSEX = '男');
16.查询"男"教师及其所教的课程。
SELECT *
FROM TEACHERS A
LEFT JOIN COURSES B
ON A.TNO = B.TNO
WHERE A.TSEX = '男';
17.查询和"李军"同性别并同班的学生姓名。
SELECT A.SNAME
FROM STUDENTS A
JOIN STUDENTS B
ON A.SNAME <> '李军'
AND B.SNAME ='李军'
AND A.SSEX = B.SSEX
AND A.CLASS = B.CLASS;