mysql 练习题及答案 50道

mysql 练习题及答案 50道此50题参考出处,题目一样,代码有出入,因为题目比较有意思,都自己做了一次。https://zhuanlan.zhihu.com/p/50662216 –1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。–方法1select*fromscoreainnerjoinscorebon(a.s_id=b.s_idanda.c_id…

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

此50题参考出处,题目一样,代码有出入,因为题目比较有意思,都自己做了一次。

https://zhuanlan.zhihu.com/p/50662216

 

— 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。
— 方法1
select * from score a inner join score b on (a.s_id = b.s_id and a.c_id=’01’ and b.c_id=’02’ and a.s_score>b.s_score);
— 方法2
select * from score a inner join score b where (a.s_id = b.s_id and a.c_id=’01’ and b.c_id=’02’ and a.s_score>b.s_score);
— 方法3
select * from  (select * from score where c_id=’01’) as a inner join (select * from score where c_id=’02’) as b on a.s_id=b.s_id
where a.s_score>b.s_score;

— 2、查询平均成绩大于60分的学生的学号和平均成绩
— 方法1
select s_id,avg(s_score) as avgs from score group by s_id HAVING avg(s_score)>60;
— 方法2
select a.s_id,a.avgs from (select s_id,avg(s_score) as avgs from score group by s_id) as a where a.avgs >60;

— 3、查询所有学生的学号、姓名、选课数、总成绩
select stu.s_id,stu.s_name,count(stu.s_id),sum(sc.s_score) from student as stu inner join score as sc on sc.s_id =stu.s_id group by (s_id);

— 4、查询姓“张”的老师的个数
select count(t_id) from teacher where t_name like “张%”;

— 5.查询没学过“张三”老师课的学生的学号、姓名(重点)sc.s_id,stu.s_name

— 错误:
select * from score as sc inner join student stu on stu.s_id=sc.s_id 
where sc.c_id not in
(select c_id from course where t_id=
(select t_id from teacher where t_name=’张三’)) group by sc.s_id;

— 正确1:
select s_id, s_name from student as c where c.s_id not in 
(select sc.s_id from score as sc inner join student stu on stu.s_id=sc.s_id where sc.c_id in
(select c_id from course where t_id=
(select t_id from teacher where t_name=’张三’)));

— 正确2 推荐:
select s_id, s_name
from Student
where s_id not in 
(select s_id from Score join Course on Score.c_id = Course.c_id
join Teacher on Course.t_id = Teacher.t_id
where t_name = ‘张三’);

— 理解过程及注意1:
— 在一对多的情况下,一定不能够直接not in某两个表里笛卡尔集的数据,要先找出唯一对应的,即学过张三的课的学生,再找没有学过的
— 1、select t_id from teacher where t_name=’张三’ 查张三的id
— 2、select c_id from course where t_id=(查张三的id) 查课程的id
— 错误:3、select * from score as sc inner join student stu on stu.s_id=sc.s_id where sc.c_id not in (查课程的id) group by sc.s_id 
— 正确3:select sc.s_id from score as sc inner join student stu on stu.s_id=sc.s_id where sc.c_id  in(查课程的id) 找出学过张三的课的学生
— 正确4:select * from student as c where c.s_id not in (找出学过张三的课的学生)

— 理解过程及注意2:推荐
— 使找出学过的id,再找没学过的id
— 1、Score 有学生id和课程id,并作为主键,可以找出唯一学过张三id的所有学生
— select s_id from Score join Course on Score.c_id = Course.c_id
— join Teacher on Course.t_id = Teacher.t_id
— where t_name = ‘张三’
— 2、使用not in 

— 6、查询学过“张三”老师所教的所有课的同学的学号、姓名
— 方法1
select * from score join course on score.c_id = course.c_id join teacher on teacher.t_id =course.t_id join student on student.s_id=score.s_id where teacher.t_name=’张三’;

— 方法2
select s_id, s_name
from Student
where s_id  in 
(select s_id from Score join Course on Score.c_id = Course.c_id
join Teacher on Course.t_id = Teacher.t_id
where t_name = ‘张三’);

— 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select student.s_id,student.s_name from (select s_id from score where score.c_id=’01’) as a 
join (select s_id from score where score.c_id=’02’) as b on a.s_id=b.s_id 
join student where student.s_id=a.s_id;

select student.s_id,student.s_name from student where s_id in (select s_id from score where score.c_id=’01’)
and s_id in (select s_id from score where score.c_id=’02’);

— 8、查询课程编号为“02”的总成绩
select sum(s_score)
from Score
where c_id = ’02’;

