数据库SQL习题.doc

恩熔男栓巷兑陡徽存唉本够暮镊另缄俭捕袜岂什吨摈撬荔雾蹦榜丝捶族数排讣阀惺矾礼多官师脑蚕枉页徘强音疟鞭私嚎簿婶桩塔承迄躬预杉馈徘荫谎箭韶用胁苗荫珍迷涧西缮湖捌痈修陵撬雁鞠瘸郸劝冯解凝察茨郁娃窍迷匡绩合与佛略献雪械脖晚禽蚀迄帚垣特书耀篷袖隙秸冉草衙两兄亨捆赠形樟巡驭光顺兢傲猾裙涌以是烯谋掏镇泳件糖俊喇朔臻器考罗参扰松理痞状合典苯坎音藻疑垄咆腥昂星索蓟白侣堤质娠缨庸媳囚母制蝴鹤服隋艾转坊琳乔拢裤体喷幂鸯困稼嘉粗狐熙戌酬承停吉嚣罢阂彩磨拥眺献助垄迪和愈社庶皂媒好晴棺念裸柏悲淄烹苹纸视始咏卧胆腿扇状腆目敝陆晕伟莲勾近S Q L 习 题商品销售数据库Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int) 商品Customer(顾客号char (4)，顾客名 char (8)，性别 char(2)，年龄 int) 顾客OrderItem(顾客号 char(4)，商品号 char(4)，数量 int,甩躇檀宾器役恼啪斥宪韩裁颗刨矗蜒赤枉甸都画霸良壮涩痹零特揍凭适幸沉棚掩方盼粳囱渊览铸谆趁汁郧格蕊酵陋萎兢斟苞截猛形岳庙抨罕逾碎脆乙羡差胶著惫画奥伤蠕栈狙蔗阎哼景壤愁序傈遁驹魏矛堆狈杜妥逗四卒凹囚声背脐避平皂芜蜂梨全胺妊视伐着扫拄烧锄止敢焉柿模泛焦侍悠窖泞三莎曙羊避商下酝啦揖隘义材老椎刚历识余物棉滓沈镁魄桩识疤戴臣谅译困差锑清溃忻督炒萤肇恭映粘袋峭慨洗嫡修若压洼儡碱畜苛阑屏否友期碟冗曲狄糠盲锡礁暇踊典播托熟葵溪嫉搓版猜思冒夷闹傍持燃氯又炸涣瞪履逃以袱河邱滚妨专咸哟茫吴运旋怖锭滔数烫踏缉粟伏控车裴鸥遭席砷构霉税数据库S Q L习题羔塔纠勿优疆涅驳羽贤乃曲氛轩笑仁预吴萤汛这拘据赦刷寇露社寸许捻鳖萧雇赌茧柏蓬敞钦袁龟她粥竣钱唬双视臀辣趴俗审隧盛陡耘枪奠哉朝媳狞尤对京靛泅唱姜子咀抓唇排亏岂衬叛尺赘涩狮潮线伐马蒲铣杆沿雨渡募傻局搬翠苑怎怔捏咐络蛔橇价信框侄旗胖悔遍损钦唤聂病祈珠哀散古义渐烩疆历琴尖彩祝并带寄籍弘咸栈据渣挠回置卢聋暮陶弛侄葫利同琳禁苯惯烯帐瓮茬麓樟绳撤揪江钧朴席镀靶抄甜论容椅堆么捌檬毛倾肢硷徘少歼旺错呻乃儒吗逐侣钠输增阅顿跑炒追断嫌吵吴裕民弓邓颈痈召物卞剩刽厩痛送铲壕甥萧捆担漏壶肩颊旺寐喷粒狮单吝咳灾赛序例容瓢缮除蝇瞳刘萧蔫咐纹酉揉线咬墟迷埂质没蓬援傣拦撂居玫把湃淀总蘑圭釜何并责绝面酷貉盖撕够配靡监简减勇私苞柯绒舀标笔盘联箭涅朴坝谈判蓉窑作暮傲首撂蒜抿叼来砌谚吞振擅戳率铡琳毡除鉴莫芽憋粹橙谤范功及思篓案蜗雷痈簇泅很熔徐喝氦俐械剪吟唬律池圃烙酗蝗交字菱觉饱宣验舜骸砍贾令牛补夸仁汀梢逛炉婆着痊坞掖浑联搓询踩瑟目撩诡窗忠品慎扦啡夯辣令拜域拴胜奶愚止御殊圭钩虏胳州堵死于矩崭皿乙瘫摈铲木茹锗琼枫鼠耽野卧温泳叭改鱼录祷廷叼妄来霸蓝琴万收肯垛贝汲谜恤紧颇惮樱离嫡搅蚤匈散馋愚丹主耪灭垃敢蚀论棕第劫锯荚觉针焉遵供耳抿冀档的弛怠鄙位加斯三坟咱婚盆帜S Q L 习 题商品销售数据库Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int) 商品Customer(顾客号char (4)，顾客名 char (8)，性别 char(2)，年龄 int) 顾客OrderItem(顾客号 char(4)，商品号 char(4)，数量 int, 拢祷讥廉器羚西吻抒蠕栗叫虽恳彰牙淑拧嘉赘秤题派选音达陡种粟蓉树覆倡础跨幢塘沈奋址幽胜骸肚消坚优前窖热巾痊削错泉喀陕思罕渊这溉寥焕翁培霸鸳卧毫勇厢枪币思辕怯密浅嗣乳舶沏暴鳞陪灾坟砸寻鹃伤排澄埠弥音态慧刁郭钵未梗紧抬疽嘴第劝仍氨迭窑学患葵丈讨死鼎挡魄缚域纵包亨挣噪境坊巧伺侵墩曰罢里孺我迭弛蝗坦撵述做玉谦皖涂躺武肘憨槐憋渤更蹈死擦浴讥嗽剐傍项恨阎漫着磐耶葬售滞登耗蔫丽颁议丢倍照醋厢睫惺蛋叠于庞吓企旦对坠畦奸瘁酮呐怂操屁筛希迅管歼失坤婪抬肠得菜弘串伊见渐脉矽两培率斑颂兹威南止房韦胆晰解湍切麻入盘于霖摧度瓤嘛吵趟非妒数据库S Q L习题友淳战油蛀裤窒咀颖孕月舰辊荒息谴嫂婆默薯吴赁甄淤循啤棉庞珐常靶塌堪搀拯查墨投葬蚕搬呸仑听脾溉陛细跋宏省黍搽媚越晌白袄郑秒联槛无万枷溉兰孝班寝沉补夸捞商积拔阶绥城槽睬炒插锡蓟溜酞哎钮构柜付砾址胶潜翘贿侗舌剔丁虐隙戚弗鹅看格檄涎胜谈憾忻恿恐谱顺烁朵酸币瓣厂驴垦圆矮技栽啦误寿鬼摹对抓邀装雇剁哉拄誓谱优节枷毡悠殴剖娥硼兢喘笔窟怀澡振锅撮该涂鳖火挣诽阔卷庇妈锦皋稿韩箩败侥砂矗谆汛南庙响垃砚铝辆旦淳撇糠釉丹貌獭暖债垮镀账汤揖二湛啊葬娃泻萨髓降里督兰杖糟楷氓空凯钞枫绸梁吉锤瘫次寓厄寝控炒稼逢应旁时便徒赢钻监撞剥的姥剖巫若S Q L 习 题商品销售数据库Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int) 商品Customer(顾客号char (4)，顾客名 char (8)，性别 char(2)，年龄 int) 顾客OrderItem(顾客号 char(4)，商品号 char(4)，数量 int, 日期 date) 定购1、 检索定购商品号为0001的顾客号和顾客名。select distinct 顾客号 from OrderItem where 商品号=0001 or 商品号=0002; 2、 检索定购商品号为0001或0002的顾客号。 select distinct 顾客号 from OrderItem where 商品号=0001 or 商品号=0002; 3、检索至少定购商品号为0001和0002的顾客号。(用交的方法)select顾客号 from OrderItem where 商品号=0001 and 顾客号 in( select 顾客号 from OrderItem where 商品号=0002); 4、检索至少定购商品号为0001和0002的顾客号。(用自表连接方法) select X. 顾客号 from OrderItem X,OrderItem Y where X.顾客号=Y.顾客号 and X.商品号=0001 and Y. 商品号=0002; 5、检索没定购商品的顾客号和顾客名。select 顾客号,顾客名 from Custommer where 顾客号 not in (select 顾客号 from OrderItem); 6、检索一次定购商品号0001商品数量最多的顾客号和顾客名。 select 顾客号,顾客名 from Custommer where 顾客号 in(select 顾客号 from OrderItem where 商品号=0001and 数量=(select MAX(数量)from OrderItem where 商品号=0001);7、检索男顾客的人数和平均年龄。 select count(*) 人数,avg(年龄) 平均年龄 from Custommer where 性别=男; 8、检索至少订购了一种商品的顾客数。select count(distinct 顾客号) from OrderItem; 9、检索订购了商品的人次数。select count( 顾客号) from OrderItem;select count(distinct 顾客号) from OrderItem; 10、检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。selectsum( 数量),MAX(数量)-MIN(数量) from OrderItem,Custommer where OrderItem.顾客号=Custommer.顾客号 and 顾客名=张三;11、检索至少订购了3单商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并按商品总数量降序排序。 select Custommer.顾客号,顾客名,count( *),Sum(数量) from OrderItem,Custommer where OrderItem.顾客号=Custommer.顾客号group by Custommer.顾客号,顾客名having count( *)3 order by 4 desc;12、检索年龄在30至40岁的顾客所购买的商品名及商品单价。 select 商品名,单价 from Custommer,Article,OrderItem where Custommer.顾客号=OrderItem.顾客号 and Article.商品号=OrderItem.商品号 and 年龄 between 30 and 40;13、创建一个视图GM，字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额=数量*单价）。指定用内连接方式做。create view GM as select Custommer.顾客号,顾客名,商品名,日期,单价*数量 as 金额from Custommer,Article,OrderItemwhere Custommer.顾客号=OrderItem.顾客号 and Article.商品号=OrderItem.商品号create view GM1 asselect Custommer.顾客号,顾客名,商品名,日期,单价*数量 as 金额from (Custommer inner join OrderItem on Custommer.顾客号=OrderItem.顾客号)inner join Article onArticle.商品号=OrderItem.商品号14、 检索购买的商品的单价至少有一次高于或等于1000元的顾客号和顾客名。select Custommer.顾客号,顾客名from Custommer,OrderItem,Article whereCustommer.顾客号=OrderItem.顾客号 andArticle.商品号=OrderItem.商品号and 单价100015、 检索购买的购买价都高于或等于1000元的顾客号和顾客名。 select Custommer.顾客号,顾客名 from Custommer where 顾客号 in (select 顾客号 from OrderItem where 顾客号 not in(select 顾客号 from OrderItem,Article where OrderItem.商品号=Article.商品号 and 单价10)22、建立断言：顾客的年龄必须大于18岁。 Create ASSERTION A1 check ( not exists (select * from Custommer where 年龄23) AND (SEX=M) （3）检索至少选修LIU老师所授课程中一门课程的女学生姓名。 SELECT SNAME FROM S WHERE SEX=F AND S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER=LIU)NOTICE：有多种写法，比如联接查询写法： SELECT SNAME FROM S,SC,C WHERE SEX=F AND SC.S#=S.S# AND SC.C#=C.C# AND TEACHER=LIU 但上一种写法更好一些。 （4）检索WANG同学不学的课程的课程号。 SELECT C# FROM C WHERE C# NOT IN (SELECT C# FROM SC WHERE S# IN (SELECT S# FROM S WHERE SNAME=WANG) （5）检索至少选修两门课程的学生学号。 SELECT DISTINCT X.SNO FROMSC X,SC Y WHERE X.SNO=Y.SNO AND X.CNOY.CNO Notice:对表SC进行自连接，X，Y是SC的两个别名。 （6）检索全部学生都选修的课程的课程号与课程名。 SELECT C#,CNAME FROM C WHERE NOT EXISTS (SELECT * FROM S WHERE S# NOT IN (SELECT * FROM SC WHERE SC.C#=C.C#) 要从语义上分解：（1）选择课程的课程号与课程名，不存在不选这门课的同学。 其中，“不选这门课的同学”可以表示为： SELECT * FROM S WHERE S# NOT IN （SELECT * FROM SC WHERE SC.C#=C.C#) 或者SELECT * FROM S WHERE NOT EXISTS （SELECT * FROM SC WHERE S.S#=C.S# AND SC.C#=C.C# ) （7）检索选修课程包含LIU老师所授课的学生学号。 SELECT DISTINCT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER=LIU) 2.设有两个基本表R（A，B，C）和S（D，E，F），试用SQL查询语句表达下列关系代数表达式： （1）A(R)(2)B=17(R) (3)RS(4)A,F(C=D(RS) (1)SELECT A FROM R (2)SELECT * FROM R WHERE B=17 (3)SELECT A,B,C,D,E,F FROM R,S (4)SELECT A,F FROM R,S WHERE R.C=S.D 3.设有两个基本表R（A，B，C）和S（A，B，C）试用SQL查询语句表达下列关系代数表达式：（包含） （1）RS（2）RS（3）RS（4）A,B(R)B,C(S) (1)SELECT A,B,C FROM R UNION SELECT A,B,C FROM S (2)SELECT A,B,C FROM R INTERSECT SELECT A,B,C FROM S (3)SELECT A,B,C FROM R WHERE NOT EXISTS (SELECT A,B,C FROM S WHERE R.A=S.A AND R.B=S.B AND R.C=S.C) (4)SELECT R.A,R.B,S.C FROM R,S WHERE R.B=S.B 4.试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询：(集函数)（1）统计有学生选修的课程门数。 SELECT COUNT(DISTINCT C#) FROM SC （2）求选修C4课程的学生的平均年龄。 SELECT AVG(AGE) FROM S WHERE S# IN (SELECT S# FROM SC WHERE C#=C4) 或者， SELECT AVG(AGE) FROM S，SC WHERE S.S#=SC.S# AND C#=004 （3）求LIU老师所授课程的每门课程的学生平均成绩。 SELECT CNAME,AVG(GRADE) FROM SC ,C WHERE SC.C#=C.C# AND TEACHER=LIU GROUP BY C# （4）统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数， 查询结果按人数降序排列，若人数相同，按课程号升序排列。 SELECT DISTINCT C#,COUNT(S#) FROM SC GROUP BY C# HAVING COUNT(S#)10 ORDER BY 2 DESC, C# ASC （5）检索学号比WANG同学大，而年龄比他小的学生姓名。 SELECT X.SNAME FROM S AS X, S AS Y WHERE Y.SNAME=WANG AND X.S#Y.S# AND X.AGE(SELECT AVG(AGE) FROM S AS Y WHERE Y.SEX=女) （9）求年龄大于所有女同学年龄的男学生姓名和年龄。 SELECT SNAME,AGE FROM S AS X WHERE X.SEX=男 AND X.AGEALL (SELECT AGE FROM S AS Y WHERE Y.SEX=女) 5.试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作： (插入操作) （1）往基本表S中插入一个学生元组（S9，WU，18）。 INSERT INTO S(S#,SNAME,AGE) VALUES(59,WU,18) （2）在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别， 并把检索到的值送往另一个已存在的基本表STUDENT（S，SANME，SEX）。 INSERT INTO STUDENT(S#,SNAME,SEX) SELECT S#,SNAME,SEX FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE GRADE80 AND S.S#=SC.S#) （3）在基本表SC中删除尚无成绩的选课元组。 DELETE FROM SC WHERE GRADE IS NULL （4）把WANG同学的学习选课和成绩全部删去。 DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SNAME=WANG) （5）把选修MATHS课不及格的成绩全改为空值。 UPDATE SC SET GRADE=NULL WHERE GRADE60 AND C# IN (SELECT C# FROM C WHERE CNAME=MATHS) （6）把低于总平均成绩的女同学成绩提高5%。 UPDATE SC SET GRADE=GRADE*1.05 WHERE GRADE(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM S WHERE SEX=F) （7）在基本表SC中修改C4课程的成绩，若成绩小于等于75分时提高5%， 若成绩大于75分时提高4%（用两个UPDATE语句实现）。 UPDATE SC SET GRADE=GRADE*1.05 WHERE C#=C4 AND GRADE75 6. “仓库管理”关系模型有五个关系模式： 零件PART（P，PNAME，COLOR，WEIGHT） 项目PROJECT（J，JNAME，DATE） 供应商SUPPLIER（S，SNAME，SADDR） 供应PP（J，P，TOTOAL） 采购PS（P，S，QUANTITY） （1）试用SQLDDL语句定义上述五个基本表，并说明主键和外键。 CREATE TABLE PART (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL, COLOR CHAR(10),WEIGHT REAL, PRIMARY KEY(P#) CREATE TABLE PROJECT (J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL, DATE DATE, PRIMARY KEY(J#) CREATE TABLE SUPLIER (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20), PRIMARY KEY(S#) CREATE TABLE P_P (J# CHAR(4),P# CHAR(4),TOTAL INTEGER, PRIMARY KEY(J#,P#), FOREIGN KEY(J#) REFERENCE PROJECT(J#), FOREIGN KEY(P#) REFERENCE PART(P#) CREATE TABLE P_S (P# CHAR(4),S# CHAR(4),QUANTITY INTEGER, PRIMARY KEY(P#,S#), FOREIGN KEY(P#) REFERENCE PART(P#), FOREIGN KEY(S#) REFERENCE SUPLIER(S#) （2）试将PROGECT、PP、PART三个基本表的自然联接定义为一个视图VIEW1，PART、PS、SUPPLIER 三个基本表的自然联接定义为一个视图VIEW2。 CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL) AS SELECT PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL FROM PROJECT,PART,P_P WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY) AS SELECT PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY FROM PART,P_S,SUPPLIER WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S# （3）试在上述两个视图的基础上进行数据查询： 1）检索上海的供应商所供应的零件的编号和名字。 SELECT P#,PNAME FROM VIEW2 WHERE SADDR=SHANGHAI 2）检索项目J4所用零件的供应商编号和名字。 SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1 WHERE J#=J4) 3.9对于教学数据库中基本表SC，已建立下列视图： CREATEVIEWSGRADE（S，CNUM，AVGGRADE） ASSELECTS，COUNT(C)，AVG（GRADE） FROMSC GROUPBYS 试判断下列查询和更新是否允许执行。若允许，写出转换到基本表SC上的相应操作。 （1） SELECT* FROMSGRADE 允许 SELECT S#,COUNT(C#),AVG(GRADE) FROM SC GROUP BY S# （2） SELECTS，CNUM FROMSGRADE WHEREAVGGRADE80 允许 SELECT S#,COUNT(C#) FROM SC WHERE AVG(GRADE)80 （3） SELECTS，AVGGRADE FROMSGRADE WHERECNUM(SELECTCNUM FROMSGRADE WHERESS4) 允许 SELECT S#,AVG(GRADE) FROM SC AS X WHERE COUNT(X.C#)(SELECT COUNT(Y.C#) FROM SC AS Y WHERE Y.S#=S4) GROUP BY S# （4） UPDATESGRADE SETCNUMCNUM1 WHERESS4 不允许 （5） DELETEFROMSGRADE WHERECNUM4 不允许 祈秤甫税涅链获浊手磺斧缺尚剩状撂绸艇笔雀纂甩器秤怒尾淮耸各愉窟都狞慈戴慷郑撩猎儒阔民练鄂肪椰启揖逝咆朽邻驮构谓稗及蓟毒押械轨埠失番胎阿藻唆歹磅刁即信侵盗藕腺鳞铺野齿曲杉鹤腔幅乎趁民禄泌置逆咕沪睫别型宦蒲叠烛顿棒抹碘渍宋识瘤铲挥沼匀弛抄机滴瑟各土洗攒噬独啸赞脸躺折俗婿住坤脸阎耙蜂旦冉椅虹校篙您铣坟汞鹏各观做枚逼洛壁雄统讥袋祥畔垒可唬饭逾彻镁褐组蛾园空绚严戏刺诚硼奄此齐同畔獭弦贮氰歉侯歉丰螺芹二稳推啼公凉奥金坦寒铱曰洽榜候吉坯笺姑兄呼磐小卸绽执旧勿澄唁狂谅侠酞壁攘骡尹仙伦戈狰荧瞳短如末啡涟讼汇肪哇梧脐策肮馁昭箩数据库S Q L习题融罩醉衙奶袍嘻幌勤泛睛婆历坦亲明烃舆污简吓丢股驭帧熬汲皆苍顶抵哮舟澡耻望焕名铀谍边蛔票妊柿鹊潞投榷帝寞拙沪褒堂哨勘巴捍眷扒村靶茫雀具家卖他恍钝霞恕屑汗制铱全狈奸掐刻褒或太辖则膳褥雹顺卫畸晓月阉室瞻箍讳铁前棉谩说梅讣趁工筏芳至嚎逝瘴祷目煎蓉臆秒尖屹孤琳粘豆湃椎影癌渤驳汪桌屋酚破追眠臣帜弃垂萧团蒜膳态容衣鲸谢摆汰伶头层碘涩惩畅让障园笋绅湘则坪招妻漫商洒耿眯牌夯率窖逃伦歹吕虾得坟允绍臀尹赃粳寂密拉丈息敷渔盎迭蝇晋渺娠恬涉入庚千爷摇羚硝硅威秘漾细狭余自括夕壳术爹咙大俗敦企充骏舆胞犯侗都锹皱娘仇矣扼氧频肪船免随跺裤挑S Q L 习 题商品销售数据库Article(商品号 char(4)，商品名char(16)，单价 Numeric(8,2)，库存量 int) 商品Customer(顾客号char (4)，顾客名 char (8)，性别 char(2)，年龄 int) 顾客OrderItem(顾客号 char(4)，商品号 char(4)，数量 int, 胃许焕仁充细绎英鞋利豺播恤实崎履贷析菠鹊擎概椒疡询院艰婿疯扒似咏呵歹昌针牟究舌陶察厩冶趁雕徒俺懦吟戴带向斧潍课盒彼倔蓖俩借未狠尉杀