1、练习题一create database mydbgouse mydbcreate table student(-学号sno varchar(3) not null primary key,-姓名sname varchar(4) not null,-性别ssex varchar(2) not null,-出生年月sbirthday datetime,-所在班级class varchar(5)create table teacher(-教工编号tno varchar(3) not null primary key,-教工姓名tname varchar(4) not null,-教工性别tsex v
2、archar(2) not null,-教工出生日期tbirthday datetime,-职称prof varchar(6),-所在部门depart varchar(10)create table course(-课程号cno varchar(5) not null primary key,-课程名称cname varchar(10) not null,-教工编号tno varchar(3) references teacher(tno)create table score(-学号sno varchar(3) not null references student(sno),-课程号cno
3、varchar(5) not null references course(cno),-成绩degree decimal(4,1)insert into studentvalues(108,曾华 ,男,1977-09-01,95033)insert into studentvalues(105,匡明 ,男,1975-10-02,95031)insert into studentvalues(107,王丽 ,女,1976-01-23,95033)insert into studentvalues(101,李军 ,男,1976-02-20,95033)insert into studentvalu
4、es(109,王芳 ,女,1975-02-10,95031)insert into studentvalues(103,陆君 ,男,1974-06-03,95031)insert into teachervalues(804,李诚 ,男,1958-12-02,副教授,计算机系)insert into teachervalues(856,张旭 ,男,1969-03-12,讲师,电子工程系)insert into teachervalues(825,王萍 ,女,1972-05-05,助教,计算机系)insert into teachervalues(831,刘冰 ,女,1958-08-14,助教,
5、电子工程系)insert into coursevalues(3-105,计算机导论,825)insert into coursevalues(3-245,操作系统,804)insert into coursevalues(6-166,数字电路,856)insert into coursevalues(9-888,高等数学,831)insert into scorevalues(103,3-245,86)insert into scorevalues(105,3-245,75)insert into scorevalues(109,3-245,68)insert into scorevalue
6、s(103,3-105,92)insert into scorevalues(105,3-105,88)insert into scorevalues(109,3-105,76)insert into scorevalues(101,3-105,64)insert into scorevalues(107,3-105,91)insert into scorevalues(108,3-105,78)insert into scorevalues(101,6-166,85)insert into scorevalues(107,6-166,79)insert into scorevalues(10
7、8,6-166,81)select * from studentselect * from teacherselect * from courseselect * from score -1、 查询 Student 表中的所有记录的 Sname、Ssex 和 Class 列。select sname,ssex,class from student-2、 查询教师所有的单位即不重复的 Depart 列。select distinct depart from teacher-3、 查询 Student 表的所有记录。select * from student-4、 查询 Score 表中成绩在 6
8、0 到 80 之间的所有记录。select * from score where degree between 60 and 80-5、 查询 Score 表中成绩为 85，86 或 88 的记录。select * from score where degree=85or degree=86or degree=88-6、 查询 Student 表中“95031”班或性别为“女” 的同学记录。select * from student where class=95031 or ssex=女-7、 以 Class 降序查询 Student 表的所有记录。select * from student
9、order by class desc-8、 以 Cno 升序、Degree 降序查询 Score 表的所有记录。select* from score order by cno ,degree desc-9、 查询“95031”班的学生人数。select count(sno) from student where class=95031-10、查询 Score 表中的最高分的学生学号和课程号。select sno,cno,degree from score where degree in(select max(degree) from score)-11、查询3-105号课程的平均分。sele
10、ct avg(degree) from score where cno=3-105-12、查询 Score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数。select avg(degree) from score where cno like3% and cno in (select cno from score group by cno having count(cno)5)-13、查询最低分大于 70，最高分小于 90 的 Sno 列。select sno from score where degree between 70 and 90-14、查询所有学生的 Sname、C
11、no 和 Degree 列。select sname,cno,degree from score,student where student.sno=score.sno-15、查询所有学生的 Sno、 Cname 和 Degree 列。select cname,student.sno,degree from score,student,course where student.sno=score.sno and o=o-16、查询所有学生的 Sname、Cname 和 Degree 列select sname,cname,degree from score,student,course whe
12、re student.sno=score.sno and o=o-17、查询“95033” 班所选课程的平均分。select 平均分= avg(degree) from course,student ,score where class=95033 and o=o and student.sno=score.sno-18、假设使用如下命令建立了一个 grade 表：-create table grade(low int,upp int,rank varchar(1)-insert into grade values(90,100,A)-insert into grade values(80,8
13、9,B)-insert into grade values(70,79,C)-insert into grade values(60,69,D)-insert into grade values(0,59,E)-现查询所有同学的 Sno、Cno 和 rank 列。select student.sno,cno,rank from score,student,grade where student.sno=score.sno and degree between low and upp -19、查询选修“3-105”课程的成绩高于 “109”号同学成绩的所有同学的记录。 /无关子查询select
14、score.sno,sname,ssex,sbirthday,class,o,cname,degree from score,student,course where student.sno=score.sno and o=o and o=3-105and degree(select degree from score where sno=109and cno=3-105)-20、查询 score 中选学多门课程的同学中分数为非最高分成绩的记录。select sno,cno,degree from score where degree not in (select max(degree)fro
15、m score group by cno) order by sno-21、查询成绩高于学号为“109” 、课程号为“3-105” 的成绩的所有记录。select * from score where degree(select degree from score where sno=109 and cno=3-105)-22、查询和学号为 108 的同学同年出生的所有学生的 Sno、Sname 和 Sbirthday 列。select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday)
16、from student where sno=108)-23、查询“ 张旭“教师任课的学生成绩。select sno,o,degree from score,course,teacher where o=o and course.tno=teacher.tno and tname=张旭-24、查询选修某课程的同学人数多于 5 人的教师姓名。select tname from teacher,course where teacher.tno=course.tno and o in (select cno from score group by cno having count(sno)5)-25
17、、查询 95033 班和 95031 班全体学生的记录。select * from student where class=95033 union select * from student where class=95031-26、查询存在有 85 分以上成绩的课程 Cno.select distinct cno from score where degree85-27、查询出“计算机系“教师所教课程的成绩表。select score.sno,o,degree from teacher,course ,score where teacher.tno=course.tno and o =o a
18、nd depart=计算机系 order by sno-28、查询“ 计算机系”与“电子工程系“ 不同职称的教师的 Tname 和 Prof。select tname,prof from teacher where depart=计算机系 and prof not in (select prof from teacher where depart=电子工程系) union select tname,prof from teacher where depart=电子工程系 and prof not in (select prof from teacher where depart=计算机系)-2
19、9、查询选修编号为“3-105“ 课程且成绩至少高于选修编号为 “3-245”的同学的 Cno、Sno和 Degree,并按 Degree 从高到低次序排序。select cno,sno,degree from score where cno=3-105 and degree any (select degree from score where cno=3-245) order by degree desc-30、查询选修编号为“3-105” 且成绩高于选修编号为“3-245”课程的同学的 Cno、Sno 和Degree.select cno,sno,degree from score wh
20、ere cno=3-105 and degree all (select degree from score where cno=3-245)-31、查询所有教师和同学的 name、sex 和 birthday.select name=tname,sex=tsex,birthday=tbirthday from teacher union select name=sname,sex=ssex,birthday=sbirthday from student-32、查询所有“女”教师和“女” 同学的 name、sex 和 birthday.select name=tname,sex=tsex,bi
21、rthday=tbirthday from teacher where tsex=女 union select name=sname,sex=ssex,birthday=sbirthday from student where ssex=女 -33、查询成绩比该课程平均成绩低的同学的成绩表。select * from score where degree=2-37、查询 Student 表中不姓“ 王”的同学记录。select * from student where sname not like 王%-38、查询 Student 表中每个学生的姓名和年龄。select sname,sage=
22、(2011-year(sbirthday) from student-39、查询 Student 表中最大和最小的 Sbirthday 日期值。select max(sbirthday) from student union select min(sbirthday) from student-40、以班号和年龄从大到小的顺序查询 Student 表中的全部记录。select sno,sname,ssex,class,sage=(2011-year(sbirthday) from student order by class desc,(2011-sbirthday) desc-41、查询“
23、男”教师及其所上的课程。select tname,tsex ,cname, depart from teacher,course where course.tno=teacher.tno and tsex=男-42、查询最高分同学的 Sno、Cno 和 Degree 列。select student.sno,cno,degree from student, score where student.sno=score.sno and degree in (select max(degree) from score)-43、查询和“李军”同性别的所有同学的 Sname.select s1.snam
24、e from student s1, student s2 where s1.ssex=s2.ssex and s2.sname=李军-44、查询和“李军”同性别并同班的同学 Sname.select s1.sname from student s1, student s2 where s1.ssex=s2.ssex and s2.sname=李军 and s1.class =s2.class-45、查询所有选修“计算机导论”课程的“男” 同学的成绩表。select score.sno,o ,degree from course,student,score where student.sno=
25、score.sno and o=o and ssex=男 and cname=计算机导论-46、查询 score 表中分数最高的学生的信息。 /多层嵌套select student.sno,sname,ssex,sbirthday,class from student, score where student.sno=score.sno and degree in (select max(degree) from score)-47、查询 score 表中的平均分在 80 分以上的学生信息。 /相关查询。无关查询select sno,sname,ssex,sbirthday,class from student where sno in( select sno from score group by sno having avg(degree)80)
