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

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