文档介绍：面试、笔试中常用的SQL语句(数据库知识必杀)一共50个!!!
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select # from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where > and #=#;
2、查询平均成绩大于60分的同学的学号和平均成绩;
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select #,,count(#),sum(score)
from Student left Outer join SC on #=#
group by #,Sname
4、查询姓“李”的老师的个数;
select count(distinct(Tname))
from Teacher
where Tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
select #,
from Student
where S# not in (select distinct( #) from SC,Course,Teacher where #=# and #=# and ='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select #, from Student,SC where #=# and #='001'and exists( Select * from SC as SC_2 where #=# and #='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where #=# and #=# and ='叶平' group by S# having count(#)=(select count(C#) from Course,Teacher where #=# and Tname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学