綜合練習(xí)(二)-習(xí)題.doc

綜合練習(xí)（二）一、用SQL語句創(chuàng)建如下三張表：學(xué)生表（Student）、課程表（Course）和學(xué)生選課表（SC），三張表結(jié)構(gòu)如下：注：一、二兩題需要將命令放入*.sql腳本中，然后使用腳本進(jìn)行創(chuàng)建表及數(shù)據(jù)的錄入Student表結(jié)構(gòu)列 名說 明數(shù)據(jù)類型約 束sno 學(xué)號字符串，長度為7主鍵sname姓名字符串，長度為10非空ssex性別字符串，長度為2取男或女 sage年齡整數(shù)取值1545sdept所在系字符串，長度為20默認(rèn)為計(jì)算機(jī)系 Course表結(jié)構(gòu)列 名說 明數(shù)據(jù)類型約 束cno課程號字符串，長度為10主鍵cname課程名字符串，長度為20非空ccredit學(xué)分整數(shù)取值大于0semster學(xué)期整數(shù)取值大于0period學(xué)時整數(shù)取值大于0SC表結(jié)構(gòu)列 名說 明數(shù)據(jù)類型約 束sno學(xué)號字符串，長度為7主鍵，引用Student的外鍵cno課程號字符串，長度為10主鍵，引用Course的外鍵grade成績整數(shù)取值0100答案：1、Student表CREATE TABLE Student ( sno VARCHAR2(7) PRIMARY KEY, sname VARCHAR2 (10) NOT NULL, ssex VARCHAR2 (2) CHECK(ssex=男 OR ssex=女), sage NUMBER(2) CHECK(sage=15 AND sage0), semester NUMBER(2) CHECK(semester0), period NUMBER(3) CHECK(period0), CONTRAINT course_cno_pk PRIMARY KEY(cno) )3、SC表CREATE TABLE SC(sno char(7) NOT NULL, cno char(10) NOT NULL,grade tinyint CHECK(grade=0 AND grade=100),CONTRAINT sc_sno_cno_pk PRIMARY KEY(sno,cno),CONTRAINT student_sno_fk FOREIGN KEY(sno) REFERENCE Student(sno),CONTRAINT course_cno_fk FOREIGN KEY(cno) REFERENCE Course (cno)二、使用SQL語句分別向Student、Course、SC表中加入如下數(shù)據(jù)：Student表數(shù)據(jù)snoSnamessexsagesdept9512101李勇男19計(jì)算機(jī)系9512102劉晨男20計(jì)算機(jī)系9512103王敏女20計(jì)算機(jī)系9512104張立男22信息系9512105錢小平女21信息系9512106吳賓女20數(shù)學(xué)系9512107王大力男19數(shù)學(xué)系Course表數(shù)據(jù)cnocnameccreditsemesterc01計(jì)算機(jī)文化學(xué)31c02Java85c03計(jì)算機(jī)網(wǎng)絡(luò)43c04數(shù)據(jù)庫基礎(chǔ)56c05高等數(shù)學(xué)62c06數(shù)據(jù)結(jié)構(gòu)74C07VB34SC 表數(shù)據(jù)snocnograde9512101c01909512101c029512102c01789512102c03669512103c04829512104c0675三、完成如下查詢1、查詢?nèi)w學(xué)生的學(xué)號和姓名 SELECT sno, sname from Student 2、查詢?nèi)w學(xué)生的姓名、學(xué)號和所在系 SELECT sname, sno, sdept from Student3、查詢?nèi)w學(xué)生的姓名及其出生年份l 分析：由于Student表中只記錄了學(xué)生的年齡，而沒有記錄學(xué)生的出生年份，所以需要經(jīng)過計(jì)算得到學(xué)生的出生年份，即用當(dāng)前年減去年齡，得到出生年份。 SELECT sname, 2011 sage FROM Student 或SELECT sname, to_number(to_char(sysdate,yyyy) sage FROM Student4、查詢?nèi)w學(xué)生的姓名和出生年份，并在出生年份列前加入一個列，此列的每行數(shù)據(jù)均為“Year of Birth”常量值 SELECT snamem, Year of Birth, to_number(to_char(sysdate,yyyy) sage FROM Student5、在選課表（SC）中查詢有哪些學(xué)生選修了課程，并列出學(xué)生的學(xué)號 SELECT sno FROM SC 在結(jié)果集中會有許多重復(fù)的行（實(shí)際上一個學(xué)生選修了多少門課程，其學(xué)號就會在結(jié)果集中重復(fù)出現(xiàn)多少次）。 使用DISTINCT關(guān)鍵字就可以去掉結(jié)果集中的重復(fù)行。去掉結(jié)果集中的重復(fù)行 SELECT DISTINCT sno FROM SC6、查詢計(jì)算機(jī)系全體學(xué)生的姓名 SELECT sname FROM Student WHEER sdept=計(jì)算機(jī)系 7、查詢所有年齡在20歲以下的學(xué)生的姓名及年齡 SELECT sname, sage FROM Student WHEER sage208、查詢考試成績不及格的學(xué)生的學(xué)號l 當(dāng)一個學(xué)生有多門不極格課程時，只列出一個學(xué)號。 SELECT DISTINCT sno FROM SC WHERE grade60 and grade is not null9、查詢年齡在2023歲之間的學(xué)生的姓名、所在系和年齡 SELECT sname, sdept, sage FROM Student WHERE sage BETWEEN 20 AND 2310、查詢年齡不在2023之間的學(xué)生的姓名、所在系和年齡 SELECT sname, sdept, sage FROM Student WHERE sage NOT BETWEEN 20 AND 2311、查詢信息系、數(shù)學(xué)系、和計(jì)算機(jī)系學(xué)生的姓名和性別 SELECT sname, ssex, FROM Student WHERE sdept IN (信息系,數(shù)學(xué)系,計(jì)算機(jī)系)12、查詢既不屬于信息系、數(shù)學(xué)系、也不屬于計(jì)算機(jī)系學(xué)生的姓名和性別 SELECT sname, ssex, FROM Student WHERE sdept NOT IN (信息系,數(shù)學(xué)系,計(jì)算機(jī)系)13、查詢姓“張”的學(xué)生的詳細(xì)信息 SELECT * FROM Student WHERE sname LIKE 張% 14、查詢學(xué)生表中姓“張”、姓“李”、姓“劉”的學(xué)生的情況SELECT * FROM Student WHERE sname LIKE 張% OR sname LIKE 李% OR sname LIKE 劉%15、查詢名字中第2個字為“小”或“大”字的學(xué)生姓名和學(xué)號 SELECT sname,sno FROM Student WHERE sname LIKE _小% OR sname LIKE _大%16、查詢所有不姓“劉”的學(xué)生 SELECT sname,sno FROM Student WHERE sname NOT LIKE 劉% 17、從學(xué)生表中查詢學(xué)號的最后一位不是2、3、5的學(xué)生的情況 SELECT * FROM Student WHERE sno NOT LIKE %2 AND NOT LIKE %3 AND NOT LIKE %518、查詢無考試成績的學(xué)生的學(xué)號和相應(yīng)的課程號 SELECT sno, cno FROM SC WHERE grade IS NULL19、查詢所有有考試成績的學(xué)生的學(xué)號和課程號 SELECT sno, cno FROM SC WHERE grade IS NOT NULL20、查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生的姓名 SELECT sname FROM Student WHERE sdept =計(jì)算機(jī)系 AND sage 332、查詢選課門數(shù)等于或大于4門的學(xué)生的平均成績和選課門數(shù) SELECT sno, AVG(grade) 平均成績, COUNT(cno) 修課門數(shù) FROM SC GROUP BY sno HAVING COUNT(cno) = 433、查詢每個學(xué)生的情況及其選課的情況l 當(dāng)不給表指定別名時，需要使用原表名SELECT Student.sno, sname, ssex, sge, sdept, cno, grade FROM Student ,SCWHERE Student.sno=SC.sno34、查詢計(jì)算機(jī)系學(xué)生的選課情況，要求列出學(xué)生的名字、所修課的課程號和成績。 SELECT sname, cno, grade FROM Student s , SC a WHERE s.sno=a.sno AND s.sdept = 計(jì)算機(jī)系35、查詢信息系選修Java課程的學(xué)生的成績，要求列出學(xué)生姓名、課程名和成績l 此查詢涉及了三張表，每連接一張表，就需要加入一個連接條件用于去掉產(chǎn)生的笛卡爾積。l 在該查詢中，雖然所要查詢的列和記錄的選擇條件均與SC表無關(guān)，但還是用了三張表，原因是Student和Course表沒有可以進(jìn)行連接的列（語義相同的列），因此，這兩張表的連接需要借助于第三張表SC表。 SELECT sname, cname, grade FROM Student s , SC a, Course c WHERE s.sno=a.sno AND o=o AND s.sdept=信息系 AND ame=Java 36、查詢學(xué)生的選課情況，包括選修課程的學(xué)生和沒有選修課程的學(xué)生 l 如果Student表中有沒有選課的學(xué)生時，也同樣輸出Student表中的數(shù)據(jù)，但是最后輸出列的cno和grade的值均為NULL，因?yàn)樵赟C表中沒有與之對應(yīng)的記錄，即對于不滿足連接條件的結(jié)果在相應(yīng)的列上放置NULL值。 SELECT s.sno, sname, cno, grade FROM Student s, SC a WHERE s.sno = a.sno(+)37、查詢與“劉晨”在同一個系的學(xué)生l 分析：實(shí)際的查詢過程如下：a. 確定“劉晨”所在的系，即執(zhí)行子查詢： SELECT sdept FROM Student WHERE sname=劉晨 b. 在子查詢的結(jié)果中查找所有在此系學(xué)習(xí)的學(xué)生：SELECT sno, sname, sdept FROM Student WHERE sdept IN (計(jì)算機(jī)系)l 通過結(jié)果可以看到，查詢結(jié)果中也有“劉晨”。如果不希望“劉晨”出現(xiàn)在查詢結(jié)果中，如何實(shí)現(xiàn)？ SELECT sno, sname, sdept FROM Student WHERE sdept IN(SELECT sdept FROM Student WHERE sname=劉晨) AND sname !=劉晨注意：這里的外層sname !=劉晨不需要使用表名前綴，因?yàn)閷τ谕鈱硬樵儊碚f，其表名沒有二義性。38、查詢成績大于90分的學(xué)生的學(xué)號和姓名 SELECT sno, sname FROM Student WHERE sno IN (SELECT sno FROM SC WHERE grade 90)n 使用多表連接的方式實(shí)現(xiàn)： SELECT sno, sname FROM Student s , SC a WHERE s.sno=a.sno AND a.grade 9039、查詢選修了“數(shù)據(jù)庫基礎(chǔ)”課程的學(xué)生的學(xué)號和姓名 SELECT sno, sname FROM Student WHERE sno IN ( SELECT sno FROM SC WHERE cno IN (SELECT cno FROM Course WHERE cname=數(shù)據(jù)庫基礎(chǔ))n 使用多表連接方式實(shí)現(xiàn)： SELECT s.sno, sname FROM Student s, SC a, Course c WHERE s.sno=a.sno AND o=o AND ame=數(shù)據(jù)庫基礎(chǔ) 40、查詢選修了課程“c02”且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績 SELECT sno, grade FROM SC WHERE cno=c02 AND grade ( SELECT AVG(grade) FROM SC WHERE cno=c02) 41、查詢選修了課程“c01”的學(xué)生的姓名和所在系SELECT sname, sdept FROM Student WHERE sno IN ( SELECT sno FROM SC WHERE cno=c01)42、查詢數(shù)學(xué)系成績在80分以上的學(xué)生的學(xué)號、姓名SELECT sno, sname FROM StudentWHERE sno IN (SEL