swpu2019数据库实验三
swpu2019数据库实验三

swpu2019数据库实验三

针对于2021.5.24日晚的sqlserver数据库实验三的记录,仅供参考,如有抄袭行为与我无关

该换个人信息的地方换一下,另外注意读注释,注意操作顺序是1,2,3,6,5,4,不然可能在做视图的时候出现没数据之类的问题。

1.使用Transact-SQL语句创建JOBS数据库

代码/截图:

CREATE Database JOBS_huanghe

Q1

 

2.使用Transact-SQL语句创建JOBS数据库包含的所有表

代码/截图:

CREATE 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 NULL ); 
 
CREATE TABLE JOBHISTORY(EMPNO SMALLINT NOT NULL, POSITION VARCHAR(50) NOT NULL,STARDATE DATE NOT NULL,ENDDATE DATE NULL,SALARY INT NOT NULL ); 
 
CREATE TABLE COURSE(COURSENO SMALLINT NOT NULL,CNAME VARCHAR(50) NOT NULL,CDATE DATE NOT NULL ); 
 
CREATE TABLE DEPARTMENT(DEPNO SMALLINT NOT NULL,DNAME VARCHAR(50) NOT NULL,LOCATION VARCHAR(10) NOT NULL,HEAD SMALLINT NOT NULL);
 
CREATE TABLE EMPCOURSE(EMPNO SMALLINT NOT NULL, COURSENO SMALLINT NOT NULL);

Q2

 

 

3.使用Transact-SQL语句创建JOBS数据库包含表的主键、外键约束条件

代码/截图:

创建主键:

ALTER TABLE EMPLOYEE  
ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO);
ALTER TABLE JOBHISTORY  
ADD CONSTRAINT PK_JOBHISTORY PRIMARY KEY (EMPNO,POSITION,STARDATE); 
ALTER TABLE COURSE  
ADD CONSTRAINT PK_COURSE PRIMARY KEY (COURSENO); 
ALTER TABLE DEPARTMENT  
ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPNO); 
ALTER TABLE EMPCOURSE  
ADD CONSTRAINT PK_EMCOURSE PRIMARY KEY (EMPNO,COURSENO);

Q3(5)
Q3
Q3(1)
Q3(2)
Q3(3)
Q3(4)

 

创建外键关系:

ALTER TABLE JOBHISTORY  
ADD CONSTRAINT FK_JOBHISTORY_EMPLOYEE FOREIGN KEY (EMPNO) 
REFERENCES EMPLOYEE (EMPNO); 
ALTER TABLE EMPCOURSE  
ADD CONSTRAINT FK_EMPCOURSE_EMPLOYEE FOREIGN KEY (EMPNO) 
REFERENCES EMPLOYEE (EMPNO); 
ALTER TABLE EMPCOURSE  
ADD CONSTRAINT FK_EMPCOURSE_COURSE FOREIGN KEY (COURSENO) 
REFERENCES COURSE (COURSENO); 
ALTER TABLE EMPLOYEE 
ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENT FOREIGN KEY (DEPNO) 
REFERENCES DEPARTMENT (DEPNO); 
ALTER TABLE DEPARTMENT 
ADD CONSTRAINT FK_DEPARTMENT_EMPLOYEE FOREIGN KEY (HEAD) 
REFERENCES  EMPLOYEE(EMPNO);

 

Q3(6)

 

查看表的约束:

EXECUTE sp_helpconstraint DEPARTMENT; 
EXECUTE sp_helpconstraint EMPCOURSE; 
EXECUTE sp_helpconstraint JOBHISTORY; 
EXECUTE sp_helpconstraint EMPLOYEE;

Q3(7)
Q3(8)
[reply]

4.备份JOBS数据库

代码/截图:

BACKUP DATABASE JOBS_huanghe TO DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\JOBS_huanghe.bak'

这个备份路径是自己设定的,不用和我一样。

 

Q4
Q4(1)

 

5. 使用Transact-SQL语句在JOBS数据库里创建视图(注意:在以下各个小题中,后续题目可以利用前面题目创建的视图)

(1) Create a view "firstview" which shows the distinct empnos who have studied one or more courses(创建一个名为“firstview”的视图,列出不重复的所有选修了课程的empno).

代码/截图:

