文档介绍：50条常用sql语句(monly used SQL statements)
50条常用sql语句
student (s #, sname, sage, sex) 学生表
course (c #, cname, t #) 课程表
sc (s #, c #, score) 成绩表
teacher (t #, tname) 教师表
问题:
1、查询"课程比" 课程成绩高的所有学生的学号;
select # . from (select s #, score from sc where c # = '001'),
(select s #, score from sc where c # = '002') b
where > and . # = #;
2、查询平均成绩大于分的同学的学号和平均成绩;
select avg (score), it #,
from sc
group by s # having avg (score) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select #, , count ( #), sum (score)
from a 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、查询学过"并且也学过编号" 课程的同学的学号、姓名;
select #, from student, sc where # = # and # = '001' and exists (select * from sc or sc _ 2 where sc _ # = # and sc _. c # = '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、查询课程编号"的成绩比课程编号" 课程低的所有同学的学号、姓名;
select sname from s #, select #, , score (the score from sc sc _ 2 where sc _ # = # and sc _. c # = '002') score2
from student, sc where # = # and c # = '001') _ 2 where score2 < score;
9、查询所有课程成绩小于分的同学的学号、姓名;
select s #, sname
from student
where's # not in (select # from student, sc where s. # = # and score > 60);
10、查询没有学全所有课的同学的学号、姓名;
select #,
from student, sc
where studen