數(shù)據(jù)庫(kù)系統(tǒng)概論復(fù)習(xí)重點(diǎn)問答題數(shù)據(jù)查詢

問答題4—數(shù)據(jù)查詢一、第3章課后習(xí)題P127-4.針對(duì)上題中建立的四個(gè)表試用 SQL完成第二章習(xí)題5中的查詢求供應(yīng)工程J1零件的供應(yīng)商號(hào)碼SNO答：SELECTSNOFROMSPJWHEREJ'NJO1='求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼SNO答：SELECTSNOFROMSPJWHEREJNO'=J1'ANDPNO'=P1'求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼SNO答：SELECTSNOFROMSPJWHEREJNO'=J1'ANDPNOIN(SELECTPNOFROMPWHERECOLOR'=紅')或：SELECTSNOFROMSPJ,PWHEREJNO'=J1'ANDSPJ.PNO=P.PNOANDCOLO'R=紅'求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào) JNO答：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERECITY='天津’)ANDPNOIN(SELECTPNOFROMPWHERECOLOR'=紅'))或：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJ,S,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY='天津’ANDP.COLOR=^')求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程號(hào)JNO答：SELECTDISTINCTJNOFROMSPJ,SPJZWHERENOTEXISTS(SELECT*FROMSPJSPJXWHERESNO=S1'ANDNOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.PNO=SPJX.PNOANDSPJY.JON=SPJZ.JNO)P127-5.針對(duì)習(xí)題3中的四個(gè)表，試用SQL完成以下各項(xiàng)操作（1）找出所有供應(yīng)商的姓名和所在城市答：SELECTSNAME,CITYFROMS（2）找出所有供應(yīng)商的姓名和所在城市答：SELECTPNAME,COLOR,WEIGHTFROMP（3） 找出使用供應(yīng)商S1所供應(yīng)零件的工程號(hào)碼答：SELECTJNOFROMSPJWHERESN'O=S1'（4） 找出工程項(xiàng)目J2使用的各種零件的名稱及其數(shù)量答：SELECTP.PNAME,SPJ.QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.J'NOJ2='（5） 找出上海廠商供應(yīng)的所有零件號(hào)碼答：SELECTDISTINCTPNOFROMSPJWHERESNOIN（SELECTSNOFROMSWHERECITY='上海'）（6） 找出使用上海產(chǎn)的零件的工程名稱答：SELECTJNAMEFROMJ,SPJ,SWHEREJ.JNO=SPJ.JNOANDSPJ.SNO=S.SNOANDS.CITY='上海'或：SELECTJNAMEFROMJWHEREJNOJNO(SELECTJNOFROMSPJ,SWHERESPJ.SNO=S.SNOANDS.CITY='上海')7）找出沒有使用天津產(chǎn)的零件的工程號(hào)碼答：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERECITY='天津'))或：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJ,SWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDS.CITY='天津')（8）把全部紅色零件的顏色改成藍(lán)色答：UPDATEPSETCOLOR=藍(lán)'WHERECOLOR=紅'（9）由S5供給J4的零件P6改為由S3供應(yīng)，請(qǐng)作必要的修改答：UPDATESPJSETSNO'=S3'WHERESN'O=S5'ANDJNO='S4'ANDPNO'=P6'（10）從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)情況關(guān)系中刪除相應(yīng)的記錄答：DELETEFROMSPJWHERESN'O=S2'DELETEFROMSWHERESNO='S2'（11）請(qǐng)將（S2，J6，P4，200）插入供應(yīng)情況關(guān)系答：INSERTINTOSPJ（SNO,JNO,PNO,QTY）VALUES（S2,J6,P4,200）或：INSERTINTOSPJVALUES（S2,P4,J6,200）二、補(bǔ)充題1．設(shè)數(shù)據(jù)庫(kù)中有基本表：教師（工號(hào),姓名,性別,職稱,工齡,基本工資,補(bǔ)貼），請(qǐng)用SQL語(yǔ)句寫出工齡在20年以上（包括20年）、基本工資低于1500元的教師所有信息。答：SELECT*FROM教師WHERE工齡>=20AND基本工資<15002．在教學(xué)管理系統(tǒng)中含有兩個(gè)關(guān)系：學(xué)生 （學(xué)號(hào),姓名,性別,出生日期,系名），成績(jī)（學(xué)號(hào),課程名,成績(jī)）。若查詢每個(gè)學(xué)生的選修課程數(shù)、總成績(jī)、平均成績(jī)，寫出實(shí)現(xiàn)上述要求的 SQL語(yǔ)句。答：SELECT學(xué)號(hào),COUNT（課程名）,SUM（成績(jī)）,AVG（成績(jī)）FROM成績(jī)GROUPBY學(xué)號(hào)3．圖書出版管理數(shù)據(jù)庫(kù)中有兩個(gè)基本表： 圖書（書號(hào),書名,作者編號(hào),出版社,出版日期）,作者（作者,編號(hào),作者名,年齡,地址），試用SQL語(yǔ)句寫出下列查詢：檢索年齡低于作者平均年齡的所有作者的作者名、書名和出版社。答：SELECT作者名,書名,出版社FROM圖書,作者WHERE圖書.作者編號(hào)=作者.作者編號(hào)AND年齡<=（SELECTAVG（年齡）FROM作者）4．基于數(shù)據(jù)庫(kù)中的成績(jī)表：成績(jī)（學(xué)號(hào),成績(jī)名,成績(jī)），用SQL語(yǔ)句寫出：只選修一門課程的學(xué)生學(xué)號(hào)、課程名及成績(jī)。答：SELECT*FROM成績(jī)XWHERENOTEXISTS（SELECT*FROM成績(jī)YWHEREY學(xué)號(hào)=X.學(xué)號(hào)ANDY.課程名<>X.課程名）?本題基于以下數(shù)據(jù)庫(kù)模式：學(xué)生關(guān)系 S(學(xué)號(hào)，姓名,性別)、課程關(guān)系C(課程號(hào)，課程名)、成績(jī)關(guān)系SC(學(xué)號(hào),課程號(hào)，分?jǐn)?shù))，請(qǐng)用SQL語(yǔ)言完成下列查詢。分別統(tǒng)計(jì)男生和女生的人數(shù)答：SELECT“男生人數(shù)為：”,COUNT(*)FROMSWHERE性另U=男'SELECT“女生人數(shù)為：”，COUNT(*)FROMSWHERE性別=‘女'檢索選修課程號(hào)為“C1”且分?jǐn)?shù)最高的學(xué)生學(xué)號(hào)和分?jǐn)?shù)答：SELECT學(xué)號(hào),MAX(分?jǐn)?shù))FROMSCWHERE課程號(hào)='C1'(3)檢索選修課程名為“DB”的學(xué)生姓名和分?jǐn)?shù)答：SELECT姓名,分?jǐn)?shù)FROMS,SC,CWHERES.學(xué)號(hào)=SC.學(xué)號(hào)ANDSC.課稱號(hào)=C.課稱號(hào)ANDC.課成名='DB'?本題基于這樣的三個(gè)表即學(xué)生表 S、課程表C和學(xué)生選課表SC,它們的結(jié)構(gòu)如下：S(S#,SN,SEX,AGE,DEPT)C(C#,CN)SC(S#,C#,GRADE),其中S#為學(xué)號(hào)，SN為姓名，SEX為性別；AGE為年齡，DEPT為系別，C#為課程號(hào)，CN為課程名，GRADE為成績(jī)。請(qǐng)用SQL語(yǔ)句寫出如下查詢。(1)檢索所有比“王華”年齡大的學(xué)生姓名、年齡和性別答：SELECTSN,AGE,SEXFROMSWHEREAGE>(SELECTAGEFROMS WHERESN='王華')檢索選修了“C2”課程的學(xué)生中成績(jī)最高的學(xué)生學(xué)號(hào)答：SELECTS#FROMSCWHEREC#='C2'ANDGRADE>=ALL(SELECTGRADEFROMSCWHEREC#= 'C2')(3)檢索學(xué)生姓名及其所選修課程的課程號(hào)和成績(jī)答：SELECTS.SN,SC.C#,GRADEFROMS,SCWHERES.S#=SC.S#(4)檢索選修4門以上課程的學(xué)生總成績(jī)(不含不及格課程)，并要求按總成績(jī)的降序排列答：SELECTS#,SUM(GRADE)FROMSCWHEREGRADE>=60GROUPBYS#HAVINGCOUNT(*)>=4ORDERBY2DESC?本題基于以下定義：圖書(總編號(hào),分類號(hào)，書名,作者，出版單位，單價(jià))，讀者(借書證號(hào)，單位，姓名,性別,職稱,地址)，借閱(借書證號(hào),總編號(hào),借書日期)，寫出下列各命令或命令組的功能。SELECTMAX(單價(jià))最高價(jià),MIN(單價(jià))最低價(jià),AVG(單價(jià))平均價(jià)FROM圖書答：求出所有圖書的最高價(jià)、最低價(jià)和平均價(jià)SELECTCOUNT(*) FROM圖書WHERE單價(jià)BETWEEN10AND20答：統(tǒng)計(jì)10至20元之間的圖書數(shù)量SELECT借書證號(hào),COUNT(*)FROM借閱WHERE借書日期<'98/01/01'GROUPBY借書證號(hào)HAVINGCOUNT(*)>=3答：分組統(tǒng)計(jì)出97年底以前借閱不低于3本圖書的借書證號(hào)及數(shù)量SELECT單位,COUNT(*)FROM讀者，借閱WHERE讀者?借書證號(hào)=借閱?借書證號(hào)GROUPBY單位答：按單位分組統(tǒng)計(jì)出被借閱圖書的數(shù)量SELECT書名,COUNT(*)FROM圖書借閱WHERE圖書.總編號(hào)=借閱?總編號(hào)GROUPBY書名ORDERBYCOUNT(*)答：求被借閱圖書的名字和借閱數(shù)量，并按借閱數(shù)量升序排列8．有2個(gè)表：Student(學(xué)號(hào)char(8)，姓名varchar(8)，年齡int,專業(yè)varchar(2)，入學(xué)日期DateTime)Score(學(xué)號(hào)char(8)，課程名varchar(10)，成績(jī)numeric(5,2))寫出下列每條語(yǔ)句或程序段的功能。(1)SELECTCOUNT(*)FROMStudentsWHEREleft(專業(yè),3)='計(jì)算機(jī)'答：統(tǒng)計(jì)計(jì)算機(jī)專業(yè)人數(shù)UPDATEScoreSET成績(jī)=@cWHERE學(xué)號(hào)=@aAND課程名=@b答：將學(xué)號(hào)為@a且課程名為@b學(xué)生的成績(jī)更改為@cDELETEFROMScoreWHERE學(xué)號(hào)=@aAND課程名=@b答：從Score表中刪除學(xué)號(hào)為@a、課程名為@b值的學(xué)生成績(jī)記錄SELECT*FROMStudent,Score WHEREStudent.學(xué)號(hào)=Score.學(xué)號(hào)答：查看學(xué)生及成績(jī)表SELECTStudent.學(xué)號(hào),姓名，專業(yè)，COUNT(*)AS門數(shù) FROMStudent,ScoreWHEREStudent.學(xué)號(hào)=Score.學(xué)號(hào)GROUPBYStudent.學(xué)號(hào),姓名,專業(yè)答：顯示學(xué)生的學(xué)號(hào)、姓名、專業(yè)和選課門數(shù)SELECT學(xué)號(hào),AVG(成績(jī))AS平均成績(jī) FROMScoreGROUPBY學(xué)號(hào)答：顯示每個(gè)學(xué)生的平均成績(jī)9．已知關(guān)系模式：Customers(customerid,name,emailaddress,city,tel,company,birthday,type)TOC\o"1-5"\h\z客戶(客戶編號(hào)，姓名，郵箱地址，城市，電話，公司，出生日期，客戶類型 )Goods(goodsname,description,storage,supplier,status,price)貨品(貨品名稱，描述，庫(kù)存量，供應(yīng)商，狀態(tài)，價(jià)格 )Orders(orderid,goodsname,customerid,quantity,ordersum,orderdate)訂單(訂單編號(hào)，貨品名稱，客戶編號(hào)，訂貨數(shù)量，總金額，訂貨日期 )請(qǐng)寫出下列各命令或命令組的功能(1)SELECTcustomerid,name,emailaddressFROMCustomersWHEREemailaddressISNOTNULL答：查詢留有郵箱地址客戶的客戶編號(hào)、客戶姓名和客戶郵箱地址(2)SELECTgoodsname,SUM(quantity)FROMOrdersGROUPBYgoodsnameHAVINGSUM(quantity)>500答：查詢訂單中貨品總量超過500(不含500)的貨品名稱和貨品總量(3)SELECTgoodsname,priceFROMGoodsWHEREprice>(SELECTAVG(price)FROMGoods)答：查詢價(jià)格高于所有貨品平均價(jià)格的貨品的名稱和價(jià)格4)SELECTGoods.goodsname,storage,priceFROMGoodsWHEREEXISTS(SELECT*FROMOrders,GoodsWHEREOrders.goodsname=Goods.goodsname)答：查詢有訂單記錄的貨品名稱、庫(kù)存量和價(jià)格(5)DELETEFROMordersWHEREcustomeridIN(SELECTcustormeridFROMcustomersWHEREcity='北京')答：刪除北京客戶的訂單記錄10．在“教學(xué)庫(kù)”中包含3個(gè)表，分別定義為：學(xué)生(學(xué)號(hào)CHAR(7),姓名CHAR(6),性別CHAR(2),出生日期DATETIME,專業(yè)CHAR(10),年級(jí)INT)；課程(課程號(hào)CHAR(4),課程名CHAR(10),課程學(xué)分INT)；選課(學(xué)號(hào)CHAR(7),課程號(hào)CHAR(4),成績(jī)INT)查詢每個(gè)學(xué)生選課的門數(shù)答：SELECT學(xué)生.學(xué)號(hào)，COUNT(*)AS選課門數(shù)FROM學(xué)生,選課WHERE學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào)GROUPBY學(xué)生.學(xué)號(hào)查詢每門課程被選修的學(xué)生數(shù)答：SELECT課程.課程號(hào),課程.課程名,COUNT(*)AS選課人數(shù)FROM課程,選課WHERE課程.課程號(hào)=選課.課程號(hào)GROUPBY課程.課程號(hào)查詢同時(shí)選修了3門課程的全部學(xué)生學(xué)號(hào)答：SELECT學(xué)號(hào)FROM選課GROUPBY學(xué)號(hào)HAVINGCOUNT(*)=3查詢至少選修了姓名為@ml學(xué)生所選課程中一門課的全部學(xué)生答：SELECTDISTINCT學(xué)生.*FROM學(xué)生,選課WHERE學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào)AND課程號(hào)=ANY(SELECT課程號(hào)FROM學(xué)生,選課WHERE學(xué)生.學(xué)號(hào)=選課?學(xué)號(hào)AND姓名=@ml）（5）查詢被8~10名學(xué)生選修的所有課程答：SELECT*FROM課程WHEREEXISTS（SELECT課程號(hào)FROM選課WHERE課程.課程號(hào)=選課.課程號(hào)GROUPBY課程號(hào)HAVINGCOUNT（*）BETWEEN8AND10）（6）查詢最多選修了2門課程（含未選任何課程）的全部學(xué)生答：SELECT*FROM學(xué)生WHERE學(xué)號(hào)IN（SELECT學(xué)號(hào)FROM選課GROUPBY學(xué)號(hào)HAVINGCOUNT（*）＜=2）ORNOTEXISTS（SELECT*FROM選課WHERE學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào)）11．設(shè)工程-零件數(shù)據(jù)庫(kù)中有4個(gè)基本表：供應(yīng)商（供應(yīng)商代碼,姓名,所在城市,聯(lián)系電話）；工程（工程代碼,工程名,負(fù)責(zé)人,預(yù)算）；零件（零件代碼,零件名,規(guī)格,產(chǎn)地,顏色）；供應(yīng)零件（供應(yīng)商代碼,工程代碼,零件代碼,數(shù)量）。試用SQL語(yǔ)句完成下列操作：（1）找出天津供應(yīng)商的姓名和電話答：SELECT姓名,聯(lián)系電話FROM供應(yīng)商WHERE所在城市='天津市'（2）查找預(yù)算在50000~100000元之間的工程信息，并將結(jié)果按預(yù)算順序排列答：SELECT*FROM工程WHERE預(yù)算BETWEEN50000AND100000ORDERBY預(yù)算DESC（3）找出使用供應(yīng)商S1所供零件的工程代碼答：SELECT工程代碼FROM供應(yīng)零件WHERE供應(yīng)商代碼='SI'（4）找出工程項(xiàng)目J2使用的各種零件名稱及數(shù)量答：SELECT零件名,數(shù)量FROM零件,供應(yīng)零件WHERE零件.零件代碼=供應(yīng)零件.零件代碼AND工程代碼='J2'5）找出上海廠商供應(yīng)的所有零件代碼答：SELECT零件代碼FROM供應(yīng)商,供應(yīng)零件WHERE供應(yīng)商.供應(yīng)商代碼=供應(yīng)零件.供應(yīng)商代碼AND所在城市='上海'（6）找出使用上海產(chǎn)的零件的工程名稱答：SELECT工程名FROM零件,供應(yīng)零件,工程WHERE零件.零件代碼=供應(yīng)零件.零件代碼AND供應(yīng)零件.工程代碼=工程.工程代碼AND產(chǎn)地='上海'（7）找出沒有使用天津供應(yīng)商供應(yīng)的零件的工程代碼答：SELECT工程代碼FROM供應(yīng)零件WHERENOTEXISTS（SELECT*FROM供應(yīng)零件WHERE供應(yīng)商代碼IN（SELECT供應(yīng)商代碼FROM供應(yīng)商WHERE所在城市='天津'））（8）將全部紅色零件的顏色改成藍(lán)色答：UPDATE零件SET顏色='藍(lán)色'WHERE顏色='紅色'（9）將由供應(yīng)商S5供給工程代碼為J4的零件P6改為由S3供應(yīng)答：UPDATE供應(yīng)零件SET供應(yīng)商代碼='S3'WHERE供應(yīng)商代碼='S5'AND工程代碼='J4'（10）從供應(yīng)商關(guān)系中刪除S2的記錄，并從供應(yīng)零件關(guān)系中刪除