50道数据库

最新推荐文章于 2022-06-27 10:27:54 发布
最新推荐文章于 2022-06-27 10:27:54 发布
阅读量109 收藏
点赞数
分类专栏： JAVA 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xxiyy0411/article/details/121880073
版权

表名和字段

–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

测试数据

–建表
–学生表
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)

– 练习题和sql语句

– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT* FROM (SELECT * from score WHERE c_id=‘01’)as a,(SELECT * from score WHERE c_id=‘02’)as b WHERE a.s_id=b.s_id and a.s_score>b.s_score

– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT * from (select * from score where c_id=‘01’) as a , (select * from score where c_id=‘02’) as b where
a.s_id = b.s_id and a.s_score<b.s_score

– 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select sc.s_id,stu.s_name,avg(sc.s_score) from score sc,student stu where sc.s_id=stu.s_id group by sc.s_id,stu.s_name having avg(sc.s_score)>=60

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
– (包括有成绩的和无成绩的)
select sc.s_id,stu.s_name,avg(sc.s_score) from score sc RIGHT JOIN student stu on sc.s_id=stu.s_id group by sc.s_id, stu.s_name having avg(sc.s_score)<60

– 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select sc.s_id,stu.s_name,count(*),sum(sc.s_score) from student stu inner join score sc on stu.s_id = sc.s_id group by sc.s_id,stu.s_name

– 6、查询"李"姓老师的数量
select count(*) from teacher where t_name like ‘李%’

– 7、查询学过"张三"老师授课的同学的信息
select distinct stu.* from teacher tea,course co,student stu,score sc where tea.t_id = co.t_id and stu.s_id=sc.s_id and sc.c_id = co.c_id and tea.t_name=‘张三’

– 8、查询没学过"张三"老师授课的同学的信息
SELECT s_id,s_name,s_sex,s_birth FROM student
WHERE s_id NOT IN
(SELECT c.s_id
FROM teacher a RIGHT JOIN course b ON a.t_id=b.t_id
RIGHT JOIN score c ON b.c_id = c.c_id
INNER JOIN student d ON c.s_id = d.s_id
WHERE a.t_name = ‘张三’ )

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

select s3.* from score s1,score s2,student s3 where s1.s_id=s2.s_id and s1.s_id=s3.s_id and s1.c_id=‘01’ and s2.c_id=‘02’

– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.s_id,b.s_name,b.s_sex,b.s_birth
FROM score a INNER JOIN student b ON a.s_id=b.s_id
WHERE a.c_id=‘01’ AND a.s_id NOT IN (SELECT s_id FROM score WHERE c_id=‘02’)

– 11、查询没有学全所有课程的同学的信息
SELECT a.s_id,b.s_name,b.s_sex,b.s_birth,COUNT(a.c_id)AS ‘修读课程’
FROM score a inner JOIN student b ON a.s_id=b.s_id
GROUP BY a.s_id
HAVING COUNT(a.c_id)❤️

– 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select DISTINCT student.* from score sc ,student
where sc.s_id=student.s_id
and sc.c_id in (select c_id from score where s_id=‘01’)

– 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select *
from student
where student.s_id not in (
select t1.s_id
from
(select student.s_id,t.c_id
from student ,(select score.c_id from score where score.s_id=‘01’) as t )as t1
left join score on t1.s_id=score.s_id and t1.c_id=score.c_id
where score.c_id is null )
and student.s_id !=‘01’

– 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name from student where s_id not in
(
select s_id from score where c_id in
(select c_id from teacher tea,course co where tea.t_id=co.t_id and tea.t_name=‘张三’)
)

– 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select sc.s_id,stu.s_name,avg(sc.s_score) from student stu LEFT JOIN score sc on stu.s_id=sc.s_id
where sc.s_score<60
group by sc.s_id,stu.s_name having count(*)>=2

– 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select stu.* from student stu,score sc where stu.s_id=sc.s_id and sc.s_score<60 ORDER BY sc.s_score desc

– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.*,a.aa from score sc,(select s_id,avg(s_score) aa from score group by s_id) a where sc.s_id=a.s_id order by
a.aa desc

– 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select sc.c_id ,max(sc.s_score)as 最高分,min(sc.s_score)as 最低分,AVG(sc.s_score)as 平均分,count()as 选修人数,sum(case when sc.s_score>=60 then 1 else 0 end )/count()as 及格率,sum(case when sc.s_score>=70 and sc.s_score<80 then 1 else 0 end )/count()as 中等率,sum(case when sc.s_score>=80 and sc.s_score<90 and sc.s_score<80 then 1 else 0 end )/count()as 优良率,sum(case when sc.s_score>=90 then 1 else 0 end )/count()as 优秀率
from score sc
GROUP BY sc.c_id
ORDER BY count()DESC,sc.c_id asc

– 19、按各科成绩进行排序，并显示排名
select c_id,ROUND(AVG(s_score),2) from score group by c_id order by avg(s_score) desc

– 20、查询学生的总成绩并进行排名
select s_id,sum(s_score) from score group by s_id order by sum(s_score) desc

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

– 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

