最完整的sql练习+答案.doc

练习题一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 varchar(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 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 studentvalues('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','助教','电子工程系')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 scorevalues('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('108','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表中成绩在60到80之间的所有记录select * from score where degree between 60 and 80--5、 查询Score表中成绩为85，86或88的记录select * from score where degree='85'or degree='86'or degree='88'--6、 查询Student表中“95031”班或性别为“女”的同学记录select * from student where class='95031' or ssex='女'--7、 以Class降序查询Student表的所有记录select * from student 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’号课程的平均分select avg(degree) from score where cno='3-105'--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数select avg(degree) from score where cno like'3%' 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、Cno和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 where 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,89,'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 score.sno,sname,ssex,sbirthday,class,o,cname,degree from score,student,course where student.sno=score.sno and o=o and o='3-105'and degree>(select degree from score where sno='109'and cno='3-105')--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录select sno,cno,degree from score where degree not in (select max(degree)from score group by cno) order by sno--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from score where degree>(select degree from score where 。