数据库课后习题.pdf

实 验 SQL语言练习实验目的：熟练掌握SQL语言。实验类型：验证实验要求：必修实验内容：结合 SQL/92 标准和 SQL-Server 的 TransactSQL,使用 SQL Server 的 SQLAnalysis图形化工具熟悉SQ L语言，并建立“学生数据库”或“供应商-项目-零件数据库”的数据库对象(表、视图、用 户 及 其 权 限)的 Script脚本；理 解 Script脚本的重要性；使 用 TransactSQL语言建立“学生数据库”或“供应商-项目-零件数据库”，并对其进行查询和更新操作；要求熟练掌握SQ L的基本使用。第三章习题3.用 SQL建立第二章习题5 中的4 个表。解答：建 S(SNO,SNAME,STATUS,CITY)表；CREATE TABLES(SNO CHAR(3),SNAME CHAR(l),STATUS CHAR(2),CITY CHAR(IO);建 P(PNO,PNAME,COLOR,WEIGHT)表；CREATE TABLEP(PNO CHAR(3),PNAME CHAR(IO),COLOR CHAR(4),WEIGHT 1NT);建 J(JNO,JNAME,C1TY)表；CREATE TABLE J(JNO CHAR(3),JNAMECHAR(IO),CITY CHAR(IO);建 SPJ(SNO,PNO,JNO,QTY)表；CREATE TABLE SPJ(SNO CHAR(3),PNO CHAR(3),JNO CHAR(3),QTY INT);在 SQL server的查询分析器中的执行结果;运行结果(建立的四个表):7th SQL Server Enterprise KanagerEE文件 操作因 查看船 工具 窗 口&帮助世)自 的X电 X 同 图 米 4力 第 第制台根目录j Microsoft SQL Serversg SQL Server 蛆-g)(local)Windows NT)三门数据库g咽 master即视图置存储过程3扩展存储过程用户血角色口规则区默认&用户定义的数据类型良用户定义的函数E E Q modelS (j msdb表 6 5个项目名称 所有者 类型 创建日期m dtpropertiesN MSreplication_options户皆尸户户用用用用国 spt_datatype_info二 sp t_dat atyp e_i nf o_ex t司spt_fallback_db1 spt_fallback_dev1spt_fallback_usgBspt_monitor国 spt_provider_types罚 spt_server_info1 sp t yalues统统统统统统统统系系系系系系系系2005-7-17 10:21:152005-7-17 10:47:032000-8-6 1:35:552005-7-17 10:47:032005-7-17 10:47:032005-7-17 10:47:032000-8-6 1 31:252000-8-6 1 31:242000-8-6 1 30:312000-8-6 1 30:312000-8-6 1 30:312000-8-6 1 30:302000-8-6 1 31:452000-8-6 1 31:262000-8-6 1 30:30统系4.针对上题中建立的4 个表试用SQL语言完成第二章习题5 中的查询。解答：(1)求供应工程J I 零件的供应商号码SNO；SELECT SNOFROM SPJWHERE JNO=J1;在 SQL server中运行结果示意:国 信。e x心I*.SQL-I Q 询1 -h u a n v a n e.m a s te r.HTlAVANCwh 一 无 机 爆11住区IX A a W D QI)即助 Qp_ ”Xn *|Q-:Q n cfi,屈S E L E C T S N OF R O M S P JW H E R E J N O=*J I ;1234567s223345ssssss口 网 格 触 播B|H U E S充成(B 0)XUMAMGb 01)0 00 00 4 r i4 !7 f j 行 1,列 1IiS K i m n(2)求供应工程J I 零件P l 的供应商号码SNO；SELECT SNOFROM SPJWHERE JNO=J1AND PNO=P1;在 SQL server中运行结果示意：S Q L表 沟 分 标 吞 1 6向 huanvane.e r.HUAA，G vh_，X叫文 伴n Mi*a w o(X)财on-不X图 h。n。4 1H s 0 .3 lu，“zJ 1 V f(A t?K J无 MAS”JSELECT SNOFROM SPJWHERE JNO JIAAXD PNO Pl;Msxo1 SI2 S3一0网 即 息IntBil%SR.h(B 0)mtAXYMNk S 3，ntr 0 00 00 2 f l 6 4,充 16_-2 m(3)求供应工程J I 零件为红色的供应商号码SNO；SELECT SNOFROM SPJWHERE JNO=J1AND PNO IN(SELECT PNOFROMPWHERE C O L O R*);在 SQL server中运行结果示意：国h q n7 i WQ O,v*|u文 件 G 0 1，IA a M助 心）(4)求没有使用天津供应商生产的红色零件的工程号JNO；SELECT JNOFROM JWHERE NOT EXISTS(SELECT*FROM SPJWHERE SPJ.JNO=J.JNO AND SNO IN(SELECT SNOFROMSWHERE CITY=天 津)AND PNO IN(SELECT PNOFROMPWHERE COLOR=tT);在SQL server中运行结果示意：叫文件9 质辐毛 查问）工 MQ）用口优）超勤电）SQL 查词分析SE-查 huanvang.a a ster.HUAWAHCvh 一 无标也2,匚 号 15(（5）求至少用了供应商S1所供应的全部零件的工程号JNO；SELECT DISTINCT JNOFROM SPJ SPJZWHERE NOT EXISTS(SELECT*FROM SPJ SPJXWHERE SNO=S1AND NOT EXISTS(SELECT*FROM SPJ SPJYWHERE SPJY.PNO=SPJX.PNOAND SPJY.JNO=SPJZ.JNO);在SQL server中运行结果示意：SQL 有询分析H -旧 询 一huanvanc.m aster.HUAJIfABGh 一 无标鬣2 U 阿，戈明文件比)阚 直 通)工 具 9 出口&)帮助出)占官蜀 殖日 He 口-,U*MtT 1 a 宜国S E L E C T D I S T I N C T J N OF R O M S P J S P J ZW H E R E N O T E X I S T S(S E L E C T *F R O M S P J S P J XW H E R E S N O-S rA N D N O T E X I S T S(S E L E C T *F R O M S P J S P J YW H E R E S P J Y P N O S P J X P N OA X D S P J Y.J N O=S P J Z J X O),J N O1 J 4口网格 抑 消 可山 专 前.一XMWW XUUTW*h S2)Mtr 0 00 00 l 行 行 JO,列 27，接 2 CAP-EnrJ 一5.针对习题3中的4个表试用SQL语言完成以下各项操作：解答：(1)找出所有供应商的姓名和所在城市。SELECT SNAME,CITYFROM S;在SQL server中运行结果示意：SQL 曲 分析J 3 C L A ft huonwang.H U A A B G vh无 林 融 2-J噌X叫 文 件 (Z)*1 1 聋、(&)工JLQ)口(X)*助 Q)-*X阈G S Q F I csM o n-v*i u 2 ”“日芍g.cs*sSELECT SNANfE.CITYFROM S.SXAME CITY红盛益锡方泰民精盛东丰为津京京津海尺匕匕氏JjTVTT口哪 t 网总I*（2）找出所有零件的名称、颜色、重量。SELECT PNAME,COLOR,WEIGHTFROM P;在 SQL server中运行结果示意:SQL 若 旬 分 析H -1有 血 huanvane.aaster.H U A W A IG vh 无标 112M 住反。文件qy 飒中 受由，）工A ID窗口a）帮助Q P-x由3 m 电曰抬c 口 /u a*st*r 三 日甘&旨 国连 接 2 Ctf A ,J 寸S E L E C T P N M E.C O L O R.W E I G H TAF R O M P;V,P N A M EC O L O R W E I G H T1 螺母红 1 22螺栓绿 1 73螺丝刀蓝 1 44螺丝刀红 1 45 凸轮蓝 4 06 齿轮红 30批查询案诙。hx&ru(3 0)HUAWA3vh(52)ntster 0:00 00 6 行 行 3,列 5（3）找出使用供应商S 1所供应零件的工程号码。SELECTJNOFROM SPJWHERE SNO=S1;在SQL server中运行结果示意:(4)找出工程项目J2使用的各种零件的名称及其数量。SELECT P.PNAME,SPJ.QTYFROM P,SPJWHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2;在SQL server中运行结果示意：SQL 查询分析B -百 询 一 huanvane.a a ste r.HTJAHVAIGvh 一 无 标=？*叫 文 件 il）筑辑电）邃询0）工具9 百口电）分助与通 口 M g n-v 旧”3日 哲 0囹 国S E L E C T P P A M E.S P J.Q T YF R O M P.S P JW H E R E P.P N O-S P J.P N OA X D S P J.J N OJ 2 ;P X A M EQ T Y123刀刀栓丝丝螺螺螺oooooo122颜圆 海 月|弛直砺庇.M tnvw 0)XUAW AGwh C52)ast*r 0:00 00 3(7 行 5,列 5连接 2 C A f o J J|(5)找出上海厂商供应的所有零件号码。SELECT DISTINCT PNOFROM SPJWHERE SNO IN(SELECT SNOFROMSWHERE CITY=W)；在SQL server中运行结果示意：?.SQL 查询分析3-LS彻 一huanvang.aaster.HUAIVAICvh 一 无 标 里2,文件Q）五 辑 查 谓&IM d）甯口Q）帮助卸连 奏2 3行；）口|（6）找出使用上海产的零件的工程名称。SELECT JNAMEFROM J,SPJ,SWHERE J.JNO=SPJ.JNOAND SPJ.SNO=S.SNOAND S.C1TY=上海，；在SQL server中运行结果示意：SQL 查询分析H huanvanc.*a s tc z.HUAIWASCvh 一 无标也2,年 文件9 M H)v9(&)IM tt)1)融助Q P目 但 便曲今。国 电日利 c U|v,|U r 三|日g&旨备SELECT JNAMEFROM J.SPJ.SW HERE J.JNO SPJ.JNOAXD SPJ.SNO=S SNOAND S.CITY=上海,，J N A M E1 1建2 一汽3 造船厂4 造船厂一 口麻回濡可%山 -MMMMWh 62)-u g r 0 00 00 彳 币-行5 7列 工8连霰2/，而 司(7)找出没有使用天津产的零件的工程号码。SELECTJNOFROM JWHERE NOT EXISTS(SELECT*FROM SPJWHERE SPJ.JNO=J.JNOAND SNO IN(SELECT SNOFROM SWHERE CITY=天津，);在SQL server中运行结果示意：SQL 叁沟分析2E-查询 一huan van c.a a s t c r.HUARfABCvh 一 无标512*J图醴口.y|u 3 日 告 上 曰 图S E L E C T J N OF R O M JW H E R E N O T E X I S T S(S E L E C T *F R O M S P JW H E R E S P J J X O-J.J N OA N D S X O I NS E L E C T S N OF R O M SW H E R E C I H=天津)J N O1 _ J 52 J 63 J 7口咻网常可效看滑完成.hutM W (B 0)HUMUGkh 62)Mtr 0 00 00 3 行 行 8,列 15：2 3,-0即（8）把全部红色零件的颜色改成蓝色。UPDATEPSET COLORS蓝WHERE COLOR=纤；在SQL server中运行结果示意：SQ L 香词分析看 查询 huanvang.a a stc c.HUAVAICvh 无标第2耳 文 件)(!)夷询皿 工R 9 窗口(X)相助电)*：国O百 触 s 0 Z|u aaittf m 日 售 3 旨 国U P D A T E P?S E T C O L O R 蓝 W H E R E C O L O R 红；（所影晌的行数为3 f f）口 网 格 国)满 月 批查防威.huMnc(8 0)KUAWAKh(52)nuter 0:00 00。行 行 4,列 IIiS T 2 CAP 中。).引(9)由 S 5供给J 4 的零件P 6 改为由S3供应，请做必要的修改。UPDATE SPJSET SNO=S3WHERE SNO=S5AND JNO=J4AND PNO=P6;在 SQL server中运行结果示意：SQL A iM分 析，-I AtfrJ h u an v an e*B a s t e r-H U A*V A C vh 一 龙M*2l-X/文 仲Q)e n q p 爱通3 工A(p 口 QP 带)qp x由.信口仃 cc?“cuj.y,ICI ”，i 日号口 iff vaUPDATE SPJSET SNO=S3WHERE SNO*So*AND JNO*J4AND PNO=P6,;(所 影 喻 的 行 效 为i行)口 网 壮 皿|批衣内完成huMA(B 0)KUAMAJKK C52)*M t r 0 00 00 0行；行 6,兜1一 搭2 3,、一,国(1 0)从供应商关系中删除S 2的记录，并从供应情况关系中删除相应的记录。DELETEFROM SPJWHERE SN0=S2;DELETEFROM SWHERE SN0=S2;在SQL server中运行结果示意:SQL 分询分析3E-悟 huanvane.a a ste r.HUA*VA*Cvh 无标912 匚 旨 仅B 文件9 4 M Q U 查询工H Q)口 招 购 剧 X；由修。1 E?H n|u 2 3 日 苦 a 囱 宙D E L E T E5F R O M S P JW H E R E S N O=S 2;D E L E T EF R O M SW H E R E S N O=S 2 ;（所彩哨的行期为6 fT）（所影嘀的行数为1 行）阿 格 曲 刷 -批查诲方咸*b u 3 g 6 0）MUAWWUh 段）Mtr 0:00 00。行 行 7,列 16连 接 2（:超 -J n 印1 1.请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数（QTY）。针对该视图完成下列查询：（1）找出三建工程项目使用的各种零件代码及其数量。（2）找出供应商S 1的供应情况。解答：建视图：CREATE VIEW V_SPJ ASSELECT SNO,PNO,QTYFROM SPJWHERE JNO=（SELECT JNOFROM JWHERE JNAME=三建）在SQL server中运行结果示意：SQL 香询分析28-叁询 一huanvan/|u M S P 3 !W Q i 日 言C R E A T E VI E W V_S PJ A S?S E LE C T S XO.PN O.Q T YF R O M S PJW H E R E JXO(S E LE C T JXOF R O M JW H E R E JN A M E 三 建 I口网电曲 痛 批查词完成.hsw gC 8。)KVAHA)IGh(S2)Bstr 0 00 00。行 行 7，刑 20连-2 一 对该视图查询：(1)找出三建工程项目使用的各种零件代码及其数量。SELECT PNO,QTYFROM V_SPJ;在SQL server中运行结果示意：SQL 询分析B -1分 虫 一huanvant.aaster.HUAIVAHCvh 无标匚 1后 反叫 文 件 S)M I&)查0 )工9口也)幅助也),y图修。法 a i M|(j2=3 1 甘a 旨 曲S E L E C T P N O.Q T YF R O M V _S P J ,|P N O Q T Y _1 P l 2 0 02 P l 2 0 03 P 3 2 0 04 P 5 1 0 05 P 3 2 0 01 口 风 格 城 帚批查闻六成 hug(8.0)K V A X Y M K h(52)()Mtr 0:00 00 S fj 行 2,列 12-2 =匕(2)找出供应商S 1的供应情况。SELECT PNO,QTYFROM V_SPJWHERE SNO=S1在SQL server中运行结果示意：SQL 询分析2E-匚 叫 交件辱 查诲/i u!三 日智a 囱 窗SELECT PXO.QTY不FROM V_SPJWHERE SNO Si lP0 QTY1 Pl 200批查再充成.buun（S O）HUAWAlKKvh 62）0:00 00 l 行 行 3,到 15连-2 0*-J 0 目1 2.针对习题3建立的表，用SQL语言完成一下各项操作：（1）把对表S的INSERT权限授予用户张勇，并允许他再将此权限授予其他用户。解答：GRANT INSERTONST O张勇WITH GRANT OPTION;在SQL server中运行结果示意：4 文件0）Mtt（D 莪西）IMO）o（x）带IhQp xSQL 左询分析鼻 爱彻 huanvans.an x ter.HIIABVA*Cvh 无标题？(2)把查询SPJ表和修改QTY属性的权限授给用户李天明。解答：GRANT SELECT,UPDATE(QTY)ON SPJT O 李天明在 SQL server中运行结果示意：Lg x?SQL 互画分析H -1查 沟 huanw an*.aaster.HUAflVABGYvh 一 无 标IMO）!