SQL試題整理(上機1)

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

23信息系統(tǒng)144操作系統(tǒng)635數據結構746數據處理

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

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

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

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