数据库实验2-4

数据库实验2-4

试一下markdown文档上传到csdn，玩一下markdown的语法。内容仅作参考。

数据库采用SQL-server，数据库文件备份在文章末尾。
包含对实验二：《SQL数据操作语言》——Company数据表的查询，实验三：《SQL数据定义语言》——使用T-SQL数据库创建JOBS数据库，实验四：《T-SQL语言编程》——使用T-SQL语言创建储存过程和触发器。

实验二：《SQL数据操作语言》

Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。

select FNAME,LNAME from EMPLOYEE

Q1. 使用查询分析器从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。员工的排序规则为：首先按工资的降序排列，然后按FNAME的字母升序排列。

select FNAME 名,lname 姓,ssn 社会保险号,bdate 生日,salary 工资 from EMPLOYEE
order by SALARY desc
select FNAME 名,lname 姓,ssn 社会保险号,bdate 生日,salary 工资 from EMPLOYEE
order by FNAME asc

Q2. Retrieve all distinct salary values.
查询不重复的员工工资值。

select distinct salary from EMPLOYEE

Q3. Retrieve the names of all employees who do not have supervisors.
查询没有直接上司的员工姓名。

select fname,lname,SUPERSSN from EMPLOYEE
where SUPERSSN is NULL

Q4. 从Employee表中查询出工资大于等于40000的员工资料。

select * from EMPLOYEE
where SALARY>=40000

Q5. 从Employee表中查询出1960年——1970年之间出生的员工资料。

select * from EMPLOYEE
where BDATE between '1960-1-1' and '1970-12-31'

Q6. 从Employee表中查询出部门号为4或者5的员工资料。

select * from EMPLOYEE
where DNO in ('4','5')

Q7. 从Employee表中查询出LNAME中含有字母o的员工资料。

select * from EMPLOYEE
where LNAME like '%o%'

Q8. 从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。

select 名=FNAME,姓=LNAME,社会保险号=SSN,人民币工资=SALARY*8 from EMPLOYEE

Q9. Retrieve the name and address of all employees who work for the ‘Research’ department.
查询所有为Research部门工作的员工姓名及地址。

select FNAME,LNAME,ADDRESS from EMPLOYEE join DEPARTMENT on EMPLOYEE.DNO=DEPARTMENT.DNUMBER
where DNAME='research'

Q10. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthdate.
对于所有位于Stafford的项目，查询项目的编号、项目负责部门编号以及该部门经理的姓、地址、生日。

select PROJECT.PNUMBER,PROJECT.DNUM,employee.LNAME,EMPLOYEE.ADDRESS,
employee.BDATE from PROJECT  
join DEPARTMENT on PROJECT.DNUM=DEPARTMENT.DNUMBER
join EMPLOYEE on DEPARTMENT.MGRSSN=EMPLOYEE.SSN
where plocation='stafford'

Q11. List the names of all employees with two or more dependents.
查询有两个或以上家属的员工姓名(此题较难，若不能完成者可只查询出员工的SSN而不是姓名)。

select FNAME,LNAME from EMPLOYEE
where SSN in(
select employee.SSN from EMPLOYEE
join DEPENDENT on EMPLOYEE.SSN=DEPENDENT.ESSN
group by EMPLOYEE.SSN
having COUNT(*)>=2)

Q12. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project.
查询在ProductX项目上每周工作时间超过10小时的部门5的员工姓名。

select FNAME,LNAME from EMPLOYEE
join WORKS_ON on EMPLOYEE.SSN=WORKS_ON.ESSN
join PROJECT on WORKS_ON.PNO=PROJECT.PNUMBER
where WORKS_ON.HOURS>10 and PROJECT.PNAME='productx' and EMPLOYEE.DNO=5

Q13. For each project, list the project name and the total hours per week (by all employees) spent on that project.
对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。

select 项目=PROJECT.PNAME,总时间=sum(WORKS_ON.HOURS) from PROJECT
join WORKS_ON on PROJECT.PNUMBER=WORKS_ON.PNO
group by PROJECT.PNAME 

Q14. For each department, retrieve the department name and the average salary of all employees working in that department.
对于每个部门，列出部门名称以及此部门员工的平均工资。

select 部门=DEPARTMENT.DNAME,平均工资=AVG(EMPLOYEE.SALARY) from DEPARTMENT
join EMPLOYEE on DEPARTMENT.DNUMBER =EMPLOYEE.DNO
group by DEPARTMENT.DNAME

Q15. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.
对于每个员工，查询其姓名以及他/她的直接上司的姓名。

select a.FNAME,a.LNAME,b.FNAME,b.LNAME from EMPLOYEE a,EMPLOYEE b
where a.SUPERSSN=b.SSN

