answer1.docx

1、answer1SQL练习题一表student,字段名name,number,sex,SQL2000,flash,net,其中SQL2000,flash,net为浮点型float完成如下查询： 输出所有男生的成绩 Select * from student where sex=男 输出所有SQL成绩在90以上的女生的成绩 Select * from student where sex=女 and sql2000=90 输出某一科目不合格所有的男生的成绩 Select * from student where sex=男 and (sql200060 or flash60 or net=70 an

2、d net=90SQL练习题二表student，course，score，teacher如下：STUDENT(SNO, SNAME, SSEX, SBIRTHDAY, CLASS)COURSE(CNO, CNAME, TNO)SCORE(SNO, CNO, DEGREE) TEACHER(TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)1、 查询Student表中的所有记录的Sname、Ssex和Class列。2、 查询教师所有的单位即不重复的Depart列。select distinct depart from teacher3、 查询Student表的所

3、有记录。4、 查询Score表中成绩在60到80之间的所有记录。5、 查询Score表中成绩为85，86或88的记录。6、 查询Student表中“95031”班或性别为“女”的同学记录。7、 以Class降序查询Student表的所有记录。8、 以Cno升序、Degree降序查询Score表的所有记录。Select * from score order by cno,degree desc9、 查询“95031”班的学生人数。Select count(*) from student where class=9503110、查询Score表中的最高分的学生学号和课程号。 Select top

4、1 sno,cno from score order by degree descSelect sno,cno from score where degree=(Select max(degree) from score)11、查询3-105号课程的平均分。Select avg(degree) from score where cno=3-10512、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 Select cno,avg(degree) From score Where cno like 3% Group by cno Having count(*)=513、查询最低

5、分大于70，最高分小于90的Sno列及他们的平均分。Select sno,avg(degree)From scoreGroup by snoHaving max(degree)7014、查询所有学生的Sname、Cno和Degree列。Select sname,cno,degree from student,score Where student.sno=score.snoSelect sname,cno,degree from student inner join score on student.sno=score.sno15、查询所有学生的Sno、Cname和Degree列。Select

6、 sno,cname,degree from score,course where score.sno=o16、查询所有学生的Sname、Cname和Degree列。 Select sname,cname,degree from student,score,course Where student.sno=score.sno and o=o17、查询“95033”班所选课程的平均分。Select avg(degree)From student,scoreWhere student.sno=score.sno and class=95033Select cno,avg(degree)From s

7、tudent,scoreWhere student.sno=score.sno and class=95033Group by cno18、假设使用如下命令建立了一个grade表：create table grade(low number(3,0),upp number(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

8、 grade values(0,59,E);现查询所有同学的Sno、Cno和rank列。19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。Select student.* from student,score where student.sno=score.sno and cno=3-105 and degree(Select degree from score where cno=3-105 and sno=109)Select student.* from student,score where student.sno=score.sno and cno=3-

9、105 and degree(Select max(degree) from score where sno=109)Select student.* from student,score where student.sno=score.sno and cno=3-105 and degreeall(Select degree from score where sno=109)20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。Select * from student where sno in(select snoFrom scoreWhere sno not in(Se

10、lect sno from score where degree=(Select max(degree) from score)Group by snoHaving count(*)1)Select * from student where sno in(select sno From scoreWhere sno not in(select sno from score,(select cno,max(degree) maxdegree from score group by cno) awhere o=o and score.degree=a.maxdegree)Group by snoH

11、aving count(*)1)21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。Select sno,sname,sbirthday 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=(

12、Select tno from teacher where tname=张旭)Select score.*From teacher,course,scoreWhere teacher.tno=course.tno and o=o and tname=张旭24、查询选修某课程的同学人数多于5人的教师姓名。Select tname from teacher where tno in(Select tno from course where cno in(Select cno from score group by cno having count(*)=5)Select tname from te

13、acher,course where teacher.tno=course.tnoAnd cno in(Select cno from score group by cno having count(*)=5)25、查询95033班和95031班全体学生的记录。Select * from student where class in (95033,95031)Select * from student where class=95033 or class=9503126、查询存在有85分以上成绩的课程Cno.Select distinct cnoFrom score where degree=

14、8527、查询出“计算机系“教师所教课程的成绩表。Select tname,cname,sno,degreeFrom teacher,course,scoreWhere teacher.tno=course.tno and o=o and depart=计算机系28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。Select tname,prof from teacher where depart=信息 and prof not in(Select prof from teacher where depart=经济)unionSelect tname,prof from

15、 teacher where depart=经济 and prof not in(Select prof from teacher where depart=信息)29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的某个同学的Cno、Sno和Degree,并按Degree从高到低次序排序。Select cno,sno,degree from score where cno=3-105 and degree(Select min(degree) from score where cno=3-245)Select cno,sno,degree from score whe

16、re cno=3-105 and degreeany(Select degree from score where cno=3-245)30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.Select cno,sno,degree from score where cno=3-105 and degree(Select max(degree) from score where cno=3-245)Select cno,sno,degree from score where cno=3-105 and degreeall(Select d

17、egree from score where cno=3-245)31、查询所有教师和同学的name、sex和birthday.Select sname name,ssex sex,sbirthday birthdayFrom studentUnionSelect tname name,tsex sex,tbirthday birthdayFrom teacher32、查询所有“女”教师和“女”同学的name、sex和birthday.Select sname name,ssex sex,sbirthday birthdayFrom studentWhere ssex=女UnionSelect

18、 tname name,tsex sex,tbirthday birthdayFrom teacherWhere tsex=女33、查询成绩比该课程平均成绩低的同学的成绩表。Select score.*From score,(Select cno,avg(degree) avgdegree from score group by cno) aWhere o=o and score.degree=237、查询Student表中不姓“王”的同学记录。Select * froms student where sname not like 王%38、查询Student表中每个学生的姓名和年龄。Sele

19、ct sname,year(getdate()-year(sbirthday) sage from student39、查询Student表中最大和最小的Sbirthday日期值。Select max(sbirthday),min(sbirthday) from student40、以班号和年龄从大到小的顺序查询Student表中的全部记录。Select * from student order by class desc,sbirthday41、查询“男”教师及其所上的课程。Select *From teacher left join courseOn teacher.tno=course.

20、tno where tsex=男42、查询最高分同学的Sno、Cno和Degree列。Select top 1 with ties *From score order by degree descSelect * from score where degree=(Select max(degree) from score)43、查询和“李军”同性别的所有同学的Sname.Select sname from student where ssex=(Select ssex from student where sname=李军)Select b.snameFrom student a,studen

21、t bWhere a.姓名=李军 and a.性别=b.性别44、查询和“李军”同性别并同班的同学Sname.Select sname from student where ssex=(Select ssex from student where sname=李军)And class=( Select lass fcrom student where sname=李军 李军所在班级)Select b.snameFrom student a,student bWhere a.sname=李军 and a.class=b.class and a.ssex=b.ssex45、查询所有选修“计算机导论

22、”课程的“男”同学的成绩表Select sname,score.* from score,student where score.sno=student.snoAnd ssex=男 and score.sno in (Select sno from course,score where o=o and cname=计算机导论)查询所有选修“计算机导论”课程的“男”同学的成绩表Select * from score where sno in(Select sno from score where cno=(Select cno from course where cname=计算机导论) and

23、 sno in(select sno from student where ssex=男)SQL练习题三1 问题描述：为管理岗位业务培训信息，建立3个表:S(SNO,SN,SD,SA)SNO,SN,SD,SA分别代表学号、学员姓名、所属单位、学员年龄C(CNO,CN)CNO,CN分别代表课程编号、课程名称SC(SNO,CNO,G)SNO,CNO,G分别代表学号、所选修的课程编号、学习成绩 1. 使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名 Select sno,sn from s where sno in (Select sno from sc where cno=(Sel

24、ect cno from c where cn=税收基础)Select s.sno,snFrom s,sc,c where s.sno=sc.sno and o=o and cn=税收基础2. 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位Select sn,sd from s where sno in (select sno from sc where cno=c2)Select sn,sd from s,sc where s.sno=sc.sno and cno=c23. 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位Select sn,sd from

25、 s where sno not in (select sno from sc where cno=c5)4. 查询选修了课程的学员人数Select count(distinct sno)From sc5. 查询选修课程超过5门的学员学号和所属单位Selct sno,sd from s where sno in(Select snoFrom scGroup by snoHaving count(*)5)2 问题描述：已知关系模式：S(SNO,SNAME）学生关系。SNO为学号，SNAME为姓名C(CNO,CNAME,CTEACHER)课程关系。CNO为课程号，CNAME为课程名，CTEACHE

26、R为任课教师SC(SNO,CNO,SCGRADE)选课关系。SCGRADE为成绩1. 找出没有选修过“李明”老师讲授课程的所有学生姓名Select sname from s where sno not in(Select distinct sno from Sc where cno in(Select cno from c where cteacher=李明)2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩列出有二门以上（含两门）不及格课程的学生sno及其平均成绩Select sno,avg(scgrade) from sc where sno in(Select snoFrom scWhere scgrade=2) group by snoSelect sname,avg(scgrade) from sc,s where sc.sno=s.sno and sno in(Select snoFrom scWhere scgrade=2) group by sno,sname3. 列出既学过“1”号课程，又学过“2”号课