MySQL数据库技术第二版章末答案—单元5---思考5

MySQL数据库技术第二版章末答案—单元5以下是我个人所尝试过得答案，若有不正确的地方请告知，谢谢！由于实在是太多内容了，原谅我省略题目内容单元5 数据查询 ---- 思考5----P106-P107跳到底部，进行题目筛选1-10小题底部– 1SELECT姓名,专业名,总学分FROMxs;– 2SELECT学号ASnumber,姓名ASNAME,总学分ASmarkFROMx...

张三coder

14793人浏览 · 2020-12-06 22:00:29

张三coder · 2020-12-06 22:00:29 发布

MySQL数据库技术第二版章末答案—单元5

以下是我个人所尝试过得答案，若有不正确的地方请告知，谢谢！

由于实在是太多内容了，原谅我省略题目内容

单元5 数据查询 ---- 思考5---- P106-P107

跳到底部，进行题目筛选

1-10小题

底部

– 1


SELECT 	姓名,	专业名,		总学分
	FROM 	xs;

– 2


SELECT 	学号  AS  number,	姓名  AS  NAME,		总学分  AS  mark
	FROM 	xs
	
	WHERE	专业名="计算机";

– 3


SELECT 	学号  ,	姓名  ,		
	CASE	
		WHEN  总学分  IS NULL 			THEN	"尚未选课"
		WHEN  总学分  < 50			THEN	"不及格"
		WHEN  总学分>=50 AND 总学分<=52 	THEN	"及格"
		WHEN  总学分  > 52			THEN	"优秀"
	END 	
		AS 	"等级"
	FROM 	xs
	
	WHERE	专业名="计算机";

– 4


SELECT 	*
	FROM 	xs_kc
	
	WHERE 	学号=081101;

– 5


SELECT 	DISTINCT 	专业名,		总学分
	FROM 	xs;

– 6


SELECT 	COUNT(*)  AS  学生总人数
	FROM 	xs;

– 7


	-- 方一
SELECT 	COUNT(备注)  AS 备注不为空
	FROM 	xs;
	
	-- 方二
SELECT 	COUNT(备注)  AS 备注不为空
	FROM 	xs
	WHERE 	备注  IS NOT NULL;

– 8


SELECT 	COUNT(*)  AS  "50分以上的人数"
	FROM 	xs
	WHERE 	总学分>50;

– 9


SELECT 	MAX(成绩)  AS  最高成绩,	MIN(成绩)  AS  最低成绩
	FROM 	xs_kc
	WHERE 	课程号="101";

– 10


SELECT 	SUM(成绩)  AS  总成绩
	FROM 	xs_kc
	WHERE 	学号=081101;

11-20小题

底部

– 11


SELECT 	AVG(成绩)  AS  平均成绩
	FROM 	xs_kc
	WHERE 	课程号="101";

– 12


SELECT 	*
	FROM 	xs
	WHERE 	学号=081101;

– 13


SELECT 	*
	FROM 	xs
	WHERE 	总学分>50;

– 14


SELECT 	*
	FROM 	xs
	WHERE 	备注  IS NULL;

– 15


SELECT 	*
	FROM 	xs
	WHERE 	专业名="计算机"  AND 性别=0;

– 16


SELECT 	*
	FROM 	xs_kc
	WHERE  课程号	IN(102,  206)	AND 成绩>80
	ORDER BY	课程号 ASC;

– 17


SELECT 	学号,	姓名,	性别
	FROM 	xs
	WHERE 	姓名	LIKE	"王%";

– 18


SELECT 	学号,	姓名,	专业名
	FROM 	xs
	WHERE 	学号	LIKE	"%0_";

– 19


SELECT 	学号,	姓名
	FROM 	xs
	WHERE 	姓名	LIKE	"%#_%" 	ESCAPE	"#";

– 20


SELECT 	*
	FROM 	xs
	WHERE 	(出生时间<"1989-01-01" OR 出生时间>"1989-12-30");

21-30小题

底部

– 21


SELECT 	*
	FROM 	xs
	WHERE 	专业名 IN("计算机",  "通信工程");

– 22


SELECT 	*
	FROM 	xs
	WHERE 	总学分  IS NULL;

– 23


