Sql試題一.有一個“學(xué)生－課程”數(shù)據(jù)庫，數(shù)據(jù)庫中包括三個表：(1)“學(xué)生”表Student由學(xué)號（Sno）、姓名（Sname）、性別（Ssex）、年齡（Sage）、所在系（Sdept）五個屬性組成，可記為：Student(Sno,Sname,Ssex,Sage,Sdept)Sno為關(guān)鍵字。(2)“課程”表Course由課程號（Cno）、課程名（Cname）、先修課號（Cpno）、學(xué)分（Ccredit）四個屬性組成，可記為：Course(Cno,Cname,Cpno,Ccredit)Cno為關(guān)鍵字。(3)“學(xué)生選課”表SC由學(xué)號（Sno）、課程號（Cno）、成績（Grade）三個屬性組成，可記為：SC(Sno,Cno,Grade)(SNO,CNO)為關(guān)鍵字。完成下列SQL語句：建立一個“學(xué)生”表Student，它由學(xué)號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成，其中學(xué)號屬性不能為空，并且其值是唯一的。向Student表增加“入學(xué)時間”列，其數(shù)據(jù)類型為日期型。3、刪除Student表4、查詢?nèi)w學(xué)生的學(xué)號與姓名5、查詢?nèi)w學(xué)生的詳細(xì)記錄6、查所有選修過課的學(xué)生的學(xué)號7、查所有年齡在20歲以下的學(xué)生姓名及其年齡8、查考試成績有不及格的學(xué)生的學(xué)號9、查詢年齡在20至23歲之間的學(xué)生的姓名、系別、和年齡10、查所有姓劉的學(xué)生的姓名、學(xué)號和性別11、查姓“歐陽”且全名為三個漢字的學(xué)生的姓名12、查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)的降序排列13、計算1號課程的學(xué)生平均成績14、查詢學(xué)習(xí)1號課程的學(xué)生最高分?jǐn)?shù)15、查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生16、將一個新學(xué)生記錄（學(xué)號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入Student表中17、將學(xué)生95001的年齡改為22歲18、將計算機科學(xué)系全體學(xué)生的成績置零19、刪除學(xué)號為95019的學(xué)生記錄20、刪除計算機科學(xué)系所有學(xué)生的選課記錄二.設(shè)有一個學(xué)生課程數(shù)據(jù)庫，包括學(xué)生關(guān)系表Student、課程關(guān)系表Course、選修關(guān)系表SC，圖1所示：表Student學(xué)號Sno姓名Sname性別Ssex年齡Sage所在系Sdept95001李勇男20計算機系95002劉晨女19自動化系95003王敏女18機械工程系95004張立男21電子系表Course課程號Cno課程名Cname先行課Cpno學(xué)分Ccredit1數(shù)據(jù)庫542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語言64表SC學(xué)號Sno課程號Cno成績Grade9500119295001285950013889500229195002380圖1

