数据库SQL进阶练习4（基于Oracle,个别语法与mysql有差别）

数据库SQL进阶练习4（基于Oracle,个别语法与mysql有差别）

表架构
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

建表语句
CREATE TABLE student(s# number, sname nvarchar(32),sage number,ssex nvarchar(8))
CREATE TABLE course(c# number, cname nvarchar(32), t# number)
CREATE TABLE sc(s# number,c# number,score number)
CREATE TABLE teacher(t# number,tname nvarchar(16))

测试数据
insert into Student select 1,N’刘一’,18,N’男’ from dual union select 2,N’钱二’,19,N’女’ from dual union select 3,N’张三’,17,N’男’ from dual union select 4,N’李四’,18,N’女’ from dual union select 5,N’王五’,17,N’男’ from dual union select 6,N’赵六’,19,N’女’ from dual

insert into Teacher select 1,N’叶平’ from dual union all select 2,N’贺高’ from dual union all select 3,N’杨艳’ from dual union all select 4,N’周磊’ from dual

insert into Course select 1,N’语文’,1 from dual union all select 2,N’数学’,2 from dual union all select 3,N’英语’,3 from dual union all select 4,N’物理’,4 from dual

insert into SC select 1,1,56 from dual union all select 1,2,78 from dual union all select 1,3,67 from dual union all select 1,4,58 from dual union all select 2,1,79 from dual union all select 2,2,81 from dual union all select 2,3,92 from dual union all select 2,4,68 from dual union all select 3,1,91 from dual union all select 3,2,47 from dual union all select 3,3,88 from dual union all select 3,4,56 from dual union all select 4,2,88 from dual union all select 4,3,90 from dual union all select 4,4,93 from dual union all select 5,1,46 from dual union all select 5,3,78 from dual union all select 5,4,53 from dual union all select 6,1,35 from dual union all select 6,2,68 from dual union all select 6,4,71 from dual
问题
1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；
select a.S# from
(select s#,score from SC where C#=(select C# from Course where Cname = ‘语文’)) a,
(select s#,score from SC where C#=(select C# from Course where Cname = ‘数学’)) b
where a.score>b.score and a.s#=b.s#;

2.查询平均分大于60的学生的学号和平均分
select S#, avg(score) 平均分 from SC group by S# having avg(score) > 60;

3.查询所有学生的学号，姓名，选课数，总分
select sc.S# 学号,s.Sname 姓名,Count(C#) 课程数,Sum(score) 总成绩 from SC sc left join Student s on s.S# = sc.S# group by sc.S#,s.Sname order by sc.S#;
–或者
select ssc.S# 学号,s.Sname 姓名,ssc.count 课程数,ssc.scores 总成绩 from student s left join (select s# , count(*) as count , sum(score) scores from sc group by s# ) as ssc on s.s# = ssc.s#

4.查询姓叶的老师的个数
select count(*) from teacher where Tname like ‘叶%’;

5.查询没学过“叶平”老师课的同学的学号、姓名；
select s.S#,s.Sname from Student s where s.S# not in (select S# from SC where C# in (select C# from Course where T# =(select T# from Teacher where Tname = ‘叶平’)));

6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；
select s.S#,s.Sname from Student s,SC c where s.S#=c.S# and c.C#=(select c# from course where cname=‘语文’) intersect
select s.S#,s.Sname from Student s,SC c where s.S#=c.S# and c.C#=(select c# from course where cname=‘数学’);

7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select sname,s# from
(select count() cou,tname from teacher t , course c where t.t# = c.t# and t.tname=‘叶平’ group by tname) a,
(select count() cou,tname,sname,stu.s# from teacher t , course c ,sc s , student stu where t.t# = c.t# and s.c# = c.c# and stu.s# = s.s# and tname = '叶平’group by tname,sname,stu.s#) b
where a.cou=b.cou
–或者
select s#, sname from student where s# in(
select b.s# from
(select count（C#）as cou1 from Course where T# =(select T# from Teacher where Tname = ‘叶平’))) a,
(select s#,count(s#) as cou2 from sc ,（select C# from Course where T# =(select T# from Teacher where Tname = ‘叶平’)）as cc# where sc.c# = cc#.c# group by s#) b
where a.cou1 = b.cou2)

8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select Student.S#,Student.Sname from Student where (select score from SC where SC.S# = Student.S# and C# = 2) < (select score from SC where SC.S# = Student.S# and C# = 1)
–或者
select a.S# , s.sname
from (select s#,score from SC where C#=(select C# from Course where Cname = ‘语文’)) a,(select s#,score from SC where C#=(select C# from Course where Cname = ‘数学’)) b, student s
where a.score>b.score and a.s#=b.s# and s.s# = a.s#;

9.查询所有课程成绩小于60分的同学的学号、姓名；
select S#,Sname from Student where S# in (select S# from SC where score<60);

10.查询没有学全所有课的同学的学号、姓名；
select Student.S#,Student.Sname from Student where (select count() from SC where SC.S# = Student.S#) < (select count() from Course)
–或者
select Studen.s#,Student.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select distinct Student.S#,Student.Sname from Student, SC where SC.S# = Student.S# and SC.C# in (select C# from SC where S# = 1) and Student.S# <>1;

12.查询和学号为“6”同学所有课程数量相等的其他同学的学号和姓名；
select s.s# , s.sname from
(select S#, count() cou from sc group by s#) a , (select count() cou from sc where s# = 6) b,student s
where a.cou = b.cou and s.s# = a.s#

13.查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；
select s.s# , s.sname from
(select S#, count() cou from sc where c# in (select C# from SC where S# = 2) and S# <>2 group by s#) a , (select count() cou from sc where s# = 2) b,student s
where a.cou = b.cou and s.s# = a.s#

14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select C#,Max(score),min(score) from SC group by C#

15.按各科平均成绩从低到高和及格率的百分数从高到低顺序
select * from
( select C#,avg(score) ret from SC group by sc.C# order by ret )
union all
select * from
(select a.c#, cou1/cou2 as ret from
(select c#, count() cou1 from sc where score > 60 group by c#) a, (select c#, count () cou2 from sc group by c#) b
where a.c# = b.c# order by ret desc)