sql每日一练

QQ糖~~~~

已于 2023-06-24 22:01:30 修改

阅读量122

点赞数

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

于 2023-06-18 15:43:46 首次发布

本文链接：https://blog.csdn.net/weixin_42181641/article/details/131272281

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章展示了如何在数据库中创建表、插入数据以及执行复杂的SQL查询，包括查找未选特定课程的学生、按课程平均成绩和及格率排序、查询教师课程平均分、统计成绩分布、获取高分记录、计算选课人数、筛选单门课程学生、统计性别比例、查找同名同性学生和年龄筛选等操作。

摘要由CSDN通过智能技术生成

先建几个表：

create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;

1、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；

方法一：分两步
(1)查询出没有学过c002课程的学生学号,c002的课程号,c002的平均成绩，并用查询出来的结果集数据建个新表叫temp

create table temp as 
with t as
 (select sno, cno, avg(score) over(partition by cno order by cno) avg_score
    from sc
   where cno = 'c002')  
select t1.sno, t.cno, t.avg_score score
  from (select sno from sc where sc.sno not in (select sno from t)) t1
 cross join t
group by t1.sno, t.cno, t.avg_score;

(2)第二步：把新表temp的数据全部插入sc表中：

insert into sc_bak3 (sno,cno,score) select * from temp ;

这个方法没有把所有未选c002的学生学号查出来，只查出了考过试的学生中未选c002课程的学号

方法二：

通过两次表连接，取出student中没有学过c002课程的学生学号；
需要插入的课程号为c002，题目已经给出，所以不需要查询直接插入就可以了；
score成绩是课程号为c002的平均成绩；

insert into SC(sno,cno,score)
select a.sno,'c002', c.avg
  from STUDENT a
  left join (select SNO from SC where CNO = 'c002') b
    on a.SNO = b.SNO,
 (select avg(SC.SCORE) avg from SC where SC.CNO = 'c002') c
 where b.SNO is null;

方法三：和前两种方法类似，只不过在取c002平均成绩的时候直接在case when后面接了个求平均成绩的子查询语句
这里的round()函数是四舍五入,round(x,2)就是给数字x四舍五入，并保留两位小数

insert into sc(sno,cno,score)
select a.sno,'c002' cno,round(case when b.score is null then 
  (select avg(score) from sc where cno='c002' group by cno) end,2)score
   from student a
   left join (select sno, cno,score
                from sc
               where cno = 'c002') b
   on a.sno=b.sno
   where b.sno is null;

方法三中的case when同样也可以换成decode来写，得到的结果是一样：

insert into sc(sno,cno,score)
select a.sno,'c002' cno,round(decode(b.score,null,(select avg(score) from sc where cno='c002' group by cno),b.score),2) score
   from student a
   left join (select sno, cno,score 
                from sc
               where cno = 'c002') b
   on a.sno=b.sno
   where b.sno is null;

方法四、全部使用多表连接：

insert into SC(sno,cno,score)
select a.sno,'c002', c.avg
  from STUDENT a
  left join (select SNO from SC where CNO = 'c002') b
    on a.SNO = b.SNO,
 (select avg(SC.SCORE) avg from SC where SC.CNO = 'c002') c
 where b.SNO is null;

这个方法看起来就很简洁

2、按各科平均成绩从低到高和及格率的百分数从高到低顺序

 select cno,avg(score) avg_score,
                    round(sum(case when score>=60 then 1 else 0 end)/count(1),4)*100||'%' 及格率
   from sc
   group by cno
   order by avg_score,及格率 desc;

在oracle中，执行顺序from->join on->where->group by->having->select->order by，order by的执行顺序在select之后，所以可以直接使用avg_score和及格率排序

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

select t.tname,t.tno,c.cno,avg(sc.score) avg_score
from sc join course c
on sc.cno=c.cno
join teacher t
on c.tno=t.tno
group by t.tname,t.tno,c.cno
order by avg_score desc;

