数据库试题整理.docVIP

查询选修课程最多的学生的学号和姓名 (3) select top 1 学号=student.sno,姓名=student.sname from student inner join grade on student.sno=grade.sno group by student.sname,student.sno order by count(student.sno) desc 2. 将“计算机”学生的“信息系统”的分数置0分处理。 (2) create view gg(score) as select score from grade,student,department,course where grade.sno=student.sno and student.dno=department.dno and department.dname=计算机 and course.cno=grade.cno and course.cname=信息系统 update gg set gg.score=0; 3.查询选修课程中，有课程没有成绩、但是其他课程的成绩均在80分以上的同学的姓名、课程号、成绩 (2) 方法1： select sname,cno,score from student,grade where student.sno = grade.sno and grade.sno in(select sno from grade where score is null) and grade.sno not in(select sno from grade where score 80) and grade.sno in(select sno from grade where score =80) group by sname,cno,score 错误： select sname,cno,score from student,grade where student.sno = grade.sno and EXISTS( select * from grade s1 where s1.score80 and student.sno=sno and exists ( select * from grade s2 where s1.sno=s2.sno and s2.score is null ) ) 4检索所有平均成绩超过80分以上的课程名称、授课教师。 方法1： create view gradescore as select cno,avg(score) average from grade group by cno select cname,teacher from course where cno in ( select cno from gradescore where average80 ) 方法2： select cname,teacher from course where cno in (select cno from student,grade where grade.sno = student.sno group by cno having avg(score)=80 ) 5. 查询姓名是三个字的学生情况 (2) 方法1： select * from student where ltrim(sname) like ___ and ltrim(sname) not like __ and ltrim(sname) not like _ and ltrim(sname) is not null 方法2： select * from student where len(ltrim(sname))=3 6. 查询所有未选修 ‘03’ 号课程的学生姓名(用存在量词) ----错误结果 select distinct student.sno,sname from student left join grade on student.sno = grade.sno where not exists(select sno from grade where cno=03and student.sno = grade.sno) ----正确 select sno,sname from student where not exists (select * f