MYSQL--基础--03--练习题

勤径苦舟

已于 2023-08-25 19:57:03 修改

阅读量397

点赞数

分类专栏： mysql 文章标签： mysql android 数据库

于 2021-08-11 19:26:05 首次发布

本文链接：https://blog.csdn.net/zhou920786312/article/details/119614946

版权

mysql 专栏收录该内容

62 篇文章 10 订阅

订阅专栏

MYSQL–基础–03–练习题

1、数据初始化

  
 
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '课程id',
  `name` varchar(10) COMMENT '课程名称',
  `tid` bigint(0)  COMMENT '教师id',
  PRIMARY KEY (`id`) 
) ENGINE = InnoDB  COMMENT = '课程表' ;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '英语', 3);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sid` bigint(0)  COMMENT '学生id',
  `cid` bigint(0)  COMMENT '课程id',
  `score` int(0)  COMMENT '分数',
  PRIMARY KEY (`id`) 
) ENGINE = InnoDB  COMMENT = '成绩表' ;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 80);
INSERT INTO `score` VALUES (2, 1, 2, 90);
INSERT INTO `score` VALUES (3, 1, 3, 99);
INSERT INTO `score` VALUES (4, 2, 1, 70);
INSERT INTO `score` VALUES (5, 2, 2, 60);
INSERT INTO `score` VALUES (6, 2, 3, 80);
INSERT INTO `score` VALUES (7, 3, 1, 80);
INSERT INTO `score` VALUES (8, 3, 2, 80);
INSERT INTO `score` VALUES (9, 3, 3, 80);
INSERT INTO `score` VALUES (10, 4, 1, 50);
INSERT INTO `score` VALUES (11, 4, 2, 30);
INSERT INTO `score` VALUES (12, 4, 3, 20);
INSERT INTO `score` VALUES (13, 5, 1, 76);
INSERT INTO `score` VALUES (14, 5, 2, 87);
INSERT INTO `score` VALUES (15, 6, 1, 31);
INSERT INTO `score` VALUES (16, 6, 3, 34);
INSERT INTO `score` VALUES (17, 7, 2, 89);
INSERT INTO `score` VALUES (18, 7, 3, 98);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '学生id ',
  `name` varchar(10)   COMMENT '姓名',
  `birth_date` datetime(0)  COMMENT '出生日期',
  `sex` varchar(10)   COMMENT '性别',
  PRIMARY KEY (`id`) 
) ENGINE = InnoDB  COMMENT = '学生表' ;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES (2, '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES (3, '孙风', '1990-12-20 00:00:00', '男');
INSERT INTO `student` VALUES (4, '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `student` VALUES (5, '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES (6, '吴兰', '1992-01-01 00:00:00', '女');
INSERT INTO `student` VALUES (7, '郑竹', '1989-01-01 00:00:00', '女');
INSERT INTO `student` VALUES (9, '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES (10, '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES (11, '李四', '2012-06-06 00:00:00', '女');
INSERT INTO `student` VALUES (12, '赵六', '2013-06-13 00:00:00', '女');
INSERT INTO `student` VALUES (13, '孙七', '2014-06-01 00:00:00', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '教师id',
  `name` varchar(10)   COMMENT '教师名称',
  PRIMARY KEY (`id`) 
) ENGINE = InnoDB  COMMENT = '教师表' ;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张三');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王五');
 
 


SET FOREIGN_KEY_CHECKS = 1;

2、练习

2.1、查询课程id “1"比"2” 成绩高的学生信息及课程分数（困难）

思路

需要获取score和sid字段
同一表，同一字段比较，考虑自连接

SELECT
	sc1.sid,
	sc1.score 
FROM
	score sc1
	LEFT JOIN score sc2 ON sc1.sid = sc2.sid 
WHERE
	1 = 1 
	AND sc1.cid = 1 
	AND sc2.cid = 2 
	AND sc1.score > sc2.score;

sql

SELECT
	* 
FROM
	student s
	INNER JOIN (
	SELECT
		sc1.sid,
		sc1.score 
	FROM
		score sc1
		LEFT JOIN score sc2 ON sc1.sid = sc2.sid 
	WHERE
		1 = 1 
		AND sc1.cid = 1 
		AND sc2.cid = 2 
	AND sc1.score > sc2.score 
	) t1 ON t1.sid = s.id

2.2、查询同时存在课程id是"1"和"2"的学生（困难）

思路

需要获取sid字段
同一表，同一字段比较，考虑自连接

SELECT
	* 
FROM
	score sc1
	LEFT JOIN score sc2 ON sc1.sid = sc2.sid 
WHERE
	1 = 1 
	AND sc1.cid = 1 
	AND sc2.cid = 2

sql

SELECT
	* 
FROM
	student s
	INNER JOIN (
	SELECT
		sc1.sid 
	FROM
		score sc1
		LEFT JOIN score sc2 ON sc1.sid = sc2.sid 
	WHERE
		1 = 1 
		AND sc1.cid = 1 
	AND sc2.cid = 2 
	) t1 ON t1.sid = s.id

2.3、查询存在课程id是"1"但可能不存在"2"的学生（困难）

思路

需要获取sid字段
同一表，同一字段比较，考虑自连接
可能不存在：包含存在和不存在情况，不考虑

SELECT
	* 
FROM
	score sc1
	RIGHT JOIN   score sc2 ON sc1.sid = sc2.sid 
WHERE
	1 = 1 
	AND sc1.cid = 1 
	AND sc2.cid = 1

sql

SELECT
	* 
FROM
	student s
	INNER JOIN (
	SELECT
		sc1.sid 
	FROM
		score sc1
		RIGHT JOIN score sc2 ON sc1.sid = sc2.sid 
	WHERE
		1 = 1 
		AND sc1.cid = 1 
	AND sc2.cid = 1 
	) t1 ON t1.sid = s.id

2.4、查询不存在课程"1"但存在"2"的学生成绩信息

思路

需要获取sid字段
不存在课程"1"：排除这一部分的sid

sql

SELECT
	* 
FROM
	score sc1 
WHERE
	sc1.sid NOT IN ( SELECT sid FROM score WHERE cid = 1 ) 
	AND sc1.cid = 2

2.5、查询学生平均成绩大于等60分的同学的学生编号和学生姓名和平均成绩

思路

平均成绩:分组
学生的平均成绩：对学生分组

SELECT
	sc1.sid,
	AVG( sc1.score ) avgScore 
FROM
	score sc1 
GROUP BY
	sc1.sid 
HAVING
	avgScore >= 60

sql


SELECT * from student s INNER JOIN (
SELECT
	sc1.sid,
	AVG( sc1.score ) avgScore 
FROM
	score sc1 
GROUP BY
	sc1.sid 
HAVING
avgScore >= 60 ) t1 ON t1.sid = s.id

2.6、查询在 score 表存在成绩的学生信息

思路

需要获取sid字段
score表的所有的数据都是有分数的学生，所以对学生分组就行


SELECT
	sc1.sid 
FROM
	score sc1 
GROUP BY
	sc1.sid

sql


SELECT
	* 
FROM
	student s 
WHERE
	s.id IN (
	SELECT
		sc1.sid 
	FROM
		score sc1 
	GROUP BY
	sc1.sid 
	)

2.7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

思路

需要获取sid字段，选课总数，所有课程的总成绩。
学生分组

 
SELECT
	sc1.sid,
	COUNT( 1 ) AS count,
	SUM( sc1.score ) score 
FROM
	score sc1 
GROUP BY	sc1.sid

sql

SELECT
	* 
FROM
	student s
	LEFT JOIN (
	SELECT
		sc1.sid,
		COUNT( 1 ) AS count,
		SUM( sc1.score ) score 
	FROM
		score sc1 
	GROUP BY	sc1.sid  
	) t1 ON t1.sid = s.id

2.8、查有成绩的学生信息

思路

查看成绩表的学生id

IN()适合B表比A表数据小的情况
EXISTS()适合B表比A表数据大的情况

SELECT sid FROM score

sql

SELECT
	* 
FROM
	student s 
WHERE
	1 = 1 
	AND s.id IN ( SELECT sid FROM score )


----

SELECT
	* 
FROM
	student s 
WHERE  EXISTS ( SELECT sid FROM score sc1 WHERE s.id=sc1.sid)

2.9、查询「李」姓老师的数量

思路

count 函数

以李开头

sql

SELECT COUNT(1) 
FROM
	teacher t
WHERE t.name  LIKE '李%'

2.10、查询学过「张三」老师授课的同学信息

思路

学生和课程有关，课程又和老师有关

sql

SELECT
	s.* 
FROM
	score sc1
	INNER JOIN course c ON c.id = sc1.cid
	INNER JOIN teacher t ON t.id = c.tid
	INNER JOIN student s ON s.id = sc1.sid 
WHERE
	t.NAME = '张三'

2.11、查询没有学全所有课程的同学信息

思路

所有课程的数量

用户已经选择的课程数量

SELECT COUNT(1) FROM course;

SELECT  sc1.sid,COUNT(1) FROM score sc1
GROUP BY sc1.sid;

sql

SELECT * FROM student s1 WHERE 
s1.id NOT IN (
SELECT  sc1.sid  FROM score sc1
GROUP BY sc1.sid 
HAVING COUNT(1) =(SELECT COUNT(1) FROM course)
)

2.12、查询至少有一门课与学生id为"1"的同学所学相同的同学信息

思路

查看学生id为"1"的课程

至少有一门课：in

SELECT cid from score sc1 WHERE sc1.sid=1

sql

SELECT
	* 
FROM
	student 
WHERE
	id IN ( 
	SELECT sid FROM score WHERE cid IN ( SELECT cid FROM score WHERE sid = 1 )
	AND sid != 1
 )

2.13、查询和学生id为"1"的同学学习的课程完全相同的其他同学的信息（困难）

思路

查看学生id为"1"的课程
列转行

GROUP_CONCAT

sql

 SELECT t2.* from 
 
 (
SELECT
sc.sid, COUNT(1) m2 ,GROUP_CONCAT(sc.cid ORDER BY cid SEPARATOR ',') m3
FROM score sc  
WHERE sc.sid = 1)t1  

INNER JOIN (

SELECT
	sc.sid, COUNT(1) m2 ,GROUP_CONCAT(sc.cid ORDER BY cid SEPARATOR ',') m3
FROM
	score sc 
	WHERE sc.sid!=1
	GROUP BY sc.sid ) t2 on  t2.m2=t1.m2 and  t2.m3=t1.m3

2.14、查询没学过"张三"老师讲授的任一门课程的学生姓名

思路

查学过"张三"老师讲课的学生

SELECT sc.sid FROM teacher t 
INNER JOIN course c on t.id=c.tid
INNER JOIN score sc on sc.cid=c.id
WHERE t.name='张三'

sql


SELECT * FROM student s
WHERE s.id NOT IN(
	SELECT sc.sid FROM teacher t 
	INNER JOIN course c on t.id=c.tid
	INNER JOIN score sc on sc.cid=c.id
	WHERE t.name='张三'
)

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

思路

两门及其以上不及格课程的同学：不及格的数量>1
平均成绩：分组


SELECT
	sc.sid 
FROM
	score sc 
WHERE
	sc.score < 60 
	GROUP BY sc.sid 
	HAVING count( 1 ) > 1

sql

SELECT s.*,t2.* FROM student s
INNER JOIN (
SELECT sc2.sid,avg(sc2.score) FROM score sc2
	WHERE sc2.sid IN
	(
		SELECT
			sc.sid 
		FROM
			score sc 
		WHERE
			sc.score < 60 
			GROUP BY sc.sid 
			HAVING count( 1 ) > 1
		)
	GROUP BY sc2.sid
)t2 on t2.sid=s.id

2.16、检索课程id是"1"课程且分数小于 60，按分数降序排列的学生信息

sql

SELECT
	* 
FROM
	student s
	INNER JOIN score sc ON s.id = sc.sid 
WHERE
	sc.cid = 1 
	AND sc.score < 60 
ORDER BY
	sc.score DESC

2.17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

sql



SELECT * FROM score sc2
INNER JOIN (

	SELECT sc.sid,AVG(sc.score) as avgSc FROM score  sc

	GROUP BY sc.sid 

) t2 on sc2.sid=t2.sid 
ORDER BY t2.avgSc DESC

2.18、查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

思路

重点：
最高分，最低分，平均分，及格率，中等率，优良率，优秀率
输出课程号和选修人数
结果按人数降序排列，若人数相同，按课程号升序排列

sql

SELECT
	sc.cid,
	AVG( sc.score ) AS avgSc,
	MIN( sc.score ) AS minSc,
	MAX( sc.score ) AS maxSc, 
	SUM(( case WHEN sc.score >= 90 THEN 1 ELSE 0 END   ))/ COUNT( 1 ) AS "优秀率" ,
	SUM(( case WHEN sc.score >= 80 THEN 1 ELSE 0 END   ))/ COUNT( 1 ) AS "优良率" ,
	SUM(( case WHEN sc.score >= 70 THEN 1 ELSE 0 END   ))/ COUNT( 1 ) AS "中等率" ,
	SUM(( case WHEN sc.score >= 60 THEN 1 ELSE 0 END   ))/ COUNT( 1 ) AS "及格率" , 
	COUNT( 1 ) AS num 
FROM
	score sc 
GROUP BY sc.cid 
ORDER BY num DESC ,sc.cid ASC

2.19、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺（困难）

Score 重复时保留名次空缺：就是说并列第1，第2这种情况
各科成绩排名：自连接，on 的条件是cid
成绩进行排序:自连接查看比自己大的分数
重复时保留名次空缺:count 查看比自己大的人数

sql

 

SELECT sc1.cid,sc1.sid,sc1.score,COUNT(1) FROM score sc1

LEFT JOIN score sc2 ON sc1.cid=sc2.cid  AND  sc1.score<sc2.score

 

GROUP BY sc1.cid,sc1.sid,sc1.score
ORDER BY sc1.cid , sc1.score DESC

2.20、查询学生的总成绩，并进行排名，总分重复时不保留名次空缺（难）

思路

使用变量来设置行号

SET @rowNum  = 0;

 
@rowNum := @rowNum + 1

sql


-- 起到行号的作用
SET @rowNum  = 0;

SELECT
-- 变量这里不能使用as
@rowNum := @rowNum + 1,
sc.sid,
SUM( sc.score ) AS totalSc 
FROM
	score sc 
GROUP BY
	sc.sid 
ORDER BY
	SUM( sc.score ) DESC

2.21、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

sql

-- 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT
	COUNT( 1 ) AS "各科总人数",
	SUM( CASE WHEN sc.score >= 85 AND sc.score <= 100 THEN 1 ELSE 0 END ) AS "100-85 人数",
	SUM( CASE WHEN sc.score >= 70 AND sc.score < 85 THEN 1 ELSE 0 END ) AS "85-70 人数",
	SUM( CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END ) AS "70-60 人数",
	SUM( CASE WHEN sc.score >= 0 AND sc.score < 60 THEN 1 ELSE 0 END ) AS "60-0 人数",
	SUM( CASE WHEN sc.score >= 85 AND sc.score <= 100 THEN 1 ELSE 0 END )/ COUNT( 1 )* 100 AS "100-85 人数 百分比",
	SUM( CASE WHEN sc.score >= 70 AND sc.score < 85 THEN 1 ELSE 0 END )/ COUNT( 1 )* 100 AS "85-70 人数 百分比",
	SUM( CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END ) / COUNT( 1 )* 100 AS "70-60 人数 百分比",
	SUM( CASE WHEN sc.score >= 0 AND sc.score < 60 THEN 1 ELSE 0 END )/ COUNT( 1 )* 100 AS "60-0 人数 百分比",
	c.`name`,
	c.id 
FROM
	score sc
	INNER JOIN course c ON sc.cid = c.id 
GROUP BY
	sc.cid

2.22、查询各科成绩前三名的记录(非常难)

思路

表内字段比较，自连接
查询各科成绩排名,取前3个

-- 查看查询各科成绩的组内排名
SELECT
	sc1.cid,
	sc1.sid,
	sc1.score 
FROM
	score sc1 -- 查看比我大的分数
	LEFT JOIN score sc2 ON sc1.cid = sc2.cid 
	AND sc1.score < sc2.score 
GROUP BY
	sc1.cid,
	sc1.sid,
	sc1.score 
ORDER BY
	sc1.cid,
	sc1.score DESC

sql

-- 查询各科成绩前三名的记录，
-- 获取到排名，通过r<=3就可以获取前3名

SET @rownum := 1 ,@GROUP := ''; 


SELECT  
-- 排名
@rownum := IF (@GROUP = t.cid ,@rownum + 1, 1) AS r,
@GROUP := t.cid AS g,
	t.cid,
	t.sid,
	t.score  
FROM(
	SELECT
		sc1.cid,
		sc1.sid,
		sc1.score 
	FROM
		score sc1 -- 查看比我大的分数
		LEFT JOIN score sc2 ON sc1.cid = sc2.cid 
		AND sc1.score < sc2.score 
	GROUP BY
		sc1.cid,
		sc1.sid,
		sc1.score 
	ORDER BY
		sc1.cid,
		sc1.score DESC
	)t

2.23、查询每门课程被选修的学生数

sql

SELECT cid,COUNT(1) FROM score 
GROUP BY cid

2.24、查询出只选修两门课程的学生学号和姓名

sql

SELECT  s.id,s.`name` FROM score sc
INNER JOIN student s  on sc.sid=s.id
GROUP BY s.id,s.`name`
HAVING COUNT(1)=2

2.25、查询男生、女生人数

sql

SELECT
	sex,
	count( 1 ) 
FROM
	student 
GROUP BY
	sex

2.26、查询名字中含有「风」字的学生信息

sql

SELECT * FROM student 
WHERE `name` like "%风%"

2.27、查询同名学生名单，并统计同名人数

sql

 

SELECT 
name,count( NAME )  
FROM
	student 
GROUP BY name
HAVING count( NAME ) >1

2.28、查询 1990 年出生的学生名单

sql

SELECT * FROM student
WHERE YEAR(birth_date)="1990"

2.29、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

sql

SELECT sc.cid,avg(sc.score) FROM score sc 
GROUP BY sc.cid
ORDER BY avg(sc.score) desc,sc.cid desc

2.30、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

sql

SELECT
	s.id,
	s.`name`,
	avg(sc.score) 
FROM
	student s INNER JOIN score sc ON sc.sid = s.id 
GROUP BY sc.sid 
	
HAVING
	avg( sc.score )>= 85

2.31、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

sql

SELECT
	* 
FROM
	student s
	INNER JOIN score sc ON sc.sid = s.id
	INNER JOIN course c ON c.id = sc.cid 
WHERE
	c.`name` = "数学"

2.32、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

sql

SELECT
	* 
FROM
	student s
	LEFT   JOIN score sc ON sc.sid = s.id

2.33、查查询每一门课程成绩在 70 分以上的姓名、课程名称和分数

sql

 SELECT
	* 
FROM
	student s
	INNER JOIN score sc1 ON s.id = sc1.sid 
	INNER JOIN course c on c.id =sc1.cid
WHERE
	sc1.sid NOT IN ( SELECT sid FROM score WHERE score < 70 )

2.34、查询存在不及格的课程名称（去重）

sql

SELECT
	DISTINCT(c.`name`)
FROM
	score sc1
	INNER JOIN course c ON c.id = sc1.cid 
WHERE
	sc1.score < 60

2.35、查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

sql


SELECT
	* 
FROM
	student s
	INNER JOIN score sc ON sc.sid = s.id 
WHERE
	sc.cid = 1 
	AND sc.score >= 80

2.36、求每门课程的学生人数

sql

SELECT cid,count(1) FROM score
GROUP BY cid

2.37、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

sql1

SELECT
	s.*,
	sc.score 
FROM
	score sc
	INNER JOIN course c ON sc.cid = c.id
	INNER JOIN teacher t ON t.id = c.tid
	INNER JOIN student s ON s.id = sc.sid 
WHERE
	t.`name` = "张三" 
ORDER BY
	sc.score DESC 
	LIMIT 1

2.38、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

sql

SELECT * FROM score sc  
	
	INNER JOIN course c ON sc.cid = c.id
	INNER JOIN teacher t ON t.id = c.tid 
	INNER JOIN student s on s.id =sc.sid
	WHERE  	t.`name` = "张三" 
	and sc.score=
	(
	SELECT
		MAX(sc.score)
	FROM
		score sc
		INNER JOIN course c ON sc.cid = c.id
		INNER JOIN teacher t ON t.id = c.tid 
		WHERE  	t.`name` = "张三" 

	 )

2.39、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

sql

SELECT
	sc1.sid,sc1.cid,sc1.score
FROM
	score sc1
	LEFT JOIN score sc2 ON sc1.score = sc2.score 
WHERE
	sc1.cid != sc2.cid 
	AND sc1.sid = sc2.sid 
GROUP BY
	sc1.sid,sc1.cid,sc1.score

2.40、统计每门课程的学生选修人数（超过 5 人的课程才统计）

sql

SELECT cid,count(1) FROM score sc
GROUP BY sc.cid
HAVING COUNT(1) >5

2.41、检索至少选修两门课程的学生编号

sql

 
SELECT sid,count(1) FROM score sc
GROUP BY sc.sid
HAVING COUNT(1) >=2

2.42、查询选修了全部课程的学生编号

sql

SELECT sid  FROM score sc
GROUP BY sc.sid
HAVING COUNT(1) =(SELECT count(1) FROM course )