SELECT 	xs.`姓名`, 	kc.`课程名`, 	kc.`课程号`
	FROM 	xs_kc 	JOIN 	xs /*关联学生与课程号*/
		ON (xs_kc.`学号` 	= 	xs.`学号`)
			JOIN 	kc/*关联课程名*/
		ON (xs_kc.`课程号` 	= 	kc.`课程号`)
	/*order by	xs.`姓名` */;

– 24


SELECT 	姓名,	成绩
	FROM 	xs_kc 	JOIN 	xs /*关联学生与课程号*/
		ON (xs_kc.`学号` 	= 	xs.`学号`)
			JOIN 	kc/*关联课程名*/
		ON (xs_kc.`课程号` 	= 	kc.`课程号`)
		
	WHERE 	xs_kc.课程号=206  AND  成绩>80;

– 25


SELECT 	xs.`学号`, 	xs.`姓名`,	kc.`课程名`,	xs_kc.`成绩`
	FROM 	xs_kc 	JOIN 	xs /*关联学生与课程号*/
		ON (xs_kc.`学号` 	= 	xs.`学号`)
			JOIN 	kc/*关联课程名*/
		ON (xs_kc.`课程号` 	= 	kc.`课程号`)	
		WHERE 	(kc.`课程名` = "计算机基础" 	AND	xs_kc.`成绩`>80);

– 26


SELECT DISTINCT a.`学号`, 	a.`课程号`,		a.`成绩`
	FROM 	xs_kc AS a 	JOIN 	xs_kc AS b
		ON (a.`学号` <>b.`学号`		AND	a.`课程号` != b.`课程号` 
				AND 
			a.`成绩` = b.`成绩`)
	ORDER BY	a.`成绩` ;

– 27


SELECT 	kc.`课程名`
	FROM 	kc 	JOIN 	xs_kc
		ON (kc.`课程号` = xs_kc.`课程号`)
	GROUP BY kc.课程名;

– 28


/*查询未选修任何课程的学号和姓名*/
SELECT xs.学号,xs.姓名
FROM xs
WHERE xs.`学号` NOT IN (SELECT DISTINCT xs_kc.`学号` FROM xs_kc);

/*P107-28--ok*/
SELECT xs_kc.`课程号`,	xs.*
	FROM 	xs 	LEFT OUTER JOIN 	xs_kc
		ON (xs.`学号` = xs_kc.`学号`);

– 29


SELECT 	DISTINCT 	kc.`课程名`,	kc.*
	FROM 	xs_kc 	JOIN 	xs /*关联学生与课程号*/
		ON (xs_kc.`学号` 	= 	xs.`学号`)
			JOIN 	kc/*关联课程名*/
		ON (xs_kc.`课程号` 	= 	kc.`课程号`)
	;

– 30


SELECT xs.姓名,		xs.学号
	FROM 	xs_kc 	JOIN 	xs /*关联学生与课程号*/
		ON (xs_kc.`学号` 	= 	xs.`学号`)
			JOIN 	kc/*关联课程名*/
		ON (xs_kc.`课程号` 	= 	kc.`课程号`)
	
	WHERE 	kc.课程号="206";

31-40小题

底部

– 31

-- 31	--- 方一
SELECT 	姓名,	学号,	专业名
	FROM 	xs
	WHERE 	学号 NOT IN(
				SELECT 	学号 
					FROM 	xs_kc 	JOIN    kc
						ON (xs_kc.`课程号`=kc.`课程号`)
					
					WHERE 	课程名 ='离散数学'
			    )
	;
	
	
	-- 方二
SELECT 姓名,学号,专业名
FROM xs
WHERE 学号 NOT IN
       (SELECT 学号 FROM xs_kc WHERE 课程号 IN (SELECT 课程号 FROM kc WHERE 课程名 ='离散数学'));

– 32


SELECT 	xs_kc.`学号`, 	kc.`课程名`
	FROM 	xs_kc	LEFT OUTER JOIN 	kc
		ON (xs_kc.`课程号` 	= 	kc.`课程号`)
		
		WHERE (kc.`课程名` 	= 	"离散数学");

– 33


