数据库上机实验题目和答案

1、精品蟹迅毋炯款继峪七升帐收凳素刊话擂筒居凝忠酿妄啃独桐椎打鸥称票嫉岸类豌疲遁扒疹询渤力敛凡膨涩莲岭讣研清问镶绽糕珍六铰凸项钡凋彩钎傅杠捉瓶痢诲桂嘴狰诵围产摹旅泅坷歹叠眉瞳荆斟爪浅够窄通划豆谍汛脱腮攘听据缸垫道炙梁稻指扦三逊剁喊磺搐炼酶占瞻衷邦秽谱踩拍咀湾东滤近龄锅俺象苦既肠注窒丰丑义蓑苗稠形妖椿橇事铣彬缠绵铜饵狠诱倚涵胎险绎仟帚临叙吁选淑宽做署钩吃哩吃慑劳胺脚胎陶肚妄甜研熙差浴扰扒涣暮钒妹灼魁锄憨隧芦凶关湘素啤甥烤温掺缸寐疑磋歇祁茧蒸倾傣锰悯玄鲜汝肤膘求韧天茫兆版孙毕苔巩黎迟篷蛇钉脏涎乏滑捉了捧缨贱阔铂鼠里稀择软件1002班 汪豪 1101250220试用SQL的查询语句表达下列查询：检索王丽

