Oracle数据库经典案例之学生选课四表联合查询

四表联合查询之学生选课查询

问题及描述：
学生和成绩表 学生是主表，成绩是子表
课程和成绩表 课程是主表，成绩是子表
老师和课程表 老师是主表，课程是子表

• 1.学生表
  Student(S#,Sname,Sage,Ssex) –S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
• 2.课程表
  Course(C#,Cname,T#) –C# –课程编号,Cname 课程名称,T# 教师编号
• 3.教师表
  Teacher(T#,Tname) –T# 教师编号,Tname 教师姓名
• 4.成绩表
  SC(S#,C#,score) –S# 学生编号,C# 课程编号,score 分数

---

1.建表并插入数据

为了方便后面的查询，所以数据会有些不合理的地方去迎合题目，但是只要能达到目的就行了

--1.创建学生表 Student(S#,Sname,Sage,Ssex) 
--S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
create table student(
   s#  varchar2(5) primary key ,  --由于是练习这里的主键约束是直接写在字段后的
   sname  varchar2(20) not null, 
   sage  date,  
   ssex  char(3) default '男' check(ssex = '男' or ssex = '女')
);
--插入学生数据
insert into student values('s101','赵灵儿','01-5月-1996','女');
insert into student values('s102','林月如','05-7月-1994','女');
insert into student values('s103','李逍遥','16-9月-1994','男');
insert into student values('s104','酒剑仙','12-12月-1985','男');
insert into student values('s105','茂茂','14-4月-1996','男');
insert into student values('s106','茂茂','14-5月-1996','男');
insert into student values('s107','赵灵儿','01-5月-1996','女');
insert into student values('s108','赵灵儿','01-5月-1990','女');

--2.创建课程表  Course(C#,Cname,T#)
--C# --课程编号,Cname 课程名称,T# 教师编号
create table course(
   c#  varchar2(5),
   cname  varchar2(50) not null, 
   t#  varchar2(5) references teacher(t#) on delete set null,      --外键约束
   constraints pk_course primary key(c#)          --推荐这种写法，可以给主键命名    
);
--插入课程数据
insert into course values('c10','语文','t110');
insert into course values('c20','数学','t111');
insert into course values('c30','英语','t112');
insert into course values('c40','体育','t113');
insert into course values('c50','艺术','t114');

--3.创建教师表 Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名 
create table teacher(
   t#  varchar2(5),
   tname  varchar2(20) not null,
   constraints pk_teacher primary key(t#)  
);
--插入教师数据
insert into teacher values('t110','紫萱');
insert into teacher values('t111','徐长卿');
insert into teacher values('t112','唐雪见');
insert into teacher values('t113','李真行');
insert into teacher values('t114','张三');

--4.创建成绩表 SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
create table sc(
   s#  varchar2(5),  
   c#  varchar2(5),
   score  number(3),
   constraints pk_sc primary key(s#,c#),
   constraints ck_sc_score check(score>=0 and score<=100),
   constraints fk_sc_s# foreign key(s#) references student(s#) 
   on delete set null,   
   constraints fk_sc_c# foreign key(c#) references course(c#) 
   on delete set null
);
--插入成绩数据  为方便查询数据有点多哈
insert into sc values('s101','c10',98);
insert into sc values('s101','c20',95);
insert into sc values('s101','c30',100);
insert into sc values('s101','c40',100);
insert into sc values('s102','c10',88);
insert into sc values('s102','c20',68);
insert into sc values('s102','c30',70);
insert into sc values('s102','c50',90);
insert into sc values('s103','c10',55);
insert into sc values('s103','c20',75);
insert into sc values('s103','c30',59);
insert into sc values('s103','c40',89);
insert into sc values('s103','c50',98);
insert into sc values('s104','c10',90);
insert into sc values('s104','c20',100);
insert into sc values('s104','c30',60);
insert into sc values('s105','c10',56);
insert into sc values('s105','c20',30);
insert into sc values('s105','c30',60);
insert into sc values('s105','c40',65);

查询内容及参考代码如下

由于是初学，可能会有不严谨和错误的地方，欢迎各位大佬指正

--1、查询"c10"课程比"c20"课程成绩高的学生的信息及课程分数
select s.*,sc1.score as c10成绩,sc2.score as c20成绩       --sql 92 写法
       from student s,sc sc1,sc sc2       
       where sc1.s# = sc2.s# 
       and s.s# = sc1.s#
       and (sc1.c# = 'c10' and sc2.c# = 'c20') 
       and sc1.score > sc2.score;
                                                         --sql 99 写法
select s.*,sc1.score as c10成绩,sc2.score as c20成绩 from student s join sc sc1 
       on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where sc1.c# = 'c10' and sc2.c# = 'c20' and sc1.score > sc2.score;     

--2、查询"c10"课程比"c20"课程成绩低的学生的信息及课程分数
select s.*,sc1.score as c10成绩,sc2.score as c20成绩 from student s join sc sc1
       on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where sc1.c# = 'c10' and sc2.c# = 'c20' and sc1.score < sc2.score;

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.s#,sname,avg_sc from student s join 
       (select s#,avg(score) avg_sc from sc 
       group by s# having avg(score) >= 60) t
       on s.s# = t.s#
       order by s.s#;

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select s.s#,sname,avg_sc from student s join 
       (select s#,avg(score) avg_sc from sc group by s# having avg(score) < 60) t
       on s.s# = t.s#;

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s#,sname,选课总数,总成绩 from student s join
       (select s#,count(c#) as 选课总数,sum(score) as 总成绩 from sc group by s#) t
       on s.s# = t.s#
       order by s.s#;

--6、查询"李"姓老师的数量
select count(tname) as 李姓老师 from teacher group by tname having tname like '李%';

--7、查询学过"张三"老师授课的同学的信息
select s.* from student s join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where tname = '张三';

--8、查询没学过"张三"老师授课的同学的信息
select  s.* from student s where s.s# not in 
       (select s.s# as tid from student s join sc on s.s# = sc.s# --学过张三老师的课程的同学
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where tname = '张三');

--9、查询学过编号为"c40"并且也学过编号为"c50"的课程的同学的信息
select s.* from student s join sc sc1 on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where sc1.c# = 'c40' and sc2.c# = 'c50';

--10、查询学过编号为"c40"但是没有学过编号为"c50"的课程的同学的信息
select s.* from student s 
       where s# in (select s# from sc sc1 where c# = 'c40') --学过c40
       and s# not in (select sc.s# from sc where c# = 'c50'); --没学过c50     

--11、查询没有学全所有课程的同学的信息
select distinct s.* from student s join sc 
       on s.s# = sc.s#
       where s.s# in(select s# from sc group by s# having count(c#) < 5);

--12、查询至少有一门课与学号为"s101"的同学所学相同的同学的信息
select distinct s.* from student s join sc sc1 on s.s# = sc1.s#
       where sc1.c# in (select c# from sc where s# = 's101')
       and s.s# <> 's101';

--13、查询和"s101"号的同学学习的课程完全相同的其他同学的信息   --分条解决条件 有点复杂，应该有简单一些的写法

select s.* from student s where s.s# in
  (select distinct s# from sc where s# <> 's101'         --编号不为s101                      
       and c# in (select c# from sc where s# = 's101')   --选修课程在s101的选修课程里 
       and s# not in (select s# from sc where c# not in (select c# from sc where s# = 's101'))                                                  
       and s# in (select s#  from sc group by s#         --选修课程都再s101的选修课程里的
       having count(c#) = (select count(c#) from sc where s# = 's101')));   
                                                         --选修课程数与s101相同的学生编号

--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select  s.* from student s where s.s# not in 
       (select s.s# as tid from student s join sc on s.s# = sc.s#  --学过张三老师的课程的同学
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where tname = '张三');

--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.s#,sname,t.avg_score from student s 
       join (select s#,avg(score) as avg_score from sc group by s#) t
       on s.s# = t.s#        
       where t.s# in (select s# from sc where score < 60 group by s# having count(s#) >= 2);

--16、检索"c10"课程分数小于60，按分数降序排列的学生信息
select s.*,sc.score as c1_score from student s join sc on s.s# = sc.s#
       where sc.c# = 'c10' and score < 60 order by score desc;

--17、查询各科成绩前三名的记录 分科目查 c10 到 c50 以c10为例
select t.s#,s.sname,t.c#,t.score from student s join 
       (select * from sc where c# = 'c10' order by score desc) t
       on s.s# = t.s#
       where rownum <= 3; 

--18、查询每门课程被选修的学生数       
select t.c#,cname,t.count_s# from course c 
       join (select c#,count(s#) as count_s# from sc group by c#) t
       on c.c# = t.c#;

--19、查询出只有两门课程的全部学生的学号和姓名  --改为4门课程
select distinct s.s#,sname from student s join sc on s.s# = sc.s#
       where s.s# in (select s# from sc group by s# having count(c#) = 4);

--20、查询男生、女生人数
select ssex,count(s#) from student group by ssex;

--21、查询名字中含有"风"字的学生信息   改为月
select * from student where sname like '%月%';

--22、查询同名同性学生名单，并统计同名人数

select sname ,count(sname) as 人数 from         --同名同性人数
   (select sname,ssex as sse from student s where sname in 
   (select sname from student group by sname having count(sname) >1 /*同名*/)) --同名同性
   group by sname ;

--23、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)    
select * from student where trunc(to_date(sage),'YEAR') = '1-1月-1990';


--24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c#,avg(score) from sc group by c# order by avg(score) desc,c#;

--25、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.s#, sname ,avg_score from student s join 
       (select s#, avg(score) as avg_score from sc group by s#) t on s.s# = t.s# 
       where avg_score >= 85;

--26、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select sname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c#
       where c.cname = '数学' and score < 60;

--27、查询所有学生的课程及分数情况；
select sname, cname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c# ;

--28、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select sname, cname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c# 
       where sc.score > 70;

--29、查询不及格的课程
select sname, cname, score from student s join sc on s.s# = sc.s#
       join course c on sc.c# = c.c# 
       where sc.score < 60;

--30、查询课程编号为c10且课程成绩在80分以上的学生的学号和姓名；
select s.s# ,sname from student s join sc on s.s# = sc.s#      
       where sc.c# = 'c10' and sc.score > 80;

--31、求每门课程的学生人数
select c.c# ,cname, count_s# from course c 
       join (select c# ,count(s#) as count_s# from sc group by c#) t
       on c.c# = t.c#;

--32、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩   
--普通做法      
select sname ,sc.score from student s join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where t.tname = '张三'  --找到选修了张三老师的同学
       and sc.score =         --与张三老师的学生分数最高的那个进行匹配
      (select max(score) from student s join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where t.tname = '张三') ;
--使用rownum       
select sname,sc_score from (select sname,sc.score as sc_score from student s 
       join sc on s.s# = sc.s#
       join course c on c.c# = sc.c#
       join teacher t on t.t# = c.t#
       where t.tname = '张三' 
       order by score desc) 
       where rownum = 1

--33、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s.s#,s.sname, sc1.c# ,sc1.score from student s join sc sc1 
       on s.s# = sc1.s#
       join sc sc2 on sc1.s# = sc2.s#
       where  sc1.c# <> sc2.c#      --不同课程
       and sc1.score = sc2.score;   --分数相同


--34、查询每门成绩最好的前两名  还是以c10为例
select t.s#,s.sname,t.c#,t.score from student s join 
       (select * from sc where c# = 'c10' order by score desc) t
       on s.s# = t.s#
       where rownum <= 2; 

--35、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，
--   查询结果按人数降序排列，若人数相同，按课程号升序排列 
select c.c# ,cname, count_s# from course c 
       join (select c# ,count(s#) as count_s# from sc group by c#) t
       on c.c# = t.c#
       where count_s# >=5
       order by count_s# desc,t.c#;


--36、检索至少选修两门课程的学生学号
select s.s# ,sname, count_c# from student s 
       join (select s#,count(c#) as count_c# from sc group by s# having count(c#) >= 2) t
       on t.s# = s.s#
       order by t.count_c# desc,s.s#;       

--37、查询选修了全部课程的学生信息
select s.s#, sname, count_c# from student s 
       join (select s#,count(c#) as count_c# from sc group by s# ) t
       on t.s# = s.s#
       where count_c# = (select count(c#) from course);  --选修所有课程

--38、查询各学生的年龄

select sname, months_between(trunc(sysdate,'YEAR'),trunc(to_date(sage),'YEAR'))/12 
      as age from student;

修行之路道阻且长，大家共勉