同样是因为order by的执行顺序是在select之后,所以可以直接order by给平均成绩排序

4、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select c.cno,c.cname,
 sum(case when sc.score <=100 and sc.score>=85 then 1 else 0 end) 优秀,
 sum(case when sc.score <=85 and sc.score>=70 then 1 else 0 end ) 及格,
 sum(case when sc.score <=70 and sc.score>=60 then 1 else 0 end) 良好,
 sum(case when sc.score <=60 then 1 else 0 end) 不及格
 from sc join course c
on sc.cno=c.cno
group by c.cno,c.cname;

case when后面也可以用sum，但是sum作为聚合函数只能累加数字类型的值，所以在用case when打标签时一般都使用1或者0

select c.cno,c.cname,
 count(case when sc.score <=100 and sc.score>=85 then '优秀' end) 优秀,
 count(case when sc.score <=85 and sc.score>=70 then '及格' end ) 及格,
 count(case when sc.score <=70 and sc.score>=60 then '良好' else '0' end) 良好,
 sum(case when sc.score <=60 then 1 else 0 end) 不及格
 from sc join course c
on sc.cno=c.cno
group by c.cno,c.cname;

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

select sno,cno,score
from (select sno,cno,score,
    row_number()over(partition by cno order by score desc) rn
    from sc)
where rn<=3;

由于题目说不考虑成绩并列的情况，所以这里使用了开窗函row_number()over()，表示自然排序
举个简单的例子：

select sc.*,row_number() over(order by score desc)
from sc
where cno='c002';

执行结果如下：

在这里插入图片描述
从上面图片可以看到,s008和s010的学生的成绩是一样的，但是他俩的名次并不一样，这就是row_number()函数的用处

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

select c.cno,count(sc.sno)
from course c left join sc 
on c.cno=sc.cno
group by c.cno

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

select sno,sname from student
where sno in (select sno
  from (select sno,
               cno,
               count(sno) over(partition by cno) one_course_student
          from sc)
  where one_course_student=1);

8、查询男生、女生人数

select ssex,count(*)
from student
group by ssex;

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

方法一：不分组，直接使用count() over()分析函数统计数量

select s1.*,count(s1.sname) over()
from student s1,student s2
where s1.sno<>s2.sno
and s1.sname=s2.sname
and s1.ssex=s2.ssex;

方法二：通过group by分组做

select a.sname, count(a.sname)
  from student a
  join student b
    on a.sname = b.sname
   and a.ssex = b.ssex
   and a.sno <> b.sno
 group by a.sname;

10、1999年出生以后的学生信息

select sno,sname,sage,ssex
from student
where sage>(select to_number(to_char(sysdate,'yyyy'))-1999 from dual);

这里用了两次转换函数，第一次用to_char(sysdate,‘yyyy’)，获取当前日期且只取出当前日期的年份，然后将日期转换成字符类型，最后使用to_number函数把字符类型的年份转换成数字类型的年份，这样就可以直接和1999进行加减了，相减得到的结果就是年龄，为什么要使用两次转换函数，先to_char再to_number而不直接使用to_number(sysdate,‘yyyy’)呢，因为to_number(sysdate,‘yyyy’)这个用法在oracle中不支持，其他sql语言不清楚哦

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

select sno,cno,score,asal
from (select sno,cno,score,round(avg(score)over(partition by cno),2) asal
from sc)
order by asal,cno desc;

QQ糖~~~~

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql每日一练

在oracle中，执行顺序from->join on->where->group by->having->select->order by，order by的执行顺序在select之后，所以可以直接使用avg_score和及格率排序。(1)查询出没有学过c002课程的学生学号,c002的课程号,c002的平均成绩，并用查询出来的结果集数据建个新表叫temp。从上面图片可以看到,s008和s010的学生的成绩是一样的，但是他俩的名次并不一样，这就是row_number()函数的用处。这个方法看起来就很简洁。
复制链接

扫一扫