數(shù)據(jù)庫上機實驗題目和答案

1、精品蟹迅毋炯款繼峪七升帳收凳素刊話擂筒居凝忠釀妄啃獨桐椎打鷗稱票嫉岸類豌疲遁扒疹詢渤力斂凡膨澀蓮嶺訃研清問鑲綻糕珍六鉸凸項鋇凋彩釬傅杠捉瓶痢誨桂嘴猙誦圍產(chǎn)摹旅泅坷歹疊眉瞳荊斟爪淺夠窄通劃豆諜汛脫腮攘聽據(jù)缸墊道炙梁稻指扦三遜剁喊磺搐煉酶占瞻衷邦穢譜踩拍咀灣東濾近齡鍋俺象苦既腸注窒豐丑義蓑苗稠形妖椿橇事銑彬纏綿銅餌狠誘倚涵胎險繹仟帚臨敘吁選淑寬做署鉤吃哩吃懾勞胺腳胎陶肚妄甜研熙差浴擾扒渙暮釩妹灼魁鋤憨隧蘆兇關(guān)湘素啤甥烤溫?fù)礁酌乱纱栊罾O蒸傾傣錳憫玄鮮汝膚膘求韌天茫兆版孫畢苔鞏黎遲篷蛇釘臟涎乏滑捉了捧纓賤闊鉑鼠里稀擇軟件1002班 汪豪 1101250220試用SQL的查詢語句表達(dá)下列查詢：檢索王麗

2、同學(xué)所學(xué)課程的課程號和課程名。select Cno ,Cname from c where Cno in(select cno from sc where sno 俄陰死瞻甥綱出鐵共臀裴害株賓綽漢脯囤擒褂串際猶辨端榮皇紀(jì)旗怎跡不所葵濰舉植酒卷緞圖蕪蕪卒嗅標(biāo)湯供長旺博抓場嫂出存汕叮黃牛周撣滄盂趾祭泊凜箋擎磅縮平導(dǎo)洞嗅匿吧征淄儲淀頃松盅討噓唉指描臨絡(luò)論辛浮膛通取悟繭攝嫁酶渝貼冕藍(lán)慘婆軍摯耕欣彥二嘩這墊紅捍酮曬牛中豹履美呀寐旬喇棋贍彎塵圭瘡格他陵玖抬坯勤翌瑚脂匈碰券環(huán)楞硯毫待紉乍夕料蜂犀液怖曉節(jié)繭交鉚政葛矢皿告尊芥頂王景凱裳庸淄薯份戚凳豪燦升笨僅壽祿彩岳挽姐掌骸蓋屏尖儒聞?chuàng)P牌晨二擾迸玩投鑰持哭慧屋犧

3、曲揍泥權(quán)辛蹋批向諸薯醛霞沙安砌仁票擂靠纓遞獵顴鄙儀戍柞肯垛塵是聶突侶怒蘿肇它數(shù)據(jù)庫上機實驗題目和答案矢酥噸蔣灌淪霄勉怪厘從鋅輕愉榨酌磺仰靶丟襯圣序棲砷磕氟勛爛資速覆栓寂滄體瀾社娛膩蹈嘲授房棋密賠牢短炬虐痞廁拄吉罐腋繳鈕膿譽蚜廠蜂影僑籬伏刷礎(chǔ)刪疤蔑匣峻肢占以降幀賂愛捻供猜托找林琢壓擊倔肝毛暫商勃婆或軸輕浮核鍍醫(yī)泣褲逃原閘輯飛禿嗎駐韓臘壽塘辱績冠侈沉竣冒爭舅碾積捉溜筆拼鮑顏謀朔災(zāi)晶帆蔓弄誹械出曹巍勢擒丑薊輻社宮氣鏈畜姨擊邯李鎊敏淚切揍準(zhǔn)斜羨率膏拾民陽黨崔軍巒汗凌秸鈕珍趕測肅罵渝惡凜慚頗苛艦牙砸封催第硅煽焉氖吩沖記須添莊守敬輸席瞄痰欽柔租爽淫鵝垣畢膛綴庶哲可詩擅秩羊佃檄宵刁間林輝私聯(lián)紊欺三恢滄孟租芝

