较难SQL语句集

以下SQL语句均在Oracle坏境下测试过：

1 为student表添加列“班级号” （10个长度定长字符串）

alter table student add class char(10);

2 将每个同学的班级号前面/后面加上“T”

update student set class=concat('T', trim(class));
update student set class=concat(trim(class), 'T');

3 删除班级号前面/后面的“T”

update student set class=substr(class, 2, length(trim(class)));
update student set class=substr(class, 0, length(trim(class))-1);

4 查询选修了课程名中包含了“语言”的课程的学生,列出学号,课程号,课程名和成绩

select sno, course.cno, cname, grade from course, sc where cname like '%语言%';

5 检索学分为5分的课程,成绩高于90分的学生的学号和姓名

select sno, sname from student where exists(select * from sc, course where sc.cno=course.cno and sno=student.sno and grade>90);

6 查询比C语言学分高的课程

select c2.cname fromCourse c1, Course c2 where c1.cname='C语言'and c2.credit>c1.credit;

7 查询平均年龄在19岁以上的系别

select sdept,avg(sage) as avgsage from Student group by sdept having avg(sage)>19;

8 查询CS系成绩最高的学生的性别

select ssex from Student, SC where Student.sno=SC.sno and sdept='CS' and grade in (select max(grade) from SC, Student where Student.sno=SC.sno and sdept='CS');

9 查询选课最少的学生

select sno, count(*) from sc group by sno having count(*) <= all(select count(*) from sc group by sno);

10 将教师列修改为非空列

alter table Course alter column teacher varchar(20) not null;

11 求每一个系的最高年龄和最低年龄

select sdept, max(sage) as maxage, min(sage) as minage from Student group by sdept;

12 检索“张三”选修的课程名及学分

select cname,credit from course where cno in (select cno from sc, student where sc.sno=student.sno and sname='张三');
select cname, credit from course where exists (select * from student ,sc where student.sno=sc.sno and student.sname='张三' and cno=course.cno);

13 列出没有学生选择的课程

select cno from Course where cno not in (select cno from SC);
select cno from course where not exists (select * from sc where cno=course.cno);

14 列出选课门数最多的学生所在系的所有学生的姓名

select sname from student where exists(select * from student s, (select sno from sc group by sno having count(*) >= all(select count(*) from sc group by sno)) maxsno where s.sno=maxsno.sno and s.sdept=student.sdept);

15 查询E系女同学的年龄比女同学的平均年龄高的学生

select sno from Student where sdept='E' and ssex='女' and sage>(select avg(sage) from Student where sdept='E' and ssex='女')

16 查询既选修了C01又选修了C02的学生

select Student.sno from Student, SC sc1, SC sc2 where sc1.sno=Student.sno and sc2.sno=Student.sno and sc1.cno='C01' and sc2.cno='C02';

17 查询不及格的门数在5门以上的学生所修的总学分。并按总学分降序排列

select sno, sum(credit) from course,sc where sc.cno=course.cno and grade>=60 and sno in (select sno from SC where grade<60 group by sno having count(*)>5) group by sno order by sum(credit) desc;

18 检索所有5学分的课程都选了的学生

Select sname from student where not exists (select * from course where credit=5 and not exists (select * from sc where sno=student.sno and cno=course.cno));

19 查询年龄高于其所在系的平均年龄的学生姓名

select sname from student where sage > (select avg(sage) from student s where student.sdept=s.sdept)

20 查询每位同学的选课中成绩最高的课程对应的学号,姓名,课程名,成绩

select student.sno,sname,cname,grade from sc,student,course where sc.sno=student.sno and sc.cno=course.cno and grade=(select max(grade) from sc where sc.sno=student.sno)  

21 为E系学生选修必修课C01

insert into SC select sno, 'C01', null from Student where sdept='E';

22 将E系,数据库课程学生的成绩加10分

update sc set grade=grade+10 where sno in (select sno from student where sdept='A') and cno=(select cno from course where cname='数据库');

23 将每位同学的最低分加10分

update sc set grade=grade+10 where grade=(select min(grade) from sc s where sc.sno=s.sno);