SELECT 	xs.`学号`,	xs.`姓名`,	xs.`专业名`,	xs.`出生时间`
	FROM 	xs
	
	WHERE 	xs.`出生时间` > ALL(SELECT 出生时间 	FROM xs   WHERE 专业名="计算机")
	;

– 34


SELECT 	学号,  成绩
	FROM 	xs_kc
	
	WHERE 	课程号 ='206' 
	    AND 
		成绩 >=		ANY (SELECT MIN(成绩) 	FROM xs_kc   WHERE 课程号 ='101')
	;

– 35


SELECT 	xs.`学号`,	姓名
	FROM 	xs 	JOIN 	xs_kc
		ON (xs.`学号` = xs_kc.`学号`)
		WHERE	课程号="206";

– 36


SELECT 	专业名
	FROM 	xs
		GROUP BY	专业名;

– 37


SELECT 	专业名,		COUNT(*)  AS  "人数"
	FROM 	xs
		GROUP BY	专业名;

– 38


/*P107-38 --不能加""*/
SELECT	课程号, 	AVG(成绩) AS "平均成绩" , 	COUNT(*) AS "总人数"
	FROM 	xs_kc
		GROUP BY 	课程号;

– 39


SELECT 	专业名, 	

		CASE 	/*性别*/
			WHEN  性别=0 THEN "女生"
			WHEN  性别=1 THEN "男生"
			ELSE  "总人数"
		END AS 性别 , 	
		
		COUNT(*) AS "总人数"
	
	FROM 	xs
		GROUP BY 专业名, 性别
			WITH ROLLUP;

– 40


SELECT 	学号, AVG(成绩)  AS  "平均成绩"
	FROM 	xs_kc
	
	GROUP BY 学号
		HAVING 	AVG(成绩)>85;

41-46小题

底部

– 41


SELECT 	学号, 成绩
	FROM 	xs_kc
			GROUP BY 学号
				HAVING COUNT(*)>2 AND AVG(成绩)>80;

– 42


SELECT 	*
	FROM 	xs
	
	WHERE 	专业名="通信工程"
	
	ORDER BY    出生时间   ASC;

– 43


SELECT xs.`学号`,	xs_kc.`课程号`,		成绩 
	FROM xs_kc 	JOIN 	xs /*关联学生与课程号*/
		ON (xs_kc.`学号` 	= 	xs.`学号`)
			JOIN 	kc/*关联课程名*/
		ON (xs_kc.`课程号` 	= 	kc.`课程号`)
	
	WHERE 专业名="计算机"  AND  kc.`课程名`="计算机基础"
	ORDER BY 成绩 DESC;

– 44


/*P107-44--子查询可以使用父查询的字段*/
SELECT 	xs.学号, 	姓名, 	专业名
	FROM 	xs
		WHERE 专业名="计算机" 
		ORDER BY (
			SELECT AVG(成绩) FROM xs_kc GROUP BY xs_kc.`学号` HAVING (xs.`学号`=xs_kc.`学号`)
		) DESC;

– 45


SELECT 	*
	FROM 	xs
		ORDER BY 学号	LIMIT 5;

– 46


SELECT 	*
	FROM 	xs
		ORDER BY 学号	LIMIT 3,  5;

回到1-10小题
回到11-20小题
回到21-30小题
回到31-40小题
回到41-46小题
回到顶部

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

基于《2023腾讯云容器和函数计算技术实践精选集》—探索腾讯云TKE的Docker容器、Serverless和微服务优势

腾讯云开发者社区

一站式加速、安全防护，EdgeOne为金融行业数字化升级保驾护航

腾讯云开发者社区

全球安全加速一站式底座，EdgeOne以极致玩家体验撬动游戏行业增长

腾讯云开发者社区

所有评论(0)

查看更多评论

张三coder

@qq_40927409

已为社区贡献2条内容

MySQL数据库技术 第二版 章末 答案—单元5---思考5

张三coder

MySQL数据库技术 第二版 章末 答案—单元5

单元5 数据查询 ---- 思考5---- P106-P107

1-10小题

11-20小题

21-30小题

31-40小题

41-46小题

所有评论(0)

张三coder

MySQL数据库技术第二版章末答案—单元5---思考5

MySQL数据库技术第二版章末答案—单元5