数据库实验三

西南石油大学实验报告课程名称：数据库原理插入你的照片实验项目名称：实验3SQL数据定义语言专业年级班级、姓名、学号：电子邮件地址：实验所用机器名：实验时间地点：2015.12.11明理楼实验指导教师：孙瑜成绩批改人批改日期注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。一、实验课时：4二、实验目的(1)掌握使用T-SQL语句创建、删除数据库的方法。(2)掌握使用T-SQL语句创建、修改、删除表的方法。(3)掌握使用T-SQL语句创建、删除数据库完整性约束条件的方法。(4)掌握使用T-SQL语句对表添加、修改、删除数据的方法。(5)掌握使用T-SQL语句创建、修改、删除、查询视图的方法。三、实验要求(1)使用SQLServer2008查询分析器。(2)严格依照操作步骤进行。(3)在本地服务器中创建和管理数据库。四、实验环境(1)PC机。(2)SQLServer2008。五、实验内容及步骤（请特别注意实验步骤:第6项的第1小项，即“插入数据”操作必须在第4项以前执行）1．使用Transact-SQL语句创建JOBS数据库，数据库名格式为JOBS_SunYu(即JOBS_你的中文名字拼音)CREATEDatabaseJOBS_DengZhiPeng;2．使用Transact-SQL语句创建JOBS数据库包含的所有表EMPLOYEECREATETABLEEMPLOYEE(EMPNOSMALLINTNOTNULL,SUPNAMEVARCHAR(50)NOTNULL,FORENAMESVARCHAR(50)NOTNULL,DOBDATENOTNULL,ADDRESSVARCHAR(50)NOTNULL,TELNOCHAR(10)NOTNULL,DEPNOSMALLINTNOTNULL);JOBHISTORYCREATETABLEJOBHISTORY(EMPNOSMALLINTNOTNULL,POSITIONVARCHAR(50)NOTNULL,STARDATEDATENOTNULL,ENDDATEDATENULL,SALARYINTNOTNULL);COURSECREATETABLECOURSE(COURSENOSMALLINTNOTNULL,CNAMEVARCHAR(50)NOTNULL,CDATEDATENOTNULL);DEPARTMENTCREATETABLEDEPARTMENT(DEPNOSMALLINTNOTNULL,DNAMEVARCHAR(50)NOTNULL,LOCATIONVARCHAR(10)NOTNULL,HEADSMALLINTNOTNULL);EMPCOURSECREATETABLEEMPCOURSE(EMPNOSMALLINTNOTNULL,COURSENOSMALLINTNOTNULL);3．使用Transact-SQL语句创建JOBS数据库包含表的主键、外键约束条件创建每个表的主键：ALTERTABLEEMPLOYEEADDCONSTRAINTPK_EMPLOYEEPRIMARYKEY(EMPNO);ALTERTABLEJOBHISTORYADDCONSTRAINTPK_JOBHISTORYPRIMARYKEY(EMPNO,POSITION,STARDATE);ALTERTABLECOURSEADDCONSTRAINTPK_COURSEPRIMARYKEY(COURSENO);ALTERTABLEDEPARTMENTADDCONSTRAINTPK_DEPARTMENTPRIMARYKEY(DEPNO);ALTERTABLEEMPCOURSEADDCONSTRAINTPK_EMCOURSEPRIMARYKEY(EMPNO,COURSENO);创建关系：ALTERTABLEJOBHISTORYADDCONSTRAINTFK_JOBHISTORY_EMPLOYEEFOREIGNKEY(EMPNO)REFERENCESEMPLOYEE(EMPNO);ALTERTABLEEMPCOURSEADDCONSTRAINTFK_EMPCOURSE_EMPLOYEEFOREIGNKEY(EMPNO)REFERENCESEMPLOYEE(EMPNO);ALTERTABLEEMPCOURSEADDCONSTRAINTFK_EMPCOURSE_COURSEFOREIGNKEY(COURSENO)REFERENCESCOURSE(COURSENO);ALTERTABLEEMPLOYEEADDCONSTRAINTFK_EMPLOYEE_DEPARTMENTFOREIGNKEY(DEPNO)REFERENCESDEPARTMENT(DEPNO);ALTERTABLEDEPARTMENTADDCONSTRAINTFK_DEPARTMENT_EMPLOYEEFOREIGNKEY(HEAD)REFERENCESEMPLOYEE(EMPNO);具体关系：EXECUTEsp_helpconstraintDEPARTMENT;EXECUTEsp_helpconstraintEMPCOURSE;EXECUTEsp_helpconstraintJOBHISTORY;EXECUTEsp_helpconstraintEMPLOYEE;(所有JOBS中表的约束图。注意：必须如上图所示清楚完整显示约束的constraint_type、constraint_name、constraint_keys等信息。)4．备份JOBS数据库5．使用Transact-SQL语句在JOBS数据库里创建视图(注意：在以下各个小题中，后续题目可以利用前面题目创建的视图)(1)创建一个名为“firstview”的视图，列出不重复的所有选修了课程的empno(插入定义该视图的SQL窗口)CREATEVIEWfirstview(EMPNO)ASSELECTDISTINCTEMPNOFROMEMPCOURSE;(插入查询该视图的SQL窗口及结果)(2)创建一个名为“secondview”的视图，列出所有empno小于5的员工信息(插入定义该视图的SQL窗口)CREATEVIEWsecondview(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)ASSELECT*FROMEMPLOYEEWHEREEMPNO5;(插入查询该视图的SQL窗口及结果)(3)创建一个名为“thirdview”的视图，列出每个empno及其相应的选修课程数(插入定义该视图的SQL窗口)CREATEVIEWthirdview(EMPNO,COURSENUM)ASSELECTEL.EMPNO,COUNT(*)FROMEMPLOYEEELJOINEMPCOURSEECONEL.EMPNO=EC.EMPNOGROUPBYEL.EMPNO;(插入查询该视图的SQL窗口及结果)(4)创建一个名为“fourthview”的视图，列出每个empno及其已经或正在从事的工作数(插入定义该视图的SQL窗口)CREATEVIEWfourthview(EMPNO,JOBNUM)ASSELECTEL.EMPNO,COUNT(*)FROMEMPLOYEEELJOINJOBHISTORYJOONEL.EMPNO=JO.EMPNOGROUPBYEL.EMPNO;(插入查询该视图的SQL窗口及结果)SELECT*FROMFOURTHVIEW;(5)创建一个合并第3和第4小题视图的SELECT语句，以查询每个empno对应的工作数和课程数。你不需要重复创建视图，只需要利用前面两个小题中已经创建好的视图。如果某员工号对应的课程数为0，则在查询结果中应显示为NULL。提示：用外连接(插入该查询的SQL窗口及结果)SELECTEL.EMPNO,T.COURSENUM,F.JOBNUMFROMEMPLOYEEELLEFTJOINTHIRDVIEWTONT.EMPNO=EL.EMPNOJOINFOURTHVIEWFONEL.EMPNO=F.EMPNO;6．使用Transact-SQL语句对表添加、修改、删除数据(1)插入数据按照ActiveSQL_JobsDB.rar文件里的数据库状态图插入所有表的数据(如果试图插入的数据将会违反第3步创建的约束条件，则可跳过该行数据的输入)。INSERTINTOCOURSE(COURSENO,CNAME,CDATE)VALUES(1,'BasicAccounting','1989-01-11');INSERTINTOCOURSE(COURSENO,CNAME,CDATE)VALUES(2,'FurtherAccounting','1989-01-25');INSERTINTOCOURSE(COURSENO,CNAME,CDATE)VALUES(3,'IssuesInAdministration','1988-09-27');INSERTINTOCOURSE(COURSENO,CNAME,CDATE)VALUES(4,'MoreAdministration','1988-10-16');select*fromcourse;ALTERTABLEEMPLOYEEALTERCOLUMNDEPNOSMALLINTNULL;INSERTINTOEMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES(1,'Jones','ElizabethBarbara','1944-01-05','26AgnewsTerrace,ShamrockBay','2123372288',NULL);INSERTINTOEMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES(2,'Smith','Robert','1947-02-07','18MarshStreet,Tollcross,Edinburgh','0317328972',NULL);INSERTINTOEMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES(3,'White','Allan','1961-05-05','6RemotePlace,NorthBerwick','1215556622',NULL);INSERTINTOEMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES(4,'Reid','Gordon','1963-08-10','9NobleRoad,Penicuik','6294246713',NULL);INSERTINTOEMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES(5,'MacCallan','Claire','1958-09-18','25CrisisAvenue,Leith,Edinburgh','0313374166',NULL);INSERTINTOEMPLOYEE(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES(6,'Murphy','BrianCharles','1954-06-30','9RobertsStreet,Biggar','3312294147',NULL);当录制完DEPARTMENT以后，再将NULL设置为相应的DEPNO值。UPDATEEMPLOYEESETDEPNO=1WHEREDEPNOISNULL;最后的结果：INSERTINTODEPARTMENT(DEPNO,DNAME,LOCATION,HEAD)VALUES(1,'accounts','floor3',1);INSERTINTODEPARTMENT(DEPN