sql语句测试题

Students学生表(SId：学号；Sname：学生姓名；age：学生年龄；sex：学生性别) Courses课程表(CId,课程编号；Cname：课程名字；TId：教师编号) SC成绩表(SId：学号；CId,课程编号；grade：成绩) Teachers教师表(TId：教师编号； Tname：教师名字) 问题：

1、查询“001”课程比“002”课程成绩高的所有学生的学生学号; select sno from sc a where

(select grade from sc b where b.cno=001 and b.sno=a.sno) < (select grade from sc c where c.cno=002 and c.sno=a.sno)

select * from sc a, sc b where

a.sno=b.sno and a.cno=001 and b.cno=002 and a.grade

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

Select sno, avg(grade) from sc group by sno having avg(grade) > 60;

3、查询所有同学的学号、姓名、选课数、总成绩； Select a.sno,a.sname, count(*), sum(b.grade)

from students a, sc b where a.sno=b.sno group by sno;

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

Select count(*) from teachers where tname like ‘李%’;

5、查询没学过“叶平”老师课的同学的学号、姓名； Select sno, sname from students

where sno not in (Select sno from sc where cno in (Select cno from courses

where tno=(Select tno from tearchers where tname=’叶平’)))

select * from students where sno not in (select sno from sc a, courses b

where a.cno=b.cno and b.tno=(select tno from teachers where tname='叶平'));

select * from students s where not exists (select * from sc a, courses b where a.cno=b.cno and b.tno=(select tno from teachers where tname='叶平') and s.sno=a.sno);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select * from students where sno in

(select a.sno from sc a, sc b where a.sno=b.sno and a.cno=001 and b.cno=002);

select * from students where sno in (select a.sno

from (select * from sc where cno=001) a, (select * from sc where cno=002) b where a.sno=b.sno);

select a.sno,a.sname from students a, sc b where a.sno=b.sno and b.cno in (001,002) group by a.sno,a.sname having count(*)=2;

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

select sno from sc group by sno having max(grade)<60;

select a.sno, a.sname from students a where 60 > all (select grade from sc b where a.sno=b.sno)

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

select sno from sc group by sno having count(*) < (select count(*) from courses)

9、删除学习“叶平”老师课的SC表记录；

Delete [from] sc where cno in (Select cno from courses

where tno=(Select tno from tearchers where tname=’叶平’)))

10、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 Select cno, max(grade), min(grade) from sc group by cno

11、查询每门课程被选修的学生数

Select cno, count(*) from sc group by cno

12、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

Select cno, avg(grade) from sc group by cno order by avg(grade) asc, cno desc

13、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； Select sno, sname from students

where sno in(Select sno from sc where cno=’003’ and grade > 80)

14、检索“004”课程分数小于60的同学学号，按分数降序排列

Select sno from sc where cno=’004’ and grade<60 order by grade desc

15、删除“002”同学的“001”课程的成绩

Delete [from] sc where sno=’002’ and cno=’001’

本文来源：https://www.bwwdw.com/article/sk1v.html