-- 一、建表
-- 1、根摇下面的内容创建表student
drop table student;
create table student
(
sno varchar2(20),-- primary key ,
sname varchar2(10),
sage DATE,
ssex varchar2(10)
)
comment on column student.sno is '学号';
comment on column student.sname is '姓名';
comment on column student.sage is '出生日期';
comment on column student.ssex is '性别';
desc SYSTEM.student:
insert into SYSTEM.student values ('01','赵雷',to_date(19900101,'yyyymmdd'),'男');
insert into SYSTEM.student values ('02','钱电',to_date(19901221,'yyyymmdd'),'男');
insert into SYSTEM.student values ('03','孙风',to_date(19900520,'yyyymmdd'),'男');
insert into SYSTEM.student values ('04','李去',to_date(19900806,'yyyymmdd'),'男');
insert into SYSTEM.student values ('05','周梅',to_date(19911201,'yyyymmdd'),'女');
insert into SYSTEM.student values ('06','吴兰',to_date(19920301,'yyyymmdd'),'女');
insert into SYSTEM.student values ('07','郑竹',to_date(19890701,'yyyymmdd'),'女');
insert into SYSTEM.student values ('08','王菊',to_date(19900120,'yyyymmdd'),'女');
select * from student;
-- 2、通过 pl/sql 工具生成增刚放查等客种语句
select sno, sname, sage, ssex from student;
insert into student
(sno, sname, sage, ssex)
values
(v_sno, v_sname, v_sage, v_ssex);
update student
set sno = v_sno,
sname = v_sname,
sage = v_sage,
ssex = v_ssex
where ;
delete student
where ;
drop table student;
create table student
(
sno varchar2(20),
sname varchar2(10),
sage DATE,
ssex varchar2(10)
)
tablespace TEST_TS;
-- create tablespace TEST_TS,
-- 3、建立表course
desc course;
drop table course;
create table course
(
cno varchar2 (10),
cname varchar2 (10),
tno varchar2(10)
）
comment on column course.cno is '学科号';
comment on column course.cname is '学科';
comment on column course.tno is '教师号';
insert into course values ('01','语文','02');
insert into course values ('02','数学','01');
insert into course values ('03','英语','03');
select * from course;
-- 4、教师表
teacher
drop table teacher;
create table teacher
(
tno varchar2 (10),
tname varchar2(10)
);
insert into teacher values('01','张三');
insert into teacher values('02','李四');
insert into teacher values('03','王五');
select * from teacher;
-- 5、成绩表
drop table sc;
truncate table sc;
create table sc
(
sno varchar2 (10),
cno varchar2(10),
score number (18,1)
);
insert into sc values('01','01',80.0);
insert into sc values('01','02',90.0);
insert into sc values('01','03',99.0);
insert into sc values('02','01',70.0);
insert into sc values('02','02',60.0);
insert into sc values('02','03',80.0);
insert into sc values('03','01',80.0);
insert into sc values('03','02',80.0);
insert into sc values('03','03',80.0);
insert into sc values('04','01',50.0);
insert into sc values('04','02',30.0);
insert into sc values('04','03',20.0);
insert into sc values('05','01',76.0);
insert into sc values('05','02',87.0);
insert into sc values('06','01',31.0);
insert into sc values('06','03',34.0);
insert into sc values('07','02',89.0);
insert into sc values('07','03',98.0);
select * from sc;
-- 二、查询
-- 1、查询 '01' '02' 课程分数
-- 1.1、查询同时存在 "01" 课程和 "02" 课程的情况
select * from student;
select * from course;
select * from teacher;
select * from sc;
select
a.*,b.score 课程01的分数,c.score 课程02的分数
from
student a
inner join (select * from sc where cno='01') b on a.sno=b.sno
inner join (select * from sc where cno='02') c on a.sno=c.sno;
-- 1.2、查询必须存在"01"课程，"02"课程可以没有的情况
-- (不存在时显示为 null)(以下存在相同内容时不再解释);
select
a.*,b.score 课程01的分数,c.score 课程02的分数
from
student a
inner join (select * from sc where cno='01') b on a.sno=b.sno
left join (select * from sc where cno='02') c on a.sno=c.sno;
--2、查询'01'课程比'02'课程成绩低的学生的信息及课程分数
--2.1、查询同时'01'课程比'02'课程分数低的数据
select
a.*,b.score 课程01的分数,c.score 课程02的分数
from
student a
inner join (select * from sc where cno='01') b on a.sno=b.sno
inner join (select * from sc where cno='02') c on a.sno=c.sno
where b.score < c.score;
-- 2.2、查询同时'01'课程比'02'课程分数低或'01'缺考的数据
select
a.*,b.score 课程01的分数,c.score 课程02的分数
from
student a
left join (select * from sc where cno='01') b on a.sno=b.sno
inner join (select * from sc where cno='02') c on a.sno=c.sno
where b.score < c.score or b.score is null;
-- 3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select * from
(select
a.sno,a.sname,round(avg(sc.score),2) as average
from
student a
left join sc on a.sno=sc.sno
group by a.sno,a.sname
)tab
where tab.average >=60;
-- 4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩
-- 4.1、有考试成绩，且小于 60 分
select * from
(select
a.sno,a.sname,round(avg(sc.score),2) as average
from
student a
left join sc on a.sno=sc.sno
group by a.sno,a.sname
)tab
where tab.average<60;
-- 4.2、包括没有考试成绩的数据
select tab.sno,tab.sname,NVL(tab.average,0) from
(select
a.sno,a.sname,round(avg( sc.score ),2) as average
from
student a
left join sc on a.sno=sc.sno
group by a.sno,a.sname
)tab
where tab.average<60 or tab.average is null;
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
-- 5.1、查询所有成绩的（不含缺考的）。
select
a.sno,a.sname,count(a.sno) 选课总数 ,sum(sc.score) 课程的总成绩
from
student a
left join sc on a.sno=sc.sno
group by a.sno,a.sname
having sum(sc.score) is not null;
-- 5.2、查询所有成绩的(包括缺考的)
select
a.sno,a.sname,count(a.sno) 选课总数 ,sum(sc.score) 课程的总成绩
from
student a
left join sc on a.sno=sc.sno
group by a.sno,a.sname
-- 6、查询"李"姓老师的数量 （有几个老师姓李）
select count(0) from teacher where substr(tname,1,1) = '李'
-- 7、哪些学生上过张三（老师）的课
select student.* from
student
inner join
(
select * from sc
where cno=
(select cno from
(select * from teacher where tname='张三') a
left join course b on a.tno=b.tno )
)s
on s.sno = student.sno
-- 8、哪些学生没上过张三（老师）的课
select student.* from student
where sno not in
(
select sno from sc
where cno =
(select cno from
(select * from teacher where tname='张三') a
left join course b on a.tno=b.tno )
)
-- 9、查询 '01' '02'都学过的同学的信息
select
a.*
from
student a
inner join (select * from sc where cno='01') b on a.sno=b.sno
inner join (select * from sc where cno='02') c on a.sno=c.sno
-- 10、查询学过编号为'01'但是没有学过编号为'02'的课程的同学的信息
select
a.*
from
student a
left join (select * from sc where cno='01') b on a.sno=b.sno
left join (select * from sc where cno='02') c on a.sno=c.sno
where b.score is not null and c.score is null
-- 11、查询没有学全所有课程的同学的信息
-- 11.1 学完所有课程的
select
a.*
from
student a
inner join (select * from sc where cno='01') b on a.sno=b.sno
inner join (select * from sc where cno='02') c on a.sno=c.sno
inner join (select * from sc where cno='03') d on a.sno=d.sno
-- 11.2 没有学完所有课程的
select
a.*
from
student a
left join (select * from sc where cno='01') b on a.sno=b.sno
left join (select * from sc where cno='02') c on a.sno=c.sno
left join (select * from sc where cno='03') d on a.sno=d.sno
where b.score is null or c.score is null or d.score is null
-- 12、查询至少有一门课与学号为'01'的同学所学相同的同学的信息
select * from student where sno in
(
select distinct sno from sc where cno in
(select sc.cno from sc where sc.sno='01')
and sno != '01'
)
-- 13、查询和'01'号的同学学习的课程完全相同的其他同学的信息 !!
select * from student where sno in
(
select sno from sc where cno in
(select sc.cno from sc where sc.sno='01')
and sno != '01'
group by sno
having count(cno) >= 3
)
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student where sno not in
(
select distinct sno from sc where cno in
(
select cno from course where tno =
(select tno from teacher where tname = '张三')
)
);
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.sno,a.sname,b.avg_score from student a
inner join
(select sno,round(avg(score),2) avg_score from sc group by sno) b
on a.sno = b.sno
where a.sno in
(select sno from sc where score < 60 group by sno having count(sno) >= 2);
-- 16、检索'01'课程分数小于 60，按分数降序排列的学生信息
select a.* ,b.score from student a
inner join
(select * from sc where cno = 01 and score < 60) b
on a.sno = b.sno
order by b.score desc;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.sno,a.sname,b.score 课程01的分数,c.score 课程02的分数,d.score 课程03的分数,avg_score
from student a
left join (select * from sc where cno='01') b on a.sno=b.sno
left join (select * from sc where cno='02') c on a.sno=c.sno
left join (select * from sc where cno='03') d on a.sno=d.sno
left join (select sno,round(avg(score),2) avg_score from sc group by sno) e on a.sno=e.sno
order by e.avg_score desc;
-- 18、查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select a.cno,cname,a.最高分,a.最低分,a.平均分,b.及格率,b.中等率,b.优良率,b.优秀率 from course
left join
(
select cno,max(score) 最高分,min(score) 最低分,round(avg(score),2) 平均分
from sc group by cno
) a
on course.cno = a.cno
left join
(
select cno,round(sum(case when score>=60.0 then 1 else 0 end)/count(0)*100,2) as 及格率,
round(sum(case when score>=70.0 and score <80 then 1 else 0 end)/count(0)*100,2) as 中等率,
round(sum(case when score>=80.0 and score <90 then 1 else 0 end)/count(0)*100,2) as 优良率,
round(sum(case when score>=90.0 then 1 else 0 end)/count(0)*100,2) as 优秀率 from sc group by cno
) b
on a.cno = b.cno
-- 19、按各科成绩进行排序，并显示排名
select sc.*,rank()over(partition by cno order by score desc) px from sc;
-- 20、查询学生的总成绩并进行排名
-- 20.1、 查询学生的总成绩
select a.sno,a.sname,NVL(b.总成绩,0) 总成绩 from student a
left join
(select sno,sum(score) 总成绩 from sc group by sno) b
on a.sno = b.sno
order by 总成绩 desc;
-- 20.2、查询学生的总成绩并进行排名。
select tab.*,rank()over(order by tab.总成绩 desc) px from
(select a.sno,a.sname,NVL(b.总成绩,0) 总成绩 from student a
left join
(select sno,sum(score) 总成绩 from sc group by sno) b
on a.sno = b.sno)tab;
-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.tno,tname,avg_score from teacher a left join course b on a.tno = b.tno
left join
(select cno,round(avg(score),2) avg_score from sc group by cno) c
on b.cno=c.cno
order by avg_score desc;
-- 22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩
-- Score 重复时保留名次空缺
select * from
(select sc.*,rank()over(partition by cno order by score desc)px from sc) a
where px>=2 and px<=3;
-- Score 重复时合并名次
select * from
(select sc.*,dense_rank()over(partition by cno order by score desc)px from sc) a
where px>=2 and px<=3;
-- 23 、统计各科成绩 各分数段人数：课程编号, 课程名称, "100-85","85-70","70-60","0-60"及所占百分比
-- 23.1 统计各科成绩 各分数段人数：课程编号, 课程名称, "100-85","85-70","70-60","0-60"
-- 横向显示
select a.cname,b.* from course a left join
(
select cno,sum(case when score>=85 then 1 else 0 end) "100-85" ,
sum(case when score<85 and score>=70 then 1 else 0 end) "85-70" ,
sum(case when score<70 and score>=60 then 1 else 0 end) "70-60" ,
sum(case when score<60 and score>=0 then 1 else 0 end) "0-60"
from sc group by cno
)b
on a.cno = b.cno;
-- 纵向显示 1(显示存在的分数段); !!
select a.cno 课程编号, a.cname 课程名称,
(case when b.score >= 85 then '85-100'
when b.score >= 70 and b.score < 85 then '70-85'
when b.score >= 60 and b.score < 70 then '60-70'
else '0-60'
end) 分数段,
count(1) 数量
from course a , sc b
where a.cno = b.cno
group by a.cno , a.cname , (
case when b.score >= 85 then '85-100'
when b.score >= 70 and b.score < 85 then '70-85'
when b.score >= 60 and b.score < 70 then '60-70'
else '0-60'
end)
order by a.cno , a.cname , 分数段
-- 24、 查询学生的平均成绩并进行排名
select tab.*, rank()over(order by avg_score desc) px from
(
select a.sno,a.sname,NVL(b.avg_score,0) avg_score from
student a
left join
(select sno,round(avg(score),2) avg_score from sc
group by sno)b
on a.sno = b.sno
)tab;
-- 25、查询各科成绩前三名的记录
-- 25.1 分数重复时保留名次空缺
select b.sno,student.sname,student.sage,student.ssex,b.cno,b.score from
(
select * from
(select sc.*, rank()over(partition by cno order by score desc) px from sc) a
where a.px <4
)b
inner join student on b.sno = student.sno;
-- 25.2 分数重复时不保留名次空缺，合并名次
select b.sno,student.sname,student.sage,student.ssex,b.cno,b.score from
(
select * from
(select sc.*, dense_rank()over(partition by cno order by score desc) px from sc) a
where a.px <4
)b
inner join student on b.sno = student.sno;
-- 26、查询每门课程被选修的学生数
select cno,count(0) 学生数 from sc group by cno;
-- 27、查询出只有两门课程的全部学生的学号和姓名
select student.sno,student.sname from student inner join
(
select sno,count(0) from sc group by sno having count(0)=2
)a
on student.sno=a.sno;
-- 28、查询男生、女生人数
select ssex,count(0) 人数 from student group by ssex;
-- 29、查询名字中含有"风"字的学生信息
select * from student where sname like '%风%';
-- 31、查询 1990 年出生的学生名单(注：Student 表中 Sage 列的类型是 date)
select * from student
where to_char(sage,'yyyy')='1990';
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，
-- 按课程编号升序排列
select cno,round(avg(score),2) avg_score from sc group by cno order by avg_score desc,cno;
-- 33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.sno,s.sname,a.avg_score from student s inner join
(select sno,round(avg(score),2) avg_score from sc group by sno having round(avg(score),2)>=85)a
on s.sno = a.sno;
-- 34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数
select sname,b.score from student s
inner join
(select * from sc where cno=
(select cno from course where cname = '数学' )
and score<60
)b
on s.sno = b.sno;
-- 35、查询所有学生的课程及分数情况；
select s.*,c.cname,sc.score from student s
left join sc
on s.sno = sc.sno
left join course c
on sc.cno = c.cno;
-- 36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数；
select s.sname,c.cname,a.score from student s
left join
(select sno,cno,score from sc where score >=70) a
on s.sno = a.sno
left join course c
on a.cno = c.cno;
-- 37、查询不及格的课程
select s.*,c.cname,a.score from student s inner join
(select * from sc where score < 60) a
on s.sno = a.sno
left join course c
on a.cno = c.cno;
-- 38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名；
select s.sno,s.sname from student s inner join
(select * from sc where cno = '01' and score >= 80) a
on s.sno = a.sno;
-- 39、求每门课程的学生人数
select c.cno,c.cname,a.ct 学生人数 from course c inner join
(select cno,count(0) ct from sc group by sc.cno) a
on c.cno = a.cno;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 !!
/*
select sno,score from sc where cno in
(
select cno from course c where tno=
(select tno from teacher where tname = '张三')
)
order by score desc limit 1;
*/
SELECT stu.sname,s.score,c.cname FROM student stu
inner JOIN sc s ON stu.sno=s.sno
inner JOIN course c ON s.cno=c.cno
inner JOIN teacher t ON t.tno=c.tno
WHERE t.tname='张三' AND
s.score=(SELECT MAX(score) FROM sc WHERE cno=
(SELECT cno FROM course WHERE tno=(SELECT tno FROM teacher WHERE tname='张三')));
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 !!
select * from sc s1 join sc s2 on s1.score = s2.score and s1.cno != s2.cno
-- 42、查询每门功成绩最好的前两名
select * from
(select sc.*,rank()over(partition by cno order by score desc) px from sc)a
where px <3
-- 44、检索至少选修两门课程的学生学号
select sno,count(0) from sc group by sno having count(0)>=2
-- 46、查询各学生的年龄
select s.*,(CAST((EXTRACT(YEAR FROM CURRENT_DATE)) AS integer)-CAST((EXTRACT(YEAR FROM sage)) AS integer)) AS 年龄 from student s
SELECT Sno, Sname, Sage, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) - EXTRACT(YEAR FROM Sage) AS 年龄 FROM Student;
select sno,sname,year(curdate())-year(sage) as sage from student
-- 47、查询本周过生日的学生 !!
select * from student where to_date(substr(sage,6,10),'mm-dd')>= trunc(next_day(sysdate-8,1)+1)
and to_date(substr(sage,6,10),'mm-dd') < trunc(next_day(sysdate-8,1)+7)+1;
select * from student
-- 48、查询下周过生日的学生 !!
select * from student where to_date(substr(sage,6,10),'mm-dd')>= trunc(next_day(sysdate-8,1)+8)
and to_date(substr(sage,6,10),'mm-dd') < trunc(next_day(sysdate-8,1)+14)+1;
-- 49、查询本月过生日的学生
select * from student where extract(month from(sysdate)) = extract(month from sage)
-- 50、查询下月过生日的学生
select * from student where (extract(month from(sysdate))+1) = extract(month from sage)
