表连接练习题

 

以上是表信息 ⬆

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;