你与写的一手好sql的大佬可能就差这一道题!-阿里云开发者社区

你与写的一手好sql的大佬可能就差这一道题!

2022-10-12 90

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 你与写的一手好sql的大佬可能就差这一道题!

你与写的一手好sql的大佬可能就差这一道题!

1:建表语句

1.1:课程表

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `c`
-- ----------------------------
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `CNO` int(11) NOT NULL DEFAULT '0' COMMENT '课程号',
  `CNAME` varchar(11) DEFAULT NULL COMMENT '课程名称',
  `CTEACHER` varchar(11) DEFAULT NULL COMMENT '课程老师名称',
  PRIMARY KEY (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of c
-- ----------------------------
INSERT INTO `c` VALUES ('1', '科学', '科学老师');
INSERT INTO `c` VALUES ('2', '编程', '编程老师');
INSERT INTO `c` VALUES ('3', '美术', '何炅老师');

1.2:学生表

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `s`
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
  `SNO` int(11) NOT NULL DEFAULT '0' COMMENT '学号',
  `SNAME` varchar(11) DEFAULT NULL,
  `AGE` int(2) DEFAULT NULL,
  `SEX` int(1) DEFAULT NULL COMMENT '0-女 1-男',
  PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of s
-- ----------------------------
INSERT INTO `s` VALUES ('1', '张三', '1', '1');
INSERT INTO `s` VALUES ('2', '李四', '2', '1');
INSERT INTO `s` VALUES ('3', '小丽', '3', '0');
INSERT INTO `s` VALUES ('4', '小花', '4', '0');
INSERT INTO `s` VALUES ('5', '王二麻子', '5', '1');

1.3:学生和课程关系表

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `sc`
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `SNO` int(11) DEFAULT NULL COMMENT '学号',
  `CNO` int(22) DEFAULT NULL COMMENT '课程号',
  `SCGRADE` decimal(11,2) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '70.00');
INSERT INTO `sc` VALUES ('1', '2', '56.00');
INSERT INTO `sc` VALUES ('1', '3', '90.00');
INSERT INTO `sc` VALUES ('2', '1', '2.00');
INSERT INTO `sc` VALUES ('2', '3', '70.00');
INSERT INTO `sc` VALUES ('3', '3', '60.00');
INSERT INTO `sc` VALUES ('4', '3', '90.00');
INSERT INTO `sc` VALUES ('2', '2', '56.00');
INSERT INTO `sc` VALUES ('3', '1', '45.00');
INSERT INTO `sc` VALUES ('3', '2', '12.00');
INSERT INTO `sc` VALUES ('4', '1', '22.00');
INSERT INTO `sc` VALUES ('4', '2', '55.00');

