– 1
SELECT 姓名, 专业名, 总学分
FROM xs;1
2
3
4
– 2
SELECT 学号 AS number, 姓名 AS NAME, 总学分 AS mark
FROM xs
WHERE 专业名="计算机";
1 2
3
4
5
6
– 3
SELECT 学号 , 姓名 ,
CASE
WHEN 总学分 IS NULL THEN "尚未选课"
WHEN 总学分 < 50 THEN "不及格"
WHEN 总学分>=50 AND 总学分<=52 THEN "及格"
WHEN 总学分 > 52 THEN "优秀"
END
AS "等级"
FROM xs
WHERE 专业名="计算机";1
2
3
4
5
6
7
8
9
10
11
12
13
– 4
SELECT *
FROM xs_kc
WHERE 学号=081101;1
2
3
4
5
6
– 5
SELECT DISTINCT 专业名, 总学分
FROM xs;
1 2
3
4
– 6
SELECT COUNT(*) AS 学生总人数
FROM xs;1
2
3
4
– 7
-- 方一
SELECT COUNT(备注) AS 备注不为空
FROM xs;
-- 方二
SELECT COUNT(备注) AS 备注不为空
FROM xs
WHERE 备注 IS NOT NULL;
1
2
3
4
5
6
7
8
9
10
– 8
SELECT COUNT(*) AS "50分以上的人数"
FROM xs
WHERE 总学分>50;
1
2
3
4
5
– 9
SELECT MAX(成绩) AS 最高成绩, MIN(成绩) AS 最低成绩
FROM xs_kc
WHERE 课程号="101";
1
2
3
4
– 10
SELECT SUM(成绩) AS 总成绩
FROM xs_kc
WHERE 学号=081101;
1
2
3
4
---11
SELECT AVG(成绩) AS 平均成绩
FROM xs_kc
WHERE 课程号="101";
1
2
3
4
5
– 12
SELECT *
FROM xs
WHERE 学号=081101;
1
2
3
4
5
– 13
SELECT *
FROM xs
WHERE 总学分>50;
1
2
3
4
5
– 14
SELECT *
FROM xs
WHERE 备注 IS NULL;
1
2
3
4
5
– 15
SELECT *
FROM xs
WHERE 专业名="计算机" AND 性别=0;
1
2
3
4
5
– 16
SELECT *
FROM xs_kc
WHERE 课程号 IN(102, 206) AND 成绩>80
ORDER BY 课程号 ASC;
1
2
3
4
5
– 17
SELECT 学号, 姓名, 性别
FROM xs
WHERE 姓名 LIKE "王%";
1
2
3
4
– 18
SELECT 学号, 姓名, 专业名
FROM xs
WHERE 学号 LIKE "%0_";
1
2
3
4
– 19
SELECT 学号, 姓名
FROM xs
WHERE 姓名 LIKE "%#_%" ESCAPE "#";
1
2
3
4
– 20
SELECT *
FROM xs
WHERE (出生时间<"1989-01-01" OR 出生时间>"1989-12-30");
1
2
3
4
21-30小题
底部
– 21
SELECT *
FROM xs
WHERE 专业名 IN("计算机", "通信工程");
1
2
3
4
– 22
SELECT *
FROM xs
WHERE 总学分 IS NULL;
1
2
3
4
– 23
SELECT xs.`姓名`, kc.`课程名`, kc.`课程号`
FROM xs_kc JOIN xs /*关联学生与课程号*/
ON (xs_kc.`学号` = xs.`学号`)
JOIN kc/*关联课程名*/
ON (xs_kc.`课程号` = kc.`课程号`)
/*order by xs.`姓名` */;
1
2
3
4
5
6
7
– 24
SELECT 姓名, 成绩
FROM xs_kc JOIN xs /*关联学生与课程号*/
ON (xs_kc.`学号` = xs.`学号`)
JOIN kc/*关联课程名*/
ON (xs_kc.`课程号` = kc.`课程号`)
WHERE xs_kc.课程号=206 AND 成绩>80;
1
2
3
4
5
6
7
8
– 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);
1
2
3
4
5
6
7
– 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.`成绩` ;
1
2
3
4
5
6
7
– 27
SELECT kc.`课程名`
FROM kc JOIN xs_kc
ON (kc.`课程号` = xs_kc.`课程号`)
GROUP BY kc.课程名;
1
2
3
4
5
6
– 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.`学号`);
1
2
3
4
5
6
7
8
9
10
– 29
SELECT DISTINCT kc.`课程名`, kc.*
FROM xs_kc JOIN xs /*关联学生与课程号*/
ON (xs_kc.`学号` = xs.`学号`)
JOIN kc/*关联课程名*/
ON (xs_kc.`课程号` = kc.`课程号`)
;
1
2
3
4
5
6
7
– 30
SELECT xs.姓名, xs.学号
FROM xs_kc JOIN xs /*关联学生与课程号*/
ON (xs_kc.`学号` = xs.`学号`)
JOIN kc/*关联课程名*/
ON (xs_kc.`课程号` = kc.`课程号`)
WHERE kc.课程号="206";
1
2
3
4
5
6
7
8
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 课程名 ='离散数学'));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
– 32
SELECT xs_kc.`学号`, kc.`课程名`
FROM xs_kc LEFT OUTER JOIN kc
ON (xs_kc.`课程号` = kc.`课程号`)
WHERE (kc.`课程名` = "离散数学");
1
2
3
4
5
6
– 33
SELECT xs.`学号`, xs.`姓名`, xs.`专业名`, xs.`出生时间`
FROM xs
WHERE xs.`出生时间` > ALL(SELECT 出生时间 FROM xs WHERE 专业名="计算机")
;
1
2
3
4
5
6
7
– 34
SELECT 学号, 成绩
FROM xs_kc
WHERE 课程号 ='206'
AND
成绩 >= ANY (SELECT MIN(成绩) FROM xs_kc WHERE 课程号 ='101')
;
1
2
3
4
5
6
7
8
– 35
SELECT xs.`学号`, 姓名
FROM xs JOIN xs_kc
ON (xs.`学号` = xs_kc.`学号`)
WHERE 课程号="206";
1
2
3
4
5
– 36
SELECT 专业名
FROM xs
GROUP BY 专业名;
1
2
3
4
– 37
SELECT 专业名, COUNT(*) AS "人数"
FROM xs
GROUP BY 专业名;
1
2
3
4
– 38
/*P107-38 --不能加""*/
SELECT 课程号, AVG(成绩) AS "平均成绩" , COUNT(*) AS "总人数"
FROM xs_kc
GROUP BY 课程号;
1
2
3
4
5
– 39
SELECT 专业名,
CASE /*性别*/
WHEN 性别=0 THEN "女生"
WHEN 性别=1 THEN "男生"
ELSE "总人数"
END AS 性别 ,
COUNT(*) AS "总人数"
FROM xs
GROUP BY 专业名, 性别
WITH ROLLUP;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
– 40
SELECT 学号, AVG(成绩) AS "平均成绩"
FROM xs_kc
GROUP BY 学号
HAVING AVG(成绩)>85;
1
2
3
4
5
6
41-46小题
底部
– 41
SELECT 学号, 成绩
FROM xs_kc
GROUP BY 学号
HAVING COUNT(*)>2 AND AVG(成绩)>80;
1
2
3
4
5
6
– 42
SELECT *
FROM xs
WHERE 专业名="通信工程"
ORDER BY 出生时间 ASC;
1
2
3
4
5
6
7
– 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; 1
2
3
4
5
6
7
8
9
– 44
/*P107-44--子查询可以使用父查询的字段*/
SELECT xs.学号, 姓名, 专业名
FROM xs
WHERE 专业名="计算机"
ORDER BY (
SELECT AVG(成绩) FROM xs_kc GROUP BY xs_kc.`学号` HAVING (xs.`学号`=xs_kc.`学号`)
) DESC; 1
2
3
4
5
6
7
8
– 45
SELECT *
FROM xs
ORDER BY 学号 LIMIT 5; 1
2
3
4
– 46
SELECT *
FROM xs
ORDER BY 学号 LIMIT 3, 5;
