Oracle语法 50道sql语句练习

/*

Student(S#,Sname,Sage,Ssex)学生表

Course(C#,Cname,T#)课程表

SC(S#,C#,score)成绩表

Teacher(T#,Tname)教师表

*/

–1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select s1.s#

from SC s1,SC s2

where s1.s#=s2.s# and s1.c#='001’and s2.c#='002’ands1.score>s2.score;

–2、查询平均成绩大于60分的同学的学号和平均成绩；

select s.s#,avg(s.score)

from SC s

groupby s.s#

havingavg(s.score)>60;

–3、查询所有同学的学号、姓名 、选课数、总成绩；

select d.s#,max(d.sname),count(distinct s.c#),sum(s.score)

from Student d,SC s

where d.s#=s.s#

groupby d.s#;

–4、查询姓“李”的老师的个数；

selectcount(0)

from Teacher t

where t.tname like’李%’;

–5、查询没学过“叶平”老师课的同学的学号、姓名；

select d.s#,d.sname

from Student d

wherenotexists(

selectdistinct(s.s#)

from SC s,Teachert,Course c

where t.t#=c.t# and s.c#=c.c# and t.tname='叶平’and d.s#=s.s#);

–6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select d.s#, d.sname

from Student d, SC s

where s.s# = d.s# and s.c# ='001’andexists

(select s1.s# from SC s1 where s1.s# = s.s# and s1.c# =‘002’);

–7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select s.s#,max(d.sname)

from SC s,Studentd,Course c,Teachert

where s.s#=d.s# and s.c#=c.c# and c.t#=t.t# and t.tname=‘叶平’

groupby s.s#

havingcount(distinct s.c#)=(

selectcount(0)from Coursec,Teachert where c.t#=t.t# and t.tname=‘叶平’);

–8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select d.s#,d.sname

from SC s1,SC s2,Studentd

where s1.s#=s2.s# and s1.s#=d.s# and s1.c#='002’and s2.c#='001’ands1.score<s2.score;

–9、查询所有课程成绩小于60分的同学的学号、姓名；

select s.s#,max(d.sname)

from Student d,SC s

where d.s#=s.s# and s.score<60

groupby s.s#

havingcount(0)=(selectcount(0)from SC s1 where s.s#=s1.s#);

–10、查询没有学全所有课的同学的学号、姓名；

select s.s#,max(d.sname)

from Student d,SC s

where d.s#=s.s#

groupby s.s#

havingcount(distinct s.c#)<>(selectcount(0)from Course);

–11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；

selectdistinct s.s#,d.sname

from Student d,SC s

where d.s#=s.s# and s.s#<>'1001’andexists(

select s1.c# from SC s1 where s1.s#='10