CREATE VIEW firstview(EMPNO) AS 
SELECT DISTINCT EMPNO FROM EMPCOURSE;

Q5

 

 

(2) Create a view "secondview" which shows all employees who have an empno less than 5(创建一个名为“secondview”的视图,列出所有empno小于5的员工信息).

代码/截图:

CREATE VIEW secondview(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) AS 
SELECT * FROM EMPLOYEE 
WHERE EMPNO < 5;

Q5(1)

 

(3) Create a view "thirdview" which lists the number of courses each empno has been on(创建一个名为“thirdview”的视图,列出每个empno及其相应的选修课程数).

代码/截图:

CREATE VIEW thirdview(EMPNO,COURSENUM) AS 
SELECT EL.EMPNO,COUNT(*) FROM EMPLOYEE EL  JOIN EMPCOURSE EC ON EL.EMPNO = EC.EMPNO 
GROUP BY EL.EMPNO;

Q5(2)

 

 

(4) Create a view “ fourthview” which lists the number of jobs each empno has been on(创建一个名为“fourthview”的视图,列出每个empno及其已经或正在从事的工作数).

代码/截图:

CREATE VIEW fourthview(EMPNO,JOBNUM) AS 
SELECT EL.EMPNO ,COUNT(*) FROM  EMPLOYEE EL JOIN JOBHISTORY JO ON EL.EMPNO = JO.EMPNO 
GROUP BY EL.EMPNO;

Q5(3)

 

 

(5) Create a SELECT statement which combines the views from questions (3) and (4) together to show how many jobs and how many courses each empno has been on. You SHOULD NOT create the views again as part of the answer, just access them with the same name used to create them in the previous questions. If the employee has been on 0 courses then the total number of courses for that employee should be blank (null). Hint: use OUTER JOIN(创建一个合并第3和第4小题视图的SELECT语句,以查询每个empno对应的工作数和课程数。你不需要重复创建视图,只需要利用前面两个小题中已经创建好的视图。如果某员工号对应的课程数为0,则在查询结果中应显示为NULL。提示:用外连接).

代码/截图:

SELECT EL.EMPNO,T.COURSENUM,F.JOBNUM FROM EMPLOYEE EL  
LEFT JOIN THIRDVIEW T ON  T.EMPNO = EL.EMPNO JOIN FOURTHVIEW F ON  EL.EMPNO =F.EMPNO;

Q5(4)

 

 

6.使用Transact-SQL语句对表添加、修改、删除数据

(1) 插入数据

按照ActiveSQL_JobsDB.rar文件里的数据库状态图插入所有表的数据(如果试图插入的数据将会违反第3步创建的约束条件,则可跳过该行数据的输入(department第五行数据))。

代码/截图:

插入course表数据:按照原图数据插入生日时会提示“从字符串转换日期和/或时间时,转换失败。”所以需要对原数据进行修改再插入

WT1

Course表数据插入:

INSERT INTO 
COURSE(COURSENO,CNAME,CDATE) 
VALUES (1,'Basic Accounting','1989-01-11'); 
INSERT INTO 
COURSE(COURSENO,CNAME,CDATE) 
VALUES (2,'Further Accounting','1989-01-25'); 
INSERT INTO 
COURSE(COURSENO,CNAME,CDATE) VALUES (3,'Issues In Administration','1988-09-27'); 
INSERT INTO 
COURSE(COURSENO,CNAME,CDATE) 
VALUES (4,'More Administration','1988-10-16'); 
select * from course;

 

Q6

这里我还有个地方不明白,为什么会划红线,一直提示列名和对象名不存在,但是插入数据仍然成功呢?

 

