數(shù)據(jù)庫系統(tǒng)實驗二

1、實驗二姓名：畢澤琨學(xué)號：23320122203798實驗內(nèi)容查詢語句使用基本select語句使用查詢條件where、having語句使用Having語句是在分組篩選時使用即與group by語句聯(lián)合使用嵌套查詢，in、exists聚集函數(shù)： max、min、avg、count集合運算： union、intersect、exceptorder by 和 group by 語句主要是熟悉嵌套查詢！在嵌套查詢中，子查詢不能使用order by語句實驗環(huán)境：WINDOWS SERVER2003, MS SQL SERVER2000實驗步驟實驗2.1數(shù)據(jù)查詢實驗要求：以School數(shù)據(jù)庫為例，在該數(shù)據(jù)庫

2、中存在四張表格，分別為：表 STUDENT(sid, sname, email, grade);表 TEACHERS(tid, tname, email, salary);表 COURSES(cid, cname, hour);表 CHOICES(no, sid, tid, cid, score)在數(shù)據(jù)庫中，存在這樣的關(guān)系：學(xué)生可以選擇課程，一個課程對應(yīng)一個教師。在表CHOICES中保存學(xué)生的選課記錄。按以下要求對數(shù)據(jù)庫進行查詢操作：實驗步驟：(1)查詢年級為2002的所有學(xué)生的姓名并按編號升序排列。(local).School - SQLQueiy 12.sqselect, snaine,

