SQL 练习

最新推荐文章于 2024-04-04 12:35:24 发布
最新推荐文章于 2024-04-04 12:35:24 发布
阅读量312 收藏
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bb524027223/article/details/117934739
版权

1.一条SQL 语句 查询出每门课都大于80 分的学生姓名
在这里插入图片描述

select name from score group by name having min(score)>80;

在这里插入图片描述
2.#删除除了自动编号不同, 其他都相同的学生冗余信息
在这里插入图片描述

delete from studentbi where autonum not in (select min(autonum)  from studentbi group by studynum,name,coursenum,course,score);

在这里插入图片描述
3. 一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合

 select a.name,b.name from team a,team b where a.name<b.name;

在这里插入图片描述
4. 请用SQL 语句实现：从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。
请注意：TestDB 中有很多科目，都有1~12月份的发生额。
AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。
数据库名：JcyAudit ，数据集：Select * from TestDB

select   
from TestDB a,
     (select Occmonth ,max(DebitOccur) as Debit101Occur
     from TestDB 
     where AccId='101'
     group by Occmonth) b
where a.Occmonth=b.Occmonth and  a.DebitOccur>b.Debit101Occur
  1. 怎么把这样一个数据表
    year month amount
    1991 1 1.1
    1991 2 1.2
    1991 3 1.3
    1991 4 1.4
    1992 1 2.1
    1992 2 2.2
    1992 3 2.3
    1992 4 2.4
    查成这样一个结果？
    year m1 m2 m3 m4
    1991 1.1 1.2 1.3 1.4
    1992 2.1 2.2 2.3 2.4
select year,
        (select amount from table  m where month=1 and m.year=table.year) as m1,
        (select amount from table  m where month=1 and m.year=table.year) as m2,
        (select amount from table  m where month=1 and m.year=table.year) as m3,
        (select amount from table  m where month=1 and m.year=table.year) as m4,
 from table
 group by year
  1. 有表A，结构如下：
    p_ID p_Num s_id
    1 10 01
    1 12 02
    2 8 01
    3 11 01
    3 8 03
    其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。
    请用SQL语句实现将上表中的数据合并，合并后的数据为：
    p_ID s1_id s2_id s3_id
    1 10 12 0
    2 8 0 0
    3 11 0 8
    其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。
select p_ID,
     sum(case when s_id =1 then p_Num else 0 end) as s1_id,
     sum(case when s_id =2 then p_Num else 0 end) as s2_id,
     sum(case when s_id =3 then p_Num else 0 end) as s3_id,
from A
group by p_ID

6.学生练习
学生表 Student

create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
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' , '女');

成绩表 SC

create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

课程表 Course

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

四张表之间的关系
在这里插入图片描述
以下题目的顺序和原文相对应）

  1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select s.*, a.score as score_01,b.score as score_02 
from Student s,
   (select Sid,score from SC where Cid='01') a,
   (select Sid,score from SC where Cid='02') b
 
where a.Sid=b.Sid and a.score>b.score and a.Sid = s.Sid;

在这里插入图片描述

  1. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.Sid,s.Sname,avg(sc.score) as sav_score 
from student s,SC sc 
where s.Sid = sc.Sid 
group by s.Sid 
having sav_score>=60;

在这里插入图片描述
3. 查询在 SC 表存在成绩的学生信息

select * from student where Sid in (select Sid from SC where score is not null);

在这里插入图片描述
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
这道题得用到left join或者right join，不能用where连接，因为题目说了要求有显示为null的，where是inner join，不会出现null，在这道题里会查不出第08号学生。

select s.Sid,s.Sname,count(sc.Cid) as course_sum,sum(sc.score) as score_sum 
from Student s left join SC sc  
on s.Sid=sc.Sid 
group by Sid

在这里插入图片描述
4.1 查有成绩的学生信息

select s.Sid,s.Sname,sum(sc.score),
    sum(case when cid = 01 then score else null end) as score_01,
    sum(case when cid = 02 then score else null end) as score_02,
    sum(case when cid = 03 then score else null end) as score_03 
from Student s,SC sc  
where s.Sid = sc.Sid 
group by Sid;