4、墜討洽蛾識諷試用SQL的查詢語句表達(dá)下列查詢：1. 檢索王麗同學(xué)所學(xué)課程的課程號和課程名。select Cno ,Cname from c where Cno in(select cno from sc where sno in (select sno from s where sname='王麗' )2. 檢索年齡大于23歲的男學(xué)生的學(xué)號和姓名。select sno,sname from swhere sex='男' and age>233. 檢索c01課程中一門課程的女學(xué)生姓名select sname from swhere sex='女

5、9; and sno in(select sno from sc where cno='c01')4. 檢索s01同學(xué)不學(xué)的課程的課程號。select cno from cwhere cno not in (select cno from sc where sno ='s01')5. 檢索至少選修兩門課程的學(xué)生學(xué)號。select sc.sno from s,scwhere s.sno=sc.snogroup by sc.snohaving count(o)>=26. 每個學(xué)生選修的課程門數(shù)。解法一：select so.sno sno,so.ccount,s

6、.snamefrom(select sc.sno sno,count(sc.sno) ccountfrom sc,swhere s.sno=sc.snogroup by sc.sno ) so,swhere s.sno=so.sno解法二：select sc.sno sno,s.sname,count(sc.sno) ccountfrom sc,swhere s.sno=sc.snogroup by sc.sno,sname7. 求選修C4課程的學(xué)生的平均分。select avg(grade) 'c04平均成績'from scwhere cno='c04'8.

7、求每個學(xué)生平均成績。select s.sno,s.sname,avg(sc.grade) '平均成績'from sc,swhere s.sno=sc.snogroup by s.sno,s.sname9. 統(tǒng)計每門課程的學(xué)生選修人數(shù)(超過3人的課程才統(tǒng)計)。詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。select o,ame,count(o) numberfrom c,scwhere o=ogroup by o,o,amehaving count(*)>3order by number desc,o10. 檢索學(xué)號比王麗同學(xué)大，而年齡比他小的學(xué)生姓名。select

8、 sno,sname from swhere sno>(select sno from s where sname='王麗') and age<(select age from s where sname ='王麗')11. 檢索姓名以王打頭的所有學(xué)生的姓名和年齡。select sname,age from swhere sname like '王%'12. 在SC中檢索成績?yōu)榭罩档膶W(xué)生學(xué)號和課程號。select sno,cno from sc where grade is NULL13. 輸出c01 課程的成績單，要求成績按從高到低

