學(xué)生選課數(shù)據(jù)庫(kù)SQL語(yǔ)句練習(xí)題(詳細(xì)分解答案)

1、、設(shè)有一數(shù)據(jù)庫(kù)，包括四個(gè)表：學(xué)生表（Student）、課程表 （Course ）、成績(jī)表（Score ）以及教師信息表（Teacher）。 四個(gè)表的結(jié)構(gòu)分別如表1-1的表（一）表（四）所示，數(shù)據(jù)如表 的表（一）表（四）所示。用SQL語(yǔ)句創(chuàng)建四個(gè)表并完 成相 關(guān)題目。 表數(shù)據(jù)庫(kù)的表結(jié)構(gòu) 表()Student 屬性名 數(shù)據(jù)類(lèi)型 可否為 空 含義 Sno Char(3) 否 學(xué)號(hào)（主 鍵） Sn ame Char(8) 否 學(xué)生姓名 Ssex Char(2) 否 學(xué)生性別 Sbirthday datetime 可 學(xué)生出生年 月 Class Char(5) 可 學(xué)生所在班 級(jí) 表（二）Course

2、屬性名 數(shù)據(jù)類(lèi)型 可否為 空 含義 Cno Char(5) 否 課程號(hào)（主 鍵） Cn ame Varchar(10) 否 課程名稱(chēng) Tno Char(3) 否 教師編號(hào) （外鍵） 表（三）Score 屬性名 數(shù)據(jù)類(lèi)型 可否為 空 含義 Sno Char(3) 否 學(xué)鍵 Cno Char(5) 否 課程號(hào)（外 鍵） Degree Decimal(4,1) 可 成績(jī) 主碼：Sno+Cno 表（四）Teacher 屬性名 數(shù)據(jù)類(lèi)型 可否為 空 含義 Tno Char(3) 否 教師編號(hào) （主鍵） Tn ame Char(4) 否 教師姓名： Tsex Char(2) 否 教師性別一 Tbirthd

3、ay datetime 可 教師出生年 月 Prof Char(6) 可 職稱(chēng) Depart Varchar(10) 否 教師所在部 門(mén) 表數(shù)據(jù)庫(kù)中的數(shù)據(jù) 表Stude ()nt Sno Sn ame Ssex Sbirthday class 108 曾華 男 1977-09 01 95033 105 匡明 男 1975-10 02 95031 107 王麗 女 1976-01 23 95033 101 李軍 男 1976-02 20 95033 109 王芳 女 1975-02 10 95031 103 陸君 男 1974-06 03 95031 表（二）Course Cno Cn ame

4、Tno 3-105 計(jì)算機(jī)導(dǎo)論 825 3-245 操作系統(tǒng) 804 6-166 數(shù)字電路 856 9-888 高等數(shù)學(xué) 831 表（三）Score Sno Cno Degree 103 3-245 86 105 3-245 75 109 3-245 68 103 3-105 92 105 3-105 88 109 3-105 76 101 3-105 64 107 3-105 91 108 3-105 78 101 6-166 85 107 6-166 79 108 6-166 81 表（四）Teacher Tno Tn ame Tsex Tbirthday Prof Depart 804

5、李誠(chéng) 男 1958-12 02 副教 授 計(jì)算機(jī)系 856 張旭 男 1969-03 12 講師 電子工程 系 825 王萍 女 1972-05 05 助教 計(jì)算機(jī)系 831 劉冰 女 1977-08 14 助教 電子工程 系 -1V查詢(xún)Student表中的所有記錄的Sname、Ssex和Class歹ij。 select sn ame,ssex,class from stude nt; -2v查詢(xún)教師所有的單位即不重復(fù)的Depart列。 select disti net depart from Teacher; -3、查詢(xún)Student表的所有記錄。 select * from stude n

6、t; -4s查詢(xún)Score表中成績(jī)?cè)?0到80之間的所有記錄。 select * from score where degree betwee n 60 and 80; -5、查詢(xún)Score表中成績(jī)?yōu)?5，86或88的記錄。 select * from score where degree in(85,86,88); -6、查詢(xún)Student表中“95031班或性別為女”的同學(xué)記錄 select * from stude nt where class = 95031 or ssex 二女: -7、以Class降序查詢(xún)Student表的所有記錄。 select * from stude nt o

7、rder by class desc; -8、以Cno升序、Degree降序查詢(xún)Score表的所有記錄V select * from score order by cno degree desc; -9、查詢(xún)“95031班的學(xué)生人數(shù)。 select class,count(*) as 學(xué)生人數(shù) from student group by class hav ing class 二95031： -10、查詢(xún)Score表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)。（子查詢(xún)或 者排 序） select sno,cno ,degree, (select max(degree) from score) as maxs

