MySQL 练习题4 参考答案

最新推荐文章于 2024-01-07 22:57:25 发布

原创最新推荐文章于 2024-01-07 22:57:25 发布 · 465 阅读

0 ·

CC 4.0 BY-SA版权

本文提供了多种复杂的SQL查询实例，涵盖成绩对比、条件筛选、多表连接等实用场景，帮助读者掌握高级SQL查询技巧。

1.表结构如下:

数据脚本

2.查询:

查询学习课程"python"比课程 "java" 成绩高的学生的学号;

#先查询"python"课程和"java"课程的学生成绩,临时表
#让两个临时表进行比较
-- select * from course c,score s where c.c_id = s.c_id and c.c_name='python';
-- select * from course c,score s where c.c_id = s.c_id and c.c_name='java';
 
select A.s_id from
(select s.s_id,s.num from course c,score s where c.c_id = s.c_id and c.c_name='python') AS A ,
 (select s.s_id,s.num from course c,score s where c.c_id = s.c_id and c.c_name='java') AS B
where A.s_id = B.s_id and A.num > B.num;

查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　

1

2

select round(avg(num),2) as num,student.s_name from score s

LEFT JOIN student ON s.s_id = student.s_id group by s.s_id having num > 65;

查询所有同学的姓名、选课数、总成绩；

#先来分析需要哪些表:学生表/成绩表
#然后进行多表查询即可
 
select s_name,count(*) '选课数',sum(num)as '总成绩' from student st,score s where st.s_id = s.s_id GROUP BY s.s_id;

查询所有的课程的名称以及对应的任课老师姓名;

1

select c_name,t_name from course,teacher where course.t_id = teacher.t_id;

查询没学过“alex”老师课的同学的姓名;

#先看看alex教什么课程
#看看谁学了alex的课程
#最后把学了的人过滤掉就是没学过的学生
-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';
 
-- select s_id from score where c_id in(2,4);
 
select s_name from student 
where s_id not in(select s_id from score where c_id in(2,4));

查询学过'python'并且也学过编号'java'课程的同学的姓名;

-- select * from score where score.c_id='1' and score.c_id='2'
#查询python和java课程号
-- select c_id from course where course.c_name in('python','java');
 
SELECT st.s_name from score s ,student st
where s.s_id = st.s_id AND s.c_id in(1,2) GROUP BY s.s_id HAVING COUNT(*) = 2;

查询学过“alex”老师所教的全部课程的同学的姓名；

#先知道alex老师教什么课程
#然后来看看学了alex课程的学生有哪些人
#按学生分组,看看谁学的课程数 = alex老师教授的课程数
 
-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';
 
select student.s_name from score,student where score.s_id =student.s_id and score.c_id in
    (select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex') GROUP BY score.s_id
 
HAVING count(*) = (select count(*) from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex');

查询挂科超过两门(包括两门)的学生姓名;

1

2

SELECT student.s_name from score,student

where score.s_id = student.s_id and score.num <60 GROUP BY student.s_id HAVING count(*)>=2;
查询有课程成绩小于60分的同学的姓名;

1

2

SELECT DISTINCT student.s_name from score,student

where score.s_id = student.s_id and score.num <60;

查询选修了全部课程的学生姓名;

-- select count(*) from course;
 
select student.s_name from score,student
where score.s_id = student.s_id GROUP BY score.s_id HAVING count(*) = (select count(*) from course)

查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；

-- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'
  
select student.s_name from score,student  where score.s_id = student.s_id and
　　score.c_id in(SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉')
 
and student.s_name <> '貂蝉' GROUP BY student.s_id;

查询学过'貂蝉'同学全部课程的其他同学姓名；

-- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'
 
select student.s_name,count(*) from score,student  where score.s_id = student.s_id
and  score.c_id in(SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉')
and student.s_name <> '貂蝉' GROUP BY student.s_id
HAVING count(*) = (SELECT count(*) from score,student where score.s_id =student.s_id and student.s_name='貂蝉');

查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；

解题思路:
#1. 找出与'貂蝉'学习课程数 相同的学生s_id (你学两门,我也学两门)　
#2. 再找出学过'貂蝉'课程的学生,剩下的一定是至少学过一门'貂蝉'课程的学生
#3. 再根据学生ID进行分组,剩下学生数count(1) = 貂蝉学生所学课程数
 
#1.找出与'貂蝉'学习课程数 相同的学生s_id (你学两门,我也学两门)
select * FROM score where score.s_id in(
    select s_id from score GROUP BY score.s_id HAVING count(*) = (
        select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'
    )
)
 
#2.然后再找出学过'貂蝉'课程的学生,剩下的一定是至少学过一门'貂蝉'课程的学生
select * FROM score where score.s_id in(
    select s_id from score GROUP BY score.s_id HAVING count(*) = (
        select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'
    )
)
and score.c_id in(select c_id from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')
 
#3.再根据学生ID进行分组,剩下学生数count(1) = 貂蝉学生所学课程数
select * FROM score where score.s_id in(
    select s_id from score GROUP BY score.s_id HAVING count(*) = (
        select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'
    )
)
and score.c_id in(select c_id from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')
 
GROUP BY score.s_id HAVING count(*) =(select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')
and score.s_id !=2;

按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示：学生ID,python,java,linux,课程数,平均分

#1.先查询单一学生的python课程分数
select num from score,course where score.c_id = course.c_id  AND course.c_name ='python' and score.s_id = 1;
 
#2.将上面查询的结果作为 列字段使用
select s.s_id,
(select num from score,course where score.c_id = course.c_id AND course.c_name ='python' and score.s_id = s.s_id ) as 'python',
(select num from score,course where score.c_id = course.c_id AND course.c_name ='java' and score.s_id = s.s_id ) as 'java',
(select num from score,course where score.c_id = course.c_id AND course.c_name ='linux' and score.s_id = s.s_id ) as 'linux',
 count(c_id)as '课程数',
 avg(num) as '平均分'
 
from score s GROUP BY s.s_id;

统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select score.c_id,course.c_name,
　　sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
　　sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
　　sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
　　sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;

查询每门课程被选修的次数

1

select c_name,count(*) from course,score where course.c_id = score.c_id GROUP BY score.c_id;

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

1

2

select student.s_id,student.s_name from student,score where

student.s_id = score.s_id GROUP BY score.s_id HAVING count(*)=1

查询学生表中男生、女生各有多少人

注意:不用group by 分组
select
　　 sum(CASE WHEN s_sex ='男' THEN 1 ELSE 0 END) as '男生',
    sum(CASE  WHEN s_sex ='女' THEN 1 ELSE 0 END) as '女生'
FROM student

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

1

2

select course.c_name,avg(num) as '平均成绩' from course,score

where course.c_id = score.c_id GROUP BY score.c_id ORDER BY avg(num),score.c_id desc;

　　
查询课程名称为“python”，且分数低于60的学生姓名和分数

1

2

select student.s_name,score.num from score,course,student

where score.c_id = course.c_id and student.s_id = score.s_id and course.c_name = 'python' and score.num < 67