在这里插入图片描述
5. 查询「李」姓老师的数量

select count(Tname) from teacher where Tname like '李%';

在这里插入图片描述
6. 查询学过「张三」老师授课的同学的信息

select s.* ,t.Tname 
from student s,SC sc,course c,teacher t 
where s.Sid=sc.Sid and sc.Cid=c.Cid and c.Tid = t.Tid and t.Tname='张三';

在这里插入图片描述
利用子查询

select * from student 
where Sid  
  in(select Sid from SC where Cid  
  in(select Cid from course where Tid = (select Tid from teacher where Tname='张三')));
  1. 查询没有学全所有课程的同学的信息
select student.* from student,SC where student.Sid=SC.Sid group by Sid having count(SC.Cid)<3;
select * from student where Sid in ( select Sid from SC group by Sid having count(Cid)<3);

在这里插入图片描述
8. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select * from student where Sid  
  in(select Sid from SC where Cid  
  in(select Cid from SC where Sid='01') and Sid !='01'  
  group by Sid having count(Cid)>=3);

在这里插入图片描述

  1. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student 
   where Sid in (select Sid from SC where Cid in(select Cid from SC where SC.sid='01'))

在这里插入图片描述
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

一般涉及到"任意"的都会用到not in这样的取反的结构：

select student.Sname from student 
where Sid not in(select sid from SC where Cid in(select Cid from course,teacher where course.Tid=teacher.Tid and teacher.Tname='张三'));
select Sname from student where Sid not 
in(select s.Sid  from student s ,SC sc,course c,teacher t 
   where s.Sid=sc.Sid and sc.Cid= C.Cid and c.Tid=t.Tid and t.Tname='张三');

在这里插入图片描述
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.sid,s.sname,avg(sc.score)       
from student s,(select sid,score from sc where score<60) as sc_60,SC 
where s.sid = sc_60.sid  and s.sid=sc.sid 
group by s.sid    
having count(sc_60.score) >= 2;


select s.sid,s.sname,avg(score) 
from student s,sc 
where s.sid=sc.sid and score<60 
group by sid 
having count(score)>=2;

在这里插入图片描述
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select * from student s,sc 
where s.sid=sc.sid and sc.cid ='01' and sc.score<60 
order by sc.score desc;

在这里插入图片描述
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s.sid,s.sname,avg(sc.score) as avgscore,
  sum(case when cid ='01' then score else null end) as s_score01,
  sum(case when cid ='02' then score else null end) as s_score02,
  sum(case when cid ='03' then score else null end) as s_score03 
from student s,sc 
where s.sid=sc.sid 
group by sid 
order by avgscore desc;

在这里插入图片描述
14. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

主要case和sum的用法

select c.cid,c.cname,count(*) as 课程人数,max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分,
    sum(case when score<60 then 1 else 0 end)/count(*) as 及格率,
    sum(case when score>=70 and score<80 then 1 else 0 end     )/count(*) as 中级率,
    sum(case when score>=80 and score<90 then 1 else 0 end )/count(*) as 优良率,
    sum(case when score>=90 then 1 else 0 end )/count(*) as 优秀率 
from sc,course c 
where sc.cid= c.cid 
group by cid 
order by count(*) desc,cid asc;

在这里插入图片描述
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select c.cname,c.cid ,
    sum(case when score>85 and score <=100 then 1 else 0 end)/count(*) as 100_85,
     sum(case when score>70 and score <=85 then 1 else 0 end)/count(*) as 85_70,
      sum(case when score>60 and score <=70 then 1 else 0 end)/count(*) as 70_60,
       sum(case when score>=0 and score <=60 then 1 else 0 end)/count(*) as 60_0 
from sc,course c 
where sc.cid=c.cid 
group by cid

在这里插入图片描述
18. 查询各科成绩前三名的记录
rank() over(patition by order by) 分组内进行排序

select * from (select *, rank() over(partition by cid order by score desc) as graderank from sc ) A  where A.graderank <=3;
  1. 查询出只选修两门课程的学生学号和姓名
select s.sid,s.sname,count(cid)
 from student s,sc where s.sid=sc.sid group by sid having count(cid)=2;