8、core- 計(jì)算最高分 from score where degree= (select max(degree) from score); -11、查詢(xún)305,號(hào)課程的平均分。 select avg(degree) as avgdegree from score group by eno hav ing eno 二3105： -12x查詢(xún)Score表中至少有5名學(xué)生選修的并以3開(kāi)頭的課程的 平均 分?jǐn)?shù)。 select avg(degree) as avgdegree from score group by eno -按照 課 程分組取平均值 hav ing eno 二(select eno f

9、rom score group by eno hav ing count(*)=5)-至少有5名學(xué)生選修的課程 and eno like *3%*;-以3開(kāi)頭的課程 -13v查詢(xún)最低分大于70,最高分小于90的Sno列 select sno, max(degree)as maxdegree, min( degree) as min degree from Score group by sno hav ing max(degree)70 一 14、查詢(xún)所有學(xué)生的Snamev Cno和Degree列。 select sn ame,c no degree from stude nt join sco

10、re on stude nt.s no 二 score.s no; 一 15、查詢(xún)所有學(xué)生的Snox Cname和Degree列。 select sno,cn ame,degree from Score join course on Score.c no 二 course.c no; 一 16、查詢(xún)所有學(xué)生的Snamev Cname和Degree列。 select sn ame,c name,degree from stude nt join score on stude nt.s no 二 score.s no join course on Score.c no 二 course.c no;

11、 -17v查詢(xún)“95033班所選課程的平均分。 select avg(degree) as avgdegree from score where sno in (select sno from stude nt where class 二95033) 18、假設(shè)使用如下命令建立了一個(gè)grade表： create table grade(low in t(3),upp in t(3),ra nk char(1) insert into grade values(90,100,* A) insert into grade values(80,89,1 B*) insert into grade v

12、alues(70,79,1 C*) insert into grade values(60,69, D) insert into grade values(0,59, E) -現(xiàn)查詢(xún)所有同學(xué)的Snov Cno和rank列。 select sno,cno, (case whe n degree betwee n 90 and 100 the n TV whe ndegree between80 and89 thenB whe ndegree between70 and79 thenC whe ndegree between60 and69 thenD whe ndegree between0 a

13、nd 59 the n END) as rank from score; -19.查詢(xún)選修“305”課程的成績(jī)高于“ 109號(hào)同學(xué)成績(jī)的所有同學(xué)的 記錄。 select * from score where eno 二05a nd degree(select degree from score where sno 二109 and eno 二3-105); “20、查詢(xún)score中選學(xué)多門(mén)課程的同學(xué)中分?jǐn)?shù)為非最高分成績(jī)的 記 錄。 select * from score where sn o i n-選學(xué)多門(mén)課程的同學(xué)中分?jǐn)?shù)為非 最 高分成績(jī)的同學(xué)的全記錄 (select sno from s

14、core group by sno hav ing coun t(c no )1-選學(xué)多 門(mén)課程的同學(xué) in tersect-取交集為選學(xué)多門(mén)課程的同學(xué)中分?jǐn)?shù)為非最高分成績(jī)的同 學(xué)。 select disti net sno from score where sno not in(-分?jǐn)?shù)為非最高分 成績(jī) 的同學(xué) select sno from score where degree=(seleet max(degree) from score)- 分?jǐn)?shù)最高成績(jī)的同學(xué) -2K查詢(xún)score中選學(xué)多門(mén)課程的同學(xué)中分?jǐn)?shù)為非同課程最高分成績(jī) 的記錄。 方法1: select * from score w

