(MySQL)SQL语句练习题【简单版】

最新推荐文章于 2024-04-07 11:22:06 发布

翻山越玲

最新推荐文章于 2024-04-07 11:22:06 发布

阅读量527

点赞数

分类专栏： MySQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/weixin_44786702/article/details/128914107

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

一、创建表

1、创建学生表

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`)
);

2、创建教师表

CREATE TABLE `Teacher`(  
`t_id` VARCHAR(20),  
`t_name` VARCHAR(20) NOT NULL DEFAULT '',  
PRIMARY KEY(`t_id`)
);

3、创建课程表

CREATE TABLE `Course`(  
`c_id`  VARCHAR(20),  
`c_name` VARCHAR(20) NOT NULL DEFAULT '',  
`t_id` VARCHAR(20) NOT NULL,  
PRIMARY KEY(`c_id`));

4、创建成绩表

CREATE TABLE `Score`(  
`s_id` VARCHAR(20),  
`c_id`  VARCHAR(20),  
`s_score` INT(3),  
PRIMARY KEY(`s_id`,`c_id`)
);

二、插入测试数据

1、插入学生表测试数据

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' , '女');

2、插入教师表测试数据

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

3、插入课程表测试数据

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

4、插入成绩表测试数据

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' , '03' , 98);
insert into Score values('07' , '03' , 98);

三、SQL语句练习

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

select t.s_id,t.s_name,round(avg(t1.s_score),2) avg_score 
from student t join score t1 on t.s_id =t1.s_id 
group by t.s_id
having avg(t1.s_score) >= 60

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

select t.s_id,t.s_name,round(avg(t1.s_score),2) avg_score from student t 
left join score t1 on t.s_id =t1.s_id 
group by t.s_id
having avg(t1.s_score) < 60 or avg(t1.s_score)  is null

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select t.s_id,t.s_name,ifnull(count(t1.c_id),0) as '选课总数',
ifnull(sum(t1.s_score),0) as '课程总成绩'	
from student t  left join score t1 on t.s_id =t1.s_id 
group by t.s_id,t.s_name

4、查询"李"姓老师的数量

select count(t.t_id) 数量 from teacher  t
where  t_name like '%李_%'

5、查询学过"张三"老师授课的同学的信息

select t4.* from score t1,course t2,teacher t3,student t4
where 
t1.c_id = t2.c_id and 
t2.t_id = t3.t_id and
t1.s_id = t4.s_id and
t3.t_name = '张三'

6、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select t3.* from score t1,score t2,student t3 
where t1.c_id ='01' and t2.c_id ='02' 
and t1.s_id = t2.s_id
and t1.s_id = t3.s_id

7、查询没有学全所有课程的同学的信息 (学习了1门课程或者2门课程)

SELECT t1.* FROM student t1 
LEFT JOIN score t2 on t1.s_id = t2.s_id
GROUP BY t1.s_id
HAVING count(t2.c_id) < (SELECT count(c_id) FROM course)

8、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT s_name FROM student WHERE s_id not in(
SELECT s_id	FROM score WHERE c_id =(
SELECT c_id FROM course WHERE t_id = (
SELECT t_id FROM teacher where t_name = '张三')))

9、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT t1.s_id,t2.s_name,ROUND(avg(t1.s_score),2) 平均分 FROM score t1,student t2
where t1.s_score<60 and 
t1.s_id = t2.s_id 
group by s_id
having count(t1.s_score)>=2

10、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT * FROM student 
WHERE S_ID IN
(SELECT s_id FROM score 
WHERE C_ID = '01' AND s_score <60
ORDER BY s_score DESC)

11、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT t2.c_id,t2.c_name,max(s_score) 最高分,min(s_score) 最低分,avg(s_score) 平均分,
SUM(CASE  WHEN  s_score>=60 THEN 1 ELSE 0 END) / SUM(CASE  WHEN  s_score THEN 1 ELSE 0 END) 及格率,
SUM(CASE  WHEN  s_score>=70 and s_score<=80 THEN 1 ELSE 0 END) / SUM(CASE  WHEN  s_score THEN 1 ELSE 0 END) 中等率,
SUM(CASE  WHEN  s_score>=80 and s_score<=90 THEN 1 ELSE 0 END) / SUM(CASE  WHEN  s_score THEN 1 ELSE 0 END) 优良率,
SUM(CASE  WHEN  s_score>=90 THEN 1 ELSE 0 END) / SUM(CASE  WHEN  s_score THEN 1 ELSE 0 END) 优秀率  FROM score t1 
JOIN course t2 on t1.c_id = t2.c_id
GROUP BY c_id

12、查询不同老师所教不同课程平均分从高到低显示

SELECT t1.t_name,t2.c_name,AVG(t3.s_score) as score  FROM teacher t1 
INNER JOIN course t2 on t1.t_id = t2.t_id
INNER JOIN score t3 on t2.c_id = t3.c_id
GROUP BY t1.t_id
ORDER BY avg(s_score) DESC

13、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT t1.c_id,t2.c_name,
SUM(CASE WHEN t1.s_score>85 and t1.s_score<=100 THEN	1 ELSE 0 END) '85-100人数',
100*(SUM(CASE WHEN t1.s_score>85 and t1.s_score<=100 THEN	1 ELSE 0 END)/SUM(CASE WHEN t1.s_score THEN	1 ELSE 0 END)) '100-85',
SUM(CASE WHEN t1.s_score>70 and t1.s_score<=85 THEN	1 ELSE 0 END) '70-85人数',
100*(SUM(CASE WHEN t1.s_score>70 and t1.s_score<=85 THEN	1 ELSE 0 END)/SUM(CASE WHEN t1.s_score THEN	1 ELSE 0 END)) '85-70',
SUM(CASE WHEN t1.s_score>60 and t1.s_score<=70 THEN	1 ELSE 0 END) '60-70人数',
100*(SUM(CASE WHEN t1.s_score>60 and t1.s_score<=70 THEN	1 ELSE 0 END)/SUM(CASE WHEN t1.s_score THEN	1 ELSE 0 END)) '70-60',
SUM(CASE WHEN t1.s_score>=0 and t1.s_score<=60 THEN	1 ELSE 0 END)   '0-100人数',
100*(SUM(CASE WHEN t1.s_score>=0 and t1.s_score<=60 THEN	1 ELSE 0 END)/SUM(CASE WHEN t1.s_score THEN	1 ELSE 0 END))'0-60' FROM score t1
INNER JOIN course t2 on t1.c_id = t2.c_id
GROUP BY t1.c_id

14、查询每门课程被选修的学生数

SELECT c_id,count(s_id) FROM score
GROUP BY c_id

15、查询出只有两门课程的全部学生的学号和姓名

SELECT t1.s_id,t2.s_name FROM score t1 
INNER JOIN student t2 on t1.s_id = t2.s_id
GROUP BY s_id
HAVING count(s_score)=2

16、查询男生、女生人数

SELECT s_sex,count(s_id) 人数 FROM student
GROUP BY s_sex

17、查询名字中含有"风"字的学生信息

SELECT * FROM student
WHERE s_name LIKE '%风%'

18、查询同名同性学生名单，并统计同名人数

方法1：
SELECT s_name,count(*) 人数 FROM student
GROUP BY s_name
HAVING 人数>1
方法2：
select a.s_name,a.s_sex,count(*) from student a  
JOIN  student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
GROUP BY a.s_name,a.s_sex

19、查询1990年出生的学生名单

SELECT s_name FROM student
WHERE s_birth like '1990%'

20、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT c_id,AVG(s_score) avg_score FROM score
GROUP BY c_id
ORDER BY avg_score DESC,c_id ASC

21、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT t.s_id,t1.s_name,avg(t.s_score) avg_score FROM score t
JOIN student t1 on t.s_id = t1.s_id
GROUP BY s_id
HAVING avg_score>=85
ORDER BY avg_score DESC,c_id ASC

22、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT t2.s_name,t.s_score FROM score t
join course t1 on t.c_id = t1.c_id and  t1.c_name = '数学'
join student t2 on t.s_id = t2.s_id
 WHERE t.s_score<60

23、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT t1.s_name,SUM((t2.c_id='01')*t.s_score) as '语文',
SUM((t2.c_id='02')*t.s_score) as '数学',
SUM((t2.c_id='03')*t.s_score)  as '英语'
			FROM score t
			INNER JOIN student t1 on t.s_id = t1.s_id
			INNER JOIN course t2  on t.c_id = t2.c_id
			WHERE t.s_score >=70
			GROUP BY  t.s_id

24、查询不及格的课程

SELECT * FROM score
WHERE s_score<60

25、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

SELECT t1.s_id,t1.s_name FROM score t
INNER JOIN student t1 on t.s_id = t1.s_id	
WHERE t.c_id = '01' and t.s_score>'80'

26、求每门课程的学生人数

SELECT c_id,count(s_id) num FROM score
GROUP BY c_id

27、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT c_id,count(t1.s_id) num FROM score t1
GROUP BY t1.c_id
HAVING count(t1.s_id)>5
ORDER BY num desc,c_id asc

28、检索至少选修两门课程的学生学号

SELECT t1.s_id  FROM student t1
INNER JOIN score  t2 on t1.s_id = t2.s_id
GROUP BY t1.s_id
HAVING count(t2.s_score) >=2

29、查询选修了全部课程的学生信息

SELECT t1.*  FROM student t1
INNER JOIN score  t2 on t1.s_id = t2.s_id
GROUP BY t1.s_id
HAVING count(t2.s_score) = (SELECT count(c_id) FROM course)

30、统计各位老师,所教课程的及格率(case)

SELECT t3.t_name,round(100*(SUM(CASE WHEN t1.s_score>=60 THEN 1 ELSE 0 END)/count(t1.s_score)),2) FROM score t1
JOIN course t2 on t1.c_id = t2.c_id
JOIN teacher t3 on t2.t_id = t3.t_id
GROUP BY t1.c_id

31、查询出只选修了一门课程的全部学生的学号和姓名

SELECT t1.s_id,t2.s_name FROM score t1
RIGHT JOIN student t2 on t1.s_id = t2.s_id 
GROUP BY s_id
HAVING count(s_score) = 1

四、参考链接

https://mp.weixin.qq.com/s/4MbK-G37I1Od3Jf2SD0aJQ

翻山越玲

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
(MySQL)SQL语句练习题【简单版】

SQL语句练习(简单版)
复制链接

扫一扫

专栏目录

(MySQL)SQL语句练习题【简单版】

文章目录

一、创建表

1、创建学生表

2、创建教师表

3、创建课程表

4、创建成绩表

二、插入测试数据

1、插入学生表测试数据

2、插入教师表测试数据

3、插入课程表测试数据

4、插入成绩表测试数据

三、SQL语句练习

四、参考链接

“相关推荐”对你有帮助么？