SQL笔试真题练习
一、现有以下两张表：
第一张表名为cust，其表结构如下：
| 字段名 | 字段说明 | 是否主键 |
| Studentno | 学号，数据类型为整型的 | 是 |
| Name | 学生名字，数据类型为字符串型的 | 否 |
| Address | 学生住址，数据类型为字符串型的 | 否 |
| Telno | 电话号码，数据类型为字符串型的 | 否 |
第二张表名为mark，其表结构如下:
| 字段名 | 字段说明 | 是否为主键 |
| studentno | 学号，数据类型为整型的 | 是 |
| english | 英语成绩，数据类型为数字型的 | 否 |
| math | 数学成绩，数据类型为数字型的 | 否 |
| computer | 计算机成绩，数据类型为数字型的 | 否 |
1) [5分]请写出计算 所有学生的英语平均成绩的sq|语句。
SELECT
a.Studentno,
a. NAME,
b.english
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
2) [5分]现有五 个学生,其学号假定分别为11,22,33,44,55;请用一条SQL语句实现列出这五个学生的数学成绩及其姓名、学生地址、电话号码;
SELECT
a. NAME,
b.math,
a.Address,
a.Telno
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
WHERE
a.Studentno IN (11, 22, 33, 44, 55);
3）[5分]查询所有学生的姓名、计算机成绩，按照计算机成绩从高到低排序;
SELECT
a. NAME,
b.computer
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
ORDER BY
b.computer DESC;
4）[5分]查询所有总成绩大于240分的学生学号、姓名、总成绩,按照总成绩从高到低排序;
SELECT
a.Studentno,
a. NAME,
sum(b.math + b.english + b.computer) zcj
FROM
cust a
JOIN mark b ON a.Studentno = b.studentno
GROUP BY
a.Studentno,
a. NAME
HAVING
zcj > 240
ORDER BY
zcj DESC;
二、
| 表名 | 字段 | 备注 |
| student | id | 学号 |
| name | 学生姓名 | |
| course | id | 课程编号 |
| name | 课程名称 | |
| sc | sid | 学号 |
| cid | 课程编号 | |
| score | 成绩 |
请写出如下SQL:
A.查询姓‘王’的学生的个数;
select count(*) from student where name like '王%';
B. 查询“数学”比“语文”成绩高的所有学生的学号;
法一：使用case...when.. then...end语句：
select a.id,a.name,
sum(case when c.name='语文' then b.score end) chinese,
sum(case when c.name='数学' then b.score end) math
from student a
join sc b on a.id=b.sid
join course c on c.id=b.cid
group by a.id,a.name
having math>chinese
法二：嵌套查询的方式：
select student.id from student,
(select * from sc where cid=(select id from course where name='语文')) chinese,
(select * from sc where cid=(select id from course where name='数学')) math
where student.id=chinese.sid
and chinese.sid=math.sid
and math.score>chinese.score
C.查询平均成绩大于90分的同学的学号和平均成绩。
不涉及到学科的问题，只需关联学生表和成绩表即可：
SELECT a.id, a. name, avg(b.score) FROM student a JOIN sc b ON a.id = b.sid GROUP BY id, name HAVING avg(b.score) > 90;
解题思路：
第二题中，①首先，要知道的是学生的id，姓名和语数成绩，查询出来如下所示：
②用case...when...then...end语句，分别查询出课程名为语文和数学的成绩，如下图所示：
注意：这里注意，为什么需要写两个case...when...then...end语句，因为我们要显示的是两列，实现分组功能
③去null值，用聚合函数，求和、最大值、最小值都可以
④筛选出数学成绩大于语文成绩的，having即可
第三题求平均成绩，不涉及到课程，只需要关联两张表即可
三、
查询学生信息表中男生一共有多少人
select count(*) from t_student where sex='男'
查询男生成绩中前3名的成绩
SELECT b.score FROM student a JOIN sc b ON a.id = b.sid WHERE sex = '男' ORDER BY b.score DESC LIMIT 3
查询男生成绩中排名第3的成绩
SELECT b.score FROM student a JOIN sc b ON a.id = b.sid WHERE sex = '男' ORDER BY b.score DESC LIMIT 2,1
如果在上一题的基础上，若第三名的男生成绩有重复的两个
distinct去重，会把重复的去掉，只保留一个
