数据库SQL语言编程实验

49实验二SQL语言编程实验（一）StructuredQueryLanguage,简称SQL,是一种介于关系代数与关系演算之间的语言,其功能包括查询、操纵、定义和控制4个方面，是一个通用的、功能极强的关系数据库语言,目前已成为关系数据库的标准语言。实验目的：使用数据库管理系统SQLServer的查询分析器，巩固学习SQL语言。教学课时数为6。实验内容：熟练查询分析器的各种操作，使用SQL语言进行编程设计。实验要求：能够熟练使用查询分析器，掌握SQL中的DDL、DML、DCL等各种语言,完成各种编程练习.理解视图的概念,能够按照要求设计数据库的外模式.实验操作题目(1)使用DDL语言定义基本表.有一个员工项目数据库,现有四张表，分别是部门（DEPT），员工（EMP），项目（PROJ）以及员工参与项目的情况表（EP）。其中一个员工属于一个部门，一个部门有多个员工；一个员工可同时参加多个项目，一个项目有多个员工一起开发。用SQL语言中的DDL语言建立这四张表,表的主码,以及设计并建立表的外码和索引，其中周期部门表（DEPT）上按部门名称设计升序唯一索引，在员工表（EMP）上按照部门设计聚簇索引，按照职工工资降序索引，在员工项目表上按员工号升序、项目号降序建立唯一索引。DEPTDNO（部门号）DNAME（部门名）CNAME（领导）D1厂部1001D2工程部1003D3技术部1004D4质监部1005。。。。。。。。。PROJPNO（项目编号）PNAME（项目名称）PCITY（项目所在城市）J1Y天津J2W上海J3X1宁波J4S北京。。。。。。。。。50EMPENO（员工号）ENAME（姓名）ESEX（性别）ESALARY（工资）DNO（所在部门）1001李月男1200D11002魏兰女1440D31003赵希青男1360D21004张明男1600D3。。。。。。。。。。。。。。。EPENO（员工号）PNO（项目号）RESPONSE（任务）1001J1总工1001J3联系人1002J1技术负责1002J2技术监督1003J1现场管理1004J3主管。。。。。。。表设计：关系名属性的名字属性含义域是否主码是否外码DEPTDNO部门号Char(20)yDNAME部门名Char(20)CNAME领导(null)Char(20)yPROJPNO项目编号Char(20)yPNAME项目名称Char(20)PCITY项目所在城市Char(20)EMPENO员工号Char(20)yENAME姓名Char(10)ESEX性别Char(10)ESALARY工资intDNO所在部门Char(20)yEPENO员工号Char(20)Y+yPNO项目号Char(20)Y+yRESPONSE任务Char(20)(1)创建表：createtableDEPT(DNOchar(20)notnull,51DNAMEchar(20)notnull,CNAMEchar(20),constraintPK_DEPTprimarykey(DNO))createtablePROJ(PNOchar(20)notnull,PNAMEchar(20)notnull,PCITYchar(20)notnull,constraintPK_PROJprimarykey(PNO))createtableEMP(ENOchar(20)notnull,ENAMEchar(10)notnull,ESEXchar(10)notnull,ESALARYint,DNOchar(20)notnull,constraintPK_EMPprimarykey(ENO),constraintfk_EMP_DNOforeignkey(DNO)referencesDEPT(DNO))createtableEP(ENOchar(20)notnull,PNOchar(20)notnull,RESPONSEchar(20),constraintPK_EPprimarykey(ENO,PNO),constraintfk_EP_ENOforeignkey(ENO)referencesEMP(ENO),constraintfk_EP_PNOforeignkey(PNO)referencesPROJ(PNO))(2)使用DML语言输入以上表中的数据(INSERT,UPDATE和DELETE语句)insertintoDEPT(DNO,DNAME)VALUES('D1','厂部')insertintoDEPT(DNO,DNAME)VALUES('D2','工程部')insertintoDEPT(DNO,DNAME)VALUES('D3','技术部')insertintoDEPT(DNO,DNAME)VALUES('D4','质监部')insertintoPROJ(PNO,PNAME,PCITY)VALUES('J1','Y','天津')insertintoPROJ(PNO,PNAME,PCITY)52VALUES('J2','W','上海')insertintoPROJ(PNO,PNAME,PCITY)VALUES('J3','X1','宁波')insertintoPROJ(PNO,PNAME,PCITY)VALUES('J4','S','北京')insertintoEMP(ENO,ENAME,ESEX,ESALARY,DNO)VALUES('1001','李月','男',1200,'D1')insertintoEMP(ENO,ENAME,ESEX,ESALARY,DNO)VALUES('1002','魏兰','女',1440,'D3')insertintoEMP(ENO,ENAME,ESEX,ESALARY,DNO)VALUES('1003','赵希青','男',1360,'D2')insertintoEMP(ENO,ENAME,ESEX,ESALARY,DNO)VALUES('1004','张明','男',1600,'D3')insertintoEP(ENO,PNO,RESPONSE)VALUES('1001','J1','总工')insertintoEP(ENO,PNO,RESPONSE)VALUES('1001','J3','联系人')insertintoEP(ENO,PNO,RESPONSE)VALUES('1002','J1','技术负责')insertintoEP(ENO,PNO,RESPONSE)VALUES('1002','J2','技术监督')insertintoEP(ENO,PNO,RESPONSE)VALUES('1003','J1','现场管理')insertintoEP(ENO,PNO,RESPONSE)VALUES('1004','J3','主管')(3)对以上表中的数据以下数据的更新:①删除有关‘J3’项目的所有数据DeletefromEPwherePNO='J3'DeletefromPROJwherePNO='J3'②将D2部门员工的工资增加5%UPDATEEMPSETESALARY=ESALARY*1.05WHEREDNO='D2'③修改J2项目的所在城市为北京UPDATEPROJSETPCITY='北京'WHEREPNO='J2'④删除张明这个员工的所有信息53DeletefromEPwhereENO=(selectENOfromEMPwhereENAME='张明')DeletefromEMPwhereENAME='张明'（4）完成以下简单查询①求参加‘J3’项目的员工号selectENOfromEPwherePNO='J3'②查询工资在1500以上的员工信息select*fromEMPwhereESALARY1500③求没有参加‘J2’项目的员工号selectENOfromEMPwherenotENO=(selectENOfromEPwherePNO='J2')⑦找出参加项目数大于3的员工号selectEP.ENOfromEPgroupbyEP.ENOhavingCOUNT(EP.PNO)=3⑧统计每个部门的最高工资、最低工资、平均工资selectEMP.DNOas部门,MAX(EMP.ESALARY)as工资最高,MIN(EMP.ESALARY)as工资最低,AVG(EMP.ESALARY)as平均工资fromEMPGROUPbyEMP.DNO⑨查询人员数目大于30的部门号selectEMP.DNOfromEMPgroupbyEMP.DNOhavingCOUNT(EMP.ENO)=3054