sql常见50题--附答案解析

最新推荐文章于 2024-05-06 22:30:05 发布

却顾所来径

最新推荐文章于 2024-05-06 22:30:05 发布

阅读量7.7k

点赞数 12

分类专栏：数据库文章标签： sql语句练习

本文链接：https://blog.csdn.net/weixin_42130471/article/details/88667867

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据库建表语句：

--建表
--学生表
CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
    `c_id`  VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id`  VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

题目：

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT st.*,sc1.s_score AS "课程01",sc2.s_score AS "课程02"
FROM student st JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01"
	        JOIN score sc2 ON st.s_id=sc2.s_id AND sc2.c_id="02"
WHERE sc1.s_score>sc2.s_score;

注：表使用别名as可省，大小写不敏感，字段加别名as不可省，且要加双引号。因为score表一行只有一门课成绩，所以要join两次。

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

SELECT st.s_id AS "学生编号",st.s_name AS "学生姓名",ROUND(AVG(sc.s_score),2) AS "平均成绩"
FROM student st JOIN score sc ON st.s_id=sc.s_id
GROUP BY st.s_id
HAVING ROUND(AVG(sc.s_score),2)>=60;

注：ROUND(AVG(sc.s_score),2)，2的意思是保留两位小数

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)

SELECT st1.s_id AS "学生编号",st1.s_name AS "学生姓名",ROUND(AVG(sc.s_score)) AS "平均成绩"
FROM student st1 JOIN score sc ON st1.s_id=sc.s_id
GROUP BY st1.s_id 
HAVING ROUND(AVG(sc.s_score))<60
UNION
SELECT st2.s_id AS "学生编号",st2.s_name AS "学生姓名",0 AS "平均成绩"
FROM student st2
WHERE st2.s_id NOT IN(
	SELECT DISTINCT s_id FROM score
);

5.查询所有同学的学生编号，学生姓名，选课总数，所有课程的总成绩

SELECT st.s_id,st.s_name,COUNT(sc.c_id) AS "选课总数",SUM(sc.s_score)-1 AS "总成绩"
FROM student st LEFT JOIN score sc ON st.s_id=sc.s_id
GROUP BY st.s_id;

注：因为有人没有成绩，所以用left join。同时count()函数内的内容也不能随便填，刚开始我写的“*”，发现没有成绩的也会有至少条。可以填sc.c_id或者sc.s_id

6.查询‘李’姓老师的数量

SELECT COUNT(*) FROM teacher WHERE t_name LIKE "李%";

注：这个简单，因为只有一个表，所以t_name前面可不加表名

7.查询学过’张三‘老师授课的同学的信息

写法一：

SELECT st.* FROM student st JOIN score sc ON st.s_id=sc.s_id  
			    JOIN course c ON sc.c_id=c.c_id 
			    JOIN teacher t ON c.t_id=t.t_id AND t.t_name="张三";

写法二：

SELECT st1.* 
FROM student st1 ,score sc ,course c,teacher t 
WHERE st1.s_id=sc.s_id AND sc.c_id=c.c_id AND c.t_id=t.t_id AND t.t_name="张三"

8.查询没学过"张三"老师授课的同学的信息

注：上题我们查询了学过张三老师授课的同学，所以只要根据id使用not in排除以上同学即可。

SELECT st.* FROM student st 
WHERE st.s_id NOT IN(
	SELECT st.s_id FROM student st JOIN score sc ON st.s_id=sc.s_id  
				       JOIN course c ON sc.c_id=c.c_id 
				       JOIN teacher t ON c.t_id=t.t_id AND t.t_name="张三"
);

9.查询学过01也学过02的课程的同学的信息

SELECT st.* FROM student st JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01"
			    JOIN score sc2 ON st.s_id=sc2.s_id AND sc2.c_id="02";

注：题目意思要求两门课都学过的同学的信息，所以使用内连接。因为score表一行只有一门课所以要把两张score表内联起来。语句倒是很简单。

10.查询学过01但是没有学过02的同学的信息

自己写的：

SELECT st.* FROM student st 
JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01" AND st.s_id NOT IN(
	SELECT st.s_id FROM student st JOIN score sc ON st.s_id=sc.s_id AND sc.c_id="02"
);

思路：首先内部先查出选了02的同学的s_id。外部查出选了01的同学的s_id，然后排除内部查询的结果。

发现：not in内部其实还可以简化一下，没必要联表

SELECT st.* FROM student st 
JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01" AND st.s_id NOT IN(
	SELECT s_id FROM score WHERE c_id="02"
);

11.查询没有学全所有课程的同学的信息

SELECT st.* FROM student st JOIN score sc ON st.s_id=sc.s_id 
GROUP BY sc.s_id 
HAVING COUNT(*) = (SELECT COUNT(c.c_id) FROM course c);

思路：选课数量=所有课程数量。先把student表和score表内联成一个表，接着按照s_id分组，然后统计每组的数量是否等于所有课程数量，使用count(*)=全部课程数量。看了网上答案基本都是事先已知课程数量，这有点不严谨。

12.查询至少有一门课与学号为’01‘的同学所学相同的同学的信息

SELECT DISTINCT st.* 
FROM student st JOIN score sc 
ON st.s_id=sc.s_id AND sc.c_id IN (
	SELECT sc.c_id FROM score sc WHERE sc.s_id="01" 
) AND sc.s_id NOT IN ("01");

思路：查询出‘01’的同学选了什么课，然后使用in即可表示“至少”。最后记得排除”01“自身。

13.查询和01号的同学学习的课程完全相同的其他同学的信息

思路：这题有难度呀。我的做法是双重否定。最内层否定：获取所选课程包含01同学没选的课程的同学。次内层否定：排除这些同学。即剩下所选课程包含在01同学所选课程的之中的同学。如：01选了“A”，“B”,"C"课程，则剩下的这些同学不能选ABC课程之外的课程且所选的课程数<=01选的课程数。所以还需要加上一个个数判断，留下满足选的课程数等于01同学所选课程数的同学，这就是group by和having count的作用。

SELECT DISTINCT st.* 
FROM student st JOIN score sc ON st.s_id=sc.s_id
WHERE st.s_id NOT IN (
	SELECT s_id FROM score WHERE c_id NOT IN(
		SELECT c_id FROM score WHERE s_id="01"
	)
)
GROUP BY s_id
HAVING COUNT(*)=(SELECT COUNT(*) FROM score WHERE s_id="01");

14.查询没学过‘张三’老师教的任一门课程的学生姓名

思路：查找出选过张三课程的，然后排除。

SELECT st.s_name FROM student st WHERE st.s_id NOT IN(
	SELECT st.s_id FROM student st JOIN score sc ON st.s_id=sc.s_id 
				       JOIN course c ON sc.c_id=c.c_id
				       JOIN teacher t ON c.t_id=t.t_id AND t.t_name="张三"
);

15.查询两门及以上不及格课程的同学的学号姓名及平均成绩

思路：

SELECT st.s_id,st.s_name,ROUND(AVG(sc.s_score)) AS "平均分"
FROM student st JOIN score sc ON st.s_id=sc.s_id 
WHERE st.s_id IN (
	SELECT sc.s_id FROM score sc
	WHERE sc.s_score<60
	GROUP BY sc.s_id
	HAVING COUNT(*)>=2
)
GROUP BY sc.s_id;

16.检索01分数小于60，按分数降序排列的学生信息

SELECT st.* 
FROM student st JOIN score sc ON st.s_id=sc.s_id 
WHERE sc.c_id="01" AND sc.s_score<60 
ORDER BY sc.s_score DESC;

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

一次性写了17道题目，剩下有时间再补充~

却顾所来径

关注

12
点赞
踩
61

收藏

觉得还不错? 一键收藏
5
评论
sql常见50题--附答案解析

数据库建表语句：--建表--学生表CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL D...
复制链接

扫一扫