2013《数据库原理及应用》实验报告答案

实验二数据定义一、实验目的1、掌握数据库引擎的使用方法，加深对SQL的语句的理解；2、使用SQL语句创建数据库、删除数据库3、熟练掌握DDL语言的数据定义操作，包括CREATE、ALTER、DROP。二、实验内容在SQL查询分析器中用CREATE、DROP、ALTER语句创建、删除、修改基本表。三、实验步骤1、根据课本中的说明，分析S表，P表，J表和SPJ表的主码和外码：(1)S表，P表，J表的主码分别是哪个属性，S表,P表,J表这三个表有没有外码？如果有，请写出。S表的主码SNOP表的主码PNOJ表的主码JNO没有外码(2)SPJ表的主码是由哪几个属性组成的属性组？SPJ表的外码有几个，分别是哪些属性？分别参照哪些表的哪个属性？SPJ主码（SNO,PNO,JNO）外码SNO参照S表的SNO，外码PNO参照P表的PNO，外码JNO参照J表的JNO。2.新建数据库SPJ，并在数据库SPJ中创建S表，P表，J表和SPJ表。（1）创建供应商表S的sql语句，要求设置主码：CREATETABLES(SNOCHAR(4)PRIMARYKEY,SNAMEVARCHAR(40),STATUSSMALLINT,CITYVARCHAR(20));（2）创建零件表P的sql语句，要求设置主码并将该约束命名为P_PK：CREATETABLEP(PNOCHAR(4)CONSTRAINTP_PKPRIMARYKEY,PNAMEVARCHAR(40),COLORCHAR(2),WEIGHTSMALLINT);（3）创建工程项目表J的sql语句，要求设置主码并命名为J_PK：CREATETABLEJ(JNOCHAR(4)CONSTRAINTJ_PKPRIMARYKEY,JNAMEVARCHAR(40),CITYVARCHAR(20));（4）创建基本表供应情况表SPJ的sql语句，要求设置主码和外码：CREATETABLESPJ(SNOCHAR(4)REFERENCESS(SNO),PNOCHAR(4)REFERENCESP(PNO),JNOCHAR(4)REFERENCESJ(JNO),QTYSMALLINT,PRIMARYKEY(SNO,PNO,JNO));3.在窗口下键入DROPTABLES命令，运行后观察结果。（1）运行结果是什么？不能执行（2）为什么会出现这种结果？破坏参照完整性。4.打开S表，并往S表中输入第一行数据。（1）如何保存输入的数据？（2）输入第二行数据时如果不输入属性SNO的值，将出现什么情况？为什么？不能执行违背实体完整性。5.在S表中增加一个新的字段“TEL”，数据类型为char(11)（1）sql语句：ALTERTABLESADDTELCHAR(11)（2）观察属性TEL的值，值为NULL。6.修改S表中属性SNAME的数据类型改为varchar(20)，其sql语句是ALTERTABLESALTERCOLUMNSNAMEVARCHAR(20);7.删除S表中的属性TEL的SQL语句：ALTERTABLESDROPCOLUMNTEL;8.为P表添加约束C1：零件重量必须在0到100之间，其SQL语句：ALTERTABLEPADDCHECK(WEIGHTBETWEEN0AND100);9.验证完整性约束：（1）在S表中输入第一条记录，若再往S表中输入一条“S1，盛锡”的记录，能不能输入，为什么？不能，主键取值唯一。（2）往P表、J表和SPJ表中分别输入第一条记录，若往SPJ表中输入第二条记录，能不能实现？为什么？不能，违背参照完整性。（3）此时若要删除S表中的第一条记录能不能删除？为什么？不能，违背参照完整性。（4）往P表中输入一条记录“P7，齿轮，蓝，150”，能不能实现？为什么？不能，违背CHECK约束。四、实验小结（实验过程中遇到什么问题？如何解决？）实验三单表查询一、实验目的1、掌握数据库引擎的使用方法，加深对SQL的语句的理解；2、使用SELECT语句进行单个表格数据查询；3、熟练掌握简单表的数据查询、数据排序的操作方法。二、实验内容1、使用SELECT语句完成简单查询操作。2、该实验包括投影、选择条件表达，数据排序，查询结果分组等。三、实验步骤1、查询所有供应商的姓名和所在城市。Sql语句：SELECTSNAME,CITYFROMS;2、查询所有零件的名称颜色和重量。Sql语句：SELECTCOLOR,WEIGHTFROMP;3、查询S1供应商所供应零件的工程号码。Sql语句：SELECTJNOFROMSPJWHERESNO=’S1’;4、查询名称带有“厂”字的工程项目的详细信息。Sql语句：SELECT*FROMJWHEREJNAMELIKE‘%厂%’;5、查询供应了零件的供应商号。Sql语句：SELECTDISTINCTSNOFROMSPJ;6、查询重量在10到20之间的红色零件。Sql语句：SELECT*FROMPWHEREWEIGHTBETWEEN10AND20ANDCOLOR=’红’;7、查询所有零件的名称，并按重量降序排列Sql语句：SELECTPNAMEFROMPORDERBYWEIGHT;8、查询地址在长春、北京或天津的工程项目的详细信息。Sql语句：SELECT*FROMJWHERECITYIN(‘长春’,’北京’,’天津’);9．查询零件表中零件的平均重量。Sql语句：SELECTAVG(WEIGHT)FROMP;10.统计每个供应商各供应了多少个零件。Sql语句：SELECTSNO,SUM(QTY)NUMFROMSPJGROUPBYSNO;11.查询至少使用了三种或以上零件的工程项目有哪些。Sql语句：SELECTJNOFROMSPJGROUPBYJNOHAVINGCOUNT(DISTINCTPNO)=3;12.查询至少提供了200个P1零件的供应商号。Sql语句：SELECTSNOFROMSPJWHEREPNO=’P1’GROUPBYSNOHAVINGSUM(QTY)=200;实验四连接查询、嵌套查询一、实验目的1、掌握SQLServer2005数据库引擎的使用方法，加深对SQL的语句的理解；2、使用数据库引擎查询文档用SELECT语句进行数据查询；3、熟练掌握运用DML语言对表中的数据进行连接查询和嵌套查询二、实验内容利用连接查询和嵌套查询等方法实现多表查询。三、实验步骤1、找出工程项目J2使用的各种零件的名称及其数量Sql语句：SELECTPNAME,QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDJNO=’J2’;该查询能否用嵌套查询实现？为什么？不能，因为查询结果涉及到两张表的信息。2、找出没有使用天津产的零件的工程号码Sql语句：SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERECITY=’天津’));或SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJ,SWHERESPJ.SNO=S.SNOANDCITY=’天津’);3、找出使用上海产的零件的工程名称Sql语句（使用连接和嵌套两种方法来实现）：连接查询SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=’上海’;嵌套查询SELECTJNAMEFROMJWHEREJNOIN(SELECTJNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERECITY=’上海’));或SELECTJNAMEFROMJWHEREJNOIN(SELECTJNOFROMSPJ,SWHERESPJ.SNO=S.SNOANDS.CITY=’上海’));4、找出既使用S1供应商提供的零件又使用S2供应商提供的零件的工程号Sql语句：SELECTJNOFROMSPJWHERESNO=’S1’ANDJNOIN(SELECTJNOFROMSPJWHERESNO=’S2’);或SELECTX.JNOFROMSPJX,SPJYWHEREX.JNO=Y.JNOANDX.SNO=’S1’ANDY.SNO=’S2’;5、求使用S2供应商供应的P3零件数目超过丰泰盛供应商供应的P6零件数目的工程号Sql语句：SELECTJNOFROMSPJXWHERESNO=’S2’ANDPNO=’P3’ANDQTY(SELECTQTYFROMSPJWHEREJNO=X.JNOANDPNO=’P6’ANDSNOIN(SELECTSNOFROMSWHERESNAME=’丰泰盛’));或SELECTX.JNOFROMSPJX,SPJYWHEREX.JNO=Y.JNOANDX.SNO=’S2’ANDX.PNO=’P3’ANDX.QTYY.QTYANDY.PNO=’P6’ANDY.SNOIN(SELECTSNOFROMSWHERESNAME=’丰泰盛’);实验五数据更新一、实验目的1、掌握更新语句的基本语法和用法，加深对SQL的语句的理解；2、了解DML语言的INSERT、UPDATE、DELETE对数据的操作，；3、能将这些更新操作应用于实际操作中去，学会在SQLServer2005中用DML语言对表中的数据进行插入、删除和修改。二、实验内容1、在查询分析器中使用INSERT、UPDATE、DELETE语句进行数据更新。三、实验步骤1、由S5供给J4的零件P6改为由S3供应，请做必要的修改Sql语句：UPDATESPJSETSNO=’S3’WHERESNO=’S5’ANDJNO=’J4’ANDPNO=’P6’;2、向零件表中插入一个零件记录(零件代码：P7；零件名：螺钉；颜色：黑；重量：10)Sql语句：INSERTINTOPVALUES(‘P7’,’螺钉’,’黑’,10);3、从SPJ表中把S1供应某工程P1零件数量低于S1供应各工程P1零件数量平均值的供应元组全部删去。Sql语句：DELETEFROMSPJWHERESNO=’S1’ANDPNO=’P1’ANDQTY(SELECTAVG(QTY)FROMSPJWHERESNO=’S1’ANDPNO=’P1’);4、将P表中零件重量小于等于20的零件颜色全部改为黄色。Sql语句：UPDATEPSETCOLOR=’黄’WHEREWEIGHT20;5、求每个供应商供应给J1工程的零件总数,并把结果存入数据库中。Sql语句：CREATETABLESJ1_SUM(SNOCHAR(4),J1NUMSMALLINT);INSERTINTOSJ1_SUMSELECTSNO,SUM(QTY)FROMSPJWHEREJNO=’J1’GROUPBYSNO;6、将S2供应商供应给J1工程的蓝色零件的零件数目增加5%Sql语句：UPDATESPJSETQTY=QTY*1.05WHERESNO=’S2’ANDJNO=’J1’ANDPNOIN(SELECTPNOFROMPWHERECOLOR=’蓝’);7、从供应商关系中删除S2的记录。Sql语句：DELETEFROMSPJWHERESNO=’S2’;DELETEFROMSWHERESNO=’S2’;思考：当进行数据的插入、删除和修改的时候需要注意哪些方面？语法，是否违背完整性。实验六视图的创建、查询与更新一、实验目的1、学会使用CREATE、DROP等SQL语句创建和删除视图2、学会使用SQLServer2005的SQLServerManagementStudio对视图进行查询和更新二、实验内容1、在数据库引擎查询文档中使用CREATE、DROP语句创建和删除视图2、使用SELECT和UPDATE语句查询和更新视图三、实验步骤请为三建工程项目建立一个供应情况的视图，包括供应商代码、零件代码、供应数量。SQL语句：CREATEVIEWSJQTYASSELECTSNO,PNO,QTYFROMSPJWHEREJNOIN(SELECTJNOFROMJW