常见SQL语句

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

1.查询”001”课程比”002”课程成绩高的学生

1
2
3
4
select a.S# from (select S#,Score from SC where C#='001')a,
(select S#,Score from SC where C#='002')b
where a.Score > b.Score
and a.S#=b.S#;

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

1
2
3
select S#,avg(Score) from SC 
group by S#
having avg(Score)>60;

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

1
2
3
select SC.S#,Sname,count(C#),sum(Score) from Student,SC 
where Student.S#=SC.S#
group by Student.S#;

4.查询姓“李”的老师的个数

1
2
select count(T#) from Teacher 
where Tname like '李%';

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

1
2
3
4
5
select SC.S#,Sname from Student,Course,SC,Teacher 
where Tname<>'叶平'
and Student.S#=SC.S#
and Course.C#=SC.C#
and Teacher.T#=Course.T#;

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

1
2
3
4
5
select SC.S#,Sname from Student,SC 
where
SC.C#='001'
and Student.S#=SC.S#
and Student.S# in (select S# from SC where C#='002' );

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

1
2
3
4
5
select SC.S#,Sname from Student,Course,SC,Teacher 
where Tname='叶平'
and Student.S#=SC.S#
and Course.C#=SC.C#
and Teacher.T#=Course.T#;

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

1
2
3
4
5
select b.S#,b.Sname from (select S#,Sname,Score from SC,Student where C#='001' and SC.S#=Student.S#)a,
(select S#,Sname,Score from SC,Student where C#='002' and SC.S#=Student.S#)b
where
a.Score > b.Score
and a.S#=b.S#;

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

1
2
select S#,Sname from Student
where S# not in (select Student.S# from Student,SC where Student.S#=SC.S# and score>60)

10.查询没有学全所有课的同学的学号、姓名;

1
2
3
4
select S#,Sname from Student,SC
where Student.S#=SC.S#
group by Student.S#,Student.Sname
having count(C#) <(select count(C#) from Course);

本文首发于http://www.miaoyunze.com/,转载请注明出处