– 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT sc.C_Id,c.c_name,
SUM(case when sc.s_score>85 and sc.s_score<=100 then 1 else 0 end) as ‘[100-85]’,
SUM(case when sc.s_score>85 and sc.s_score<=100 then 1 else 0 end)/count(sc.C_Id) as ‘百分比’,
SUM(case when sc.s_score>70 and sc.s_score<=85 then 1 else 0 end) as ‘[85-70]’,
SUM(case when sc.s_score>70 and sc.s_score<=85 then 1 else 0 end)/count(sc.C_Id) as ‘百分比’,
SUM(case when sc.s_score>60 and sc.s_score<=70 then 1 else 0 end) as ‘[70-60]’,
SUM(case when sc.s_score>60 and sc.s_score<=70 then 1 else 0 end)/count(sc.C_Id) as ‘百分比’,
SUM(case when sc.s_score>0 and sc.s_score<=60 then 1 else 0 end) as ‘[60-0]’,
SUM(case when sc.s_score>0 and sc.s_score<=60 then 1 else 0 end)/count(sc.C_Id) as ‘百分比’
FROM score as sc join course as c on sc.C_Id=c.c_id
GROUP BY sc.C_Id,c.c_name

– 24、查询学生平均成绩及其名次
select s1.s_name,avg(s2.s_score) from student s1 RIGHT JOIN score s2 on s1.s_id = s2.s_id GROUP BY s1.s_id order by avg(s2.s_score) desc

– 25、查询各科成绩前三名的记录
– 1.选出b表比a表成绩大的所有组
– 2.选出比当前id成绩大的 小于三个的

– 26、查询每门课程被选修的学生数
select c_id,count(*) from score group by c_id

– 27、查询出只有两门课程的全部学生的学号和姓名
select sc.s_id,stu.s_name from score sc,student stu
where sc.s_id=stu.s_id group by sc.s_id having count(*)=2

– 28、查询男生、女生人数
select stu.s_sex,count(*) from score sc,student stu
where sc.s_id=stu.s_id group by stu.s_sex

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

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

select s1.s_name,count(s1.s_name) from student s1,student s2 where s1.s_name=s2.s_name and s1.s_id!=s2.s_id group by s1.s_name

– 31、查询1990年出生的学生名单
select * from student where YEAR(student.s_birth)=1990

– 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,avg(s_score) from score group by c_id order by avg(s_score) desc,c_id asc

– 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s1.s_id,s1.s_name,avg(s2.s_score) from student s1 RIGHT JOIN score s2 on s1.s_id = s2.s_id GROUP BY s1.s_id,s1.s_name having avg(s2.s_score)>85

– 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.s_name,s_score from course co,score sc,student s where co.c_id = sc.c_id and sc.s_id = s.s_id and co.c_name=‘数学’ and s_score>=60

– 35、查询所有学生的课程及分数情况；
select student.s_name,score.C_Id,score.s_score from Student left join score on student.S_Id=score.S_Id

– 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select b.s_name,c.c_name,a.s_score from score a,student b,course c where a.s_id=b.s_id and a.c_id=c.c_id and s_score>70

– 37、查询不及格的课程
select distinct c_id from score where s_score<60

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

select a.s_id,s_name from score a,student b where a.s_id = b.s_id and c_id=‘01’ and s_score>=80

– 39、求每门课程的学生人数
select c_id,count(*) from score group by c_id

– 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

    -- 查询老师id   

    -- 查询最高分（可能有相同分数）

    -- 查询信息

select student.*,score.s_score
from student ,course ,teacher ,score
where course.C_Id=score.C_Id
and course.T_Id=teacher.T_Id
and teacher.T_name=‘张三’
and student.S_Id =score.S_Id
LIMIT 1

– 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from score t1
where exists
(select * from score t2 where t1.s_id=t2.s_id and t1.c_id!=t2.c_id and t1.s_score =t2.s_score )

– 42、查询每门功成绩最好的前两名
select * from score s1 where (select count(*) from score s2 where s1.c_id=s2.c_id and s2.s_score>s1.s_score)<2
ORDER BY s1.c_id asc

– 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id,count() from score group by c_id HAVING count()>5 order by count(*) desc,c_id asc

– 44、查询至少选修两门课程的学生学号
select a.s_id from student a,score b where a.s_id=b.s_id group by a.s_id having count(*)>=2

– 45、查询选修了全部课程的学生信息
select a.s_id,a.s_name from student a,score b where a.s_id=b.s_id group by a.s_id,a.s_name having count()=(select count() from course)

– 46、查询各学生的年龄
select student.s_id as 学生编号,student.s_name as 学生姓名,TIMESTAMPDIFF(YEAR,student.s_birth,CURDATE()) as 学生年龄
from student

– 47、查询本周过生日的学生
select * from student where YEARWEEK(s_birth)=YEARWEEK(CURDATE())

– 48、查询下周过生日的学生
select * from student
where YEARWEEK(s_birth)=CONCAT(YEAR(CURDATE()),week(CURDATE())+1)

