实验6 SQL交互式查询—连接和嵌套.docx

实验6 SQL交互式查询一连接和嵌套实验日期和时间：2012-10-21 7:40-9:15实验室：2#206实验环境:1. 硬件：主频：2.67GHz;内存：2G 磁盘空间：500G2. 软件：操作系统：Windows 7旗舰版SQL2005实验原理：连接查询有：广义笛卡尔积等值连接(含自然连接)非等值连接查询自身连接查询 外连接查询 复合条件连接查询实验主要任务：建立数据库工程供应商(SPJ )：供应商表S、零件表P、工程项目表J构成和供应情况表SPJ零件表PPK零件零件 ^{PNAME) 颇色(COLOR) 罩量(训EIGHT)PK供应商代码用g供应商名称(SWAM FJ 供应商状态(STATUS) 供应商.所在城市(CITY)匸稈项n表jPK工稈项n吒码(JNO)工程项目^(JNAME) 匸程项D所在^iIj-(CJTY)供应情况.农SPJu:RPKSFK1PK,FK2PKJK3供应商代码梧NO、 零枠代码fpxcn111 : 1 :fJU企情况我粗二FK11 : 1 : 11 : 1 : I岸件表P供应^SPJfKid:R—1—=—111 \ 1工理项冃代码tJiscnu:霍1 :1 :1 : . r . r： 1 ： 1： 1 ： 1： 1 ： 1： 1 ： 1dik 1 : 1 :1 :1 :1 : : 1 : !■■■■： 1 ： 1： 1 ： 1： 1 ： 1： 1 ： 1供应数呈(QTY〕 r : 1 :—— : F : F-■： 1 ： 1： 1 ： 1： 1 ： 1： 1 ： 1 : 1 : I--"用SQL语句完成下列査询任务1. 取出由供应商S1提供零件的工程名称；2. 求供应工程J1零件为红色的供应商号码SNO；3. 取出供应商与工程所在城市相同的供应商提供的零件代号；4. 找出工程项目J2使用的各种零件的名称及其数量；5. 找出上海厂商供应的所有零件号码；6. 找出没有使用天津产的零件的工程号码；7. 求至少用了供应商S1所供应的全部零件的工程号JNO；8. 把全部红色零件的颜色改称蓝色；9. 从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录;10. 请将(S2,P4,J6,200 )插入供应情况关系。

查询1：代码及执行结果：select jname from j where jno in(select jno from SPJ where sno='s1')结果是否正确：正确方法分析：采用了嵌套查询的方法，采用in谓词，在SPJ表中将找到的由si提供的零件的 工程编号嵌入到父查询中，在表j中找到工程编号的工程名称查询2：代码及执行结果： select sno from SPJ where jno='j1' and pno in (select pno from P where color二'红')觀结果—切消息吕no1拓1 ]2S3结果是否正确：正确方法分析：采用嵌套查询的方法，采用in谓词，在P表中找到颜色为红色的零件代码嵌入到 父查询中，在SPJ表中找到供应商代码查询3:代码及执行结果： select distinct pno from J,S,SPJ where S.sno二SPJ.sno andJ.jno二SPJ.jno and S.ci ty 二J.ci ty结果是否正确：正确方法分析：连接查询，采用符合条件查询，J, S, P三表连接查询4:代码及执行结果：select pname,qty from P,SPJ where SPJ.jno='j2' and P.pno=SPJ.pno结果是否正确：正确方法分析：连接查询，两表连接的符合条件连接查询查询5:代码及执行结果：select distinct pno from SPJ where sno in (select sno from S where city二'上海')」结果口消息pno1 叵"]2 "h •3 PG结果是否正确：正确方法分析：采用嵌套查询，带有in谓词，在S表中查找城市为上海的供应商代码嵌入到父查 询中，在SPJ表中找到零件代码查询6:代码及执行结果：select jno from J where not exists(select * from S,SPJ where S.sno二SPJ.sno and J.jno二SPJ.jno and S.city二'天津')匿结果白消息jno1 | J5 "|23 J7结果是否正确：正确方法分析：采用嵌套查询，用not exists谓词，从S，SPJ表中用两表连接的符合条件连接查 询供应商所在城市为天津的嵌入到父查询中，从J表中查询不在子查询中的工程项目代码。

