数据库习题答案

1.select Sname,Ssex,Class from Student2.select distinct depart from teacher3.select*from Student4.select*from score where degree between 60 and 805.select*from score where degree in(85,86,88)6.select*from Student where class='95031'or ssex='女'7.select*from Student order by class desc8.select*from score order by cno,degree desc9.select count(*)人数from Student where class='95031'10.select Sno 学号,cno 课程号, degree 最高分from scorewhere degree=(select max(degree)from score)11.select avg(degree)平均分from Score where cno='3-105'12.select cno,avg(degree)平均分from score where cno like'3%'group by cno having count(*)>513.select Sno from scoregroup by Sno having min(degree)>70 and max(degree)<9014.select Student.sname,o,score.degree from Student,score where student.sno=score.sno15.select Score.sno,ame,score.degree from course,score where o=o16.select student.sname,ame,score.degree fromstudent,course,scorewhere o=o and student.sno=score.sno17.select o,avg(score.degree)平均分from student,scorewhere student.Sno=score.Sno and student.class='95033'group by ocreate table grade(low numeric(3,0),upp numeric(3),rank char(1)); insert into grade values(90,100,'A');insert into grade values(80,89,'B');insert into grade values(70,79,'C');insert into grade values(60,69,'D');insert into grade values(0,59,'E');commit;18.select Sno,Cno,rank from score,grade where degree between low and upp order by rank19.select o,a.Sno,a.degree from score a,score bwhere o='3-105'and a.degree>b.degree and b.sno='109'ando='3-105'20.select*from score awhere degree<(select max(degree)from score b where o=o)and sno in(select Sno from score group by Sno having count(*)>1)21.select*from score where degree>(select degree from score wheresno='109'and cno='3-105')22.select*from student where year(sbirthday)=(select year(sbirthday) from student where sno='108')23.select*from score where cno in(select cno from course where tno in(select tno from teacher where tname='张旭'))24.select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5))25.select*from student where class in('95033','95031')26.select distinct cno from score where degree>8527.select*from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'))28.select tname,prof from teacher where depart='计算机系'and prof not in(select prof from teacher where depart='电子工程系')29.select*from score where cno='3-105'and degree>any(select degree from score where cno='3-245')order by degree desc30.select*from score where cno='3-105'anddegree>all(select degree from score where cno='3-245')order by degree desc31.select tname,tsex,tbirthday from teacherunion select sname,ssex,sbirthday from student32.select tname,tsex,tbirthday from teacher where tsex='女'union select sname,ssex,sbirthday from student where ssex='女'33.select*from score awhere degree<(select avg(degree)from score b where o=o)34.select tname,depart from teacher where tno in(select tno from course)35.select tname,depart from teacher where tno not in(select tno from course)36.select class from student where ssex='男'group by class having count(*)>=237.select*from student where sname not like'王%'38.select sname,(year(getdate())-year(sbirthday))年龄from student39.select max(sbirthday)最大日期,min(sbirthday)最小日期from student40.select*from student order by class desc,sbirthday41.select teacher.tname,ame from teacher,coursewhere teacher.tno=course.tno and teacher.tsex='男'42.select*from score where degree=(select max(degree)from score)43.select sname from student where ssex=(select ssex from student where sname='李军')44.select sname from student where ssex=(select ssex from student where sname='李军')and class=(select class from student where sname='李军')45.select*from score where sno in(select sno from student where ssex='男')andcno in(select cno from course where cname='计算机导论')。