create table Student(Sno varchar2(10),Sname varchar2(10),Sage date,Ssex varchar2(10));
insert into Student values('01' , '赵雷' , to_date('1990-01-01','yyyy-mm-dd') , '男');
insert into Student values('02' , '钱电' , to_date('1990-12-21','yyyy-mm-dd') , '男');
insert into Student values('03' , '孙风' , to_date('1990-05-20','yyyy-mm-dd') , '男');
insert into Student values('04' , '李云' , to_date('1990-08-06','yyyy-mm-dd') , '男');
insert into Student values('05' , '周梅' , to_date('1991-12-01','yyyy-mm-dd') , '女');
insert into Student values('06' , '吴兰' , to_date('1992-03-01','yyyy-mm-dd') , '女');
insert into Student values('07' , '郑竹' , to_date('1989-07-01','yyyy-mm-dd') , '女');
insert into Student values('08' , '王菊' , to_date('1990-01-20','yyyy-mm-dd') , '女');
create table Course(Cno varchar2(10),Cname varchar2(10),Tno varchar2(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(Tno varchar2(10),Tname varchar2(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(Sno varchar2(10),Cno varchar2(10),score number(4,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);
select * from student;
select * from course;
select * from teacher;
select * from sc;
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
1、查询01课程的学生
select cno,score from sc s where cno='01'
2、查询02课程的学生
select cno ,score from sc s where cno='02'
3、查询01课程比02课程高的学生信息
select * from student s,sc where
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select st.*,s1.score from sc s1,sc s2,student st
where s1.sno=s2.sno and s1.cno='01' and s2.cno='02' and s1.score>s2.score and st.sno=s1.sno
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select st.* ,s1.score from sc s1,sc s2,student st
where s1.sno=s2.sno and s1.cno='01' and s2.cno='02'
and s1.score<s2.score and st.sno=s1.sno
--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
1、平均成绩大于等于60的同学
select s.sno,avg(score) avgscore from sc s
group by s.sno having avg(score)>60
2、成绩满足条件的学生编号和学生姓名和成绩
select st.sno,st.sname ,avgscore from student st,(select s.sno,avg(score) avgscore from sc s
group by s.sno having avg(score)>60) s
where st.sno=s.sno
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
平均成绩小于60的同学
select sno,avg(score) avgscore from sc
group by sno having avg(score)<60
select st.sno,st.sname,avgscore
from student st ,(select sno,avg(score) avgscore from sc
group by sno having avg(score)<60) s
where st.sno=s.sno
--5、查询所有同学的学生编号、学生姓名、
--选课总数、所有课程的总成绩
select sno ,count(cno) scno,sum(score) sscore from sc group by sno
select st.sno,st.sname,scno,sscore
from student st,(select sno ,count(cno) scno,sum(score) sscore from sc group by sno) s
where st.sno=s.sno
--6、查询"李"姓老师的数量
select count(1) from teacher
where tname like '李%'
--7、查询学过"张三"老师授课的同学的信息
1、查找张三 01
select tno from teacher where tname='张三'
2、cno=02
select cno from course where tno=(select tno from teacher where tname='张三')
3、select sno from sc where cno=(select cno from course where tno=(select tno from teacher where tname='张三'))
4、select * from student st where st.sno in(select sno from sc where cno=(select cno from course where tno=(select tno from teacher where tname='张三')))
--8、查询没学过"张三"老师授课的同学的信息 intersect交集 minus差集 ---Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
---Union All，对两个结果集进行并集操作，包括重复行，不进行排序；
select * from student st where st.sno not in(select sno from sc where cno=(select cno from course where tno=(select tno from teacher where tname='张三')))
--9、查询学过编号为"01"并且
--也学过编号为"02"的课程的同学的信息
学过01 课程的同学
select * from sc where cno='01'
select * from sc where cno='02'
select * from sc s1 ,sc s2
where s1.sno=s2.sno and s1.cno='01' and s2.cno='02'
select st.* from sc s1 ,sc s2 ,student st
where s1.sno=s2.sno and s1.cno='01' and s2.cno='02' and
st.sno=s1.sno
--10、查询学过编号为"01"但是
--没有学过编号为"02"的课程的同学的信息
select sno from sc where cno='02'
select * from student st where sno not in (select sno from sc where cno='02')
select distinct st.* from student st right join sc on (st.sno=sc.sno)
where sc.sno not in(select sc.sno from sc where cno='02')
--11、查询没有学全所有课程的同学的信息
没有学全所有课程
select sno,count(cno) from sc group by sno having count(cno)<3
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
学号为"01"的同学所学的课程
select cno from sc
where sno='01'
select cno ,count(1)from sc group by cno having count(1)>1
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from sc
select cno from sc where sno='01'
select * from sc ,(select cno from sc where sno='01') c
where sc.cno=c.cno
select distinct st.* from student st,(select * from sc ,(select cno from sc where sno='01') c
where sc.cno=c.cno ) s
where st.sno=s.sno
--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from sc where sno='01'
select * from sc ,(select * from sc where sno='01') s
where sc.cno=s.cno
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select tno from teacher t where tname='张三' 01
select cno from course c where tno=(select tno from teacher t where tname='张三') cno=02
select sno from sc where cno=(select cno from course c where tno=(select tno from teacher t where tname='张三'))
select * from student st where sno not in(select sno from sc where cno=(select cno from course c where tno=(select tno from teacher t where tname='张三')))
--15、查询两门及其以上不及格课程的同学的学号，
--姓名及其平均成绩
查询两门及其以上不及格课程
select sno ,avg(score) avgs from sc where score<60 group by sno having count(1)>=2
select st.sno ,st.sname, avgs from student st,(select sno ,avg(score) avgs from sc where score<60 group by sno having count(1)>=2) s
where st.sno=s.sno
--16、检索"01"课程分数小于60，按分数降序排列的学生信息
select * from sc where cno='01' and score<60
select * from student st ,(select * from sc where cno='01' and score<60) s
where st.sno=s.sno order by score desc
--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sno,avg(score) from sc group by sno order by avg(score)desc
--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 !
--及格为>=60，中等为：-80，优良为：-90，优秀为：>=90
select cno,max(score) maxs,min(score)mins,avg(score) avgs from sc group by cno
select c.cno,c.cname,maxs,mins,avgs from course c , (select cno,max(score) maxs,min(score)mins,avg(score) avgs from sc group by cno ) c1
where c.cno=c1.cno
select sc.cno,max(sc.score),min(sc.score),avg(sc.score) ,count(*) as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end)/count(*) 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(*) 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(*)优良率,
sum(case when sc.score>=90 then 1 else 0 end)/count(*) 优秀率
from sc
group by sc.cno
-- order by count(*) desc ,sc.cno,asc
select cno ,count(1) from sc group by cno
--19、按各科成绩进行排序，并显示排名 !
select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc;
select s1.cno,s1.sno,s1.score ,count(s2.score) from sc s1,sc s2
where s1.cno=s2.cno and s1.score<s2.score
group by s1.sno,s1.cno,s1.score
order by s1.cno
--20、查询学生的总成绩并进行排名
select sno,sum(score) s from sc group by sno order by s desc
select r.*,rownum from (select sno,sum(score) s from sc group by sno order by s desc) r
select s.sname,sum(s1.score),rank() over(order by sum(s1.score) desc) from sc s1,student s where s1.sno=s.sno group by s.sname
--21、查询不同老师所教不同课程平均分从高到低显示
select tno from teacher t
select cno from course c where tno in(select tno from teacher t)
select cno ,avg(score) s from sc group by cno order by s desc
select * from course c ,(select cno ,avg(score) s from sc group by cno order by s desc) s1 where c.cno=s1.cno
select t.tname,t1.cname ,t1.s from teacher t,(select * from course c ,(select cno ,avg(score) s from sc group by cno order by s desc) s1 where c.cno=s1.cno) t1 where t.tno=t1.tno
--22、查询所有课程的成绩第3名到第 6名的学生信息及该课程成绩
select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc;
select * from (select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc) r where rank between 3 and 6
select st.*,sco.score ,sco.rank from student st , (select * from (select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc) r where rank between 3 and 6) sco where st.sno=sco.sno
--23、统计各科成绩各分数段人数：课程编号,课程名
称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select sc.cno, course.cname,
sum(case when sc.score>85 and sc.score<=100 then 1 else 0 end) "[100-85]",
sum(case when sc.score>70 and sc.score<=85 then 1 else 0 end)"[85-70]",
sum(case when sc.score>60 and sc.score<=70 then 1 else 0 end)"[70-60]",
sum(case when sc.score>0 and sc.score<=60 then 1 else 0 end)"[0-60]"
from sc , course where sc.cno=course.cno
group by sc.cno,course.cname
--24、查询学生平均成绩及其名次
学生平均成绩
select sno ,avg(score) avgs from sc group by sno order by avgs desc
名次
select s.*, rownum rank from (select sno ,avg(score) avgs from sc group by sno order by avgs desc) s
--25、查询各科成绩前三名的记录
select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc;
select * from (select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc) s where rank between 1 and 3
--26、查询每门课程被选修的学生数
select cno ,count(1) from sc group by cno
--27、查询出只有两门课程的全部学生的学号和姓名
select sno, count(1) from sc group by sno having count(1)=2
select st.sno, st.sname,count(1) from sc,student st where sc.sno=st.sno group by st.sno, st.sname having count(1)=2
--28、查询男生、女生人数
select ssex , count(1) from student st
group by ssex
--29、查询名字中含有"风"字的学生信息
select * from student where sname like '%风%'
--30、查询同名学生名单，并统计同名人数
--31、查询1990年出生的学生名单(注：Student表中 Sage列的类型是 datetime)
select * from student where substr(to_char(student.sage,'yyyy-mm-dd'),0,4)='1990'
select * from student where to_char(student.sage,'yyyy')='1990'
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按
--课程编号升序排列
select cno ,avg(score) a from sc group by cno order by a desc,cno asc
--33、查询平均成绩大于等于 60的所有学生的学号、姓名和平均成绩
select sno, avg(score) from sc group by sno having avg(score)>=60
select st.sname,st.sno, avg(score) from sc,student st where st.sno=sc.sno group by st.sno,st.sname having avg(score)>=60
--34、查询课程名称为"数学"，且分数低于 60的学生姓名和分数
select cno from course where cname='数学'
select * from sc where cno=(select cno from course where cname='数学') and sc.score<60
select student.sname, sc.cno,sc.score from sc ,student where cno=(select cno from course where cname='数学') and sc.score<60 and student.sno=sc.sno
--35、查询所有学生的课程及分数情况；
select * from sc
--36、查询任何一门课程成绩在 70分以上的姓名、课程名称和分数；
select * from sc where score>70
select st.sname ,sc.cno,sc.score from sc ,student st where score>70 and st.sno=sc.sno
--37、查询不及格的课程
select * from sc where score<60
--38、查询课程编号为'03'且课程成绩在 80分以上的学生的学号和姓名；
select * from sc where cno='03' and score>80
select st.sname ,st.sno from sc,student st where cno='03' and score>80 and st.sno=sc.sno
--39、求每门课程的学生人数
select cno ,count(cno) from sc group by cno
--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select t.tno from teacher t where t.tname='张三'
select cno from course where tno=(select t.tno from teacher t where t.tname='张三') cno
=02
select max(score) from sc where cno=(select cno from course where tno=(select t.tno from teacher t where t.tname='张三')) score=90
select sno from sc where cno='02' and score=90
select * from student where sno=(select sno from sc where cno='02' and score=90)
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 !
select s1.cno,s1.sno,s1.score
from sc s1,sc s2
where s1.sno=s2.sno and s1.cno!=s2.cno and s1.score=s2.score
group by s1.cno,s1.sno,s1.score
--42、查询每门功成绩最好的前两名 !
select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc;
select * from (select a.cno,a.sno,a.score,count(b.score)+1 rank
from sc a left join sc b
on a.score<b.score and a.cno=b.cno
group by a.cno,a.sno,a.score
order by a.cno ,rank asc) s where rank between 1 and 2
--43、统计每门课程的学生选修人数（超过人的课程才统计）。要求输出课程号和
--选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cno, count(1) "选课人数"from sc group by cno order by cno asc
--44、检索至少选修两门课程的学生学号
select sno from sc group by sno having count(1)>=2
--45、查询选修了全部课程的学生信息
select count(cno) from course
select sno from sc group by sno having count(1)in(select count(cno) from course)
--46、查询各学生的年龄
select from student st
select to_char(sysdate,'yyyy') from dual
select to_char(sysdate,'yyyy') -to_char(sage,'yyyy') age from student
--47、查询本周过生日的学生
select sno,to_char(sage,'dd') from student
select * from student
--48、查询下周过生日的学生
--49、查询本月过生日的学生
select to_char(sage,'mm') from student
select to_char(sysdate,'mm')from dual
select * from student where to_char(sysdate,'mm')=to_char(sage,'mm')
--50、查询下月过生日的学生
select * from student where to_char(sysdate,'mm')+1=to_char(sage,'mm')
--19、按各科成绩进行排序，并显示排名
select s1.cno,s1.sno,s1.score from sc s1,sc s2
where s1.sno=s2.sno group by s1.sno,s1.cno,s1.score order by s1.cno
select s.*,rank() over(partition by s.cno order by s.score) from (select s1.cno,s1.sno,s1.score from sc s1,sc s2
where s1.sno=s2.sno group by s1.sno,s1.cno,s1.score order by s1.cno) s