— 9、查询所有课程成绩小于60分的学生的学号、姓名
— select student.s_id,student.s_name from score join student on student.s_id=score.s_id  group by (score.s_id) having max(score.s_score)<60  –这个会缺少没有成绩的学生,当然,题目也没有明确
— 方式1
select student.s_id,student.s_name from student where student.s_id not in (select s_id from score group by (score.s_id) having max(score.s_score)>=60);

— 方式2
select s_id, s_name
from Student 
where s_id NOT IN (SELECT s_id FROM Score where s_score >=60);

— 10、查询没有学全所有课的学生的学号、姓名(题目没有明确要不要完全没学的学生)

select s_id, s_name
from Student 
where s_id NOT IN (SELECT s_id FROM Score group by s_id having count(*)=3); — 含那个没成绩的学生

— 方式1
select s_id, s_name
from Student 
where s_id  IN (SELECT s_id FROM Score group by s_id having count(*)<3);  — 不含那个没成绩的学生

— 方式2
SELECT student.s_id,student.s_name FROM Score join student on student.s_id=score.s_id group by score.s_id
having count(*)<3; — 不含那个没成绩的学生

— 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
— 方式一
select student.s_id,student.s_name from score join student on student.s_id=score.s_id  where score.c_id in 
(select c_id from score where s_id=’01’) and score.s_id !=’01’ group by score.s_id;
— 方式2
select distinct a.s_id,a.s_name
from Student a join Score b on a.s_id= b.s_id
where c_id in
(select c_id from Score where s_id = ’01’)
and a.s_id<>’01’;

— 12、查询和“01”号同学所学课程完全相同的其他同学的学号

select sc.s_id,count(sc.s_id) from score as sc 
where 
sc.s_id <>’01’ and  sc.c_id in (select c_id from score where s_id=’01’) 
group by sc.s_id 
having 
count(sc.s_id)=(select COUNT(*) from score where s_id=’01’) 
and 
(select COUNT(*) from score where s_id=’01’)=(select COUNT(*) from score where s_id=sc.s_id)

— 以下是作者提供的答案,个人认为不正确。例如,把01换成07,没有考虑多出来的情况
select s_id
from Score
where c_id in
(select c_id from Score where s_id=’01’)
and s_id <> ’01’
group by s_id
having count(c_id)=(select count(c_id) from Score where s_id=’01’);

— 13.把“Score”表中“张三”老师教的课的成绩都更改为此课程的平均成绩(难点)

— 方式1
update  score as a join (select Score.c_id,avg(s_score) as t from score where score.c_id=(select c_id from course where t_id=(select t_id from teacher where t_name=’张三’)) group by c_id) as b on a.c_id=b.c_id set a.s_score=t;

— 方式2
UPDATE Score AS a
JOIN (
    SELECT
        AVG( s_score ) AS t,
        Score.c_id 
    FROM
        Score
        JOIN Course ON Score.c_id = Course.c_id
        JOIN Teacher ON Teacher.t_id = Course.t_id 
    WHERE
        t_name = ‘张三’ 
    GROUP BY
        c_id 
    ) AS b ON a.c_id = b.c_id 
    SET a.s_score = b.t;

— 14、查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名(同12题)

— 15、删除学习“张三”老师课的SC表记录

delete from Score
where c_id =(select c_id from Course join Teacher on Course.t_id=Teacher.t_id
where t_name =’张三’)

— 16.检索”01″课程分数小于60,按分数降序排列的学生信息
select * from score join student on student.s_id=score.s_id where score.s_score<60 and score.c_id=’01’ order by score.s_score desc

— 17、按平均成绩从高到低显示所有学生的“数据库”(c_id=’04’)、“企业管理”(c_id=’01’)、“英语”(c_id=’06’)三门的课程成绩,按如下形式显示:学生ID,数据库,企业管理,英语,有效课程数,有效平均分

select s_id as ‘学生ID’,
(case when c_id=’04’ then s_score else NULL end) as ‘数据库’,
(case when c_id=’01’ then s_score else NULL end) as ‘企业管理’,
(case when c_id=’06’ then s_score else NULL end) as ‘英语’,
count(c_id) as ‘有效课程数’,
avg(s_score) as ‘有效平均分’
from Score
group by s_id
order by avg(s_score) DESC;

— 18、查询各科成绩最高和最低的分: 以如下的形式显示:课程ID,最高分,最低分

select c_id,max(s_score),min(s_score) from score group by c_id 

— 19、按各科平均成绩从低到高和及格率的百分数从高到低排列,以如下形式显示:课程号,课程名,平均成绩,及格百分数
select course.c_id,course.c_name,avg(sc.s_score),concat((select count(*) from score as sc1 where sc1.s_score>=60 and sc1.c_id=sc.c_id)/count(*)*100,”%”)
 from score as sc join course on course.c_id=sc.c_id group by sc.c_id order by avg(sc.s_score)

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

