SQL经典练习题（1）

已知有如下4张表：

从上到下代表：课程表、成绩表、学生表及教师表。
课程表的创建如下：
成绩表的创建如下：
学生表的创建如下：
教师表的创建如下：
接下来就是向数据表中插入合适的数据，sql执行语句如下：

/*插入数据到student表中*/
INSERT INTO student (sname,sage,ssex) VALUES ('张三',14,'男');
INSERT INTO student (sname,sage,ssex) VALUES ('李四',16,'男');
INSERT INTO student (sname,sage,ssex) VALUES ('王五',18,'男');
INSERT INTO student (sname,sage,ssex) VALUES ('赵六',12,'男');
INSERT INTO student (sname,sage,ssex) VALUES ('陈七',18,'男');
INSERT INTO student (sname,sage,ssex) VALUES ('王九',16,'女');
INSERT INTO student (sname,sage,ssex) VALUES ('刘十',15,'女');
INSERT INTO student (sname,sage,ssex) VALUES ('猴哥',17,'男');
INSERT INTO student (sname,sage,ssex) VALUES ('八戒',12,'女');
INSERT INTO student (sname,sage,ssex) VALUES ('师弟',14,'女');

/*插入数据到course表*/
INSERT INTO course (cno,cname,tno) VALUE (1001,'英语','xh01');
INSERT INTO course (cname,tno) VALUE ('计算机','xh02');
INSERT INTO course (cname,tno) VALUE ('语文','xh03');
INSERT INTO course (cname,tno) VALUE ('数学','xh04');
INSERT INTO course (cname,tno) VALUE ('体育','xh05');
INSERT INTO course (cname,tno) VALUE ('政治','xh06');
INSERT INTO course (cname,tno) VALUE ('美术','xh07');
INSERT INTO course (cname,tno) VALUE ('马克思','xh08');
INSERT INTO course (cname,tno) VALUE ('化学','xh09');
INSERT INTO course (cname,tno) VALUE ('物理','xh10');

/*插入数据到teacher表中*/
INSERT INTO teacher (tno,tname) VALUES ('xh01','唐僧');
INSERT INTO teacher (tno,tname) VALUES ('xh02','如来');
INSERT INTO teacher (tno,tname) VALUES ('xh03','观音');
INSERT INTO teacher (tno,tname) VALUES ('xh04','妖怪');
INSERT INTO teacher (tno,tname) VALUES ('xh05','李哥');
INSERT INTO teacher (tno,tname) VALUES ('xh06','小狗');
INSERT INTO teacher (tno,tname) VALUES ('xh07','老吴');
INSERT INTO teacher (tno,tname) VALUES ('xh08','金钱');
INSERT INTO teacher (tno,tname) VALUES ('xh09','刘亦菲');
INSERT INTO teacher (tno,tname) VALUES ('xh10','大哥');