15、here sno in-選學(xué)多門(mén)課程的同學(xué)中 分?jǐn)?shù) 為非同課程最高分成績(jī)的同學(xué)的全記錄 (select sno from score group by sno hav ing coun t(c no )1- 選學(xué)多 門(mén)課程的同學(xué) in tersect-取交集為選學(xué)多門(mén)課程的同學(xué)中分?jǐn)?shù)為非同課程最高分成績(jī) 的同學(xué)。 select disti net sno from score where sno not in(- 非同課程分?jǐn)?shù)最 高成績(jī)的同學(xué) select disti net sno from score where degree in (- 同課程分?jǐn)?shù)最高 成績(jī)的同學(xué) select max(

16、degree)from score group by eno)-同課程分?jǐn)?shù)最高成 績(jī) 方法2： select * from score where sno in-選學(xué)多門(mén)課程的同學(xué)中 分 數(shù)為非同課程最高分成績(jī)的同學(xué)的全記錄 (select sno from score group by sno hav ing eoun t(c no )1- 選學(xué) 多門(mén)課程的同學(xué) in tersect -取交集為選學(xué)多門(mén)課程的同學(xué)中分?jǐn)?shù)為同課程非最高分成績(jī) 的同學(xué) select disti net sno from score where sno not in - 選出非同課程最 高分成績(jī)的同學(xué) (selec

17、t dist inct sno from score as si where degree=(select max(degree) from score as s2 where sl.c no=s2.c no group by cn o);-使用關(guān)聯(lián)子查詢(xún)選出同課程最 高分成績(jī)的同學(xué) -22x查詢(xún)1975年之后出生的學(xué)生的所學(xué)課程以及成績(jī)。 select sn ame,C name,degree from stude nt join score on stude nt.s no 二 score.s no join course on score.c no 二 course.c no where

18、 sbirthday=,1975-01-01: -23、查詢(xún)和學(xué)號(hào)為107的同學(xué)同年出生的所有學(xué)生的Sno、 Sname 和 Sbirthday 列。 select sno,sn ame,sbirthday from stude nt where datepart(year,sbirthday)= (select datepart(year,sbirthday) from stude nt where sno 二107)一 學(xué) 號(hào)為107的同學(xué)的出生年份 and sno not排除學(xué)號(hào)為107的同學(xué) -24、查詢(xún) 張旭”教師任課的學(xué)生成績(jī)。 select degree from score w

19、here eno 二 (select eno from course join teacher on teacher.t no 二 course.t no where tname 二張旭);一張 旭老師所任課程 -25.查詢(xún)選修某課程的同學(xué)人數(shù)多于5人的教師姓名 select tn ame from teacher 11/16 joi n course on teacher.t no 一 course.t no where eno in (select eno from score group by eno having count(*)5);-多于5名同學(xué)選修的課程 -26x查詢(xún)95033班

20、和95031班全體學(xué)生的記錄。 select * from stude nt where class in (95033；95031); -27v查詢(xún)存在有85分以上成績(jī)的課程Cno. select disti net cno from score where degree85; -2-計(jì)算機(jī)系教師 的教師編號(hào) -29、查詢(xún) 計(jì)算機(jī)系”與電子工程系”不同職稱(chēng)的教師的Tname和 Pro仁 select tname,prof from teacher where depart in(計(jì)算機(jī)系T電子工 程系計(jì)算機(jī)系”與電子工程系”所有教師Tname和Prof and prof not in -計(jì)算

21、機(jī)系”與 電子工程系”不同職稱(chēng)的教師Prof (select prof from teacher where depart =計(jì)算機(jī)系 in tersect select prof from teacher where depart =* 電子工程系)-計(jì)算機(jī)系”與 電 子工程系”相同職稱(chēng)的教師Prof -30、查詢(xún)選修編號(hào)為“305課程且成績(jī)至少高于一個(gè)選修編號(hào)為“345” 的同學(xué)的Cnox Sno和Degree,并按Degree從高到低次 序排序。 select eno,sno ,degree from score where eno二？105-選修編號(hào)為“305”課程的同學(xué) and de

22、greeany -大于任意一個(gè)選修編號(hào)為345的同學(xué)的成績(jī) (select degree from score where eno 二3245)- 選修編號(hào)為345” 的同學(xué)的成績(jī) order by degree desc -31、查詢(xún)選修編號(hào)為“305課程且成績(jī)高于所有選修編號(hào)為“3 245”的同學(xué)的CnOsSno和Degree,并按Degree從高到低次序掃E序。 select eno,sno ,degree from score where eno二?3-105-選修編號(hào)為“305”課程的同學(xué) and degreeall -大于所有選修編號(hào)為“345”的同學(xué)的成績(jī) (select deg

23、ree from score where eno 22*3-245)-選修編號(hào)為345” 的同學(xué)的成績(jī) order by degree desc 14 / -32x查詢(xún)所有教師和同學(xué)的name、sex和birthday. select sn ame as n ame,ssex as sex,sbirthday as birthday from stude nt union select tn ame as n ame,tsex as sex,tbirthday as birthday from teacher -33x查詢(xún)所有 女”教師和 女乃同學(xué)的names sex和birthday. se

24、lect sn ame as n ame,ssex as sex,sbirthday as birthday from student where ssex=* 女 union select tn ame as n ame,tsex as sex,tbirthday as birthday from teacher where tsex=* 女 -34、查詢(xún)成績(jī)比該課程平均成績(jī)低的同學(xué)的成績(jī)表。 select * from score as si where degree1 ； select Class,COUNT(*) from Stude nt where Ssex=男g(shù)roup by C

25、lass havi ng COUNT(*)=2 ； -3 select max(year(sbirthday)as max, min( year(sbirthday) as min from stude nt; -41、以班號(hào)和年齡從大到小的順序查詢(xún)Student表中的全部記 錄。 select * from stude nt order by class desc,Sbirthday -42、查詢(xún)男”教師及其所上的課程。 select tn ame,tsex,c name from teacher left join course on course.t no 二 teacher.t no

26、where tsex= 男 一43、查詢(xún)最高分同學(xué)的Snox Cno和Degree列。 select stude nt.s no,cno ,degree from stude nt join Score on Score.s no 二 stude nt.s no where degree=(select max(degree) from score); -44x查詢(xún)和李軍”同性別的所有同學(xué)的Sname. select sn ame from stude nt where ssex=-與李軍同性別的同學(xué)姓名 (select ssex from stude nt where sn ame=* 李軍)一李軍的性另 U and sname not in(李軍)