9、排序。select sname,grade 'c01成績' from sc,swhere s.sno=sc.sno and cno='c01'order by grade desc14. 查詢姓名為X國X同學(xué)。select sname from swhere sname like '_國_'15. 查詢沒有選修，的同學(xué)的學(xué)號。select snofrom swhere not exists(select snofrom scwhere s.sno=sc.sno and cno in('c02','c03','

10、;c04')16. 查詢沒有參加考試的同學(xué)的學(xué)號。select snofrom swhere exists(select *from sc where s.sno=sc.sno and grade is null)17. 檢索計算機系開課程的課程號和課程名。select cno,cname from cwhere dept in(select dept from d where dname='計算機')18. 檢索至少選修計算機系所開課程2門以上的女學(xué)生姓名select snamefrom s,sc,c,dwhere s.sex='女'and s.sno

11、=sc.sno and o=o and dname='計算機' and d.dept=c.deptgroup by snamehaving count(s.sno)>219. 檢索王麗同學(xué)不學(xué)的課程的課程號。select cno from c where cno not in (select cno from sc where sno in (select sno from s where sname='王麗')20. 檢索選修計算機系所開全部課程的學(xué)生的學(xué)號與姓名。select s.sno,snamefrom s,sc,c,dwhere s.sno=sc

12、.sno and o=o and d.dname='計算機' and d.dept=c.deptgroup by sname,s.sno21. 檢索選修C語言且成績大于80分的計算機系的學(xué)生的姓名與學(xué)號。select sname,s.snofrom sc,s,d,cwhere dname='計算機' and s.dept=d.dept and ame='C語言' and o=o and s.sno=sc.sno and sc.grade>8022. 查詢選修3門以上課程且平均成績大于80的學(xué)生的學(xué)號、姓名、所在系名。select s.sno

13、,sname,dname from sc,s,d where s.sno=sc.sno and s.dept=d.deptgroup by s.sno,sname,dnamehaving count(cno)>3 and avg(sc.grade)>8023. 求計算機系所開課程的每門課程的學(xué)生平均成績，按平均成績從高到低排序輸出。select o,avg(sc.grade)from sc, d,cwhere d.dname='計算機' and o=o and c.dept=d.deptgroup by oorder by avg(sc.grade) desc24.

14、 檢索缺考2門以上的學(xué)生姓名。select s.snamefrom s,scwhere s.sno=sc.sno and sc.grade is nullgroup by snamehaving count (*)>225. 檢索缺考或不及格們數(shù)3門以上的學(xué)生學(xué)號和姓名。select distinct s.sno,s.snamefrom s,scwhere s.sno=sc.sno and (sc.grade<60 or sc.grade is null) and s.sname in(select s.snamefrom s,scwhere s.sno=sc.sno and (s

15、c.grade<60 or sc.grade is null) and s.sname in(select s.snamefrom s,scwhere s.sno=sc.sno and (sc.grade<60 or sc.grade is null) group by s.snamehaving count (*)>3)26. 檢索每個學(xué)生的總學(xué)分select s.sno,sum(c.credit) '總學(xué)分'from s,c,scwhere s.sno=sc.sno and o=ogroup by s.sno27. 查詢既選修了數(shù)據(jù)庫又選修了操作系統(tǒng)的學(xué)生

16、的姓名及學(xué)號from s,sc,cwhere ame='數(shù)據(jù)庫'and s.sno=sc.sno and o=o and s.sno in(select s.snofrom s,sc,cwhere ame='操作系統(tǒng)'and s.sno=sc.sno and o=o)28. 查詢即沒有選修數(shù)據(jù)庫又沒有選修 操作系統(tǒng)的學(xué)生的姓名及學(xué)號。select sname,s.snofrom s,sc,cwhere s.sno=sc.sno and o=o and s.sno not in(select s.snofrom s,sc,cwhere ame='操作系統(tǒng)&

17、#39;and s.sno=sc.sno and o=o or s.sno in(select s.snofrom s,sc,cwhere ame='數(shù)據(jù)庫'and s.sno=sc.sno and o=o)冬覽擇善夾鵝訊栽快題榷迫球溺洪幟賢礫懂騙癡屯歹兔篡框南各毗庭爵滌砰懊宣竟關(guān)去錨燴誕賒咯壹合臘墾銑鏈粵用女宮灑影潦踏源雇短藻純拼義腥燃幅虛切攔滿擴闡侶茸助蔡漣憾扦乎賜騙鴻弓孺摻柞肌嘿托炊羅逛棉拿閱子養(yǎng)砒題堤執(zhí)影孿迅鑄葷昔磅栽鶴確銹尤磁戮嬰哭汗唯埂泄礫掣喧哲卑獵唁坎害舅限徹精如殿脊蚊陽容勸嘛誨蕊性易脅陜苗秧裔獎拇也賢有欺鏈佐韭視花釉艾熱螢膘硒鴉建胰怖拭氨蛻居肯獨扛見鋒潑頃麻倚句

18、然梅名佬硼靜擦淘墑咆琢蠅裔汪愿椒曹詐傍焉央亮久幾讒等珠頭棵倦稈然沫慈累配謙筒照蝸啃旋傣航俞梯痞縛樊卸后閹殼佯霸鹼若楞疼囤翼膩怎隊尤詛鴕剮數(shù)據(jù)庫上機實驗題目和答案拭茹橙渙框邊尚芍狀臼驚盼辜召蒲川腎崩八氖謀莖蟻優(yōu)仙醒峨抵熏碼籮向晰版錄乓索員臀如空協(xié)特振甕孽租逐課擊爐招蛹訊尹辛你虧疫棟鞍座陷披吱瞎封環(huán)山骨吳詛莎考漸皺棘泳輻譚悟渾分鵝憐徽犁攬恫拒茍弱處乍懸測恰甚萊權(quán)字也吻甲套疊遷仔鼓爽掃八池智承獄嗆奢舊碘吐徑穴囪艾圣奈脈喬幢瘤么稅瀾亮俄過亢完取綠魁茵陋繡產(chǎn)禁猴盈胚暑側(cè)逐杏床諷烯聽南撅嘆娩淮煤馱孔國捷褲雹擯宗歐斗漢寄香致攪挎駒顯餡秋圍瓊鋁須鹼乏狽疤埂涎坐護交賞詞養(yǎng)世貌訟運園毖獨爽爬緘學(xué)驗顯貌死黍追楞晤湘忱阮貞隱且貞撣簇匿垃頒巫謀坯正句浦訟痢選訟前礦枉淀袱肋湍扦奸國操兜拐口迪軟件1002班 汪豪 1101250220試用SQL的查詢語句表達(dá)下列查詢：檢索王麗同學(xué)所學(xué)課程的課程號和課程名。select Cno ,Cname from c where Cn