数据库试验三

1、西南石油大学实验报告课程名称：数据库原理插入你的照片实验工程名称：实验3 SQL数据定义语言专业年级班级、姓名、学号：电子邮件地址：实验所用机器名：实验时间地点：明理楼实验指导教师：孙瑜成绩批改人批改日期注意：在粘贴截图时请保存窗口完整标题,但只需保存关键界面,多余的空白界面请删除.一、实验课时：4二、实验目的(1) 掌握使用T-SQL语句创立、删除数据库的方法.掌握使用T-SQL语句创立、修改、删除表的方法.(3) 掌握使用T-SQL语句创立、删除数据库完整性约束条件的方法.(4) 掌握使用T-SQL语句对表添加、修改、删除数据的方法.(5) 掌握使用T-SQL语句创立、修改、删除、查询视图

2、的方法.三、实验要求(1) 使用SQL Server 2021查询分析器.(2) 严格依照操作步骤进行.(3) 在本地效劳器中创立和治理数据库.四、实验环境(1) PC 机.(2) SQL Server 200&五、实验内容及步骤(请特别注意实验步骤：第6项的第1小项,即“插入数 据操作必须在第4项以前执行)? 1 使用Transact-SQL语句创立JOBS数据库,数据库名格式为 JOBS_SunYi即JOBS你的中文名字拼音)CREATE DatabaseJOBS_DengZhiPeng ;2 使用Transact-SQL语句创立JOBS数据库包含的所有表EMPLOYEECREAT

