数据库实验三

注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的空白界面请删除。

一、实验课时：4二、实验目的(1) 掌握使用T-SQL语句创建、删除数据库的方法。

(2) 掌握使用T-SQL语句创建、修改、删除表的方法。

(3) 掌握使用T-SQL语句创建、删除数据库完整性约束条件的方法。

(4) 掌握使用T-SQL语句对表添加、修改、删除数据的方法。

(5) 掌握使用T-SQL语句创建、修改、删除、查询视图的方法。

三、实验要求(1) 使用SQL Server 2008查询分析器。

(2) 严格依照操作步骤进行。

(3) 在本地服务器中创建和管理数据库。

四、实验环境(1) PC机。

(2) SQL Server 2008。

五、实验内容及步骤（请特别注意实验步骤:第6项的第1小项，即“插入数据”操作必须在第4项以前执行）1．使用Transact-SQL语句创建JOBS数据库，数据库名格式为JOBS_SunYu(即JOBS_你的中文名字拼音)CREATE Database JOBS_DengZhiPeng;2．使用Transact-SQL语句创建JOBS数据库包含的所有表EMPLOYEECREATE TABLE EMPLOYEE(EMPNO SMALLINT NOT NULL,SUPNAME VARCHAR(50)NOT NULL,FORENAMES VARCHAR(50)NOT NULL, DOB DATE NOT NULL,ADDRESS VARCHAR(50)NOT NULL,TELNO CHAR(10)NOT NULL,DEPNO SMALLINT NOT NULL);JOBHISTORYCREATE TABLE JOBHISTORY(EMPNO SMALLINT NOT NULL,POSITION VARCHAR(50)NOT NULL,STARDATE DATE NOT NULL,ENDDATE DATE NULL,SALARY INT NOT NULL);COURSECREATE TABLE COURSE(COURSENO SMALLINT NOT NULL,CNAME VARCHAR(50)NOT NULL,CDATE DATE NOT NULL);DEPARTMENTCREATE TABLE DEPARTMENT(DEPNO SMALLINT NOT NULL,DNAME VARCHAR(50)NOT NULL,LOCATION VARCHAR(10)NOT NULL,HEAD SMALLINT NOT NULL);EMPCOURSECREATE TABLE EMPCOURSE(EMPNO SMALLINT NOT NULL,COURSENO SMALLINT NOT NULL);3．使用Transact-SQL语句创建JOBS数据库包含表的主键、外键约束条件创建每个表的主键：ALTER TABLE EMPLOYEEADD CONSTRAINT PK_EMPLOYEEPRIMARY KEY (EMPNO);ALTER TABLE JOBHISTORYADD CONSTRAINT PK_JOBHISTORYPRIMARY KEY (EMPNO,POSITION,STARDATE); ALTER TABLE COURSEADD CONSTRAINT PK_COURSEPRIMARY KEY (COURSENO);ALTER TABLE DEPARTMENTADD CONSTRAINT PK_DEPARTMENTPRIMARY KEY (DEPNO);ALTER TABLE EMPCOURSEADD CONSTRAINT PK_EMCOURSEPRIMARY KEY (EMPNO,COURSENO);创建关系：ALTER TABLE JOBHISTORYADD CONSTRAINT FK_JOBHISTORY_EMPLOYEE FOREIGN KEY (EMPNO)REFERENCES EMPLOYEE(EMPNO);ALTER TABLE EMPCOURSEADD CONSTRAINT FK_EMPCOURSE_EMPLOYEE FOREIGN KEY (EMPNO)REFERENCES EMPLOYEE(EMPNO);ALTER TABLE EMPCOURSEADD CONSTRAINT FK_EMPCOURSE_COURSE FOREIGN KEY (COURSENO)REFERENCES COURSE(COURSENO);ALTER TABLE EMPLOYEEADD CONSTRAINT FK_EMPLOYEE_DEPARTMENT FOREIGN KEY (DEPNO)REFERENCES DEPARTMENT(DEPNO);ALTER TABLE DEPARTMENTADD CONSTRAINT FK_DEPARTMENT_EMPLOYEE FOREIGN KEY (HEAD)REFERENCES EMPLOYEE(EMPNO);具体关系：EXECUTE sp_helpconstraint DEPARTMENT; EXECUTE sp_helpconstraint EMPCOURSE; EXECUTE sp_helpconstraint JOBHISTORY; EXECUTE sp_helpconstraint EMPLOYEE;(所有JOBS中表的约束图。

注意：必须如上图所示清楚完整显示约束的constraint_type、constraint_name、constraint_keys等信息。

)4．备份JOBS数据库5．使用Transact-SQL语句在JOBS数据库里创建视图(注意：在以下各个小题中，后续题目可以利用前面题目创建的视图)(1)创建一个名为“firstview”的视图，列出不重复的所有选修了课程的empno(插入定义该视图的SQL窗口)CREATE VIEW firstview(EMPNO)ASSELECT DISTINCT EMPNOFROM EMPCOURSE;(插入查询该视图的SQL窗口及结果)(2)创建一个名为“secondview”的视图，列出所有empno小于5的员工信息(插入定义该视图的SQL窗口)CREATE VIEW secondview(EMPNO,SUPNAME,FORENAMES,DOB,ADDRESS,TELNO,DEPNO)ASSELECT*FROM EMPLOYEEWHERE EMPNO< 5;(插入查询该视图的SQL窗口及结果)(3)创建一个名为“thirdview”的视图，列出每个empno及其相应的选修课程数(插入定义该视图的SQL窗口)CREATE VIEW thirdview(EMPNO,COURSENUM) ASSELECT EL.EMPNO,COUNT(*)FROM EMPLOYEE EL JOINEMPCOURSE EC ON EL.EMPNO=EC.EMPNO GROUP BY EL.EMPNO;(插入查询该视图的SQL窗口及结果)(4)创建一个名为“fourthview”的视图，列出每个empno及其已经或正在从事的工作数(插入定义该视图的SQL窗口)CREATE VIEW fourthview(EMPNO,JOBNUM) ASSELECT EL.EMPNO,COUNT(*)FROM EMPLOYEE ELJOIN JOBHISTORY JO ON EL.EMPNO=JO.EMPNOGROUP BY EL.EMPNO;(插入查询该视图的SQL窗口及结果)SELECT*FROM FOURTHVIEW;(5)创建一个合并第3和第4小题视图的SELECT语句，以查询每个empno对应的工作数和课程数。