3、email from 5TUDEUT5where gT?ade=l 2002 1 rder- by aid desc;rrr門結(jié)果-J消息snameemail1bzrxoevn2syymhdg.riet2dlmccn&fnfkdtui iqsjpfcwb3ca 4jwsyglynerjhcpt(&poemtstvdf341 apz .arg$率頃 HYPERLINK mailto:lqvw lqvw7uyeme HYPERLINK mailto:2dvmjrnn.ong 2dvmjrnn.ongRfjywtKri5dcal7hlqz.o-rgejqorqBDcfntulchprnl.goir(

4、2)查詢學(xué)生的選課成績合格的課程成績，并把成績換算為積點(60分對應(yīng)積點為1, 每增加1分，積點增加0.1)。(local).ShQol - jES. -SQLQuerylO.sql*| (locaD.School - SQLQuary9.&qI select choz-ces scene J. 0.1 score 60 GPA teachers tnamewaere caoices.5Goie=16Q口結(jié)果消息sconeGPAtnsme1p6 2 -6abed2B73.7abed3924.2abed4B23.2abed5772.7abedG71.7abed1904.DabedS6D1.Dab

5、ed9702.D呂bed10S23.2abed11762.6abed12SS3.-Sabed137222abed1441.4abed(3)查詢課時是48或64的課程的名稱。(IocsI).Echool -&QLQueryl3.sql* (locaI).Scaclcu L u：LdiL.c 1 un. C(?LrR.3E3wAere ?io j.r 4 8 己二?io j.r =64 .nir結(jié)果消息cname1! computer graphics2java3c-m-4design pattern5data structure6software engineering7computer net

6、workSanchiterfonics9 compiling principle1Dnealiime system11c12computer interface 查詢所有課程名稱中含有data的課程編號。(locaI).School - EQLQueryLS.sql* (local).select cid| from COURSESwhe re cnaju.e like 11 :rrr結(jié)果壹消息cid12 DOI |z1OTD8(5)查詢所有選課記錄的課程號(不重復(fù)顯示)。(1ocal).SchGd SQLQucryl3.jql* (IccaD，School - SQLQu&rylS.sqP

7、select, distinct cid |frox TEACHERS CHOICES wnere C?iOI2E5 tid TEACHERS tid and CHOICES.no? 0;4|1(|1_結(jié)果消息id1100012ICuK3i(mm541000551000661CMJ77昭SidSb91OT10101C311111C312121OT13(6)統(tǒng)計所有教師的平均工資。jocab.School - &QLQueryL3.sql* (l&select a：_E salary卜nrffi結(jié)果LM消息|無列名) 1：抑7(Iocal)_&chool -正在.-EQLQuerylS.sql*

8、 select tidf a.v scoregicjp by tid, cidcidei toy avciscore)desc;4nrn結(jié)果J消息tidI無刊名)1j 293S3BGi 992250455灑95320S4723019S4期心迎調(diào)5245453773996272195S129S7224324C579922779356G993225757f?95mHdCCT TTHHinn(8)統(tǒng)計各個課程的選課人數(shù)和平均成績。(locab.School - SQLQueryS.sqh (local).School - SQLQueryB.sql*9 :select coiiTses -cidf

9、counr (choices - sidi / svg- (c-hoices. scare) froni CDUiTBeBK ciioiceswhere coiaises . cid= choices . cidgi-oup by coiarsea - cid|4ppr卜ll站果_J消息cidl無列名)I無列名)11DS5&S57&21洶毗75310S1559W7&410DD560317651003360337510M2G0137&7iom537575W10WK6ttW91*1,6027751010S1458676111D042G0117&12100295955761310D475955751

10、41OT0158&751510S175902(Ioeal).chool -SQLQuery&.sqF (locaf&chool - SQLQuery/.s seIecV caoIces 目M froir. st.jdent.5,cnoices wnere students sid=cn3ice5 5id snd3 fade nt b aid in (select 3id jEiqih caoices grcap bv Bidhaving 口口二二七(=)=41IIPJ結(jié)果_Jl消息sid1844-2O8C5B2S37227R7538854395534S24251G5458祁42制E6SD136

11、48W7W1679C882滿51將9SS19豌即1D551 312711B61231 暗(10)查詢編號800028649的學(xué)生所選的全部課程的課程名和成績。(local).SchQQl - SQLQu&ry7.sqF| (locab，School - select cnanie,tnane,scoie from courses,teacneis enoices w：here choLces . sd= 90002-z 63* and choLces cz_d coaises cid and caolces. tId. tld;|/rrrJ結(jié)果宜消息cnanietnamescone1datab

12、asehlgoef512computer networkpbchk盟(11)查詢所有選修了 database的學(xué)生的編號。(local).&chsQl -正在.-SQ LQu e-ry 2O.sq I * (locSelect, s fade nt s aid from co jrseBf Bt Jdents . cjnoice wAere crLame=1 datatoa3e r41mn結(jié)果-J1消息sid1MDD12162SCDDJ12163SGuu3121648C22312165BC：D121668C223121678C223121688ffiHM1l2169SffiHM 譙inRmon

13、mK(12)求出選擇了同一個課程的學(xué)生數(shù)。(local).chool -EQLQueryl7.sql* flocafSchool - SQL select. COURSE5 canie/ CGUM? ( C?1GICE5 . sidj from 二5JR5E5,二雄二二E5 wnere COURSES cid=C4DIES cid4rvr二結(jié)果_的消息cnamel無列名)J_algorithm59022anchitectaniGS3c60114C+5compiling principle5355Gco m p uter grap hi cs5757computer interface5555S

14、computer network9dart 日 struGtune瞄10database583811design pattern12operating system6013Urealtime system14softwane enginee.602715tcp/ip protocol5900(13)求出至少被兩名學(xué)生選修的課程編號。docalySchool -SQLQueryL7.&ql* (locallSchc select cidfram CHOICES7工 u _ip by oidwCUML (distinct 日坦) =2;4m二結(jié)果1消息cidJ1D&D1 j TOC o 1-5 h

15、 z 10M21DM31D&D51DIHK1DK710DDSE 1D&D5910&WID 1 即11(14)查詢選修了編號894037661的學(xué)生所選的某個課程的學(xué)生編號。(local).School -正在.-SQLQucryl&.s-q* select- st-Jdents aid from st- jd&nt-s, caoices where cid Ln (select cid f ram clriciices, studentswhere students. sid=cfioices. aid and clfioiceB - 5id=B390376611 and ahaicea. a

16、ldo1 3940376611J結(jié)果消息siC2DD29333SC0W575348C：6825SCK94168CuK75957SC2Wa558SCZDD&O*980M0M99(15)查詢學(xué)生的基本信息及選修課程編號和成績。(lacal).Scliad -SQLQueryL9.iql* locaSchool -正在“ -SQLQuery： select STUDENTS sidr sname r emailF grade.cidF score from 5rUDENTSr2H0ZCESwhere STUDENTS sid=CHOl|CES aid1. EZ結(jié)果_J1消息

17、sidsnameemailgradeddscene1B5157G370vaamjwekq_ 禽 rnthnet15Mimi722833721177vdxakvukh HYPERLINK mailto:k1r76krrc2.Bdu k1r76krrc2.Bdu19(941D0459S3S92903792njscrzcghqwrSrc cado .org19961睥1SO4眼365173latch nps HYPERLINK mailto:v5bgw v5bgwj%51DD44NULL5S45755457ririJoab HYPERLINK mailto:iitl4 iitl41997123246

18、76S9M9S4BSmxJvnsse HYPERLINK mailto:ghwsqita.gav ghwsqita.gav2M21uuK747S725S2324obryrkfr HYPERLINK mailto:hii587 hii58720021DD29SSSSS412734Bsdllwhrfgg22r_&jf 疵1D025，9858455054apdcyybird HYPERLINK mailto:sclkiDp sclkiDp19912312651GSG4293549fhabbin36lhxsiaei .gov19661D03CGD1105438413bdhyrne HYPERLINK

19、mailto:292v1t_ 292v1t_j%51uD128112S54121274psgmtiuduSoE HYPERLINK mailto:E E19911DD29SO13S4+K1623iwzcwfi5f(baqG.net1W51：D1562(16)查詢學(xué)號850955252的學(xué)生的姓名和選修的課程名及成績。(lQcal).SchaolSQLQuery勵而qlj (1口匚甘1)京hgl - SQLQuei select snaiue, cname tnarae . scot efrom SLUDENTS.BOURSES.TEA：方EES.2HOI2E5where CHOZCES sid

20、=c50and CHOICES . cid=coiiiises - cidand CHCICES.tld=TEACtiERS . tld;回結(jié)果_J消息msname cname tname scone(17)查詢與學(xué)號850955252的學(xué)生同年級的所有學(xué)生資料。(locBiySchool -SQLQuery2O.sql ClocaI).School - SQLQuerselect frarn STUDENTSw：here gi-ade =(SElect grade Irani 5TUDEETSwneie aid = zSOSSSS11? 1_5結(jié)果_j消息sidsnameemailg啟曲1:

21、8D102BO44 :ztozkr33ISrn l mykh.go v2D012觥叫1出9pgmrkdhhxpqi2wc 屈hrjtp .edu20D13BCDD70739nkzdnfq HYPERLINK mailto:ptc7n ptc7n20014順 152632qtxblqisvtdvh(mnBU .&du頌15蜘湘赧ctvxn5b&z(g2CD16齡1枷花aimnfvytkyo xeh.ong2D0175C317714Gvaesalavet la 7d4p 2CD1xiraegdlg HYPERLINK mailto:_rcw_itimbc.Bdu _rcw_itimbc.Bdu2D

22、015W259dwjny HYPERLINK mailto:9pax 9pax20D11D顧269肝5vqjfxfci5w9bm (s 2CD1(18)查詢所有有選課的學(xué)生的詳細(xì)信息。(leeal).liool -正在一.-SQLQue-iyZS.sqP(lacal).h&ol - QLQu&ryl9.5qK-xJ- 一 F _可用魴岸zt *. uh口 i. 巳 5wheie cid is not null and students - sid = choices. sidrrrJ結(jié)果消息aidsnameemailgradenosidtidGic ,1B43750723tdlgyqznmaw

23、ri91 th .govsowW75D723卻倒路D4ioL2shrnnxmcjhtz5si (pK2W3皿尚如2734521C3邸511NS2nfdngjxtndvfS199554)2172324S8511DD622331SI2701C4S1DD52WIjipljykq&j l195S5A2174小涌泓&2S49M4021C5654553bzorzlawil lybyp.Qrg20C45A2175916S654565032072114391CGS144244S3gasrprntqd HYPERLINK mailto:5 520D2M217SA32S144244S270542S-131C1oj

24、wsasbvdqvuumn93tpk .com2MM5A21S1735O4395S524992S2591CS&7G2OC135gthkrrijkovtqfj.&ng20045A2154443S7G20G1352175431721C(19)查詢沒有選修10005課程的學(xué)生姓名。(local).CchQol -正在.-&QLQuery20.iql* (localySchool - &QLQu&ryL9.sql*select saine|from sfjdeLts, choiceswhere BtJidentB Bid choices Bidand choices sid not in select

25、 sid from choLces where cid=L005f :J結(jié)果消息snsme1 eltfjvs2jacqdwaj3hyfnt/rnvl4z&ovli5mxcvkvwzg6ugjdlpr7xerf8uzmcgami9brkcgbdnt(20)查詢平均分不及格的學(xué)生人數(shù)。(lGcal)nSchcol -件摘要.select count ( Cxoitl sEudenus. clioices wrere choices.sil m(select choices. sid from ehoiees group by ctioiees .sid having avg (eTioieas .

26、 aearei ；)m村莊消息此列藥1：S3&JOOOW ：(21)找出選修課程成績最好的選課記錄。(Iaeal).li&l -QLQuery2.s.qFf (local.chool - SQLQue select *= rarci choices wriere scor已=莓丁 select ir.ax score fram cAoices41結(jié)果_J1消息nosidtidddscore154H&54M55&M219617250Z5531D10D39392501 D65M1S773&8fi17299G7079110D253935C1D51776煩247KB1+6594501096562295

27、7735451002S395BU4&177G22海41D1CD2D396301100110S1D63fi14fi228fi1356310D37397啊皿45WJ52G5SC22S7752011M3G39854)1111242BS31437552642222M1M27399501123339B3W7S712D921S7G61CDD9391Q511377制繃231151DD335911501167619R3491EE2226S54mE51(WM43912M117532731泌蹈1DD2159135011B1453316731132403244GB1004239145U1182251&1732969

28、-2S43753M1M37S3155011856678fi563fl73525404641.510C4739(22)找出和課程UML或課程C+的課時一樣課程名稱。(lseal)_&choel - QLQuery20.q(local).School - SElect -jEzciin COURSES wheze ncaz In ( select, noJi from C OURS E 5whsie cname= UML 心 cn.ane= 2|-一# rrr結(jié)果消息ddcnamehour1ZM5g-m-ffl21000Sdsts structureGO31DD14camp liter netwo

29、rk即利用集合運算，查詢選修課程C+或選修課程Java的學(xué)生的編號。(I oca I) .School - SQLQu eryZO.sql (loca Ij.S-c SElECt. aid from CHOICES were aid in ( select Bid from BOURSES where cnaifle = p j ava1 )select匚?wnere sid in ( select Bid from BOURSES wriere cname = F c一一1 Z3結(jié)果3消息yii_sidi 史提、S福i滿B0155516ES1231721SS5&35070GS90Q54&S4

30、71134B2SB31D44S32實現(xiàn)集合交運算，查詢既選修課程C+又選修課程Java的學(xué)生的編號。(locab.School -SQLQu&ryZOsql ClocalJ.Scho-Ql - SQLQu&rylS.sqrselect Bid tqhiCHOZCE5where sid in ( select aid ilornCOT7R5E5 -where cnaiiLe = c intexsect5ElEct aid iiornCHOICESwheie aidZ (select sid jEinm COURSES where cnaiae : j ava實現(xiàn)集合減運算，查詢選修課程C+而沒有

31、選修課程Java的學(xué)生的編號。(Iocal).&eheol -QL-Qu&ry20.sql* (localj.&chool - SQLQuetyig.s-qrSelectsli From GHQ二匚E5 BOURSESWheze COURSES cid CHOICES cid Azid. COURSES cnaiae=, c一一And sid not in(Eelect distinct sidFiom CHOICERfCOURSESWhexe CCUK5E5 .cid C?10-wE5 . cidand UCKJRSES cnaiae = j ava J結(jié)果 J1消息sidjSkS3S“ii

32、BBi豆方甘8002343370033103230D&12920G SD0&239377SDD5421實驗2.2空值和空集的處理實驗要求及步驟：(1) 查詢所有選課記錄的成績并將它換算為五分制(滿分5分，合格3分)，注意SCORE 取NULL值的情況。(hcal)chQcl -TQLQu&ryZsqF (localSchool - S select sid,tid,cid,score/20 score rcm cnoices4rI:結(jié)果j消息gidtidcidscore1&23IK9S2&24353649710D373z8293482732D25W416Vum43S5O955252234145

33、61心1002124E47KW742920434511DD2545M063591423W1 顆100334GR2S7S556227315S9GB10D2R37&2213713721SS22IK61CU113&23105022673460421CD054&由7湖56&2532)0517310M731D81352A1692253554921M23NULL11U119G7SS223441951110D213127543431522341DD4S4(2) 通過查詢選修編號10028的課程的學(xué)生的人數(shù)，其中成績合格的學(xué)生人數(shù)，不合格 的學(xué)生人數(shù)，討論NULL值的特殊含義。(local).S=SQunic

34、n.select co3t-1 sid：where cid.= F LDD2 = r1 co jt_3f3j.pa33 * from choices and score 604:結(jié)果U消息count stu755W2GW(3) 通過實驗檢驗在使用ORDER BY進行排序時，取NULL的項是否出現(xiàn)在結(jié)果中?如果有，在什么位置？(locBl).School -SQLQueryS.sqi* Clocal).School - SQLQuerylselectchoicesoidezc by scenew函結(jié)果鳥消息nosidtidddscore1W1&837B42393542539724521畋NULL

35、2迥瀏E4S5K4+Gm452盛1DD2DNULL354)21734632333357611CD14NULL454)2191B2CB61SS7J0D2057755411C319NULL55D22A1747B3fl-2138SD265723fl891CDZDNULL6502200559S27510134253S3795410054)NULL75C221295155345275523也MNULL502217177E2392712flSSfi101C2J3NULL954122349D4S34S1411426063311DD13NULL10觀233164B5415321-82873iJ2fl671Cu

36、J1NULL11502233654BU7DGS2S2M67319B10041NULL12502233702W542Z43Z52fi24M71D022NULLI35OZZ454I 1E7I378I I7Z45346760I00S3NULL1454J2246397SS72725SS2117913SS10M2NULL(4)在上面的查詢過程中如果加上保留字DISTINCT會有什么效果?(local).Shool -正在.-SQLQueryl.sqr Clocal).School -select diBtrnct |from ehoices older by score園結(jié)果直消息nosidtidcid

37、scone1MJ21S337S545239394259724521uD31NULL254)21 G9DSG543&S&UG273452A5G1uD20NULL35fl217M6&2333357A11DD14NULL454)2131220577M11：D19NULL5502201747S30213SS-D265723W10020NULL6502203毆52751D134253B373541uD50NULL754)22129915St5345267552iSS1000SNULL8的2217177B52M9271NULL954)2234904894S1411426fl68fl33110013NULL

38、1fl541223516455415321B2&73021睥1NULL11卸2233654B147A6B2325473-1381DD41NULL12502233702顧54224325-224B471DD22NULL(5) 通過實驗說明使用分組GROUP BY對取值為NULL的項的處理。(local)_&chool - SQLQuery2.sqF*| (local).S select scoieflorn cjioicesgzrcuip by scoze國結(jié)果上消息score國結(jié)果_、消息aver_scon& total_num max_scane min_scane sum_scone(lo

39、cal).School - SQLQuery2;sql* (local).School - SQLQueryl.sql*select avg (scorei aver score,P count ( k) total rmm1Pmax (score) max 3c.orefmin (score rain score, sum (score J sum scorefronci choices293440995020517649結(jié)合分組，使用集合函數(shù)求每個同學(xué)的平均分、總的選課記錄、最高成績、最低成 績和總成績。J結(jié)果消息aver_scone total_num max_scone min_scone sum_sconeD NULL NULL NULL(7) 查詢成績小于60的選課記錄，統(tǒng)計總數(shù)、平均分、最大值和最小值。(local).School - SQLQueryZ.sql*(Iocal).School - SQLQueryl.sql*select avg (sc-orei aver score, count ( J total num.rmax (sc-ore) max scoreiFw：here scQxe0NULLmin (score) niin 3core,r sum (score) swi scorefrom choices（8） 采用嵌套查詢的方式，利用比較