实验内容
(针对实验一的数据库进行如下操作。注意查询结果至少有一条以上,结果要截图)
实验要求
(1)查询员工的姓名、住址和收入水平,3000以下显示低收入、3000—7000显示中等收入、7000以上显示高收入;(提示:SELECT 语句中使用CASE语句,如
(2)查询财务部年龄不低于研发部雇员年龄的雇员姓名;
(3)查询财务部收入在5000元以上的雇员姓名及其薪水情况;
(4)查询财务部雇员的最高和最低收入;
(5)创建视图Employees_view,包含员工号码、姓名、实际收入三项,创建视图DS_VIEW,包含Departments的全部列;
(6)从视图Employees_view中查询姓名为‘王琳’(也可是其他人)的员工的实际收入;
(7)可否修改视图Employees_view,为什么?
(8)创建北京地区的员工视图BJ_view,包含员工号码、姓名、教育、出生日期和部门号,并使用With CHECK OPTION子句;
(9)尝试通过BJ_view插入一条员工信息、更新一条北京地区的员工的姓名和不是北京员工的姓名,观察执行结果,并对结果进行解释。
代码
create table Departments(
DepartmentID char(3) not null primary key,
DepartmentName char(20) not null,
Note varchar(400)
);
create table Employees(
EmployeeID char(6) not null primary key ,
Name char(10) not null ,
Education char(4) not null ,
Birthday date not null ,
Sex bit not null default (1),
Workyer int ,
Address varchar(40),
PhoneNumber char(12),
DepartmentID char(3) not null ,
foreign key (DepartmentID)references Departments(DepartmentID)
);
create table Salary(
EmployeeID char(6) not null primary key,
Income float not null,
Outcome float not null
foreign key (EmployeeID)references Employees(EmployeeID)
);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('01','研发部',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('02','市场部',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('03','销售部',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('04','咨询部',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('05','生产部',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('06','财务部',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('07','人力资源部',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('08','经理办公室',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('09','秘书办公室',NULL);
insert into Departments(DepartmentID,DepartmentName,Note)
Values('10','技术部',Null)
insert into Departments(DepartmentID,DepartmentName,Note)
Values('11','安全部',Null)
delete
from Departments
where DepartmentName='安全部'
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365480','芋圆','本科','20010101','1','20200515','北京市海淀区','18025693164','02')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365480',6500,3800)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365481','麻薯','硕士','19870205','0','20020612','上海市普陀区','18647895462','03')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365481',7500,7000)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365482','红豆','博士','20020203','1','20200401',' 北京市朝阳区建国门南大街6号','18025693369','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365482',8962,6500)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365483','芋头','大专','20010215','1','20200603','北京市朝阳区平乐园100号','19825693164','06')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365483',3500,2000)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365484','茶冻','硕士','20010622','1','20180912','定福庄东街1号','14565693164','01')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365484',7500,3600)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365485','奶盖','本科','20010711','1','20190302','北京市朝阳区惠新东街10号','17525693164','10')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365485',4500,3600)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365486','布丁','本科','19830506','1','19920304','西藏中路268号上海来福士广场01-01D','18321453164','08')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365486',3246,3800)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365487','冰激凌','大专','19920331','1','20120328','西藏中路268号上海来福士广场01-01D','18167693164','09')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365487',4500,4000)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365488','爆珠','本科','20010911','1','20200515','淞沪路111号万达广场C座1312号','13025693164','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365488',5000,7000)
insert
into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165489','珍珠','本科','20010131','1','20200303','徐家汇路618号日月光中心广场泰康区1层','15921693164','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165489',6500,2500)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165490','波霸','硕士','20031202','1','20200404','上海漕宝路3366号七宝万科广场1层','13695543164','06')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165490',8700,5600)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365491','双皮奶','本科','20010314','1','20200718','上海漕宝路3366号七宝万科广场1层','13445693164','07')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365491',5400,2500)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165492','绵绵冰','博士','20020214','1','2021912','南京西路1601号芮欧百货5楼','17895664164','01')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165492',9500,4500)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('123493','巧克力','本科','20030302','1','20220214','南京西路1601号芮欧百货5楼','13524593164','02')
insert into Salary(EmployeeID,Income,Outcome)
Values ('123493',5000,4000)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365494','苹果','本科','19980302','1','20200614',' 肇嘉浜路1111号美罗城B区1层','14525653164','03')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365494',6500,5600)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165495','椰子','本科','20030201','1','20201009','长宁路1018号龙之梦购物中心','13425693564','10')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165495',15000,5000)
insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165496','麦芬','本科','20010112','1','20201203','欣宁街15号北京荟聚F1','18854193135','09')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165496',8650,5460)
update Employees
set Address = '上海市普陀区121号'
where Name='麻薯';
update Employees
set Birthday = '20031202'
where Name='波霸'
-----------------------------------------------------------------查询
delete from Salary
where EmployeeID ='165496';
delete from Employees
where EmployeeID='165496';
select *
from Employees
select PhoneNumber,Address
from Employees
select EmployeeID
from Salary
where Income>6000;
select EmployeeID,Income-OutCome 'RealCome'
from Salary
----------------查询财务部正确格式
select COUNT(*)
from Employees
where DepartmentID in
(
select DepartmentID
from Departments
where Departments.DepartmentName='财务部'
)
select Name '姓名',Birthday '出生日期'
from Employees
where Sex=0;
alter table Salary add Name char(6);
update Salary
set Name = '椰子'
where EmployeeID='165495';
update Salary
set Name = '巧克力'
where EmployeeID='123493';
-----------------------------------------------------------删库
alter table Salary drop column Name;
--drop table Salary;
--drop table Employees;
--drop table Departments;
--drop database YGGL;
----------------------------------------------------------------------------------------------------------作业2
--查询员工的姓名、住址和收入水平,3000以下显示低收入、3000—7000显示中等收入、7000以上显示高收入;
select Employees.Name as '姓名' , Address as '住址' , Income as '收入水平' ,Income = case
when Income < 3000 then '低收入'
when Income >=3000 and Income <=7000 then '中等收入'
when Income >7000 then '高收入'
end
from Employees,Salary
where Employees.EmployeeID=Salary.EmployeeID
------------------------------------学习两种日期查询方式
select GETDATE()
select year(GETDATE()),MONTH(GETDATE())as month,day(GETDATE())as'日'
--(2)查询财务部年龄不低于研发部雇员年龄的雇员姓名;
select Name as '财务部',year(GETDATE())-year(Birthday) as '年龄'
from Employees,Departments
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='财务部'
select Name as '研发部',year(GETDATE())-year(Birthday) as '年龄'
from Employees,Departments
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='研发部'
select Name as '财务部年龄不低于研发部雇员年龄的雇员姓名',year(GETDATE())-year(Birthday) as '年龄'
from Employees,Departments
where Employees.DepartmentID=Departments.DepartmentID and DepartmentName='财务部' and Birthday
< all (select Birthday
from Employees,Departments
where Employees.DepartmentID=Departments.DepartmentID and DepartmentName='研发部')
--(3)查询财务部收入在5000元以上的雇员姓名及其薪水情况
select Name as '财务部',Income as '薪水'
from Employees,Departments,Salary
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='财务部'and Employees.EmployeeID=Salary.EmployeeID
select Name as '姓名',Income as '薪水'
from Employees e,Salary s,Departments d
where e.EmployeeID=s.EmployeeID and Income>5000 and e.DepartmentID=d.DepartmentID and DepartmentName='财务部'
--(4)查询财务部雇员的最高和最低收入;
select Name as '财务部',Income as '薪水'
from Employees,Departments,Salary
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='财务部'and Employees.EmployeeID=Salary.EmployeeID
select max (Income) as'财务部最高薪水',min(Income) as '财务部最低薪水'
from Employees e,Departments d ,Salary s
where e.DepartmentID=d.DepartmentID and DepartmentName='财务部' and e.EmployeeID=s.EmployeeID
--(5)创建视图Employees_view,包含员工号码、姓名、实际收入三项,创建视图DS_VIEW,包含Departments的全部列;
go
create view Employees_view(EmployeeID,EmployeeName,RealIncome)
as
select Employees.EmployeeID,Employees.Name,Salary.Income-Salary.Outcome
from Employees,Salary
where Employees.EmployeeID=Salary.EmployeeID
go
create view DS_VIEW
as
select*
from Departments
--(6)从视图Employees_view中查询姓名为‘王琳’(也可是其他人)的员工的实际收入;
select EmployeeName as '名字',RealIncome as '真实收入'
from Employees_view
where EmployeeName='巧克力'
--(7)可否修改视图Employees_view,为什么?
update Employees_view
set EmployeeName='牛奶巧克力'
where EmployeeName='巧克力'
/*--转换后的更新
update Employees
set Name='牛奶巧克力'
where Name='巧克力'
*/
--失败的更新
update Employees_view
set EmployeeID=123
where EmployeeName='牛奶巧克力'
--(8)创建北京地区的员工视图BJ_view,包含员工号码、姓名、教育、出生日期和部门号,并使用With CHECK OPTION子句;
go
create view BJ_view(员工号码,姓名,教育,出生日期,部门号)
as select Employees.EmployeeID,Name,Education,Birthday,DepartmentID
from Employees
where Employees.Address like '北京市%'
with check option
--(9)尝试通过BJ_view插入一条员工信息
insert into BJ_view
values(1234,'牛肉汉堡','硕士','2002-02-14','03')
--更新一条北京地区的员工的姓名和不是北京员工的姓名
update BJ_view
set 姓名 = '鸡蛋汉堡'
where 姓名 ='芋圆'
update BJ_view
set 姓名 = '巧克力'
where 姓名 = '牛奶巧克力'
select Name,Education,Birthday,Address
from Employees