學(xué)生-課程數(shù)據(jù)庫寫出完成下列要求的SQL語句：查詢所有年齡在20歲（包括20歲）以下的學(xué)生信息。2）查詢姓“劉”的學(xué)生信息。3）查詢先行課為6的課程名稱。4）查詢課程學(xué)分大于3的課程號和課程名稱。5）在表SC中，按照學(xué)生成績降序排列。6）求最高成績7）求平均成績8）查詢成績在90分以上的學(xué)生姓名和課程名。9）向表Student中添加一條記錄：學(xué)號為“95005”，姓名為“孫輝”，性別為“男”，年齡為“19”，所在系為“機電系10）刪除表Course中課程名為“操作系統(tǒng)”的課程信息。11）將學(xué)生劉晨的年齡改為“17”，所在系改為“會計系”三.1 將SQL2000SERVER自帶的NORTHWIND數(shù)據(jù)庫打開，完成以下的操作。2 新建視圖testview，顯示1996年12月份的分類銷售情況匯總（CategorySalesfor1996/12）。要求視圖顯示的字段為“CategoryName”、“CategorySales”。注意：需要用到的基表為：“Orders”、“OrderDetails”、“Products”和“Categories”。3 在表“Employees”中增加一字段，字段名為“MobelPhone”，數(shù)據(jù)類型為“char(20)”,可以為空,其中“NancyDavolio”的手機號為4 修改表“Suppliers”中，CompanyName為“Pavlova,Ltd.”的ContactName為“NancyDavolio”。5 為表“EmployeeTerritories”建立一個參照完整性(既外鍵),要求表“EmployeeTerritories”的所有記錄的“EmployeeID”字段的值，在表“Employees”中已經(jīng)存在，并以“k1”6 為表“Orders”建立一個參照約束（check約束），要求表“Orders”的字段“ShippedDate”必須比“OrderDate”晚一周，且比“RequiredDate”早二周，且以“y1”7 建立刪除觸發(fā)器，要求當(dāng)表“Orders”的記錄被刪除后，表“OrderDetails”中相應(yīng)的記錄也能自動刪除,相應(yīng)產(chǎn)品在表“Products”的字段“UnitsInStock”和“UnitsOnOrder”的數(shù)字也要做相應(yīng)的修改，并以“cfq1”8 將books.txt中的數(shù)據(jù)，導(dǎo)入到數(shù)據(jù)庫中（注意：文件books.txt為純文本文檔）。9 編寫一個存儲過程cc1，要求為“CategoryName”為“Meat/Poultry”的所有Products在原價格（指“UnitPrice”）的基礎(chǔ)上提價10%。10建立一個角色sales，要求此角色能夠勝任根據(jù)客戶要求，輸入新定單（Order）且對已有的定單進(jìn)行修改的工作所需要的相應(yīng)權(quán)限,要求只賦予必要的最小權(quán)限。11對表“Suppliers”建立一個全文索引index，然后在表“Suppliers”中查找所有包含字符串“SalesManager”的記錄，并將查找結(jié)果以表cts保存在數(shù)據(jù)庫中。12打開查詢分析器，首先聲明一個游標(biāo)yb,要求返回表“Products”中“CategoryName”為“Condiments”（即“CategoryID”為“2”），“QuantityPerUnit”為“36boxes”的產(chǎn)品，且該游標(biāo)允許前后滾動和修改；然后打開該游標(biāo)；第三步對該游標(biāo)所指定的記錄進(jìn)行修改，將“QuantityPerUnit”由原來的“36boxes”改為“24boxes”；然后關(guān)閉該游標(biāo)并刪除該游標(biāo)。該過程在查詢分析器中調(diào)試成功后，將上述程序過程以“cc213 建立一個用戶函數(shù)SalesSum，要求以年、月份(如：1996年10月)和員工姓（LastName）為實參，返回該員工在該月的銷售總金額。14 備份數(shù)據(jù)庫到考試目錄下，并以test為文件名保存。練習(xí)題1：現(xiàn)有圖書管理數(shù)據(jù)庫的三個關(guān)系模式：圖書（總編號，分類號，書名，作者，出版單位，單價）讀者（借書證號，單位，姓名，性別，職稱，地址）借閱（借書證號，總編號，借書日期）利用SQLServer2000創(chuàng)建圖書管理庫和圖書、讀者和借閱三個基本表的表結(jié)構(gòu)：利用SQLServer2000在三個表中分別插入以下所給元組：圖書：總編號分類號書名作者出版單位單價445501TP3/12數(shù)據(jù)庫導(dǎo)論王強科學(xué)出版社17.90445502TP3/12數(shù)據(jù)庫導(dǎo)論王強科學(xué)出版社17.90445503TP3/12數(shù)據(jù)庫導(dǎo)論王強科學(xué)出版社17.90332211TP5/10計算機基礎(chǔ)李偉高等教育出版社18.00112266TP3/12FoxBASE張三電子工業(yè)出版社23.60665544TS7/21高等數(shù)學(xué)劉明高等教育出版社20.00114455TR9/12線性代數(shù)孫業(yè)北京大學(xué)出版社20.80113388TR7/90大學(xué)英語胡玲清華大學(xué)出版社12.50446601TP4/13數(shù)據(jù)庫基礎(chǔ)馬凌云人民郵電出版社22.50446602TP4/13數(shù)據(jù)庫基礎(chǔ)馬凌云人民郵電出版社22.50446603TP4/13數(shù)據(jù)庫基礎(chǔ)馬凌云人民郵電出版社22.50449901TP4/14FoxPro大全周虹科學(xué)出版社32.70449902TP4/14FoxPro大全周虹科學(xué)出版社32.70118801TP4/15計算機網(wǎng)絡(luò)黃力鈞高等教育出版社21.80118802TP4/15計算機網(wǎng)絡(luò)黃力鈞高等教育出版社21.80讀者：借書證號單位姓名性別職稱地址111信息系王維利女教授1號樓424112財會系李立男副教授2號樓316113經(jīng)濟系張三男講師3號樓105114信息系周華發(fā)男講師1號樓316115信息系趙正義男工程師1號樓224116信息系李明男副教授1號樓318117計算機系李小峰男助教1號樓214118計算機系許鵬飛男助工1號樓216119計算機系劉大龍男教授1號樓318120國際貿(mào)易李雪男副教授4號樓506121國際貿(mào)易李爽女講師4號樓510122國際貿(mào)易王純女講師4號樓512123財會系沈小霞女助教2號樓202124財會系朱海男講師2號樓210125財會系馬英明男副教授2號樓212借閱：借書證號總編號借書日期1124455011997-3-191253322111997-2-121114455031997-8-211121122661997-3-141146655441997-10-211201144551997-11-21201188011997-10-181194466031997-12-121124499011997-10-231154499021997-8-211181188011997-9-10練習(xí)題2：學(xué)會利用導(dǎo)入的方法創(chuàng)建上面的數(shù)據(jù)庫并用SQL完成如下查詢：找出姓李的讀者姓名和所在單位。列出圖書庫中所有藏書的書名及出版單位。查找高等教育出版社的所有圖書及單價，結(jié)果按單價降序排序。查找價格介于10元和20元之間的圖書種類，結(jié)果按出版單位和單價升序排序。查找書名以計算機打頭的所有圖書和作者。檢索同時借閱了總編號為112266和449901兩本書的借書證號。查找所有借了書的讀者的姓名及所在單位。找出李某所借圖書的所有圖書的書名及借書日期。查詢1997年10月以后借書的讀者借書證號、姓名和單位。找出借閱了FoxPro大全一書的借書證號。找出與趙正義在同一天借書的讀者姓名、所在單位及借書日期。查詢1997年7月以后沒有借書的讀者借書證號、姓名及單位。練習(xí)題3：學(xué)會利用導(dǎo)出的方法創(chuàng)建圖書管理數(shù)據(jù)庫并完成下面SQL高級查詢：求科學(xué)出版社圖書的最高單價、最低單價、平均單價。求信息系當(dāng)前借閱圖書的讀者人次數(shù)。求出各個出版社圖書的最高價格、最低價格和冊數(shù)。分別找出各單位當(dāng)前借閱圖書的讀者人數(shù)。找出當(dāng)前至少借閱了2本圖書的讀者及所在單位。分別找出借書人次超過1人次的單位及人次數(shù)。找出藏書中各個出版單位的冊數(shù)、價值總額。查詢經(jīng)濟系是否還清所有圖書。如果還清，顯示該系所有讀者的