select course.c_id,teacher.t_name,course.c_name,avg(sc.s_score),concat((select count(*) from score as sc1 where sc1.s_score>=60 and sc1.c_id=sc.c_id)/count(*)*100,”%”)
 from score as sc join course on course.c_id=sc.c_id join teacher on teacher.t_id=course.t_id group by sc.c_id order by avg(sc.s_score);

— 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称

select sc.c_id,cu.c_name, sum(case when sc.s_score>=85 and sc.s_score <=100 then 1 else 0 end) ‘[100-85]’,
sum(case when sc.s_score>=70 and sc.s_score <=85 then 1 else 0 end) ‘[85-70]’,
sum(case when sc.s_score>=60 and sc.s_score <=70 then 1 else 0 end) ‘[70-60]’,
sum(case when sc.s_score>=0 and sc.s_score <60 then 1 else 0 end) ‘[<60]’
from score as sc join course as cu on sc.c_id=cu.c_id group by c_id

— 24、查询学生平均成绩及其名次
— 作者提示要点:算出在所有同学中有几个同学的平均分高于某个ID,然后+1,就是名次
— 方式1:推荐
select table1.s_id as ‘学号’,avg as ‘平均成绩’ ,@a:=@a+1 as ‘名次
‘ from (select *,avg(sc.s_score) as avg from score as sc group by sc.s_id order by avg(sc.s_score) desc) as table1,(select @a:=0) as b
— 方式2:
SELECT s_id as ‘学号’,平均成绩,
      (SELECT COUNT(*) FROM(SELECT s_id,AVG(s_score)AS ‘平均成绩’
                            FROM Score GROUP BY s_id)AS b 
       WHERE b.平均成绩>a.平均成绩)+1 as RANK
FROM (select s_id,avg(S_score) as 平均成绩 from Score group by s_id)AS a
order by 平均成绩 desc;

— 25、查询各科成绩前三名的记录(不考虑成绩并列情况)

select sc1.c_id,sc1.s_id,sc1.s_score from score as sc1
where (select count(*) from score as sc2 where sc1.s_score < sc2.s_score and sc1.c_id=sc2.c_id)<=2
ORDER BY sc1.c_id ASC,sc1.s_score DESC;

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

select c_id,count(*) from score group by c_id

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

select score.s_id,student.s_name from score join student on student.s_id=score.s_id group by s_id HAVING count(*)=2

— 28、查询男生、女生人数
select s_sex,count(*) from student GROUP BY s_sex

— 29、查询名字中含有“风”字的学生信息
select * from student where s_name like ‘%风%’

— 30、查询同名同姓学生名单并统计同名人数
— 方式1:
select * from student as s1 where (select count(*) from student s2 where s2.s_name=s1.s_name and s1.s_id<>s2.s_id) 
— 方式2:
select * from student as s1 join student as s2 on s1.s_id<>s2.s_id and s1.s_name=s2.s_name
— 方式3:推荐
select s_name,count(*) from student GROUP BY s_name HAVING count(*)>1

— 31、1990年出生的学生名单(注:Student表中s_birth列的类型是datetime)
select * from student where s_birth like “1990%”

select * from student where year(s_birth)=1990

— 32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select student.s_id,student.s_name,avg(s_score) from student join score on student.s_id=score.s_id group by (score.s_id) HAVING avg(s_score)>85

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

select c_id,avg(s_score) as avg from score group by c_id order by avg asc,c_id desc

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

select student.s_name,score.s_score from score join course on score.c_id=course.c_id join student on student.s_id=score.s_id where course.c_name=’数学’ and score.s_score<60;

— 35、查询所有学生的选课情况

select student.s_name,course.c_name from student join score on score.s_id=student.s_id join course on course.c_id=score.c_id

— 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s_name as 姓名,c_name as 课程名称,s_score as 分数 from student join score on student.s_id=score.s_id join course on course.c_id=score.c_id where s_score>70

— 37、查询不及格的课程并按课程号从大到小排列
select * from score where s_score<60 order by c_id

— 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
— 方式一:
select * from student join score on student.s_id=score.s_id where s_score>80 and c_id=’03’
— 方式二:
select * from student where s_id in (select s_id from score where s_score>80 and c_id=’03’)

— 39、查询选了课程的学生人数
— 方式一:推荐
select count(distinct s_id) from score
— 方式二:
select count(*) from (select count(*) from score group by s_id) as b

— 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩
— 方式1
select s_name,max(s_score) from teacher join course on teacher.t_id = course.t_id join score on score.c_id=course.c_id join student on student.s_id=score.s_id where t_name=’张三’ group by (teacher.t_id)

— 方式2
select s_name,s_score from teacher join course on teacher.t_id = course.t_id join score on score.c_id=course.c_id join student on student.s_id=score.s_id where t_name=’张三’ order by s_score desc limit 1

