学习大数据DAY08 SQL基础语法8，习题巩固

目录

作业

一道比较困难的题

---

习题练习日

今天老师不讲新内容，就刷题，讲题。

作业

--1、检索“c001”课程分数小于80，按分数降序排列的同学学号

select sno from sc where cno='c001' and score<80 order by score desc;



--2、查询各个课程及相应的选修人数

select cno,count(1) from sc

group by cno;



--3、按照不同老师编号分类显示课程列表，课程之间用逗号隔开

select tno,listagg(cname,',')within group(order by tno) 

from course group by tno;



--4、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno as 课程ID,max(score) as 最高分,min(score) as 最低分 from sc

group by cno;

--5、查看成绩表信息，多加一列标注是否及格

select sc.*,case

when score>=60 then '及格'

  else '不及格'

    end as 是否及格 from sc;

    

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

select * from

(

select dense_rank()over(partition by cno order by score desc ) 排名,sc.*

from sc) where 排名<=3;



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

select sc.sno,sname

from sc left join student

on sc.sno=student.sno

where sc.sno in

  (

select sno

from sc

group by sno having count(1)=1

) ;



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

select cno,avg(score) from sc

group by cno

order by avg(score) asc,cno desc;



--9、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select 学生平均成绩.sno,sname,Y from (

(

select sno,avg(score) Y

from  sc group by sno) 学生平均成绩

left join student

 on 学生平均成绩.sno=student.sno)

where Y>85 ;

--10、统计各科成绩,各分数段人数:分数段为[100-85] a,[84-70] b,[69-60] c,[ <60] d

select cno,

nvl(case when score between 85 and 100 then count(1)over(partition by cno) end,0) A,

   nvl(case when score between 70 and 84 then count(1)over(partition by cno) end,0) B,

    nvl(case when score between 60 and 69 then count(1)over(partition by cno) end,0) C,

      nvl(case when score<60 then count(1)over(partition by cno) end,0) D

from sc;



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

select sname,score from student

left join sc on student.sno=sc.sno

where score<60 and cno in

(

select cno from course

where cname='Oracle'

);



--12、查询所有学生的选课情况，选了多少门课程；

select sname,nvl2(选课数,to_char(选课数),'没选课') as 选课情况 from student

left join 

(

select sno,count(1) as 选课数

from sc

group by sno

) scc on student.sno=scc.sno;



/*13、统计每门课程的学生选修人数（超过1人的课程才统计）。要求输出课程号和选修人

数，查询结果按人数降序排列，若人数相同，按课程号升序排列*/

select cno,count(1) from sc

group by cno order by count(1) desc;



/*14、查询全部学生都选修的课程的课程号和课程名；执行上机练习14的第9题测试；*/

select cno,cname from course

where cno in

(

select cno from sc

group by cno having count(1)=

(

select count(1) from student

)

);



--15、查询没有学全所有课的同学的学号、姓名；

select sno,sname from student

where sno in

(

select sno from sc

group by sno having count(1) not in

(

select count(1) from course

)

);



--16、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select C1.sno from (

(select * from sc where cno='c001') C1

left join 

(select * from sc where cno='c002') C2

on C1.sno=C2.sno

) where C1.score>C2.score;


--17、查询没学过“谌燕”老师课的同学的学号、姓名；

select sno,sname from student

where sno not in

(

select sno from sc where cno in

(

select cno from course where tno='t002'

)

);



--18、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

select sno,sname from student

where sno in

(

select sno from sc

where cno='c002' and sno in

(

select sno from sc

where cno='c001'

)

);

--19、查询出每个学科排名第一名的学生姓名列表,包括课程编号，学生姓名，学生成绩

select cno,sname,score from sc

left join student on sc.sno=student.sno

where (cno,score) in

(

select cno,max(score) from sc

group by cno

);



--20、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select sname,score from sc

left join student on sc.sno=student.sno

where score in

(

select max(score) from sc

where cno in

(

select cno from course

where tno='t002'

)

);



--21、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

select sno,sname from student

where sno in

(

select sno from 

(

select sno,listagg(cno,',')within group(order by cno) from sc

group by sno having (select listagg(cno,',')within group(order by cno) from course where tno='t002')

in listagg(cno,',')within group(order by cno)

)

);

--22、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名

select sno,sname from student

where sno in

(

select sno from sc

where cno in

(

select cno from sc

where sno='s001'

)

) and sno!='s001';



--23、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名

select sno,sname from student

where sno in

(

 select sno from

 (

 select sno,listagg(cno,',')within group(order by cno) as 课程 from sc group by sno

 ) where 课程=

 (

 select listagg(cno,',')within group(order by cno) from sc where sno='s001'

 )

) and sno!='s001';

一道比较困难的题

老师推荐的一道比较困难的题，不是必做，本人完成作业后闲暇之余钻研的代码，这里分享一下。

复杂语句-2005(20分)

数据来源：题目所需文件 CARD_ID(身份证)、TNAME(姓名) 、SCORES(分数)、OPENID(设备ID) 、INSERTTIME(提交成绩时间) 题目要求 分数>=60分视为考试通过，有一次通过即为考核通过 OPENID重复的两个人视为一个人替另外一个人代考，

设定第一次在某设备上提交成绩的人员为A，后续在该设备提交的成绩均为A帮助其他人员代考，成绩应为A的成绩，查询有哪几个人员帮助他人代考，这些人员的考核最高分是多少？结果列输出姓名、考核最高分 得分点（完成得满分，未完成得0分）： 逻辑&结果正确 20分

--查看2005表

select * from "2005";





select tname,最高分 from

(

select tname,open_id from "2005" where card_id in --对应的名字

(

select card_id from "2005" where open_id in --交的最早的人的open_id对应的card_id

(

select open_id--存在多次提交但名字不同的open_id

from "2005"

group by open_id

having count(distinct tname) > 1

) and insert_time in

(

select min(insert_time) --每个open_id提交最快时间

from "2005"

group by open_id

)

) ) people

left join 

(

select open_id,max(scores) as 最高分 from "2005" where open_id in --对应open_id的最高分

(

select open_id from "2005" where card_id in --对应的open_id

(

select card_id from "2005" where open_id in --交的最早的人的open_id对应的card_id

(

select open_id--存在多次提交但名字不同的open_id

from "2005"

group by open_id

having count(distinct tname) > 1

) and insert_time in

(

select min(insert_time) --每个open_id提交最快时间

from "2005"

group by open_id

)

) group by open_id

) group by open_id

) grades on people.open_id=grades.open_id;