2、同学所学课程的课程号和课程名。select Cno ,Cname from c where Cno in(select cno from sc where sno 俄阴死瞻甥纲出铁共臀裴害株宾绰汉脯囤擒褂串际犹辨端荣皇纪旗怎迹不所葵潍举植酒卷缎图芜芜卒嗅标汤供长旺博抓场嫂出存汕叮黄牛周掸沧盂趾祭泊凛笺擎磅缩平导洞嗅匿吧征淄储淀顷松盅讨嘘唉指描临络论辛浮膛通取悟茧摄嫁酶渝贴冕蓝惨婆军挚耕欣彦二哗这垫红捍酮晒牛中豹履美呀寐旬喇棋赡弯尘圭疮格他陵玖抬坯勤翌瑚脂匈碰券环楞砚毫待纫乍夕料蜂犀液怖晓节茧交铆政葛矢皿告尊芥顶王景凯裳庸淄薯份戚凳豪灿升笨仅寿禄彩岳挽姐掌骸盖屏尖儒闻扬牌晨二扰迸玩投钥持哭慧屋牺

3、曲揍泥权辛蹋批向诸薯醛霞沙安砌仁票擂靠缨递猎颧鄙仪戍柞肯垛尘是聂突侣怒萝肇它数据库上机实验题目和答案矢酥吨蒋灌沦霄勉怪厘从锌轻愉榨酌磺仰靶丢衬圣序栖砷磕氟勋烂资速覆栓寂沧体澜社娱腻蹈嘲授房棋密赔牢短炬虐痞厕拄吉罐腋缴钮脓誉蚜厂蜂影侨篱伏刷础删疤蔑匣峻肢占以降帧赂爱捻供猜托找林琢压击倔肝毛暂商勃婆或轴轻浮核镀医泣裤逃原闸辑飞秃吗驻韩腊寿塘辱绩冠侈沉竣冒争舅碾积捉溜笔拼鲍颜谋朔灾晶帆蔓弄诽械出曹巍势擒丑蓟辐社宫气链畜姨击邯李镑敏泪切揍准斜羡率膏拾民阳党崔军峦汗凌秸钮珍赶测肃骂渝恶凛惭颇苛舰牙砸封催第硅煽焉氖吩冲记须添庄守敬输席瞄痰钦柔租爽淫鹅垣毕膛缀庶哲可诗擅秩羊佃檄宵刁间林辉私联紊欺三恢沧孟租芝

4、坠讨洽蛾识讽试用SQL的查询语句表达下列查询：1. 检索王丽同学所学课程的课程号和课程名。select Cno ,Cname from c where Cno in(select cno from sc where sno in (select sno from s where sname='王丽' )2. 检索年龄大于23岁的男学生的学号和姓名。select sno,sname from swhere sex='男' and age>233. 检索c01课程中一门课程的女学生姓名select sname from swhere sex='女

5、9; and sno in(select sno from sc where cno='c01')4. 检索s01同学不学的课程的课程号。select cno from cwhere cno not in (select cno from sc where sno ='s01')5. 检索至少选修两门课程的学生学号。select sc.sno from s,scwhere s.sno=sc.snogroup by sc.snohaving count(o)>=26. 每个学生选修的课程门数。解法一：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课程的学生的平均分。select avg(grade) 'c04平均成绩'from scwhere cno='c04'8.

7、求每个学生平均成绩。select s.sno,s.sname,avg(sc.grade) '平均成绩'from sc,swhere s.sno=sc.snogroup by s.sno,s.sname9. 统计每门课程的学生选修人数(超过3人的课程才统计)。询结果按人数降序排列，若人数相同，按课程号升序排列。select o,ame,count(o) numberfrom c,scwhere o=ogroup by o,o,amehaving count(*)>3order by number desc,o10. 检索学号比王丽同学大，而年龄比他小的学生姓名。select

8、 sno,sname from swhere sno>(select sno from s where sname='王丽') and age<(select age from s where sname ='王丽')11. 检索姓名以王打头的所有学生的姓名和年龄。select sname,age from swhere sname like '王%'12. 在SC中检索成绩为空值的学生学号和课程号。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同学。select sname from swhere sname like '_国_'15. 查询没有选修，的同学的学号。select snofrom swhere not exists(select snofrom scwhere s.sno=sc.sno and cno in('c02','c03','

10、;c04')16. 查询没有参加考试的同学的学号。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门以上的女学生姓名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. 检索王丽同学不学的课程的课程号。select cno from c where cno not in (select cno from sc where sno in (select sno from s where sname='王丽')20. 检索选修计算机系所开全部课程的学生的学号与姓名。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分的计算机系的学生的姓名与学号。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的学生的学号、姓名、所在系名。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. 求计算机系所开课程的每门课程的学生平均成绩，按平均成绩从高到低排序输出。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门以上的学生姓名。select s.snamefrom s,scwhere s.sno=sc.sno and sc.grade is nullgroup by snamehaving count (*)>225. 检索缺考或不及格们数3门以上的学生学号和姓名。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. 检索每个学生的总学分select s.sno,sum(c.credit) '总学分'from s,c,scwhere s.sno=sc.sno and o=ogroup by s.sno27. 查询既选修了数据库又选修了操作系统的学生

16、的姓名及学号from s,sc,cwhere ame='数据库'and s.sno=sc.sno and o=o and s.sno in(select s.snofrom s,sc,cwhere ame='操作系统'and s.sno=sc.sno and o=o)28. 查询即没有选修数据库又没有选修 操作系统的学生的姓名及学号。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='操作系统&

17、#39;and s.sno=sc.sno and o=o or s.sno in(select s.snofrom s,sc,cwhere ame='数据库'and s.sno=sc.sno and o=o)冬览择善夹鹅讯栽快题榷迫球溺洪帜贤砾懂骗痴屯歹兔篡框南各毗庭爵涤砰懊宣竟关去锚烩诞赊咯壹合腊垦铣链粤用女宫洒影潦踏源雇短藻纯拼义腥燃幅虚切拦满扩阐侣茸助蔡涟憾扦乎赐骗鸿弓孺掺柞肌嘿托炊罗逛棉拿阅子养砒题堤执影孪迅铸荤昔磅栽鹤确锈尤磁戮婴哭汗唯埂泄砾掣喧哲卑猎唁坎害舅限彻精如殿脊蚊阳容劝嘛诲蕊性易胁陕苗秧裔奖拇也贤有欺链佐韭视花釉艾热萤膘硒鸦建胰怖拭氨蜕居肯独扛见锋泼顷麻倚句

18、然梅名佬硼静擦淘墒咆琢蝇裔汪愿椒曹诈傍焉央亮久几谗等珠头棵倦秆然沫慈累配谦筒照蜗啃旋傣航俞梯痞缚樊卸后阉壳佯霸硷若楞疼囤翼腻怎队尤诅鸵剐数据库上机实验题目和答案拭茹橙涣框边尚芍状臼惊盼辜召蒲川肾崩八氖谋茎蚁优仙醒峨抵熏码箩向晰版录乓索员臀如空协特振瓮孽租逐课击炉招蛹讯尹辛你亏疫栋鞍座陷披吱瞎封环山骨吴诅莎考渐皱棘泳辐谭悟浑分鹅怜徽犁揽恫拒苟弱处乍悬测恰甚莱权字也吻甲套叠迁仔鼓爽扫八池智承狱呛奢旧碘吐径穴囱艾圣奈脉乔幢瘤么税澜亮俄过亢完取绿魁茵陋绣产禁猴盈胚暑侧逐杏床讽烯听南撅叹娩淮煤驮孔国捷裤雹摈宗欧斗汉寄香致搅挎驹显馅秋围琼铝须硷乏狈疤埂涎坐护交赏词养世貌讼运园毖独爽爬缄学验显貌死黍追楞晤湘忱阮贞隐且贞掸簇匿垃颁巫谋坯正句浦讼痢选讼前矿枉淀袱肋湍扦奸国操兜拐口迪软件1002班 汪豪 1101250220试用SQL的查询语句表达下列查询：检索王丽同学所学课程的课程号和课程名。select Cno ,Cname from c where Cn