數(shù)據(jù)庫系統(tǒng)概論復習重點問答題數(shù)據(jù)查詢

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