/*插入数据到score表中*/
/*学生1的成绩 */
INSERT INTO score (sno,cno,score) VALUES (1,1001,88);
INSERT INTO score (sno,cno,score) VALUES (1,1002,94);
INSERT INTO score (sno,cno,score) VALUES (1,1003,96);
INSERT INTO score (sno,cno,score) VALUES (1,1004,98);
INSERT INTO score (sno,cno,score) VALUES (1,1005,91);
INSERT INTO score (sno,cno,score) VALUES (1,1006,87);
INSERT INTO score (sno,cno,score) VALUES (1,1007,97);
INSERT INTO score (sno,cno,score) VALUES (1,1008,92);
INSERT INTO score (sno,cno,score) VALUES (1,1009,100);
/*学生2的成绩*/
INSERT INTO score (sno,cno,score) VALUES (2,1001,91);
INSERT INTO score (sno,cno,score) VALUES (2,1002,95);
INSERT INTO score (sno,cno,score) VALUES (2,1003,97);
INSERT INTO score (sno,cno,score) VALUES (2,1004,99);
INSERT INTO score (sno,cno,score) VALUES (2,1005,92);
INSERT INTO score (sno,cno,score) VALUES (2,1006,97);
INSERT INTO score (sno,cno,score) VALUES (2,1007,98);
INSERT INTO score (sno,cno,score) VALUES (2,1008,93);
INSERT INTO score (sno,cno,score) VALUES (2,1009,99);
/*学生3的成绩*/
INSERT INTO score (sno,cno,score) VALUES (3,1001,76);
INSERT INTO score (sno,cno,score) VALUES (3,1002,88);
INSERT INTO score (sno,cno,score) VALUES (3,1003,54);
INSERT INTO score (sno,cno,score) VALUES (3,1004,77);
INSERT INTO score (sno,cno,score) VALUES (3,1005,65);
INSERT INTO score (sno,cno,score) VALUES (3,1006,87);
INSERT INTO score (sno,cno,score) VALUES (3,1007,86);
INSERT INTO score (sno,cno,score) VALUES (3,1008,98);
INSERT INTO score (sno,cno,score) VALUES (3,1009,84);
/*学生4的成绩*/
INSERT INTO score (sno,cno,score) VALUES (4,1001,91);
INSERT INTO score (sno,cno,score) VALUES (4,1002,67);
INSERT INTO score (sno,cno,score) VALUES (4,1003,76);
INSERT INTO score (sno,cno,score) VALUES (4,1004,87);
INSERT INTO score (sno,cno,score) VALUES (4,1005,76);
INSERT INTO score (sno,cno,score) VALUES (4,1006,91);
INSERT INTO score (sno,cno,score) VALUES (4,1007,65);
INSERT INTO score (sno,cno,score) VALUES (4,1008,56);
INSERT INTO score (sno,cno,score) VALUES (4,1009,70);
/*学生5的成绩*/
INSERT INTO score (sno,cno,score) VALUES (5,1001,55);
INSERT INTO score (sno,cno,score) VALUES (5,1002,69);
INSERT INTO score (sno,cno,score) VALUES (5,1003,87);
INSERT INTO score (sno,cno,score) VALUES (5,1004,67);
INSERT INTO score (sno,cno,score) VALUES (5,1005,69);
INSERT INTO score (sno,cno,score) VALUES (5,1006,79);
INSERT INTO score (sno,cno,score) VALUES (5,1007,88);
INSERT INTO score (sno,cno,score) VALUES (5,1008,91);
INSERT INTO score (sno,cno,score) VALUES (5,1009,90);
/*学生6的成绩*/
INSERT INTO score (sno,cno,score) VALUES (6,1001,78);
INSERT INTO score (sno,cno,score) VALUES (6,1002,77);
INSERT INTO score (sno,cno,score) VALUES (6,1003,75);
INSERT INTO score (sno,cno,score) VALUES (6,1004,75);
INSERT INTO score (sno,cno,score) VALUES (6,1005,78);
INSERT INTO score (sno,cno,score) VALUES (6,1006,73);
INSERT INTO score (sno,cno,score) VALUES (6,1007,98);
INSERT INTO score (sno,cno,score) VALUES (6,1008,91);
INSERT INTO score (sno,cno,score) VALUES (6,1009,79);
/*学生7的成绩*/
INSERT INTO score (sno,cno,score) VALUES (7,1001,89);
INSERT INTO score (sno,cno,score) VALUES (7,1002,56);
INSERT INTO score (sno,cno,score) VALUES (7,1003,78);
INSERT INTO score (sno,cno,score) VALUES (7,1004,84);
INSERT INTO score (sno,cno,score) VALUES (7,1005,81);
INSERT INTO score (sno,cno,score) VALUES (7,1006,71);
INSERT INTO score (sno,cno,score) VALUES (7,1007,83);
INSERT INTO score (sno,cno,score) VALUES (7,1008,72);
INSERT INTO score (sno,cno,score) VALUES (7,1009,91);
/*学生8的成绩*/
INSERT INTO score (sno,cno,score) VALUES (8,1001,91);
INSERT INTO score (sno,cno,score) VALUES (8,1002,82);
INSERT INTO score (sno,cno,score) VALUES (8,1003,83);
INSERT INTO score (sno,cno,score) VALUES (8,1004,84);
INSERT INTO score (sno,cno,score) VALUES (8,1005,87);
INSERT INTO score (sno,cno,score) VALUES (8,1006,78);
INSERT INTO score (sno,cno,score) VALUES (8,1007,91);
INSERT INTO score (sno,cno,score) VALUES (8,1008,68);
INSERT INTO score (sno,cno,score) VALUES (8,1009,89);
/*学生9的成绩*/
INSERT INTO score (sno,cno,score) VALUES (9,1001,89);
INSERT INTO score (sno,cno,score) VALUES (9,1002,75);
INSERT INTO score (sno,cno,score) VALUES (9,1003,78);
INSERT INTO score (sno,cno,score) VALUES (9,1004,69);
INSERT INTO score (sno,cno,score) VALUES (9,1005,90);
INSERT INTO score (sno,cno,score) VALUES (9,1006,60);
INSERT INTO score (sno,cno,score) VALUES (9,1007,58);
INSERT INTO score (sno,cno,score) VALUES (9,1008,91);
INSERT INTO score (sno,cno,score) VALUES (9,1009,67);
/*学生10的成绩*/
INSERT INTO score (sno,cno,score) VALUES (10,1001,65);
INSERT INTO score (sno,cno,score) VALUES (10,1002,98);
INSERT INTO score (sno,cno,score) VALUES (10,1003,57);
INSERT INTO score (sno,cno,score) VALUES (10,1004,65);
INSERT INTO score (sno,cno,score) VALUES (10,1005,48);
INSERT INTO score (sno,cno,score) VALUES (10,1006,49);
INSERT INTO score (sno,cno,score) VALUES (10,1007,85);
INSERT INTO score (sno,cno,score) VALUES (10,1008,72);
INSERT INTO score (sno,cno,score) VALUES (10,1009,70);

