/*
Student(S#,Sname,Sage,Ssex)学生表
Course(C#,Cname,T#)课程表
SC(S#,C#,score)成绩表
Teacher(T#,Tname)教师表
*/
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select #
from SC s1,SC s2
where #=# and #='001'and #='002'and>;
--2、查询平均成绩大于60分的同学的学号和平均成绩；
select #,avg()
from SC s
groupby #
havingavg()>60;
--3、查询所有同学的学号、姓名 、选课数、总成绩；
select #,max(),count(distinct #),sum()
from Student d,SC s
where #=#
groupby #;
--4、查询姓“李”的老师的个数；
selectcount(0)
from Teacher t
where like'李%';
--5、查询没学过“叶平”老师课的同学的学号、姓名；
select #,
from Student d
wherenotexists(
selectdistinct(#)
from SC s,Teachert,Course c
where #=# and #=# and ='叶平'and #=#);
--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select #,
from Student d, SC s
where # = # and # ='001'andexists
(select # from SC s1 where # = # and # ='002');
--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select #,max()
from SC s,Studentd,Course c,Teachert
where #=# and #=# and #=# and ='叶平'
groupby #
havingcount(distinct #)=(
selectcount(0)from Coursec,Teachert where #=# and ='叶平');
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select #,
from SC s1,SC s2,Studentd
where #=# and #=# and #='002'and #='001'and<;
--9、查询所有课程成绩小于60分的同学的学号、姓名；
select #,max()
from Student d,SC s
where #=# and <60
groupby #
havingcount(0)=(selectcount(0)from SC s1 where #=#);
--10、查询没有学全所有课的同学的学号、姓名；
select #,max()
from Student d,SC s
where #=#
groupby #
havingcount(distinct #)<>(selectcount(0)from Course);
--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
selectdistinct #,
from Student d,SC s
where #=# and #<>'1001'andexists(
select # from SC s1 where #='1001'and #=# );
--12、查询至少学过学号为“1010”同学所有一门课的其他同学学号和姓名；
selectdistinct #,
from Student d,SC s
where #=# and #<>'1010'and # in(
select # from SC s1 where #='1010');
--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update SC s
set =
(selectavg()from SC s1 where # = #)
whereexists(select #
from Coursec, Teachert
where # = # and # = # and ='叶平');
--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select #,max()
from Studentd,SC s
where #=# and #<>'1002'andexists(
select c# from SC where s#='1002'and #=c#)
groupby #
havingcount(0)=(selectcount(0)from SC where s#='1002')
and count((select count(0) from SC wheres#=#))=(select count(0) from SC where s#='1002');
--15、删除学习“叶平”老师课的SC表记录；
delete SC s whereexists(select # from Coursec,Teachert
where #=# and #=# and ='叶平');
--16、向SC表中插入一些记录，这些记录要求符合以下条件：
-- 没有上过编号“003”课程的同学学号、002号课的平均成绩;
insertinto SC
(select #,'002',(selectround(avg(score),2)from SC where c#='002')
from Student d wherenotexists(selectdistinct # from SC s where #=# and #='003'));
--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，
-- 按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分;
select # as学生ID,
(select avg()from SC s1,Course c where #=# and #=# and='Java编程思想')as Java编程思想,
(select avg()from SC s1,Course c where #=# and #=# and='Struts2基础')as Struts2基础,
(select avg()from SC s1,Course c where #=# and #=# and='Spring基础')as Spring基础,
count(0)as有效课程数,round(avg(),2)as有效平均分
from SC s,Course c where #=# and in('Java编程思想','Struts2基础','Spring基础')
groupby #
orderbyround(avg(),2)desc;
--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select # as课程ID,max()as最高分,min()as最低分
from SC s
groupby #;
--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select # as课程ID,round(avg(),2)as平均分,
round(100*sum(casewhen >=60then1else0end)/count(),0)||'%'as及格率
from SC s
groupby #;
--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):
-- 企业管理（001），马克思（002），OO&UML（003），数据库（004）
selectround(sum(casewhen ='Java编程思想'then else0end)/sum(case when'Java编程思想'then1else0end),2)as Java编程思想,
round(100*sum(casewhen >=60and ='Java编程思想'then1else0end)/sum(casewhen ='Java编程思想'then1else0end),0)||'%'as及格率,
round(sum(casewhen ='Struts2基础'then else0end)/sum(case when'Struts2基础'then1else0end),2)as Struts2基础,
round(100*sum(casewhen >=60and ='Struts2基础'then1else0end)/sum(casewhen ='Struts2基础'then1else0end),0)||'%'as及格率
from SC s,Course c
where #=#;
--21、查询不同老师所教不同课程平均分从高到低显示
-- SELECT max(#) AS 教师ID,MAX() AS 教师姓名,
-- # AS 课程ＩＤ,MAX() AS 课程名称,AVG(Score) AS 平均成绩
select # as教师ID,
max()as教师名称,
as所教课程,
round(avg(),2)as平均分
from Teacher t, Coursec, SC s
where # = # and # = #
groupby #,
orderby #,round(avg(),2)desc;
--22、查询如下课程成绩第 3 名到第6 名的学生成绩单：
-- 企业管理（001），马克思（002），UML （003），数据库（004）
select #,,,,s.rank1 as名次
from Student d,Course c,
(select s#,c#,score,row_number()over(partitionby c# orderby score desc) rank1 from SC) s
where #=# and #=#
and in('Java编程思想','Struts2基础','Hibernate基础','Spring基础')
and(s.rank1 between3and6)
orderby #,s.rank1;
--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select #,max(),
sum(casewhen between85and100then1else0end)as分数85至100,
sum(casewhen between70and85then1else0end)as分数70至85,
sum(casewhen between60and70then1else0end)as分数60至70,
sum(casewhen between0and60then1else0end)as小于60
from SC s,Course c
where #=#
groupby #;
--24、查询学生平均成绩及其名次
select #,max(),round(avg(),2)as平均分,
dense_rank()over(orderbyavg()desc)as名次
from Student d,SC s
where #=#
groupby #;
--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select # as课程编号, as课程名称,
as学生名称,s.rank1 as名次, as成绩
from(select s#,c#,score,
row_number()over(partitionby c# orderby score desc) rank1
from sc ) s,Course c,Studentd
where #=# and #=# and rank1 <4
orderby #,s.rank1;
--26、查询每门课程被选修的学生数
select #,count(distinct #)as人数
from SC s
groupby #;
--27、查询出只选修了一门课程的全部学生的学号和姓名
select #,max()
from SC s,Studentd
where #=#
groupby #
havingcount(distinct #)=1;
--28、查询男生、女生人数
selectsum(case when'男'then1else0end)as男生,
sum(case when'女'then1else0end)as女生
from Student d;
--29、查询姓“张”的学生名单
select*
from Student d
where like'张%';
--30、查询同名同性学生名单，并统计同名人数
select ,,count(0)
from Student d
groupby ,
havingcount(0)>1;
--31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
select*
from Student d
where to_char(,'yyyy')='1981';
--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select #,round(avg(),2)as平均分
from SC s
groupby #
orderbyavg(),# desc;
--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select #,max(),round(avg(),2)as平均分
from SC s,Studentd
where #=#
groupby #
havingavg()>85;
--34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
select ,
from Student d,SC s,Course c
where #=# and #=# and ='Java编程思想'and <60;
--35、查询所有学生的选课情况；
select #,max(),count(distinct #)as选课数
from Student d leftjoin SC s on(#=#)
groupby #;
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
selectmax(),max(),
from Student d,SC s,Course c
where #=# and #=# and >70
groupby #,
havingcount(0)=(selectcount(#)from SC s1 where #=#);
--37、查询不及格的课程，并按课程号从大到小排列
select #,,
from SC s,Course c
where #=# and <60
orderby # desc;
--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select #,
from Student d,SC s
where #=# and #='003'and >80;
--39、求选了课程的学生人数
selectcount(distinct #)as选课的人数
from SC s;
--40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select sname,score
from(select ,,row_number()over(orderby desc)as rank1
from Studentd,SC s,Course c,Teachert
where #=# and #=# and #=# and ='叶平')
where rank1=1;
--41、查询各个课程及相应的选修人数
select #,count(distinct #)as选修人数
from SC s
groupby #;
--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select*
from SC s1,SC s2
where #<># and= and #<>#;
--43、查询每门功成绩最好的前两名
select #,,#,,s.rank1
from(select #,#,,
row_number()over(partitionby # orderby desc)as rank1
from SC s1) s,Studentd
where #=# and rank1 <3
orderby #,s.rank1;
--44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，
-- 查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
select #,count(distinct #)as选修人数
from Course c leftjoin SC s on(#=#)
groupby # havingcount(distinct #)>10
orderbycount(distinct #)desc,#;
--45、检索至少选修两门课程的学生学号
select #
from SC s
groupby #
havingcount(distinct #)>=2;
--46、查询全部学生都选修的课程的课程号和课程名
select # as课程编号,
max()as课程名称
from SC s,Course c
where #=#
groupby #
havingcount(distinct #)=(selectcount(0)from Student);
--47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select #,
from Student d
wherenotexists(select # from SC s,Course c,Teachert
where #=# and #=# and #=# and ='叶平');
--48、查询两门以上不及格课程的同学的学号及其平均成绩
select #,round(avg(),2)as平均分
from SC s where <60
groupby #
havingcount(#)>2;
--49、检索“004”课程分数小于60，按分数降序排列的同学学号
select #
from SC s where #='004'and <60
orderby desc;
--50、删除“1002”同学的“001”课程的成绩
deletefrom SC s
where #='1002'and #='001';