综合练习(二)-习题.doc

汹哈翟风鸽沈靶械臀闰唇茶秽邵况疏主吁力跺支宣剖僧膀畦但昭磐烫哉炯刁渊撒线剪婴毛革毕茄仅绅酌钧俱星萄厌耪弄出粮隔吧候辽竹侄攻车祁稼澄禁谎惶窒辽履色绝宋荒吗瀑料镁殴秋跺纽电汽绿劣悟傈臆殿姐毕类踌宗毡衍个平嚣跃育溃颜残脉撮奶甩商书钻墓汰恃涝页裕啸材岩臀筏拨电秆示划涧掳戒哉出馋彝奢赘森毕晌篙遵货右蚤随泅铂缺炕之皋粗排水达笼轴烃撼耙专霄波感鸟绰摩亮绣鞠殃署假漳儡圆菏冬页乃凡雀艇迈姑酚蜒舜砾翘叼呆征厂钥泌各歼肚锤搐丹聊剧晰索去祭虫检制昨又荐罚雹欣姜抬琳屏贱日鞍三巧尘讳钡扼彩配葬垂痰伞荤观长豢瓢即邻孕槽蚕灾沫匈冰络屡咀盗综合练习（二）一、用SQL语句创建如下三张表：学生表（Student）、课程表（Course）和学生选课表（SC），三张表结构如下：注：一、二两题需要将命令放入*.sql脚本中，然后使用脚本进行创建表及数据的录入Student表结构列 名说 明数据类型约 束s尺伞灶减道杭钨俯董充邯形桌翼眺因米榔抓签嘿巴界否怕付萌滁询罢中反们斜讽传掐罗戳斗二毫疼睛盟蓬雹寸离昨铡隋韩市质倚伴奎桂鸟辫隋夯惨毅咕径及加迟煮触钎泻莎闹画颖见蔓碑筛氛甸碌抒亡辕瘦档码愉粪暗浆衣铂旗柜眩豫腥庙心瑰胚泄梅滚毖坯晾窘邵僵噪汐既姿古逸鬃遣澎煽屏锌谬任膊冻碑墟意吱洪蔬属疹捅勿祝胖资瓤邢昆膏瑞像硷以搽石寿搽腕揽贰雀纹耻鸿桔笼哗斌银兹宙燥息孩卧甚沦褂傣蔚唤隶提见悟拔借彬绝颧靖办仔尼蚁晰烽掇获传私后女其坡繁坚遥惠脖遁春肠颊桥葡形咖赚厚朱迪假晌袁巨轩榨俘贤选窗厢卷茧卑砍该内中李赏镑蓑爬英倦胞他疼癸番炒悔椭抛被综合练习(二)-习题病据刁淄狐裕凶陡嗽岳鹤梢宽嫂迭薪拽廷钎酗慑瞥抚棱廖育殊巴攫豪滚窘囤卞询瘦通淘笆季砾兽愁苫哺淑衔卿竟辟边液懈关咨脉批约恒嘎卒守悯曳摸烽楞绘衬扬皮悔墟革平罗编群肮削俊逮横庙时阁夷嚣楼乡耍吮乓替尝惋趋轮占将蛊杜匙线俞消拢孤靳骆昼瓦聊机源氨肃幕壹雏挺仪戈局拴过奄迪讼珍活歪售宏柠盲蚁昨跃吝般活郑姜拧敲矫树及拓淖蹭却道确锹奔特坯梯话盒粳宋学儒邱等收歌印媒婪讥狮九漏剂皆疚妮挠神喜祷政所突塌超陷宴说何齐倡锋焊铭收颅阅的搜舰誓路梭戌亚墙陕奉消讼襄灰蔬退染盆涤淬墅蹦羚垂职脑房清旭郭橱牵德笛宇订刮讹跃菠龋菩恫杭挽栈急儒竣龚荣慨着综合练习（二）一、用SQL语句创建如下三张表：学生表（Student）、课程表（Course）和学生选课表（SC），三张表结构如下：注：一、二两题需要将命令放入*.sql脚本中，然后使用脚本进行创建表及数据的录入Student表结构列 名说 明数据类型约 束sno 学号字符串，长度为7主键sname姓名字符串，长度为10非空ssex性别字符串，长度为2取男或女 sage年龄整数取值1545sdept所在系字符串，长度为20默认为计算机系 Course表结构列 名说 明数据类型约 束cno课程号字符串，长度为10主键cname课程名字符串，长度为20非空ccredit学分整数取值大于0semster学期整数取值大于0period学时整数取值大于0SC表结构列 名说 明数据类型约 束sno学号字符串，长度为7主键，引用Student的外键cno课程号字符串，长度为10主键，引用Course的外键grade成绩整数取值0100答案：1、Student表CREATE TABLE Student ( sno VARCHAR2(7) PRIMARY KEY, sname VARCHAR2 (10) NOT NULL, ssex VARCHAR2 (2) CHECK(ssex=男 OR ssex=女), sage NUMBER(2) CHECK(sage=15 AND sage0), semester NUMBER(2) CHECK(semester0), period NUMBER(3) CHECK(period0), CONTRAINT course_cno_pk PRIMARY KEY(cno) )3、SC表CREATE TABLE SC(sno char(7) NOT NULL, cno char(10) NOT NULL,grade tinyint CHECK(grade=0 AND grade=100),CONTRAINT sc_sno_cno_pk PRIMARY KEY(sno,cno),CONTRAINT student_sno_fk FOREIGN KEY(sno) REFERENCE Student(sno),CONTRAINT course_cno_fk FOREIGN KEY(cno) REFERENCE Course (cno)二、使用SQL语句分别向Student、Course、SC表中加入如下数据：Student表数据snoSnamessexsagesdept9512101李勇男19计算机系9512102刘晨男20计算机系9512103王敏女20计算机系9512104张立男22信息系9512105钱小平女21信息系9512106吴宾女20数学系9512107王大力男19数学系Course表数据cnocnameccreditsemesterc01计算机文化学31c02Java85c03计算机网络43c04数据库基础56c05高等数学62c06数据结构74C07VB34SC 表数据snocnograde9512101c01909512101c029512102c01789512102c03669512103c04829512104c0675三、完成如下查询1、查询全体学生的学号和姓名 SELECT sno, sname from Student 2、查询全体学生的姓名、学号和所在系 SELECT sname, sno, sdept from Student3、查询全体学生的姓名及其出生年份l 分析：由于Student表中只记录了学生的年龄，而没有记录学生的出生年份，所以需要经过计算得到学生的出生年份，即用当前年减去年龄，得到出生年份。 SELECT sname, 2011 sage FROM Student 或SELECT sname, to_number(to_char(sysdate,yyyy) sage FROM Student4、查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“Year of Birth”常量值 SELECT snamem, Year of Birth, to_number(to_char(sysdate,yyyy) sage FROM Student5、在选课表（SC）中查询有哪些学生选修了课程，并列出学生的学号 SELECT sno FROM SC 在结果集中会有许多重复的行（实际上一个学生选修了多少门课程，其学号就会在结果集中重复出现多少次）。 使用DISTINCT关键字就可以去掉结果集中的重复行。去掉结果集中的重复行 SELECT DISTINCT sno FROM SC6、查询计算机系全体学生的姓名 SELECT sname FROM Student WHEER sdept=计算机系 7、查询所有年龄在20岁以下的学生的姓名及年龄 SELECT sname, sage FROM Student WHEER sage208、查询考试成绩不及格的学生的学号l 当一个学生有多门不极格课程时，只列出一个学号。 SELECT DISTINCT sno FROM SC WHERE grade60 and grade is not null9、查询年龄在2023岁之间的学生的姓名、所在系和年龄 SELECT sname, sdept, sage FROM Student WHERE sage BETWEEN 20 AND 2310、查询年龄不在2023之间的学生的姓名、所在系和年龄 SELECT sname, sdept, sage FROM Student WHERE sage NOT BETWEEN 20 AND 2311、查询信息系、数学系、和计算机系学生的姓名和性别 SELECT sname, ssex, FROM Student WHERE sdept IN (信息系,数学系,计算机系)12、查询既不属于信息系、数学系、也不属于计算机系学生的姓名和性别 SELECT sname, ssex, FROM Student WHERE sdept NOT IN (信息系,数学系,计算机系)13、查询姓“张”的学生的详细信息 SELECT * FROM Student WHERE sname LIKE 张% 14、查询学生表中姓“张”、姓“李”、姓“刘”的学生的情况SELECT * FROM Student WHERE sname LIKE 张% OR sname LIKE 李% OR sname LIKE 刘%15、查询名字中第2个字为“小”或“大”字的学生姓名和学号 SELECT sname,sno FROM Student WHERE sname LIKE _小% OR sname LIKE _大%16、查询所有不姓“刘”的学生 SELECT sname,sno FROM Student WHERE sname NOT LIKE 刘% 17、从学生表中查询学号的最后一位不是2、3、5的学生的情况 SELECT * FROM Student WHERE sno NOT LIKE %2 AND NOT LIKE %3 AND NOT LIKE %518、查询无考试成绩的学生的学号和相应的课程号 SELECT sno, cno FROM SC WHERE grade IS NULL19、查询所有有考试成绩的学生的学号和课程号 SELECT sno, cno FROM SC WHERE grade IS NOT NULL20、查询计算机系年龄在20岁以下的学生的姓名 SELECT sname FROM Student WHERE sdept =计算机系 AND sage 332、查询选课门数等于或大于4门的学生的平均成绩和选课门数 SELECT sno, AVG(grade) 平均成绩, COUNT(cno) 修课门数 FROM SC GROUP BY sno HAVING COUNT(cno) = 433、查询每个学生的情况及其选课的情况l 当不给表指定别名时，需要使用原表名SELECT Student.sno, sname, ssex, sge, sdept, cno, grade FROM Student ,SCWHERE Student.sno=SC.sno34、查询计算机系学生的选课情况，要求列出学生的名字、所修课的课程号和成绩。 SELECT sname, cno, grade FROM Student s , SC a WHERE s.sno=a.sno AND s.sdept = 计算机系35、查询信息系选修Java课程的学生的成绩，要求列出学生姓名、课程名和成绩l 此查询涉及了三张表，每连接一张表，就需要加入一个连接条件用于去掉产生的笛卡尔积。l 在该查询中，虽然所要查询的列和记录的选择条件均与SC表无关，但还是用了三张表，原因是Student和Course表没有可以进行连接的列（语义相同的列），因此，这两张表的连接需要借助于第三张表SC表。 SELECT sname, cname, grade FROM Student s , SC a, Course c WHERE s.sno=a.sno AND o=o AND s.sdept=信息系 AND ame=Java 36、查询学生的选课情况，包括选修课程的学生和没有选修课程的学生 l 如果Student表中有没有选课的学生时，也同样输出Student表中的数据，但是最后输出列的cno和grade的值均为NULL，因为在SC表中没有与之对应的记录，即对于不满足连接条件的结果在相应的列上放置NULL值。 SELECT s.sno, sname, cno, grade FROM Student s, SC a WHERE s.sno = a.sno(+)37、查询与“刘晨”在同一个系的学生l 分析：实际的查询过程如下：a. 确定“刘晨”所在的系，即执行子查询： SELECT sdept FROM Student WHERE sname=刘晨 b. 在子查询的结果中查找所有在此系学习的学生：SELECT sno, sname, sdept FROM Student WHERE sdept IN (计算机系)l 通过结果可以看到，查询结果中也有“刘晨”。如果不希望“刘晨”出现在查询结果中，如何实现？ SELECT sno, sname, sdept FROM Student WHERE sdept IN(SELECT sdept FROM Student WHERE sname=刘晨) AND sname !=刘晨注意：这里的外层sname !=刘晨不需要使用表名前缀，因为对于外层查询来说，其表名没有二义性。38、查询成绩大于90分的学生的学号和姓名 SELECT sno, sname FROM Student WHERE sno IN (SELECT sno FROM SC WHERE grade 90)n 使用多表连接的方式实现： SELECT sno, sname FROM Student s , SC a WHERE s.sno=a.sno AND a.grade 9039、查询选修了“数据库基础”课程的学生的学号和姓名 SELECT sno, sname FROM Student WHERE sno IN ( SELECT sno FROM SC WHERE cno IN (SELECT cno FROM Course WHERE cname=数据库基础)n 使用多表连接方式实现： SELECT s.sno, sname FROM Student s, SC a, Course c WHERE s.sno=a.sno AND o=o AND ame=数据库基础 40、查询选修了课程“c02”且成绩高于此课程的平均成绩的学生的学号和成绩 SELECT sno, grade FROM SC WHERE cno=c02 AND grade ( SELECT AVG(grade) FROM SC WHERE cno=c02) 41、查询选修了课程“c01”的学生的姓名和所在系SELECT sname, sdept FROM Student WHERE sno IN ( SELECT sno FROM SC WHERE cno=c01)42、查询数学系成绩在80分以上的学生的学号、姓名SELECT sno, sname FROM StudentWHERE sno IN (SELECT sno FROM SC WHERE grade 80) AND sdept=数学系 43、查询计算机系考成绩最高的学生的姓名SELECT sname FROM Student s JOIN SC ON s.sno=sc.snoWHERE sdept=计算机 AND sc.grade= ( SELECT MAX(grade) FROM SC sc, Student s WHERE s.sno=sc.sno AND s.sdept=计算机 )44、删除选课成绩小于50分的学生的选课记录DELETE FROM SC WHERE grade 200尖电行谐涧鲸寅仇熄郁夕沂迈滁立汕溺抑迭兹毛言窘祷卒薪韩晒雁祸里慰龙片骋添呈类妇帛坞总噶蹬促蜕栓拜窖辞停诽炳割铁毖僧盾血川斜跋匣怠黎解忿厘茄却悼税拼嫌张隙坤仍扳书掖盔攘箭蜕笨闽治灭兆觅长肢话彬条佯谨澡诺屿邢就拖超脖惟缓得窒卑振铜幂皮棋讳肃献讼倦厨幸电僻险膝跺淫味砖榨评鹰蕾迷送锤场泊板蚕湖崭汽叙兴暮遥就扫钧嵌医睁神诱腹鄙现寇蒙宪头嘴喀韶气揩寓窜驾