Q16. Select all combinations of EMPLOYEE SSN and DEPARTMENT DNAME in the Company database.
查询Company数据库中所有员工SSN与DNAME(部门名称)的组合。

select SSN 员工编号,DEPARTMENT.DNAME 部门名称 from EMPLOYEE
join DEPARTMENT on EMPLOYEE.DNO=DEPARTMENT.DNUMBER

Q17. 查询有两个或以上“工资大于等于30000员工”的部门名称。

select DEPARTMENT.DNAME from DEPARTMENT
where DNUMBER in(select Dno from EMPLOYEE
where SALARY>=30000 
group by DNO 
having COUNT(*)>=2)

Q18. Retrieve the names of all employees who do not work on any project controlled by department number 5.
查询没有参与任何部门5控制项目的员工姓名。

select FNAME,LNAME from EMPLOYEE
where SSN not in(/*查询没参与以下项目的员工编号*/
select ESSN from WORKS_ON
where PNO in(/*查询部门控制项目编号*/
select PNUMBER from PROJECT

Q19. Retrieve the names of employees whose salary is greater than the salary of all the employees in department 5.
查询工资超过部门5所有员工工资的员工姓名。

select FNAME,LNAME from EMPLOYEE
where SSN in (/*人数非唯一，用in*/
select ssn from EMPLOYEE
where SALARY>all(/*薪资超过所有符合条件的人*/
select SALARY from EMPLOYEE
where DNO='5'))

Q20. Retrieve the names of all employees who work on every project.
查询参与了所有项目的员工姓名。

select FNAME,LNAME from EMPLOYEE
where SSN in (/*可能不止一个人*/
select ESSN from WORKS_ON
group by ESSN
having COUNT(*)=(/*参加项目个数是否等于总项目个数*/
select COUNT(*) from PROJECT)
)

Q21. Find the names of employees who work on all the projects controlled by department number 4.
查询至少参与了所有部门4控制项目的员工姓名。

select FNAME,LNAME from EMPLOYEE
where SSN in (
select ESSN from WORKS_ON
where PNO in (
select PNUMBER from PROJECT
where DNUM='4')
group by ESSN
having COUNT(pno)=(
select COUNT(*) from PROJECT
where DNUM='4')
)

Q22. Retrieve the names of employees who work on all the projects that ‘John Smith’ works on.
查询至少参与了所有John Smith参与项目的员工姓名。

select FNAME,LNAME from EMPLOYEE
where SSN in (
select ESSN from WORKS_ON
where PNO=any(
select pnumber from PROJECT
where PNUMBER in (
select PNO from WORKS_ON
where ESSN in(
select SSN from EMPLOYEE
where FNAME='JOHN' and LNAME='SMITH'))))

Q23. List the names of managers who have at least one dependent.
查询至少有一个家属的部门经理姓名。

select FNAME,LNAME from EMPLOYEE
where SSN in(
select ESSN from DEPENDENT
group by ESSN
having COUNT(*)>=1) and 
SSN in (
select MGRSSN from DEPARTMENT
)

Q24. 查询有两个或以上隶属员工的部门名称及其“工资大于等于30000员工”总数。

select DNAME,(
select COUNT(*) from EMPLOYEE
where SALARY>=30000 and 
EMPLOYEE.DNO=DEPARTMENT.DNUMBER
group by DNO) 总数 from DEPARTMENT
where DNUMBER in(
select DNO from EMPLOYEE
group by DNO
having COUNT(*)>=2)

Q25. List the names of all employees with two or more dependents.
查询有两个或以上家属的员工姓名。

select FNAME,LNAME from EMPLOYEE
where SSN in (
select ESSN from DEPENDENT
group by ESSN
having COUNT(*)>=2)

Q26. Make a list of project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.
查询符合以下任意条件的项目编号：(1)参与此项目员工的LNAME是Smith；(2)控制此项目的部门经理的LNAME是Smith。

select PNO from WORKS_ON
join EMPLOYEE on EMPLOYEE.SSN=WORKS_ON.ESSN
where EMPLOYEE.LNAME='smith' 
UNION
select PNUMBER from PROJECT
join DEPARTMENT on PROJECT.DNUM=DEPARTMENT.DNUMBER
JOIN EMPLOYEE ON EMPLOYEE.SSN=DEPARTMENT.MGRSSN
where LNAME='Smith'

实验三：《SQL数据定义语言》

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

create database JOBS

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

/*懒得废话，直接建完*/
use JOBS
create table course(
courseeno char not null,
cname char(30) not null,
cdate char(10) not null,
)
create table department(
depno char not null,
dname char(20) not null,
location char(10) not null,
head char not null,
)
create table empcourse(
empno char not null,
courseno char not null,
)
create table employee(
empno char not null,
surname char(10) not null,
forenames char(20) not null,
dob char(10) not null,
address char(50) not null,
telno char(11) not null,
depno char not null,
)
create table jobhistory(
empno char not null,
position char(30) not null,
startdate char(10) not null,
enddata char(10),
salary int not null,
 )
 create table cat(
 tablename char(10) not null,
 tabletype char(5) not null,
 )

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

use JOBS
/*主键*/
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_empcourse
primary key(empno,courseno)
alter table employee
add constraint pk_employee
primary key(empno)
alter table jobhistory
add constraint pk_jobhistory
primary key(empno,position)
/*外键*/
alter table employee
add constraint employee_k
foreign key(depno) references department(depno)
alter table empcourse
add constraint empcourse_k
foreign key(empno) references employee(empno)
alter table jobhistory
add constraint jobhistory_k
foreign key(empno) references employee(empno)
alter table empcourse
add constraint course_k
foreign key(courseno) references course(courseno)
/*语法有点啰嗦，运行无误*/

4．备份JOBS数据库

USE JOBS
execute sp_helpconstraint course
execute sp_helpconstraint department
execute sp_helpconstraint empcourse
execute sp_helpconstraint employee
execute sp_helpconstraint jobhistory
backup database jobs to disk='F:\jobs.bak' with init

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

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

create view secondview(EMPNO,SUNNAME,FORNAMES,DOB,ADDRESS,TELNO,DEPNO)
as
select * from employee
where empno<5

(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 EMPLOYEE.EMPNO,COUNT(*) from empcourse
JOIN employee ON empcourse.empno=employee.empno
GROUP BY employee.empno

(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 empno,COUNT(*) FROM jobhistory
GROUP BY EMPNO

(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 employee.empno,thirdview.coursenum,fourthview.jobnum 
FROM employee LEFT 
JOIN thirdview on thirdview.empno=employee.empno
JOIN fourthview on fourthview.empno=employee.empno 

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

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

/*绝不啰嗦，一次塞满*/
USE JOBS
INSERT INTO course(courseno,cname,cdate) 
VALUES
('1','Basic Accounting','11-JAN-89'),
('2','Further Accounting','25-JAN-89'),
('3','Issues In Administration','27-SEP-88'),
('4','More Administration','16-OCT-88')
INSERT INTO department(depno,dname,location,head)
VALUES
('1','accounts','floor3','1'),
('2','administration','floor2','1'),
('3','softwave design','floor1','2'),
('4','communications','floor4','3'),
('5','engineering','floor5','5')
INSERT INTO employee(empno,surname,forenames,dob,address,telno,depno)
VALUES
('1','Jones','Elizabeth Barbara','05-JAN-44','26 Agnews Terrace,Shamrock Bay','2123372288','1'),
('2','Smith','Robert','07-FEB-47','18 Marsh Street,Tollcross,Edinburgh','0317328972','1'),
('3','While','Allan','05-MAY-61','6 Remote Place,North Berwick','1215556622','1'),
('4','Reid','Gordon','10-AUG-63','9 Noble Road,Penicuik','6294246713','1'),
('5','MacCallan','Claire','18-SEP-58','25 Crisis Avenue,Leith,Edinburgh','0313374166','1'),
('6','Murphy','Brian Charles','30-Jun-54','9 Roberts Street,Biggar','3312294147','1')
INSERT INTO empcourse(empno,courseno)
VALUES
('1','1'),('1','2'),('2','1'),('2','2')
INSERT INTO jobhistory(empno,position,startdate,enddate,salary)
VALUES
('1','Accounts Manager','12-JAN-76',null,30000),
('1','Assistant Accounts Manager','11-JAN-72','12-JAN-76',22000),
('1','Accountant','10-MAR-68','11-FEB-72',15000),
('1','Junior Accountant','09-APR-64','10-MAR-68',6000),
('2','Assistant Accounts Manager','08-MAY-76',null,25000),
('2','Accountant','07-JUN-71','08-MAY-76',16000),
('2','Junior Accountant','06-JUL-67','07-JUN-71',8000),
('3','Accountant','05-AUG-84',null,16000),
('3','Junior Accountant','04-SEP-81','05-AUG-84',8000),
('4','Accountant','05-OCT-89',null,16000),
('5','Accountant','02-NOV-80',null,16000),
('5','Junior Accountant','01-DEC-78','02-NOV-80',8000),
('6','Accountant','12-JAN-80',null,16000)

(2) 修改数据
将所有1960年以后出生员工的部门编号修改为3。
由于以上数据已经进行了插入，为防止删表重新建表等冗余操作，直接使用编辑表前200行，更改dob属性的值，最终更改后的表数据截图如下：

use JOBS
update employee
set depno=3
where dob>='1960-1-1'

(3) 删除数据
删除所有员工以前的工作历史。

SELECT * FROM jobhistory
DELETE FROM jobhistory
WHERE enddata IS NOT NULL
SELECT * FROM jobhistory

实验四：《T-SQL语言编程》

（一）存储过程（截图内容：存储过程、调用语句、执行结果）
1、创建并调用带参数存储过程，计算某个部门的平均工资

CREATE PROCEDURE XYZ1 @DNO CHAR(1)
AS
SELECT DNO '部门',AVG(SALARY) '平均工资' FROM EMPLOYEE
WHERE DNO=@DNO
GROUP BY DNO
go
EXEC XYZ1 5

2、创建并调用存储过程，计算指定员工（工号）的每周总工作小时数，在存储过程中使用一个输入参数（工号）和一个输出参数（总工作小时数）。

CREATE PROCEDURE XYZ2 @ESSN INT
AS
SELECT ESSN '工号', SUM(HOURS) '总工作小时数' FROM WORKS_ON
WHERE ESSN=@ESSN
GROUP BY ESSN
go
EXEC XYZ2 333445555

（二）触发器（截图内容：触发器创建语句、触发语句、执行结果）
1、在项目表上创建一个触发器，当插入一条信息时，显示插入的信息，当删除一条信息时，显示删除的信息，当修改项目信息时分别显示更新前和更新后的项目信息。

/*创建触发器*/
CREATE TRIGGER XYZ3 ON PROJECT
FOR INSERT,DELETE,UPDATE
AS 
SELECT * FROM inserted
SELECT * FROM deleted
go
/*插入数据*/
INSERT INTO PROJECT VALUES('PROJECTA','15','nanchong','4')
go
/*更新数据*/
UPDATE  PROJECT
SET DNUM='5'
WHERE PNAME='PROJECTA'
go
/*删除数据*/
DELETE FROM PROJECT 
WHERE PNAME='PROJECTA'

2、修改部门表结构，新增员工数量字段 Sums，初始值为现有部门的员工数，在此基础上创建触发器实现以下功能：员工表中每插入一条信息，相应部门的Sums值加1.

alter table department add sums int
go
update DEPARTMENT
set sums = (select COUNT(*) from EMPLOYEE 
where DEPARTMENT.DNUMBER=EMPLOYEE.DNO
group by EMPLOYEE.DNO)
go
create trigger XYZ4 on employee
for insert as/*只对增加数据有效，若数据减小，不会更改sums值*/
update DEPARTMENT
set sums+=1
where DEPARTMENT.DNUMBER=(select dno from inserted)

select * from DEPARTMENT
go
insert into EMPLOYEE values
('Yu','X','Chen','123456781','2023-4-21','home','M',90000,'333445555','5')
go
select * from DEPARTMENT

3、设计一个更新触发器，员工工资只能升不能降

create trigger XYZ5 on employee
for update as
declare @newsalary int,@oldsalary int
select @newsalary=inserted.salary,@oldsalary=deleted.salary
from inserted,deleted
if @newsalary<@oldsalary
begin
print '不能降工资！'
rollback
end
go
update EMPLOYEE
set SALARY=89999
where EMPLOYEE.LNAME='Chen'

4、为员工表创建一个实现级联删除的触发器，当删除员工表中的某个员工信息时，先检测该员工是否存在，如果不存在，显示“此员工不存在！”，若该员工存在，则先删除该员工在工作记录表和家属表中的信息，最后删除该员工在员工表中的信息。（Instead of类型触发器）

USE Company_Chenxiaoyu
GO
CREATE TRIGGER XYZ6 ON EMPLOYEE
INSTEAD OF DELETE
AS
DECLARE @Ssn NCHAR(10)
SELECT @Ssn = Ssn FROM DELETED
IF EXISTS(SELECT *FROM EMPLOYEE WHERE Ssn =@Ssn)
begin
DELETE FROM WORKS_ON
WHERE Essn=@Ssn;
DELETE FROM DEPENDENT
WHERE Essn=@Ssn;
DELETE FROM EMPLOYEE
WHERE Ssn=@Ssn;
end
else
begin
print '此员工不存在！'
end
go
/*测试1*/
delete from EMPLOYEE where SSN='123456780'
go
/*测试2*/
delete from EMPLOYEE where SSN='666884444'
go
select * from EMPLOYEE
select * from WORKS_ON
select * from DEPENDENT

end

练习数据库
内含一个data.bak和一个jobs.bak，其中data.bak就是Company数据库，jobs为JOBS数据库。