四表联合查询之学生选课查询
问题及描述:
学生和成绩表 学生是主表,成绩是子表
课程和成绩表 课程是主表,成绩是子表
老师和课程表 老师是主表,课程是子表
- 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;
修行之路道阻且长,大家共勉