在做练习题之前需要记住：查哪张表用哪张表，查多张表用多张表，涉及嵌套多层查询的分步编写sql。同时注意基本的优先级顺序
( 8 ) SELECT ( 9 ) DISTINCT ( 11 ) < Top Num > < select list >
( 1 ) FROM [ left_table ]
( 3 ) < join_type > JOIN < right_table >
( 2 ) ON < join_condition >
( 4 ) WHERE < where_condition >
( 5 ) GROUP BY < group_by_list >
( 6 ) WITH < CUBE | RollUP >
( 7 ) HAVING < having_condition >
( 10 ) ORDER BY < order_by_list >
练习50题：
1、查询课程编号为“1001”的课程比“1002”的课程成绩高的所有学生的学号：

SELECT 
  a.sno,
  a.score,
  b.score
FROM
  score a 
  INNER JOIN
  score b 
  ON a.cno = 1001 AND b.cno = 1002  AND a.sno = b.sno 
  AND a.score > b.score 

结果为：

备注：自连接很常用，比如研发过程中的权限分配的sql
2、查询平均成家大于60分的学生的学号和平均成绩：

SELECT 
  sno "学号",
  AVG(score) "平均成绩" 
FROM
  score 
GROUP BY sno HAVING AVG(score) > 60 

结果为：

备注：研发中一些分组统计常用到Group by
3、查询所有的学生的学号、姓名、选课数、总成绩

SELECT 
  a.sno "学号",
  a.sname "姓名",
  COUNT(b.score) "选课数",
  SUM(b.score) "总成绩" 
FROM
  student a 
  INNER JOIN
  score b 
  ON a.sno = b.sno 
GROUP BY b.sno 

结果为：
4、查询姓“刘”的老师的个数

SELECT 
  COUNT(tname)  "姓“刘”的老师的个数"
FROM
  teacher 
WHERE tname LIKE "刘%" 

结果为:

5、查询没学过“大哥”老师课的学生的学号和姓名

SELECT 
  d.sno '学号',
  d.sname '姓名' 
FROM
  student d 
WHERE EXISTS 
  (SELECT DISTINCT 
    (c.sno) 
  FROM
    score c 
  WHERE NOT EXISTS 
    (SELECT 
      b.cno 
    FROM
      course b 
    WHERE EXISTS 
      (SELECT 
        a.tno 
      FROM
        teacher a 
      WHERE a.tname = '大哥' 
        AND a.tno = b.tno) 
      AND b.cno = c.cno) 
    AND d.sno = c.sno)

结果为：

注：本题目考察In的嵌套使用，但我是用了exists取代了in，因为在sql优化中明确了In和Exists之间的性能，如果查询的两个表带下相当，那么IN和EXISTS差别不大，如果两个表一个较小，一个较大，则子查询表大的用exists，子查询表小的用in，not in进行内外表的全表扫描，没有用到索引；而not exists的子查询依然能用到表中的索引，索引not exists效率更高，个人建议尽量不要使用in或not in。
6、查询学过“唐僧”老师所教的所有课的同学学号、姓名