— 41、查询各个课程及相应的选修人数
select score.c_id,course.c_name,count(*) from score join course on score.c_id=course.c_id group by c_id

— 42、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct sc1.s_id,sc1.c_id,sc1.s_score from score as sc1 join score as sc2 on sc1.s_id=sc2.s_id and sc1.c_id<>sc2.c_id and sc1.s_score=sc2.s_score;

— 43、查询每门课程成绩最好的前两名 同25题
select * from score as sc1 where (select count(*) from score as sc2 where sc2.s_score>sc1.s_score and sc1.c_id=sc2.c_id)<2 order BY sc1.c_id

— 44、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序

select c_id,count(*) from score group by c_id having count(*)>5 order by count(*) desc,c_id asc

— 查询至少选修两门课程的学生学号
select s_id,count(*) from score group by s_id having count(*)>=2

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

select s_id,count(*) from score group by s_id having count(*)=(select count(*) from course)

— 47、查询没学过“张三”老师讲授的任一门课程的学生姓名

select * from student where s_id not in ( select score.s_id from teacher join course on course.t_id=teacher.t_id join score on score.c_id=course.c_id join student on student.s_id=score.s_id where teacher.t_name=’张三’ group by score.s_id)

— 48、查询两门以上不及格课程的同学的学号及其平均成绩

select s_id,avg(s_score) from score where s_score<60 group by s_id

— 49、检索课程编号为“01”且分数小于60的学生学号,结果按按分数降序排列
select s_id
from Score
where c_id=’01’
and s_score <60
order by s_score DESC;

— 50、删除学生编号为“02”的课程编号为“01”的成绩
delete from score where s_id=’02’ and c_id=’01’
 

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/192373.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 推荐几个长期有效的免费服务器和免费vps游戏服务器亲测再用

    推荐几个长期有效的免费服务器和免费vps游戏服务器亲测再用对于新手,搞网络购买现有的服务器和VPS成本太高!这里我长期测试筛选了几个长期有效好用的服务器!(当然免费虽好请勿滥用)1.FREEWHA这个服务器已经存在10几年了,好用谷歌收录也可以。提供1.5g空间免费流量,SSL申请免费空间很少有提供SSL的!这个空间的缺点就是无法上传大文件,最好用FTP上传。如果你做博客网站,选择SQLIVE数据库的最好。比如;ZBLOG的网站程序.2.Freehosting一个非常稳定的免费空间,提供10G空间和无限流量。这个空间其他功能都需要购买,数据库链接有流量限制

  • vue生命周期函数_有哪些vue生命周期函数

    vue生命周期函数_有哪些vue生命周期函数生命周期函数生命周期函数代表的是Vue实例,或者是Vue组件,在网页中各个生命阶段所执行的函数。生命周期函数可以分为创建阶段和运行期间以及销毁期间。其中创建期间的函数有beforeCreate、c

  • pycharmdjango项目实战_pycharm django环境搭建

    pycharmdjango项目实战_pycharm django环境搭建创建项目我们创建django项目有两种方式,命令行方式和使用pycharm工具创建,本文就介绍常用的pycharm工具创建首先点击django,输入项目的名称,选择创建好的虚拟环境,最后点击cre

  • 1192啥意思_拓扑排序怎么排

    1192啥意思_拓扑排序怎么排由于无敌的凡凡在2005年世界英俊帅气男总决选中胜出,Yali Company总经理Mr.Z心情好,决定给每位员工发奖金。公司决定以每个人本年在公司的贡献为标准来计算他们得到奖金的多少。于是Mr.Z下令召开 m 方会谈。每位参加会谈的代表提出了自己的意见:“我认为员工 a 的奖金应该比 b 高!”Mr.Z决定要找出一种奖金方案,满足各位代表的意见,且同时使得总奖金数最少。每位员工奖金最少为100元,且必须是整数。输入格式第一行包含整数 n,m,分别表示公司内员工数以及参会代表数。接下来 m

  • SQL 清空表数据的方法

    SQL 清空表数据的方法truncatetabletablenamedeletefromtablenamedelete方法最大的问题,当有标志字段时就有问题一般建議盡可能使用delete去刪除表的字段,它具有選擇性刪除的作用,所以常常deletefromtablenamewhere條件delete和truncate作用其實是一樣的,隻是truncate后面不跟where條件句,它的作用是刪除表中所有的行(記錄)兩者最大的區別就是delete是寫日誌文件的,而truncate則不寫日誌直接刪除

  • APC 注入

    APC注入APC介绍APC(AsynchronousProcedureCalls,异步过程调用),APC是函数在特定的线程被异步执行。在Windows中APC是一种并发机制,用于异步的IO或

    2021年12月13日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号