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小题
回到顶部
更多推荐
MySQL数据库技术 第二版 章末 答案—单元5---思考5
发布评论