你不需要重复创建视图，只需要利用前面两个小题中已经创建好的视图。

如果某员工号对应的课程数为0，则在查询结果中应显示为NULL。

提示：用外连接(插入该查询的SQL窗口及结果)SELECT EL.EMPNO,T.COURSENUM,F.JOBNUM FROM EMPLOYEE ELLEFT JOIN THIRDVIEW T ONT.EMPNO=EL.EMPNOJOIN FOURTHVIEW F ONEL.EMPNO=F.EMPNO;6．使用Transact-SQL语句对表添加、修改、删除数据(1)插入数据按照ActiveSQL_JobsDB.rar文件里的数据库状态图插入所有表的数据(如果试图插入的数据将会违反第3步创建的约束条件，则可跳过该行数据的输入)。

INSERT INTOCOURSE(COURSENO,CNAME,CDATE)VALUES (1,'Basic Accounting','1989-01-11');INSERT INTOCOURSE(COURSENO,CNAME,CDATE)VALUES (2,'Further Accounting','1989-01-25');INSERT INTOCOURSE(COURSENO,CNAME,CDATE)VALUES (3,'Issues InAdministration','1988-09-27');INSERT INTOCOURSE(COURSENO,CNAME,CDATE)VALUES (4,'More Administration','1988-10-16');select*from course;ALTER TABLE EMPLOYEE ALTER COLUMN DEPNO SMALLINT NULL;INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) VALUES (1,'Jones','Elizabeth Barbara', '1944-01-05','26 AgnewsTerrace,Shamrock Bay',, NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) VALUES (2,'Smith','Robert','1947-02-07','18 MarshStreet,Tollcross,Edinburgh',, NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) VALUES (3,'White','Allan','1961-05-05','6 Remote Place,North Berwick',, NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) VALUES (4,'Reid','Gordon','1963-08-10','9 Noble Road,Penicuik', , NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) VALUES (5,'MacCallan','Claire','1958-09-18','25 CrisisAvenue,Leith,Edinburgh',, NULL);INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO)VALUES (6,'Murphy','Brian Charles','1954-06-30','9 Roberts Street,Biggar', , NULL);当录制完DEPARTMENT以后，再将NULL设置为相应的DEPNO值。