在这里插入图片描述
22. 查询名字中含有「风」字的学生信息

select * from student where like '%风%';

在这里插入图片描述
23. 查询 1990 年出生的学生名单

select * from student where year(sage)=1990;

在这里插入图片描述
24. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select s.*,max(score)   
from student s,sc,course c,teacher t where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid and t.tname='张三';


select student.*,maxscore.maxs  from student,(select max(sc.score) as maxs  
from student s,sc,course c,teacher t where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid and t.tname='张三') as maxscore,sc where student.sid=sc.sid and sc.score=maxscore.maxs;

在这里插入图片描述
34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select *
from (select sc.*, DENSE_RANK() OVER(order by score desc)  A 
      from sc
      where cid =(select cid from course where tid =(select tid from teacher where tname='张三') )) B 
where B.A=1;

在这里插入图片描述
35. 查询各学生的年龄，只按年份来算

select student.sname,year(now())-year(sage) as age from student;

在这里插入图片描述
36. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select sname, timestampdiff(year, sage, now()) as age from student
  1. 查询本周过生日的学生
select * from student where week(now()) = week(sage);
  1. 查询下周过生日的学生
select * from student where (week(now())+1) = week(sage)
  1. 查询本月过生日的学生
select * from student where month(now()) = month(sage)
  1. 查询下月过生日的学生
select * from student where (month(now())+1) = month(sage)

参考 https://blog.csdn.net/paul0127/article/details/82529216

pengbobcloud
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
sql练习工具
01-07
mysql数据库连接工具，亲测sql初学者练习最佳
经典SQL练习题
热门推荐
qaz13177_58_的专栏
05-10 26万+
今天在网上找了几道经典的SQL练习题做了一下，虽然都不难，但是对打基础是很有好处的，在明白的基础上可以进一步做分析，来研究一下各种解法的优劣，甚至进行简单的优化。。现在将题目和答案分享一下。我使用的是MYSQL 5.0，但是绝大部分都是标准SQL。表结构：CREATE TABLE STUDENT(SNO VARCHAR(3) NOT NULL, SNAME VARCHAR(4) NOT NULL,
SQL语句练习-入门篇，抖音四面被拒再战头条终获offer
最新发布
2401_84103875的博客
04-04 781
学好 Python 不论是就业还是做副业赚钱都不错，但要学会 Python 还是要有一个学习规划。最后大家分享一份全套的 Python 学习资料，给那些想学习 Python 的小伙伴们一点帮助！
SQL练习
DOONDO的技术窝
11-22 331
问题及描述： --1.学生表 Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 --2.课程表 Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号 --3.教师表 Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名 --...
经典50道SQL练习题
wbc199685的博客
09-15 2万+
前言 作为一个SQL小白，在一个月的努力后终于把这50道练习题做完了，分享一下我的答案给大家，说不定能给你提供一些思路。 我使用的是在线SQL，个人认为十分方便，使用的版本是“My SQL Server 2017”。 一、数据表 学生表 Student(SId,Sname,Sage,Ssex)–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 create table Student(SId varchar(10),Sname varchar(10),Sage dateti
sql练习
qq_41609957的博客
12-11 432
use master create database Test go -------------- use Test --学生表 if exists (select *from sysobjects where name='Students') drop table Students,Score go create table Students( stuId int primar...
经典sql练习
10-25
经典sql练习，链接给出题型： https://www.jobui.com/mianshiti/it/shujuku/5838/ 本意无需资源分数，但csdn变坏了，没有0分可以选，没有分数的，发qq邮箱索要1943680751
SQL经典练习题_sql练习_
10-03
一些可供练习的SQL题目以及案例，个人珍藏，好多是面试题》
sql练习语句
04-02
数据库查询语句
sql练习及实例
11-26
sql练习及实例，相关练习语句案例。时间编写的写法等。
mabites sql练习
06-21
Mabites SQL练习是一项注重实践和交互式学习的数据库学习计划。该计划旨在通过结合理论和实践的方式，教授SQL语言的基础和高级知识，帮助学习者掌握SQL查询、存储过程和触发器等关键技能。 在Mabites SQL练习中，...

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

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值