SQL試題整理(上機(jī)1)

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

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

27PASCAL語(yǔ)言64表SC學(xué)號(hào)Sno課程號(hào)Cno成績(jī)Grade9500119295001285950013889500229195002380圖1

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

姓名、所在單位和職稱。二、數(shù)據(jù)查詢及更新1、對(duì)于教學(xué)數(shù)據(jù)庫(kù)的三個(gè)基本表：學(xué)生S(S#,SNAME,AGE,SEX)--字段含義[學(xué)號(hào),姓名,年齡,性別]學(xué)習(xí)SC(S#,C#,GRADE)--字段含義[學(xué)號(hào),課程號(hào),成績(jī)]課程C(C#,CNAME,TEACHER)--字段含義[課程號(hào),課程名,授課老師]試用SQL的查詢語(yǔ)句表達(dá)下列查詢：（1）檢索LIU老師所授課程的課程號(hào)和課程名。SELECTC#，CNAMEFROMCWHERETEACHER=‘LIU’（2）檢索年齡大于23歲的男學(xué)生的學(xué)號(hào)和姓名。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é)的課程的課程號(hào)。SELECTC#FROMCWHEREC#NOTIN(SELECTC#FROMSCWHERES#IN(SELECTS#FROMSWHERESNAME='WANG'))（5）檢索至少選修兩門課程的學(xué)生學(xué)號(hào)。SELECTDISTINCTX.SNOFROMSCX,SCYWHEREX.SNO=Y.SNOANDX.CNO<>Y.CNONotice:對(duì)表SC進(jìn)行自連接，X，Y是SC的兩個(gè)別名。（6）檢索全部學(xué)生都選修的課程的課程號(hào)與課程名。SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERES#NOTIN(SELECT*FROMSCWHERE

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