sql练习题

àì夳堔傛蜴生んèń 2021-12-22 13:07 302阅读 0赞

# 建表和插入数据库的sql #

DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course`  (
      `cid` int(11) NOT NULL,
      `cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `tid` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`cid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES (1, 'c语言程序设计', 102);
    INSERT INTO `course` VALUES (2, '数据结构', 102);
    INSERT INTO `course` VALUES (3, '计算机操作系统', 101);
    INSERT INTO `course` VALUES (4, '计算机网络', 101);
    INSERT INTO `course` VALUES (5, 'java程序设计', 103);
    INSERT INTO `course` VALUES (6, '编译原理', 103);
    INSERT INTO `course` VALUES (7, '算法(4)', 102);
    
    -- ----------------------------
    -- Table structure for sc
    -- ----------------------------
    DROP TABLE IF EXISTS `sc`;
    CREATE TABLE `sc`  (
      `sid` int(11) NOT NULL,
      `cid` int(11) NULL DEFAULT NULL,
      `score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of sc
    -- ----------------------------
    INSERT INTO `sc` VALUES (1001, 1, '88');
    INSERT INTO `sc` VALUES (1001, 2, '66');
    INSERT INTO `sc` VALUES (1001, 3, '88');
    INSERT INTO `sc` VALUES (1001, 4, '88');
    INSERT INTO `sc` VALUES (1001, 5, '88');
    INSERT INTO `sc` VALUES (1002, 1, '77');
    INSERT INTO `sc` VALUES (1002, 2, '68');
    INSERT INTO `sc` VALUES (1002, 5, '77');
    INSERT INTO `sc` VALUES (1003, 3, '59');
    INSERT INTO `sc` VALUES (1003, 4, '66');
    INSERT INTO `sc` VALUES (1003, 5, '58');
    INSERT INTO `sc` VALUES (1004, 1, '68');
    INSERT INTO `sc` VALUES (1004, 3, '68');
    INSERT INTO `sc` VALUES (1004, 6, '68');
    INSERT INTO `sc` VALUES (1005, 2, '56');
    INSERT INTO `sc` VALUES (1005, 5, '22');
    INSERT INTO `sc` VALUES (1001, 7, '75');
    INSERT INTO `sc` VALUES (1002, 7, '80');
    INSERT INTO `sc` VALUES (1001, 6, '78');
    INSERT INTO `sc` VALUES (1002, 3, '66');
    INSERT INTO `sc` VALUES (1002, 4, '67');
    INSERT INTO `sc` VALUES (1002, 6, '78');
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `sid` int(11) NOT NULL,
      `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `sage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `ssex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`sid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES (1001, '小明', '22', '1');
    INSERT INTO `student` VALUES (1002, '小华', '23', '1');
    INSERT INTO `student` VALUES (1003, '小静', '21', '0');
    INSERT INTO `student` VALUES (1004, '小强', '22', '1');
    INSERT INTO `student` VALUES (1005, '小美', '22', '0');
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher`  (
      `tid` int(11) NOT NULL,
      `tname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`tid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES (101, '张伟');
    INSERT INTO `teacher` VALUES (102, '叶平');
    INSERT INTO `teacher` VALUES (103, '李飞飞');
    INSERT INTO `teacher` VALUES (104, '李明');

习题和答案

-- 1、查询“1”课程比“2”课程成绩高的所有学生的学号；
    SELECT
    	a.sid 
    FROM
    	( SELECT sid, score FROM sc WHERE cid = 1 ) a
    	JOIN ( SELECT sid, score FROM sc WHERE cid = 2 ) b ON a.sid = b.sid 
    WHERE
    	a.score > b.score;
    	
    -- 也可以这样写
    SELECT
    	a.sid
    FROM
    	sc a,
    	sc b 
    WHERE
    	a.sid = b.sid 
    	AND a.cid = '1' 
    	AND b.cid = '2' 
    	AND a.score > b.score
    	
    	
    
     -- 2 查询平均成绩大于60分的同学的学号和平均成绩；
    
    SELECT
    	sid,
    	avg( score ) 
    FROM
    	sc 
    GROUP BY
    	sid 
    HAVING
    	avg( score ) > 60;
    
    -- 3 查询所有同学的学号、姓名、选课数、总成绩
    
    SELECT
    	stu.sid,
    	stu.sname,
    	count( cid ),
    	sum( score ) 
    FROM
    	student stu
    	JOIN sc s ON stu.sid = s.sid 
    GROUP BY
    	stu.sid
    
    
    -- 4 查询姓“李”的老师的个数；
    SELECT
    	count( tid ) 
    FROM
    	teacher 
    WHERE
    	tname LIKE '李%'
     
    -- 5 查询没学过“叶平”老师课的同学的学号、姓名；
    -- 此道题可分为三步解决 
    
    -- 5.1 叶平老师所带的课程编号
      SELECT
    cid 
    FROM
    	course 
    WHERE
    	tid = ( SELECT tid FROM teacher WHERE tname = "叶平" );
    	
    	
      -- 5.2 选过叶平老师课程的学生编号(重要)
    SELECT DISTINCT sid 
    FROM
    	sc 
    WHERE
    	cid IN ( SELECT cid FROM course WHERE tid = ( SELECT tid FROM teacher WHERE tname = "叶平" ) );
    	
      -- 5.3 没有选过叶平老师课程的学生
      SELECT
    sid,
    sname 
    FROM
    	student 
    WHERE
    	sid NOT IN (
    SELECT DISTINCT
    	sid 
    FROM
    	sc 
    WHERE
    	cid IN ( SELECT cid FROM course WHERE tid = ( SELECT tid FROM teacher WHERE tname = "叶平" ) ) 
    	);
    
    
    
    -- 6 查询学过编号“1”并且也学过编号“2”课程的同学的学号、姓名；
    
    SELECT
    	sid,
    	sname 
    FROM
    	student 
    WHERE
    	sid IN (
    SELECT
    	a.sid 
    FROM
    	( SELECT sid FROM sc WHERE cid = 1 ) AS a 
    WHERE
    	a.sid IN ( SELECT sid FROM sc WHERE cid = 2 )
    )
    -- 这道题如果你抽风容易写成 where sid=1 and sid=2 大错特错！！！
    
    -- 7 查询学过“叶平”老师所教的所有课的同学的学号、姓名；
    
    SELECT
    	sid -- 子查询结果如果要当临时表来使用的话需要起个别名(比如这里面的t)
    	
    FROM
    	(-- 学过该老师课程的同学学号(包含只学一门)
    SELECT
    	sid,
    	count( cid ) num 
    FROM
    	sc 
    WHERE
    	cid IN ( -- 该老师教了哪些课程
    	SELECT cid FROM course WHERE tid IN ( SELECT tid FROM teacher WHERE tname = '叶平' ) ) 
    GROUP BY
    	sid 
    	) t 
    WHERE
    	t.num = ( -- 统计该老师总课程数
    	SELECT count( cid ) FROM course WHERE tid = ( SELECT tid FROM teacher WHERE tname = '叶平' ) )
    
    
    -- 8 查询所有课程成绩小于60分的同学的学号、姓名；
    
    select sid,sname
    from student
    where sid in(
    	select distinct sid
    	from sc
    	where score<60
    )
    -- 9 查询没有学全所有课的同学的学号、姓名；
    
    select sid,sname
    from student
    where sid in(
    	select sid
    	-- 子查询结果如果要当临时表来使用的话需要起个别名(比如这里面的t)
    	from (
    		select sid,count(cid) num
    		from sc
    		group by sid having  num < (
    			select count(cid)
    			from course
    		)
    	) t
    )
    
    
    -- 10 查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
    select sid,sname
    from student
    where sid in(
    	select distinct sid
    	from sc
    	where cid in(
    		select cid
    		from sc
    		where sid='1001'
    	) and sid != '1001'
    )
    
    -- 11 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
    SELECT
    	cid,
    	max( score ) max,
    	min( score ) min 
    FROM
    	sc 
    GROUP BY
    	cid
    
    
    -- 12 删除学习“叶平”老师课的SC表记录；
    
       delete from sc where cid  in (select cid from course where tid = (select tid from teacher where tname="叶平"));