2017-07-27

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select a.*,b.score,c.score from Student a , SC b , SC c where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score < c.score

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

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select s.s#,s.sname,c.score from student s,
(select s#,trunc(avg(score)) score from sc group by s# order by s# asc)c
where s.s#=c.s# and c.score<60;

--4.1、查询在sc表存在成绩的学生信息的SQL语句。

select s.s#,s.sname,s.sage,s.ssex from student s,
(select s#,trunc(avg(score)) score from sc group by s# order by s# asc)c
where s.s#=c.s#

--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
----方法一
select s.s#,s.sname,s.sage,s.ssex ,c.score from student s,
(select s#,trunc(avg(score)) score from sc group by s# order by s# asc)c
where s.s#=c.s#(+) and c.score is null

----方法二
select student.s#,sc.score from student full join
sc
on student.s# = sc.s# where sc.score is null

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

--5.1、查询所有有成绩的SQL。
select s.s#,s.sname ,b.avgs from student s
full join
(select s#,round(avg(score)) avgs from sc group by s#) b
on s.s#= b.s#
where b.avgs is not null order by s.s#
--6、查询"李"姓老师的数量
select count(tname) 李姓老师数量 from teacher where tname like '李%'

--7、查询学过"张三"老师授课的同学的信息
select s.s#,s.sname,s.sage,s.ssex from student s,sc,course,teacher where s.s#=sc.s# and sc.c#=course.c# and course.t#=teacher.t# and teacher.tname in('张三') order by s.s#

--8、查询没学过"张三"老师授课的同学的信息

select distinct( s.s#),s.sname,s.sage,s.ssex from student s,sc,course,teacher where s.s#=sc.s# and sc.c#=course.c# and course.t#=teacher.t# and teacher.tname not in('张三')

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select s.s#,s.sname,s.sage,s.ssex from student s,
(select s#,c# from sc where c#='01')a,
(select s#,c# from sc where c#='02')b
where s.s#=a.s# and s.s#=b.s#
--10、查询没有学全所有课程的同学的信息
select s.s#,s.sname,s.sage,s.ssex from student s,
(select s#,count(c#) c from sc group by s#)a
where s.s#=a.s# and a.c<3