查询7:代码及执行结果：select distinct jno from SPJ SPJx where not exists(select * from S SPJy where sno='s1' and not exists (select * from SPJ SPJz where SPJy.pno二SPJz.pno and SPJz.jno二SPJx.jno))H结果一&消息jno1 I J4 "|结果是否正确：正确方法分析：上面的查询可变为：不存在这样的零件，供应商S1提供的，而工程项目没有使用; 查询应用了嵌套查询，采用not exists谓词查询8:代码及执行结果：update P set color二'蓝'where color二'红'表-dbo.P USER-PC.SPJ - SQLQuetyLsql* 表-dbo.J 表-dboSPJ 表-pnop 仃 amecolorweight►mi12P2螺栓绿17P3螺丝刀lPt mi14P斗蠟丝刀权 rm14P5凸轮mi的P6齿轮侯 rm30米NLH.L结果是否正确：正确 方法分析：修改数据查询9:代码及执行结果：delete from SPJ where sno='s2' delete from S where sno='s2'表-dbo.SPJ 表-dboPV USER-PC.SPJ - SQLQu已ryl.sql* 表-dboj|^snopnojnoqty►PlJI200S1PlJ3100S1PlJ4700S1P2J2100S3PlJI200S3P3JI200S4P5JI100阴P6J3300冏P6J4200S5P2J斗100S5P3JI200S5P6J2200S5P6J斗500米/Of/ 表-dbc.£ 表-dbo.SPJ|^ - dbo.P|jUSER-PC.SPJ - SQLQueryl.sql* | 表ssnosnamestatuscity►精益20天津S3东方红30北京酣丰泰盛20天津S5为民30上海米NLH.L结果是否正确：正确方法分析：数据删除：分别把子S表和SPJ表中的关于S2的记录删除查询10：代码及执行结果：inser t into S values('s2','','','')inser t into SPJ values('s2','p4','j6','200')義-dbo.SPJ 表-- dbo.Pj USER-PC.SPJ - SQLQuecyL.sql*sno pro jno qtySIPlJI20051PlJ3100SIPl700SIP2J2100eZP-1200S3PlJI200S3P3JI200S4P5JI10054P5J330054PS200S5P2J4100S5P3JI20055P6J2200SSPSJ4500MLNULLNULLNULL结果是否正确：正确方法分析：数据插入：要插入到SPJ表中，首先要在S表中插入S2本次实验小结：（不少于100字）1. 本次试验中，遇到问题的有第七个，忘记了书上讲到的一些全称量词的转换，转换之后运 用一些嵌套查询就查询成功了。

2. 应用的主要是杳询的一些方法，有嵌套杳询，连接杳询，还涉及到几题数据更新的问题， 插入元组，修改数据，删除数据要求：1. 报告格式和内容要求：a. 内容和格式整齐大标题采用黑体四号字加粗，小标题采用小四号字加粗正 文采用五号宋体，单倍行距b. 贴图时请剪裁到适当大小，要保证打印时可以看清，但也不要太大以免“越界”c. 不要在报告中写与实验无关的话，内容要有条理、完整、并能突出重点，要将 遇到的主要问题说明2. 提交方式和时间：一周内完成可以将文件包发到我邮箱misshz@注明主题: “交 SQL 作业”3. 提交文件格式要求：a. 将实验成果放入一个文件夹中，文件夹的内容包括：本实验报告、分离后的数 据库、如有查询代码Csql文件）和其它文件也一并放入b. 文件夹以“学号姓名班级_S6”为文件名注意：你的学号放在姓名前c. 注意：所有文件保存后关闭，然后再打包成RAR文件，以免提交的内容丢失或 打不开4. 主动查阅资料，坚持自己亲手完成实验，弄清每个步骤和相关原理。