SQL_50题

Wesley13
• 阅读 336

--1、查询“1001”课程比“1002”课程成绩高的所有学生的学号;
select a.S# from
(select S#,score from sc where C#=1001) a,
(select S#,score from sc where C# = 1002) b
where a.score>b.score and a.s#=b.s#;

--2、查询平均成绩大于60分的同学的学号和平均成绩
select S# as 学号,avg(score) as 平均成绩 from sc group by S# having avg(score) > 60;

--3、查询所有同学的学号、姓名、选课数、总成绩;
select sc.S# as 学号,Sname as 姓名,count(sc.c#) as 选棵数,sum(score) as 总成绩
from sc,student s where sc.s# = s.s# group by sc.S#,Sname;

--4、查询姓“李”的老师的个数;
select count(*) from teacher where Tname like '张%';

--5、查询没学过“叶平”老师课的同学的学号、姓名;
select S# as 学号,Sname as 姓名 from student where S# not in(
select S# from sc,course c,teacher t where sc.c#=c.c# and c.t#=t.t# and t.tname = '叶平');

--6、查询学过“1001”并且也学过编号“1002”课程的同学的学号、姓名;
select sc.S#,Sname from sc,student s where sc.s# = s.s# and sc.C# = 1001 and
exists(select * from sc sc2 where sc.s# = sc2.s# and sc2.c#=1002)
--注:先执行exists语句,如果查询到结果,返回ture并执行前面的语句;
-- 如果没有查询到结果,返回false,前面的查询不在执行

--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select S#,Sname from student where S# in (
select sc.S# from sc,course c,teacher t where sc.c#=c.c# and c.t#=t.t#
and Tname = '叶平' group by S# having count(sc.C#) = (
select count(C#) from course c,teacher t where c.t#=t.t#
and Tname = '叶平'))

--8、查询课程编号“1001”的成绩比课程编号“1002”课程低的所有同学的学号、姓名;
select S#,Sname from student where S# in (
select a.S# from (select S#,score from sc where C#=1001) a,
(select S#,score from sc where C#=1002) b where a.score < b.score and a.s#=b.s#);

--9、查询所有课程成绩小于60分的同学的学号、姓名;
select S#,Sname from student where S# not in (
select S# from sc where score > 60 );

--10、查询没有学全所有课的同学的学号、姓名;
select sc.s#,Sname from sc,student s where sc.s#=s.s# group by sc.S#,Sname
having count(sc.C#) < (select count(*) from course );

--11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
select sc.S#,Sname from sc,student s
where sc.s#=s.s# and sc.c# in (select C# from sc where s#=1)
group by sc.S#,Sname;

--12、查询至少学过学号为“1”同学所有一门课的其他同学学号和姓名;
select distinct S# from sc where C# in(
select C# from sc where S#=1)

--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
update sc
set score =
(select avg(score)
from sc
where c# =
(select c#
from course
where t# = (select t# from teacher where tname = '叶平')))
where c# = (select c#
from course
where t# = (select t# from teacher where tname = '叶平'));

-- (14)查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;

select student.S#, sname
from student
join sc
on student.s# = sc.s#
where C# in (select C# from SC where S# = '1')
group by student.S#, sname
having count(*) = (select count(*) from SC where S# = '1');
--15、删除学习“叶平”老师课的SC表记录;
delete from sc
where c# = (select c#
from course, teacher
where course.t# = teacher.t#
and tname = '叶平')

--(16)向SC表中插入一些记录,这些记录要求符合以下条件:
select * from sc;
--①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩;
insert into sc select s.S#,1002,(select avg(score) from sc where C#=1002)
from Student s where S# not in(select S# from sc where C#=1002)

-- (17)按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,
--按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;
select t.S#,
(select sc.score from sc where sc.s#=t.s# and sc.c#=1001) as 语文,
(select sc.score from sc where sc.s#=t.s# and sc.c#=1002) as 数学,
(select sc.score from sc where sc.s#=t.s# and sc.c#=1003) as 英语,
count(t.c#) as 课程数,
avg(t.score) as 平均分
from sc t group by t.s# order by avg(t.score)

--18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
select C#,max(score),min(score) from sc group by C#

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

select C#,avg(score),sum(case when score>=80 then 1 else 0 end)/count(*)*100 || '%' as 及格率
from sc group by C# order by avg(score) asc;

----20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):
-- 数学(001),马克语文思(002),英语 (003),物理(004)
select avg(a.score) as 语文平均成绩,
sum(case when sc.c#=1001 and sc.score>=60 then 1 else 0 end)/sum(case when sc.c#=1001 then 1 else 0 end)*100
|| '%' as 语文及格率,
avg(a.score) as 数学平均成绩,
sum(case when sc.c#=1002 and sc.score>=60 then 1 else 0 end)/sum(case when sc.c#=1002 then 1 else 0 end)*100
|| '%' as 数学及格率,
avg(a.score) as 英语平均成绩,
sum(case when sc.c#=1002 and sc.score>=60 then 1 else 0 end)/sum(case when sc.c#=1003 then 1 else 0 end)*100
|| '%' as 英语及格率
from
(select score from sc where c#=1001) a,
(select score from sc where c#=1002) b,
(select score from sc where c#=1003) c,sc

--21、查询不同老师所教不同课程平均分从高到低显示
select sc.c#, course.cname, teacher.tname, avg(score)
from teacher, sc, course
where teacher.t# = course.t#
and course.c# = sc.c#
group by sc.c#, course.cname, teacher.tname
order by avg(score) desc

--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select sc.c# as 课程号,Cname as 课程名,
sum(case when score between 85 and 100 then 1 else 0 end) as "[100-85]",
sum(case when score between 70 and 85 then 1 else 0 end) as "[85-70]",
sum(case when score between 60 and 70 then 1 else 0 end) as "[70-60]",
sum(case when score < 60 then 1 else 0 end) as "[ <60] "

from sc,course c where sc.c#=c.c# group by sc.c#,Cname

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

select C#,S#,score from
(select S#,C#,score,rank() over(partition by C# order by score desc) rn from sc)
where rn<=3 order by C#,S#;

--26、查询每门课程被选修的学生数
select C#,count(s#) from sc group by C#;

--27、查询出只选修了一门课程的全部学生的学号和姓名
select sc.S#,Sname from student s,sc where sc.s#=s.s# group by sc.s#,Sname having count(sc.c#)=1

--28、查询男生、女生人数
select distinct
(select count(1) from student where ssex='m') as 男生人数,
(select count(1) from student where ssex='w') as 女生人数
from student group by student.ssex;

--29、查询姓“张”的学生名单
select sname from student where sname like '张%'

--30、查询同名学生名单,并统计同名人数
select Sname,count(*) from student group by Sname having count(*) > 1;

--31、1999年出生的学生名单

--select to_number(Sage) from Student; --将varchar2转为number
--select to_number(to_char(sysdate,'yyyy')) from dual;

select S#,Sname from Student where Sage=(
select to_number(to_char(sysdate,'yyyy'))-1999 from dual
)

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

select C#, avg(score) from sc group by C# order by avg(score) ,C# desc

--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select sc.S#,Sname,avg(score) from sc,student s where sc.s#=s.s# having avg(score)>85 group by sc.S#,Sname

--34、查询课程名称为“数学”,且分数低于80的学生姓名和分数
select s.Sname,score from Student s,sc,course c where
s.s#=sc.s# and sc.c#=c.c# and c.cname='数学' and sc.score<80;

-- 35、查询所有学生的选课情况;??????????
select s.S#,s.Sname,c.Cname from Student s,sc,course c where s.s#=sc.s#
and sc.c#=c.c# group by s.S#,s.Sname,c.Cname order by s.S# ;

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
select Sname,c.Cname,score from Student s,course c,sc where
s.s#=sc.s# and sc.c#=c.c# and sc.score>70 order by s.s#;

-- 37、查询不及格的课程,并按课程号从大到小排列
select c# from sc where score<60 order by C# desc;

--38、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
select sc.s#,sc.c#,Sname from sc, student s where sc.s#=s.s# and C#=1003 and score>80;

--39、求选了课程的学生人数
select count(distinct S#) from sc

--40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
select Sname,score from sc,student s,course c,teacher t where
sc.s#=s.s# and c.c#=sc.c# and c.t#=t.t# and t.tname='张老师'
and score=(select max(score) from sc sc2 where sc2.s#=s.s#)

--41、查询各个课程及相应的选修人数
select C#,count(S#) from sc group by C# order by C#;

--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct a.S#,a.C#,b.score from sc a,sc b where a.score=b.score and a.c#<>b.c#;

--43、查询每门功成绩最好的前两名
select * from (select S#,C#,score,row_number() over(partition by C# order by score desc) rn
from sc) where rn<=2;

--44、统计每门课程的学生选修人数(超过人的课程才统计)。
--要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列
select C# as 课程号, count(S#) as 选修人数 from sc group by C#
having count(S#)>4 order by count(S#) desc,C# asc;

--45、检索至少选修两门课程的学生学号
select S#, count(C#) from sc group by S# having count(C#)>=2;

--46、查询全部学生都选修的课程的课程号和课程名
select sc.C#,c.Cname from course c,sc where c.c#=sc.c# group by sc.c#,c.Cname
having count(sc.c#)=(select count(*) from Student)

--45、检索至少选修两门课程的学生学号

select sc.C#,c.Cname from SC sc,Course c
where sc.C#=c.C#
group by sc.C#,c.Cname
having COUNT(sc.S#)=(select COUNT(distinct s.S#) from Student s)

--47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select S#,Sname from student where S# not in(
select S# from SC where C# in(
select C# from course where T#=(select T# from teacher where Tname='张叶平老师')))

--
select sname
from student
where s# not in (select s#
from sc, course, teacher
where sc.c# = course.c#
and teacher.t# = course.t#
and tname = '叶平')

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

select s#, avg(score)
from sc
where s# in (select sc2.s#
from sc sc2
where sc2.score < 79
group by sc2.s#
having count(sc2.c#) > 2)
group by s#

--49、检索“1004”课程分数大于60的学号,按分数降序排列的同学学号

select S#,score from sc where C#=1004 and score>60 order by score desc;

--50、删除“1”同学的“1001”课程的成绩
delete from sc where S#=1 and C#=1001

点赞
收藏
评论区
推荐文章
技术小男生 技术小男生
4个月前
linux环境jdk环境变量配置
1:编辑系统配置文件vi/etc/profile2:按字母键i进入编辑模式,在最底部添加内容:JAVAHOME/opt/jdk1.8.0152CLASSPATH.:$JAVAHOME/lib/dt.jar:$JAVAHOME/lib/tools.jarPATH$JAVAHOME/bin:$PATH3:生效配置
光头强的博客 光头强的博客
4个月前
Java面向对象试题
1、请创建一个Animal动物类,要求有方法eat()方法,方法输出一条语句“吃东西”。创建一个接口A,接口里有一个抽象方法fly()。创建一个Bird类继承Animal类并实现接口A里的方法输出一条有语句“鸟儿飞翔”,重写eat()方法输出一条语句“鸟儿吃虫”。在Test类中向上转型创建b对象,调用eat方法。然后向下转型调用eat()方
刚刚好 刚刚好
4个月前
css问题
1、在IOS中图片不显示(给图片加了圆角或者img没有父级)<div<imgsrc""/</divdiv{width:20px;height:20px;borderradius:20px;overflow:h
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
1年前
Java爬虫之JSoup使用教程
title:Java爬虫之JSoup使用教程date:201812248:00:000800update:201812248:00:000800author:mecover:https://imgblog.csdnimg.cn/20181224144920712(https://www.oschin
Stella981 Stella981
1年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Wesley13 Wesley13
1年前
Java日期时间API系列36
  十二时辰,古代劳动人民把一昼夜划分成十二个时段,每一个时段叫一个时辰。二十四小时和十二时辰对照表:时辰时间24时制子时深夜11:00凌晨01:0023:0001:00丑时上午01:00上午03:0001:0003:00寅时上午03:00上午0
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序selectfromtable_nameorderiddesc;2.按照指定(多个)字段排序selectfromtable_nameorderiddesc,statusdesc;3.按照指定字段和规则排序selec
Stella981 Stella981
1年前
Angular material mat
IconIconNamematiconcode_add\_comment_addcommenticon<maticonadd\_comment</maticon_attach\_file_attachfileicon<maticonattach\_file</maticon_attach\
Wesley13 Wesley13
1年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
helloworld_34035044 helloworld_34035044
6个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为