姓名、所在單位和職稱。二、數(shù)據(jù)查詢及更新1、對于教學(xué)數(shù)據(jù)庫的三個基本表：學(xué)生S(S#,SNAME,AGE,SEX)--字段含義[學(xué)號,姓名,年齡,性別]學(xué)習(xí)SC(S#,C#,GRADE)--字段含義[學(xué)號,課程號,成績]課程C(C#,CNAME,TEACHER)--字段含義[課程號,課程名,授課老師]試用SQL的查詢語句表達(dá)下列查詢：（1）檢索LIU老師所授課程的課程號和課程名。SELECTC#，CNAMEFROMCWHERETEACHER=‘LIU’（2）檢索年齡大于23歲的男學(xué)生的學(xué)號和姓名。SELECTS#，SNAMEFROMSWHERE(AGE>23)AND(SEX=‘M’)（3）檢索至少選修LIU老師所授課程中一門課程的女學(xué)生姓名。SELECTSNAMEFROMSWHERESEX=‘F’ANDS#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERETEACHER=‘LIU’)NOTICE：有多種寫法，比如聯(lián)接查詢寫法：SELECTSNAMEFROMS,SC,CWHERESEX=‘F’ANDSC.S#=S.S#ANDSC.C#=C.C#ANDTEACHER='LIU'但上一種寫法更好一些。（4）檢索WANG同學(xué)不學(xué)的課程的課程號。SELECTC#FROMCWHEREC#NOTIN(SELECTC#FROMSCWHERES#IN(SELECTS#FROMSWHERESNAME='WANG'))（5）檢索至少選修兩門課程的學(xué)生學(xué)號。SELECTDISTINCTX.SNOFROMSCX,SCYWHEREX.SNO=Y.SNOANDX.CNO<>Y.CNONotice:對表SC進(jìn)行自連接，X，Y是SC的兩個別名。（6）檢索全部學(xué)生都選修的課程的課程號與課程名。SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERES#NOTIN(SELECT*FROMSCWHERE

SC.C#=C.C#))（7）檢索選修課程包含LIU老師所授課的學(xué)生學(xué)號。SELECTDISTINCTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERETEACHER='LIU'))2、設(shè)有兩個基本表R（A，B，C）和S（D，E，F(xiàn)），試用SQL查詢語句表達(dá)下列關(guān)系代數(shù)表達(dá)式：；（1）πA(R)(2)σB='17'(R)(1)SELECTAFROMR；(2)SELECT*FROMRWHEREB='17'；4、試用SQL查詢語句表達(dá)下列對教學(xué)數(shù)據(jù)庫中三個基本表S、SC、C的查詢：（1）統(tǒng)計有學(xué)生選修的課程門數(shù)。SELECTCOUNT(DISTINCTC#)FROMSC（2）求選修C4課程的學(xué)生的平均年齡。SELECTAVG(AGE)FROMSWHERES#IN(SELECTS#FROMSCWHEREC#='C4')或者SELECTAVG(AGE)FROMS，SCWHERES.S#=SC.S#ANDC#='004'（3）求LIU老師所授課程的每門課程的學(xué)生平均成績。SELECTCNAME,AVG(GRADE)FROMSC,CWHERESC.C#=C.C#ANDTEACHER='LIU'GROUPBYC#（4）統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過10人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。SELECTDISTINCTC#,COUNT(S#)FROMSCGROUPBYC#HAVINGCOUNT(S#)>10ORDERBY2DESC,C#ASC（5）檢索學(xué)號比WANG同學(xué)大，而年齡比他小的學(xué)生姓名。SELECTX.SNAMEFROMSASX,SASYWHEREY.SNAME='WANG'ANDX.S#>Y.S#ANDX.AGE<Y.AGE（6）檢索姓名以WANG打頭的所有學(xué)生的姓名和年齡。SELECTSNAME,AGEFROMSWHERESNAMELIKE'WANG%'（7）在SC中檢索成績?yōu)榭罩档膶W(xué)生學(xué)號和課程號。SELECTS#,C#FROMSCWHEREGRADEISNULL（8）求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。SELECTSNAME,AGEFROMSASXWHEREX.SEX='男'ANDX.AGE>(SELECTAVG(AGE)FROMSASYWHEREY.SEX='女')（9）求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。SELECTSNAME,AGEFROMSASXWHEREX.SEX='男'ANDX.AGE>ALL(SELECTAGEFROMSASYWHEREY.SEX='女')5、試用SQL更新語句表達(dá)對教學(xué)數(shù)據(jù)庫中三個基本表S、SC、C的各個更新操作：（1）往基本表S中插入一個學(xué)生元組（‘S9’，‘WU’，18）。INSERTINTOS(S#,SNAME,AGE)VALUES('59','WU',18)（2）在基本表S中檢索每一門課程成績都大于等于80分的學(xué)生學(xué)號、姓名和性別，并把檢索到的值送往另一個已存在的基本表STUDENT（S＃，SANME，SEX）。INSERTINTOSTUDENT(S#,SNAME,SEX)SELECTS#,SNAME,SEXFROMSWHERENOTEXISTS(SELECT*FROMSCWHEREGRADE<80ANDS.S#=SC.S#)（3）在基本表SC中刪除尚無成績的選課元組。DELETEFROMSCWHEREGRADEISNULL（4）把WANG同學(xué)的學(xué)習(xí)選課和成績?nèi)縿h去。DELETEFROMSCWHERES#IN(SELECTS#FROMSWHERESNAME='WANG')（5）把選修MATHS課不及格的成績?nèi)臑榭罩?。UPDATESCSETGRADE=NULLWHEREGRADE<60ANDC#IN(SELECTC#FROMCWHERECNAME='MATHS')（6）把低于總平均成績的女同學(xué)成績提高5%。UPDATESCSETGRADE=GRADE*1.05WHEREGRADE<(SELECTAVG(GRADE)FROMSC)ANDS#IN(SELECTS#FROMSWHERESEX='F')（7）在基本表SC中修改C4課程的成績，若成績小于等于75分時提高5%，若成績大于75分時提高4%（用兩個UPDATE語句實現(xiàn)）。UPDATESCSETGRADE=GRADE*1.05WHEREC#='C4'ANDGRADE<=75UPDATESCSETGRADE=GRADE*1.04WHEREC#='C4'ANDGRADE>75《數(shù)據(jù)庫原理及應(yīng)用》習(xí)題集參考答案一、簡答題1．什么是數(shù)據(jù)庫管理系統(tǒng)答：一種負(fù)責(zé)數(shù)據(jù)庫的建立，操作，管理和維護的軟件系統(tǒng)。。2．?dāng)?shù)據(jù)庫系統(tǒng)有哪幾種模式分別用來描述什么答：(1) 外模式 是用戶的數(shù)據(jù)視圖，用來描述數(shù)據(jù)的局部邏輯結(jié)構(gòu)，是模式的子集。(2) 模式 是所有用戶的公共數(shù)