SELECT 
  d.sname "姓名",
  d.sno "学号"
FROM
  student d 
  INNER JOIN
  (SELECT DISTINCT 
    (c.sno) 
  FROM
    score c 
  WHERE EXISTS 
    (SELECT 
      a.cno 
    FROM
      course a 
      LEFT JOIN
      teacher b 
      ON a.tno = b.tno 
    WHERE b.tname = "唐僧" 
      AND c.cno = a.cno)) e 
  ON d.sno = e.sno 

结果为：

7、查询学过“1001”并且也学过编号“1002”课程的学生学号和姓名

SELECT 
  a.sname '姓名',
  a.sno '学号' 
FROM
  student a 
  LEFT JOIN
  score b 
  ON b.cno = 1001 
  AND b.cno = 1002 

结果为：

8、查询课程编号“1002”的成绩比课程编号“1001”课程低的所有同学的学号和姓名

SELECT 
  c.sname '姓名',
  c.sno '学号' 
FROM
  student c 
WHERE EXISTS 
  (SELECT 
    aa.sno 
  FROM
    (SELECT 
      a.sno,
      a.score 
    FROM
      score a 
    WHERE a.cno = 1002) aa 
    LEFT JOIN
    (SELECT 
      b.sno,
      b.score 
    FROM
      score b 
    WHERE b.cno = 1001) bb 
    ON aa.sno = bb.sno 
  WHERE aa.score < bb.score 
    AND C.sno = aa.sno)

结果为：

9、查询所有课程成绩小于92的同学的学号、姓名：

SELECT 
  b.sno '学号',
  b.sname '姓名' 
FROM
  student b 
  INNER JOIN
  (SELECT 
    a.sno 
  FROM
    score a 
  GROUP BY a.sno 
  HAVING MAX(a.score) < 92) aa 
  ON b.sno = aa.sno 

结果为：

10、查询没有学全所有课的同学的学号、姓名

SELECT 
  a.sname '姓名',
  a.sno '学号' 
FROM
  student a 
  RIGHT JOIN
  score b 
  ON a.sno = b.sno 
GROUP BY b.sno 
HAVING COUNT(b.sno) < 
  (SELECT 
    COUNT(cno) 
  FROM
    course)

结果为：
11、查询至少有一门课与学号为“1”同学所学相同课程的同学的学号和姓名：

SELECT DISTINCT 
  (a.sname),
  b.sno 
FROM
  student a 
  RIGHT JOIN
  score b 
  ON a.sno = b.sno 
WHERE EXISTS 
  (SELECT 
    c.cno 
  FROM
    score c 
  WHERE c.sno = 1
    AND b.cno = c.cno AND a.sno !=1 )

结果为：

12、检索“1001”课程分数小于90，按分数降序排列的同学学号：

SELECT 
  sno '学号',
  score '成绩' 
FROM
  score 
WHERE cno = 1001 
  AND score < 90 
ORDER BY score DESC 

结果为：

13、查询两门以上不及格课程的同学的学号以及其平均成绩

SELECT 
  b.sno '学号',
  AVG(b.score) '平均成绩'
FROM
  score b 
WHERE b.sno IN 
  (SELECT 
    a.sno 
  FROM
    score a 
  WHERE a.score < 60 
  GROUP BY a.sno 
  HAVING COUNT(a.score) > 2) 
GROUP BY b.sno 

结果为：

14、查询不同课程成绩相同的学生和学号、课程号、学生成绩：

SELECT 
  a.cno,
  a.score,
  a.sno 
FROM
  score a 
  LEFT JOIN
  score b 
  ON a.score = b.score 
WHERE a.cno != b.cno 

15、统计下列各科成绩，各分数段人数：课程ID，课程名称，[100-85],[85-70],[70-60],[ 小于60] ：

    SELECT a.cno '课程ID', b.cname '课程名称',
    SUM(CASE WHEN a.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) '[100 - 85]'  ,
    SUM(CASE WHEN a.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)  '[85 - 70]',
    SUM(CASE WHEN a.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END)  '[70 - 60]',
    SUM(CASE WHEN a.score < 60 THEN 1 ELSE 0 END)  '[60 -]'     
    FROM score a,course b    
    WHERE a.cno = b.cno 
    GROUP BY a.cno,b.cname 

结果为：

转载：https://blog.csdn.net/qq_33944946/article/details/102463029