最完整的sql練習(xí)答案

1、練習(xí)題一create database mydbgouse mydbcreate table student(-學(xué)號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(-學(xué)號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('

4、107','王麗','女','1976-01-23','95033')insert into studentvalues('101','李軍','男','1976-02-20','95033')insert into studentvalues('109','王芳','女','1975-02-10','95031')insert into studentvalues

5、('103','陸君','男','1974-06-03','95031')insert into teachervalues('804','李誠','男','1958-12-02','副教授','計算機(jī)系')insert into teachervalues('856','張旭','男','1969-03-12','講師','電子工程

6、系')insert into teachervalues('825','王萍','女','1972-05-05','助教','計算機(jī)系')insert into teachervalues('831','劉冰','女','1958-08-14','助教','電子工程系')insert into coursevalues('3-105','計算機(jī)導(dǎo)論','82

7、5')insert into coursevalues('3-245','操作系統(tǒng)','804')insert into coursevalues('6-166','數(shù)字電路','856')insert into coursevalues('9-888','高等數(shù)學(xué)','831')insert into scorevalues('103','3-245','86')insert into scor

8、evalues('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

9、','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 s

10、corevalues('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、 查詢教師所有的單位即不重復(fù)的Depa

11、rt列。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表中成績?yōu)?5，86或88的記錄。select * from score where degree='85'or degree='86'or degree='88'-6、 查詢Student表中“95031”班或

12、性別為“女”的同學(xué)記錄。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”班的學(xué)生人數(shù)。select count(sno) from student where class='95031

13、'-10、查詢Score表中的最高分的學(xué)生學(xué)號和課程號。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名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)。select avg(degree) from score where cno like'3%' and cno in (select cn

14、o from score group by cno having count(cno)>5)-13、查詢最低分大于70，最高分小于90的Sno列。select sno from score where degree between 70 and 90-14、查詢所有學(xué)生的Sname、Cno和Degree列。select sname,cno,degree from score,student where student.sno=score.sno-15、查詢所有學(xué)生的Sno、Cname和Degree列。select cname,student.sno,degree from score,s

15、tudent,course where student.sno=score.sno and o=o-16、查詢所有學(xué)生的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

16、-18、假設(shè)使用如下命令建立了一個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')-現(xiàn)查詢所

17、有同學(xué)的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”號同學(xué)成績的所有同學(xué)的記錄。/無關(guān)子查詢select score.sno,sname,ssex,sbirthday,class,o,cname,degree from score,student,course where student.sno=score.sno and o=o an

18、d o='3-105'and degree>(select degree from score where sno='109'and cno='3-105')-20、查詢score中選學(xué)多門課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄。select sno,cno,degree from score where degree not in (select max(degree)from score group by cno) order by sno-21、查詢成績高于學(xué)號為“109”、課程號為“3-105”的成績的所有記錄。select * fr

19、om score where degree>(select degree from score where sno='109' and cno='3-105')-22、查詢和學(xué)號為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno='108')-23、查詢“張旭“教師任課的學(xué)生成績。select sno,

20、o,degree from score,course,teacher where o=o and course.tno=teacher.tno and tname='張旭'-24、查詢選修某課程的同學(xué)人數(shù)多于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、查詢95033班和95031班全體學(xué)生的記錄。select * from student w

21、here class='95033' union select * from student where class='95031'-26、查詢存在有85分以上成績的課程Cno.select distinct cno from score where degree>85-27、查詢出“計算機(jī)系“教師所教課程的成績表。select score.sno,o,degree from teacher,course ,score where teacher.tno=course.tno and o =o and depart='計算機(jī)系' order

22、 by sno-28、查詢“計算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof。select tname,prof from teacher where depart='計算機(jī)系' 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=&#

23、39;計算機(jī)系')-29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學(xué)的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”課程的同學(xué)的Cno、Sno和Degree.selec

24、t cno,sno,degree from score where cno='3-105' and degree >all (select degree from score where cno='3-245')-31、查詢所有教師和同學(xué)的name、sex和birthday.select name=tname,sex=tsex,birthday=tbirthday from teacher union select name=sname,sex=ssex,birthday=sbirthday from student-32、查詢所有“女”教師和“女”同學(xué)的

25、name、sex和birthday.select name=tname,sex=tsex,birthday=tbirthday from teacher where tsex='女' union select name=sname,sex=ssex,birthday=sbirthday from student where ssex='女'-33、查詢成績比該課程平均成績低的同學(xué)的成績表。select * from score where degree<any(select avg(degree) from score group by cno)-34、查

26、詢所有任課教師的Tname和Depart.select tname,depart from teacher-35 查詢所有未講課的教師的Tname和Depart. select tname,depart from teacher,course where teacher.tno=course.tno and cno in (select cno from course where not exists (select * from score where cno=o)-36、查詢至少有2名男生的班號。select class from student where ssex='

27、男' group by class having count(ssex)>=2-37、查詢Student表中不姓“王”的同學(xué)記錄。select * from student where sname not like '王%'-38、查詢Student表中每個學(xué)生的姓名和年齡。select sname,sage=(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、查詢“男”教師及其所上的課程。select tname,tsex ,cname, depart from teacher,course where course.tno=teac