1、SQL查詢練習(xí) 找了好久的查詢練習(xí), 附有答案 CREATE TABLE STUDENT (SNO VARCHAR(3 NOT NULL, SNAME VARCHAR(4 NOT NULL, SSEX VARCHAR(2 NOT NULL, SBIRTHDAY DATETIME, CLASS VARCHAR(5 go CREATE TABLE COURSE (CNO VARCHAR(5 NOT NULL, CNAME VARCHAR(10 NOT NULL, TNO VARCHAR(10 NOT NULL go CREATE TABLE SCORE (SNO VARCHAR(3 NOT NUL

2、L, CNO VARCHAR(5 NOT NULL, DEGREE NUMERIC(10, 1 NOT NULL go CREATE TABLE TEACHER (TNO VARCHAR(3 NOT NULL, TNAME VARCHAR(4 NOT NULL, TSEX VARCHAR(2 NOT NULL, TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6, DEPART VARCHAR(10 NOT NULL INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS VALUES (108 ,'曾

3、華' ,'男' ,1977-09-01,95033; INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS VALUES (105 ,'匡明' ,'男' ,1975-10-02,95031; INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS VALUES (107 ,'王麗' ,'女' ,1976-01-23,95033; INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRT

4、HDAY,CLASS VALUES (101 ,'李軍' ,'男' ,1976-02-20,95033; INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS VALUES (109 ,'王芳' ,'女' ,1975-02-10,95031; INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS VALUES (103 ,'陸君' ,'男' ,1974-06-03,95031; GO INSERT I

5、NTO COURSE(CNO,CNAME,TNOVALUES ('3-105' ,'計(jì)算機(jī)導(dǎo)論',825 INSERT INTO COURSE(CNO,CNAME,TNOVALUES ('3-245' ,'操作系統(tǒng)' ,804; INSERT INTO COURSE(CNO,CNAME,TNOVALUES ('6-166' ,'數(shù)據(jù)電路' ,856; INSERT INTO COURSE(CNO,CNAME,TNOVALUES ('9-888' ,'高等數(shù)學(xué)' ,10

6、0; GO INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (103,'3-245',86; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (105,'3-245',75; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (109,'3-245',68; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (103,'3-105',92; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES

7、 (105,'3-105',88; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (109,'3-105',76; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (101,'3-105',64; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (107,'3-105',91; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (108,'3-105',78; INSERT INTO SCORE(

8、SNO,CNO,DEGREEVALUES (101,'6-166',85; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (107,'6-106',79; INSERT INTO SCORE(SNO,CNO,DEGREEVALUES (108,'6-166',81; GO INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART VALUES (804,'李誠(chéng)','男','1958-12-02','副教授

9、','計(jì)算機(jī)系' INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART VALUES (856,'張旭','男','1969-03-12','講師','電子工程系' INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART VALUES (825,'王萍','女','1972-05-05','助教','計(jì)算

10、機(jī)系' INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART VALUES (831,'劉冰','女','1977-08-14','助教','電子工程系' 1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。 2、 查詢教師所有的單位即不重復(fù)的Depart列。 3、 查詢Student表的所有記錄。 4、 查 詢Score表中成績(jī)?cè)?0到80之間的所有記錄。 5、 查詢Score表中成績(jī)?yōu)?5，86或88的記錄。 6、 查詢

11、Student表中“95031”班或性別為“女”的同學(xué)記錄。 7、 以Class降序查詢Student表的所有記錄。 8、 以Cno升序、Degree降序查詢Score表的所有記錄。 9、 查詢“95031”班的學(xué)生人數(shù)。 10、查詢Score表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)。 11、查詢3-105號(hào)課程的平均分。 12、查詢Score表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)。 13、查詢最低分大于70，最高分小于90的Sno列。 14、查詢所有學(xué)生的Sname、Cno和Degree列。 15、查詢所有學(xué)生的Sno、Cname和Degree列。 16、查詢所有學(xué)生的 Sname、Cna

12、me和Degree列。 17、查詢“95033”班所選課程的平均分。 18、假設(shè)使用如下命令建立了一個(gè)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

13、into grade values(0,59,'E' commit; 現(xiàn)查詢所有同學(xué)的Sno、Cno和rank列。 19、查詢選修“3-105”課程的成績(jī)高于“109”號(hào)同學(xué)成績(jī)的所有同學(xué)的記錄。 20、查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績(jī)的記錄。 21、查詢成績(jī)高于學(xué)號(hào)為“109”、課程號(hào)為“3-105”的成績(jī)的所有記錄。 22、查詢和學(xué)號(hào)為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。 23、查詢“張旭“教師任課的學(xué)生成績(jī)。 24、查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名。 25、查詢95033班和95031班全體學(xué)生的記

14、錄。 26、查詢存在有85分以上成績(jī)的課程Cno. 27、查詢出 “計(jì)算機(jī)系“教師所教課程的成績(jī)表。 28、查詢“計(jì)算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof。 29、查詢選修編號(hào)為“3-105“課程且成績(jī)至少高于選修編號(hào)為“3-245”的同學(xué)的Cno、Sno和Degree,并按Degree從高到低次序排序。 30、查詢選修編號(hào)為“3-105”且成績(jī)高于選修編號(hào)為“3-245”課程的同學(xué)的Cno、Sno和Degree. 31、查詢所有教師和同學(xué)的 name、sex和birthday. 32、查詢所有“女”教師和“女”同學(xué)的name、sex和bir thday. 33、查詢成績(jī)

15、比該課程平均成績(jī)低的同學(xué)的成績(jī)表。 34、查詢所有任課教師的Tname和Depart. 35 查詢所有未講課的教師的Tname和Depart. 36、查詢至少有2名男生的班號(hào)。 37、查詢Student表中不姓“王”的同學(xué)記錄。 38、查詢Student表中每個(gè)學(xué)生的姓名和年齡。 39、查詢Student表中最大和最小的Sbirthday日期值。 40、以班號(hào)和年齡從大到小的順序查詢Student表中的全部記錄。 41、查詢“男”教師及其所上的課程。 42 、查詢最高分同學(xué)的Sno、 Cno和Degree列。 43、查詢和“李軍”同性別的所有同學(xué)的Sname. 44、查詢和“李軍”同性別并同班

16、的同學(xué)Sname. 45、查詢所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績(jī)表 不好意思哈，時(shí)間太長(zhǎng)給忘記了，下面是參考答案： SQL語(yǔ)句練習(xí)題參考答案 1、 select Sname,Ssex,Class from Student; 2、 select distinct depart from teacher; 3、 select Sno as '學(xué)號(hào)',Sname as '姓名',Ssex as '性別',Sbirthday as'出生日期',Class as'班號(hào)'from student; 或 select

17、Sno as 學(xué)號(hào),Sname as 姓名,Ssex as 性別,Sbirthday as 出生日期,Class as 班號(hào) from student; 4、 select * from score where degree between 60 and 80; 或select * from score where degree>=60 and degree<=80; 5、 select * from score where degree in (85,86,88; 6、 select * from student where class='95031'or Sse

18、x='女' 7、 select * from student order by class desc; 8、 select * from score order by cno asc ,degree desc; 或select * from score order by cno ,degree desc; 9、 select count(* as CNT from student where class='95031' 10、select Sno as '學(xué)號(hào)',cno as '課程號(hào)', degree as '最高分&#

19、39; from score where degree=(select max(degree from score 11、select avg(degreeas 課程平均分 from score where cno='3-105' 12、 select cno,avg(degree from score where cno like'3%'group by cno having count(* >5; 13、select Sno from score group by Sno having min(degree>70 and max(degree&l

20、t;90; 14、select student.Sname,score.Cno,score.degree from student,score where student.Sno=score.Sno group by sno; 15、select x.Sno,y.Cname,x.degree from score x,course y where x.Cno=y.Cno; 16、select x.Sname,y.Cname,z.degree from student x,course y,score z where x.Sno=z.Sno and z.Cno=y.Cno; 17、 select

21、 o,avg(y.degree from student x,score y where x.sno=y.sno and x.class='95033' group by o; 18、select Sno,Cno,rank from score,grade where degree between low and upp order by rank; 19、select distinct o,x.sno,x.degree from score x,score y where o = '3-105' and x.degree > y.degree and y

22、.sno='109' and o='3-105' 20、 1,查詢成績(jī)非本科最高 select di stinct * from score b where degree <(select max(degree from score a where o=o; 2,查詢成績(jī)非本科最高并且選2門以上的學(xué)生的成績(jī): 21、select o,x.sno,x.degree from score x,score y where x.degree > y.degree and y.sno='109'and o='3-105' sel

23、ect cno,sno,degree from score where degree > (select degree from score where sno='109' and cno='3-105' 22、 select sno,sname,sbirthday from student where to_char(sbirthday,'yyyy'=(select to_char(sbirthday,'yyyy' from student where sno='108' 23、select cno,sno

24、,degree from score where cno=(select o from course x,teacher y where x.tno=y.tno and y.tname='張旭' 24、select tname from t eacher where tno in(select x.tno from course x,score y where o group by x.tno having count(x.tno>5; 25、select * from student where class in('95033','95031&#

25、39; 26、select distinct cno from score where degree in (select degree from score where degree>85; 27、select * from score where cno in(select o from course x,teacher y where y.tno=x.tno and y.depart='計(jì)算機(jī)系' 28、select tname,prof from teacher where depart='計(jì)算機(jī)系' and prof not in (select

26、 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 desc; 30、select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245

27、' 31、select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student; 32、select tname,tsex,tbirthday from teacher where tsex='女' union select sname,ssex,sbirthday from student where ssex='女' 33、select * from score a where degree<(select avg(degree from

28、score b where o=o; 34、 select tname,depart from teacher a where exists (select * from course b where a.tno=b.tno; 35、select tname,depart from teacher a where not exists (select * from course b where a.tno=b.tno; 36、select class from student where ssex='男'group by class having count(*>=2; 37、 select * from student where sname not