插入EMPLOYEE数据此处不能直接插入最后一栏数据,需要先让它保持空值,不然会和department表约束冲突)(后面等department表数据全部录入之后再填入该栏数据

WT2

EMPLOYEE表数据插入:

INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) 
VALUES (1,'Jones','Elizabeth Barbara', '1944-01-05','26 Agnews Terrace,Shamrock Bay','2123372288' , NULL); 
INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) 
VALUES (2,'Smith','Robert', '1947-02-07','18 Marsh Street,Tollcross,Edinburgh', '0317328972', NULL); 
INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) 
VALUES (3,'White','Allan', '1961-05-05','6 Remote Place,North Berwick','1215556622' , NULL); 
INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) 
VALUES (4,'Reid','Gordon', '1963-08-10','9 Noble Road,Penicuik', '6294246713', NULL); 
INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) 
VALUES (5,'MacCallan','Claire', '1958-09-18','25 Crisis Avenue,Leith,Edinburgh', '0313374166', NULL); 
INSERT INTO EMPLOYEE(EMPNO,SUPNAME, FORENAMES,DOB,ADDRESS,TELNO,DEPNO) 
VALUES (6,'Murphy','Brian Charles', '1954-06-30','9 Roberts Street,Biggar', '3312294147', NULL); 
select * from EMPLOYEE

Q6(1)

插入department数据为啥插入不了最后一行数据?约束条件咋只针对最后一行?插入employee表用过的null方法在这里也用不了

Q6(2)

Department表数据插入:

INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD) VALUES (1,'accounts','floor3',1); 
INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD) VALUES (2,'administration','floor2',1); 
INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD) VALUES (3,'software design','floor1',2); 
INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD) VALUES (4,'communications','floor4',3); 
INSERT INTO DEPARTMENT(DEPNO,DNAME,LOCATION,HEAD) VALUES (5,'engineering','floor5',7); 
select * from DEPARTMENT;

Q6(3)

 

 

 

更新employee表最后一栏数据

UPDATE EMPLOYEE SET DEPNO = 1 
WHERE DEPNO IS NULL;  
select * from EMPLOYEE;

Q6(5)

 

 

EMPCOURSE表数据插入

INSERT INTO EMPCOURSE(EMPNO,COURSENO) VALUES (1,1); 
INSERT INTO EMPCOURSE(EMPNO,COURSENO) VALUES (1,2); 
INSERT INTO EMPCOURSE(EMPNO,COURSENO) VALUES (2,1); 
INSERT INTO EMPCOURSE(EMPNO,COURSENO) VALUES (2,2); 
select * from EMPCOURSE;

Q6(4)

 

 

JOBHISTORY表数据插入:

INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (1,'Accounts Manager','1976-01-12',NULL,30000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (1,'Assistant Accounts Manager','1972-02-11','1976-01-12',22000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (1,'Accountant','1968-03-10','1972-02-11',15000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (1,'Junior Accountant','1964-04-09','1968-03-10',6000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (2,'Assistant Accounts Manager','1976-05-08',NULL,25000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (2,'Accountant','1971-06-07','1976-05-08',16000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (2,'Junior Accountant','1967-07-06','1971-06-07',8000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (3,'Accountant','1981-08-05',NULL,16000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (3,'Junior Accountant','1981-09-04','1984-08-05',8000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (4,'Accountant','1989-10-05',NULL,16000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (5,'Accountant','1980-11-02',NULL,16000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (5,'Junior Accountant','1978-12-01','1980-11-02',8000); 
INSERT INTO JOBHISTORY(EMPNO,POSITION,STARDATE,ENDDATE,SALARY) 
VALUES (6,'Accountant','1980-01-12',NULL,16000); 
select * from JOBHISTORY;

Q6(6)

 

 

(2) 修改数据

将所有1960年以后出生员工的部门编号修改为3。

代码/截图:

UPDATE EMPLOYEE SET DEPNO = 3 
WHERE DOB > '1960-01-01'; 
SELECT * FROM EMPLOYEE;

 

 

修改前:

Q6(7)

 

修改后:

Q6(8)

 

(3) 删除数据

删除所有员工以前的工作历史。

代码/截图:

DELETE FROM JOBHISTORY 
WHERE ENDDATE IS NOT NULL; 
SELECT * FROM JOBHISTORY;

 

 

删除之前:

Q6(9)

 

删除以后:

Q6(10)

[/reply]
演示到此结束。

SWPU2019数据库实验四

SWPU2019数据库实验二

 

后面附上此次实验的参考数据图

ActiveSQL Employee
ActiveSQL JOBHISTORY
ActiveSQL Course
ActiveSQL Department
ActiveSQL EMPCourse

5 1 投票
Article Rating
订阅
提醒
19 评论
最新评论
最久评论 最受欢迎的评论
内联反馈
查看所有评论
19
0
有什么想说的吗?评论几句?x
()
x