3、E TABLE EMPLOYEE( EMPNO SMALLINT NOT NULL, SUPNAME VARCHAR( 50 ) NOT NULL, FORENAMESVARCHAR( 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,

4、STARDATE DATE NOT NULL, ENDDATE DATE NULL, SALARY INT NOT NULL);COURSECREATE TABLE COURSE( COURSENOSMALLINT 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 SMALLI

5、NT NOT NULL);EMPCOURSECREATE TABLE EMPCOURS(E EMPNO SMALLINT NOT NULL,COURSENOSMALLINT NOT NULL);3.使用Transact-SQL语句创立JOBS数据库包含表的主键、外键约束条 件创立每个表的主键：ALTER TABLE EMPLOYEEADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY ( EMPNO);ALTER TABLE JOBHISTORYADD CONSTRAINT PK_JOBHISTORYPRIMARY KEY ( EMPNO, POSITION , STA

6、RDATE); ALTER TABLE COURSEADD CONSTRAINT PK_COURSEPRIMARY KEY ( COURSENO);ALTER TABLE DEPARTMENTADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY ( DEPNO);ALTER TABLE EMPCOURSEADD CONSTRAINT PK_EMCOURSE PRIMARY KEY ( EMPNO, COURSENO); 创立关系：ALTER TABLE JOBHISTORYADD CONSTRAINT FK_JOBHISTORY_EMPLOYEE FOREIGN K

7、EY ( 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_EMPL

8、OYEE_DEPARTMENTFOREIGN KEY ( DEPNO)REFERENCES DEPARTMENT ( DEPNO);ALTER TABLE DEPARTMENTADD CONSTRAINT FK_DEPARTMENT_EMPLOYEEFOREIGN KEY ( HEAD)REFERENCES EMPLOYEE( EMPNO); 具体关系：EXECUTE sp_helpconstraint EXECUTE sp_helpconstraint EXECUTE sp_helpconstraint EXECUTE sp_helpconstraintDEPARTMENT; EMPCOUR

9、SE; JOBHISTORY; EMPLOYEE;所有 JOBS 中表的约束图. 注意：必须如上图所示清楚完整显示约束的constraint_type constraint_name constraint_keys等信息.4 备份JOBS数据库5.使用Transact-SQL语句在JOBS数据库里创立视图注意：在以下各 个小题中,后续题目可以利用前面题目创立的视图 1 创立一个名为“ firstview 的视图,列出不重复的所有选修了课程的 empno插入定义该视图的SQL窗口CREATE VIEW firstview EMPNOASSELECT DISTINCT EMPNOFROM EMPC

10、OURS;E插入查询该视图的SQL窗口及结果创立一个名为“ 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(

11、 EMPNO, COURSENUM)ASSELECT EL. EMPNO, COUNT(*)FROM EMPLOYEE EL JOINEMPCOURSEEC ON EL. EMPNO = EC. EMPNO GROUP BY EL. EMPNO;(插入查询该视图的SQL窗口及结果) 创立一个名为“ fourthview 的视图,列出每个 empno及其已经或正在 从事的工作数(插入定义该视图的SQL窗口)CREATE VIEW fourthview( EMPNO, JOBNUM)ASSELECT EL. EMPNO , COUNT(*) FROM EMPLOYEE ELJOIN JOBHIST

12、ORY 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. JOBNUMFROM EMPLOYEE ELLEFT JOIN T

13、HIRDVIEW 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-0111&#

14、39; );INSERT INTOCOURSE( COURSENO, CNAME, CDATE)VALUES ( 2, 'Further Accounting' , '198901-25' );INSERT INTOCOURSE( COURSENO, CNAME, CDATE)VALUES ( 3, 'Issues InAdministration' , '1988-09-27' );INSERT INTOCOURSE( COURSENO, CNAME, CDATE)VALUES ( 4, 'More Administra

15、tion' , '198810-16' );select * from course ;ALTER TABLE EMPLOYEE ALTER COLUMNDEPNO SMALLINT NULL;INSERT INTO EMPLOYEE( EMPNO, SUPNAME, FORENAMES, DOB, ADDRESS, TELNO, DEPNO) VALUES ( 1, 'Jones' , 'Elizabeth Barbara' '1944-01-05' , '26 Agnews Terrace,Shamrock B

16、ay' , 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) VALUE

17、S ( 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);I

18、NSERT INTO EMPLOYEE( EMPNO, SUPNAME, FORENAMES, DOB, ADDRESS, TELNO, DEPNO) VALUES ( 5, 'MacCallan', 'Claire' ,'1958-09-18' , '25 Crisis Avenue,Leith,Edinburgh' , NULL);INSERT INTO EMPLOYEE( EMPNO, SUPNAME, FORENAMES, DOB, ADDRESS, TELNO, DEPNO)VALUES ( 6, 'Murphy

19、' , 'Brian Charles','1954-06-30' , '9 Roberts Street,Biggar' , NULL);当录制完DEPARTMENT以后,再将NULL设置为相 应的DEPN0值.UPDATE EMPLOYEESET DEPN0 = 1WHEREDEPN0IS NULL;最后的结果：INSERT INT0DEPARTMENT(DEPN0,DNAME, L0CATI0N, HEAD)VALUES (1, 'accounts' , 'floor3' , 1);INSERT INT

20、0DEPARTMENT(DEPN0,DNAME, L0CATI0N, HEAD)VALUES (2, 'administration' , 'floor2' ,1);INSERT INT0DEPARTMENT(DEPN0,DNAME, L0CATI0N, HEAD)VALUES (3, 'software design' , 'floor1' , 2);INSERT INT0DEPARTMENT(DEPN0,DNAME, L0CATI0N, HEAD)VALUES ( 4, 'communications' , &

21、#39;floor4' , 3); select * from DEPARTMENT;INSERT INT0 EMPC0URSE(EMPN0, C0URSEN0)VALUES (1, 1);INSERT INT0 EMPC0URSE(EMPN0, C0URSEN0)VALUES (1, 2);INSERT INT0 EMPC0URSE(EMPN0, C0URSEN0)VALUES (2, 1);INSERT INT0 EMPC0URSE(EMPN0, C0URSEN0)VALUES ( 2, 2);select * from EMPCOURS;EINSERT INTOJOBHISTOR

22、Y( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 1, 'Accounts Manager' , '1976-0112' ,NULL, 30000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 1, 'Assistant AccountsManager' , '1972-02-11' , '1976-0112' , 22000 );INSERT

23、INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 1, 'Accountant' , '1968-0310' , '1972-02-11' , 15000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 1, 'Junior Accountant' , '196404-09' , '1968-03-10'

24、, 6000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 2, 'Assistant AccountsManager' , '1976-05-08' ,NULL, 25000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 2, 'Accountant' , '1971-0607' , '1976-05-0

25、8' , 16000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 2, 'Junior Accountant', '196707-06' , '1971-06-07', 8000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 3, 'Accountant' , '1981-0805' ,N

26、ULL, 16000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 3, 'Junior Accountant', '198109-04' , '1984-08-05', 8000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 4, 'Accountant' , '1989-1005' ,NULL, 16000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 5, 'Accountant' , '1980-1102' ,NULL, 16000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 5, 'Junior Accountant