1.4:学生和课程关系拓展表

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `sc_c`
-- ----------------------------
DROP TABLE IF EXISTS `sc_c`;
CREATE TABLE `sc_c` (
  `CNO` int(11) NOT NULL DEFAULT '0',
  `CNAME` varchar(11) DEFAULT NULL,
  `AVG_GRADE` decimal(11,4) DEFAULT NULL,
  PRIMARY KEY (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sc_c
-- ----------------------------

2:题目

1:把SC表中每门课程的平均成绩插入到另外一个已经存在的表SC_C(CNO,CNAME,AVG_GRADE)中,其中AVG_GRADE表示每门课程的平均成绩
2:从SC表中把选择何炅老师的课程的女生的选课记录删除
3:查询选何炅老师的课程的女生且成绩在80分以上
4:找出没有选修过何炅老师的课程的所有学生的姓名(两种写法)
5:列出有两门以上(包含两门)不及格课程(成绩小于60分)的学生的姓名及其平均成绩(两种方式)   
6:列举出即学过1(科学)又学过2(编程)的所有学生姓名(两种方式)
7:列出1(科学)成绩比2(编程)的学生成绩高的所有学生的学号和姓名
8:列出1(科学)成绩比2(编程)的学生成绩高的所有学号及其1号课(科学)和2号课(编程)的成绩

3:参考答案

-- 1:把SC表中每门课程的平均成绩插入到另外一个已经存在的表SC_C(CNO,CNAME,AVG_GRADE)中,其中AVG_GRADE表示每门课程的平均成绩
INSERT INTO SC_C (CNO, CNAME, AVG_GRADE) SELECT
  SC.CNO,
  C.CNAME,
  AVG(SC.SCGRADE)
FROM
  `SC`
INNER JOIN C ON C.CNO = SC.CNO
GROUP BY
  SC.CNO;
-- 2:从SC表中把选择何炅老师的课程的女生的选课记录删除
DELETE
FROM
  SC,
  S,
  C
WHERE
  SC.SNO = S.SNO
AND SC.CNO = C.CNO
AND C.CTEACHER = '何炅老师'
-- 3:查询选何炅老师的课程的女生且成绩在80分以上
SELECT
  S.*
FROM
  S
INNER JOIN SC ON SC.SNO = S.SNO
INNER JOIN C ON C.CNO = SC.CNO
WHERE
  C.CTEACHER = '何炅老师'
AND SC.SCGRADE > 80
AND S.SEX = 0 
-- 4:找出没有选修过何炅老师的课程的所有学生的姓名(两种写法)
SELECT
  S.SNAME
FROM
  S
WHERE
  S.SNO NOT IN (
    SELECT
      S.SNO
    FROM
      S
    INNER JOIN SC ON SC.SNO = S.SNO
    INNER JOIN C ON C.CNO = SC.CNO
    WHERE
      C.CTEACHER = '何炅老师'
  ) 
SELECT
    SNAME
  FROM
    S
  WHERE
    NOT EXISTS (
      SELECT
        *
      FROM
        SC,
        C
      WHERE
        SC.CNO = SC.CNO
      AND SC.SNO = S.SNO
      AND C.CTEACHER = '何炅老师'
    ) 
-- 5:列出有两门以上(包含两门)不及格课程(成绩小于60分)的学生的姓名及其平均成绩(两种方式)
SELECT
  S.SNAME,
  AVG(sc.SCGRADE)
FROM
  SC
INNER JOIN S ON S.SNO = sc.SNO
WHERE
  sc.SNO IN (
    SELECT
      sc.SNO
    FROM
      SC
    WHERE
      SC.SCGRADE < 60
    GROUP BY
      sc.SNO
    HAVING
      COUNT(1)>= 2
  )
GROUP BY
  sc.SNO;
SELECT
  S.SNAME,
  AVG(sc.SCGRADE)
FROM
  s,
  sc,
  (
    SELECT
      sc.SNO
    FROM
      SC
    WHERE
      SC.SCGRADE < 60
    GROUP BY
      sc.SNO
    HAVING
      COUNT(1) >= 2
  ) A
WHERE
  A.SNO = s.SNO
AND s.SNO = sc.SNO
GROUP BY
  sc.SNO
-- 6:列举出即学过1(科学)又学过2(编程)的所有学生姓名(两种方式)
SELECT
  s.SNAME
FROM
  s,
  sc
WHERE
  s.sno = sc.SNO
AND sc.CNO = 2
AND sc.SNO IN (
  SELECT
    sc.SNO
  FROM
    sc
  WHERE
    sc.CNO = 1
) 
SELECT
    s.SNAME
  FROM
    s,
    (
      SELECT
        SNO
      FROM
        sc
      WHERE
        CNO IN (1, 2)
      GROUP BY
        SNO
      HAVING
        count(CNO) >= 2
    ) AS a
  WHERE
    s.sno = a.SNO
-- 7:列出1(科学)成绩比2(编程)的学生成绩高的所有学生的学号和姓名
SELECT
  s.sno,
  s.SNAME
FROM
  s,
  (
    SELECT
      sc.SNO,
      sc.SCGRADE
    FROM
      sc
    WHERE
      sc.CNO = 1
  ) k,
  (
    SELECT
      sc.SNO,
      sc.SCGRADE
    FROM
      sc
    WHERE
      sc.CNO = 2
  ) b
WHERE
  s.SNO = k.SNO
AND k.SNO = b.SNO
AND k.SCGRADE > b.SCGRADE
-- 8:列出1(科学)成绩比2(编程)的学生成绩高的所有学号及其1号课(科学)和2号课(编程)的成绩
SELECT
  s.sno,
  s.SNAME,
  k.SCGRADE AS "1",
  b.SCGRADE AS "2"
FROM
  s,
  (
    SELECT
      sc.SNO,
      sc.SCGRADE
    FROM
      sc
    WHERE
      sc.CNO = 1
  ) k,
  (
    SELECT
      sc.SNO,
      sc.SCGRADE
    FROM
      sc
    WHERE
      sc.CNO = 2
  ) b
WHERE
  s.SNO = k.SNO
AND k.SNO = b.SNO
AND k.SCGRADE > b.SCGRADE