– 49、查询本月过生日的学生
select * from student
where EXTRACT(YEAR_MONTH FROM s_birth)=EXTRACT(YEAR_MONTH FROM CURDATE())

– 50、查询下月过生日的学生
select *
from student
where EXTRACT(YEAR_MONTH FROM s_birth)=EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))

– 51,按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select student.s_id as 学生编号,student.s_name as 学生姓名,TIMESTAMPDIFF(YEAR,student.s_birth,CURDATE()) as 学生年龄
from student

小码鑫
关注
专栏目录
数据库经典50道题
灰哀之雪的博客
08-20 6649
数据库经典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 DEFAULT '', PRIMARY KEY(`s_id`) ); -- 课程表 CREATE TABLE `Course`( `c_
Oracle数据库DBA面试题50道及答案.pdf
03-03
Oracle数据库DBA（Database Administrator）是负责Oracle数据库系统运行管理的专业技术人员。在准备面试Oracle数据库管理员岗位时，掌握相关的技术知识和处理问题的能力是非常重要的。以下是针对给定的Oracle数据库...
50道SQL题答案
02-05
最全50道SQL题答案 最全 最齐 oracle 50道
msql语句练习题
良丶良良良良良良良
01-05 295
习题来源于网络 表名和字段 学生表 Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别 课程表 Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号 教师表 Teacher(t_id,t_name) --教师编号,教师姓名 成绩表 Score(s_id,c_id,s_score) --学生编号,...
50道sql
weixin_44044168的博客
05-15 348
sql题目所在链接 第一题 select * from Student right join (select t1.sid,class1,class2 from (select sid,score as class1 from sc where cid ='01') t1, (select sid,score as class2 from sc where cid ='02') t2 ...
数据库（MYSQL-50道练习题10）
31画的博客
05-29 877
数据库（MYSQL-50道练习题37）
31画的博客
06-04 144
50道数据库查询语句题目及答案
11-26
适合新手练习，熟悉SQL语句。 不会不要紧，依葫芦画瓢，慢慢地也就会了。
oracle 数据库 50道经典例题
05-14
oracle 数据库 50道经典例题
MySQL数据库练习题(50道).pdf
04-25
MySQL数据库练习题50道.pdf 本资源为MySQL数据库练习题，共50道题，涵盖数据库设计、数据表创建、数据插入、查询等多方面的知识点。下面是对该资源的详细知识点总结： 一、数据库设计 * 数据库设计是指根据具体的...
数据库（MYSQL-50道练习题36）
31画的博客
06-04 203
mySQL 50个查询系列
weixin_30647065的博客
12-26 254
http://bubufx.com/detail-1749088.html http://www.jb51.net/article/67932.htm Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表问题： 1、查询“001”课程比“002...
mysql练习题
YiRan_Zhao的博客
02-26 1066
Mysql Sql 语句练习题 （50道） MySql 语句练习50题 表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名...
数据库考试笔记
qq_51824841的博客
04-28 354
数据库笔记
SQL数据库面试题以及答案
kowloon的博客
04-09 1209
SQL数据库面试题以及答案 Student(Sid,Sname,Sage,Ssex)学生表 Sid：学号 Sname：学生姓名 Sage：学生年龄 Ssex：学生性别 Course(Cid,Cname,T#)课程表 Cid：课程编号 Cname：课程名称 Tid：教师编号 SC(Sid,Cid,score)成绩表 Sid：学号 Cid：课程编号 score：成绩 Teacher(Tid,Tna...
面试(七)
b8288060的博客
08-28 777
一、基础题 1 下面代码执行完后输出值是（） 　　Int count = 1； 　　for（int i = 1；i<=5; i++）count +=i; 　　System.out.println(count); 　　A 14 B 16 C 9 D 1 2 下面程序输出结果是（） 　　String s1 = “abc”,s2 = “abc”; 　　If(s1 =...
SQL语法
qq_41644412的博客
06-10 612
假设现在有三张表: student 表含有 student_id（主键）, name, age, sex.。 score表 student_id（主键）, course_id, score。 course 表 course_id（主键）, name。 在同一个Sql语句中，where> group by > having 出...
数据库表--增删改查的操作实例
weixin_34273046的博客
02-17 483
一、设置数据库 包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。 # 创建数据库 create database if not exists z_stu; #创建学生表（Student）...
数据库高级题
学习之路，创业之光
06-27 1100
练习题第一题、如下图所示：有学生表student（学号，姓名，出生日期，性别），课程表course（C_id表示课程号，c_name表示课程名字t_id表示老师编号），成绩表score（学号，课程号，成绩）： 使用SQL语句为完成以下操作：1.用函数实现，查询"马"姓老师的数量select count(*)from studentwhere s_name like ‘马%’SELECT COUNT(*) FROM TEACHER WHERE TNAME LIKE '马%';select sc.s_id
mysql数据库练习题50道
最新发布
09-13
很抱歉，但我无法直接提供整个MySQL数据库练习题的答案。这是因为练习题的内容需要根据具体需求和数据结构来设计，并且需要根据具体的SQL知识来编写查询语句。然而，我可以为您提供一些关于MySQL数据库练习题的一般...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值