1、打开自己专属的ENTERPRISE数据库,用SQL更新语句完成以下操作:
(1)在 Departments中插入一个部门,部门编号为4,部门为销售部。
INSERT INTO Departments(DepartmentID,DepartmentName,Note)
values('4','销售部','销售部')
(2)自拟数据,对Employee表插入4个元祖,要求都是销售部的员工。
INSERT INTO Employee(Address,Birthday,DepartmentID,EmailAddress,EmployeeID,Name,PhoneNumber,Sex,Zip)
values('陕西','2004-1-14','4','zs@qq.com','4001','张三','13112526333','FALSE','475012')
INSERT INTO Employee(Address,Birthday,DepartmentID,EmailAddress,EmployeeID,Name,PhoneNumber,Sex,Zip)
values('陕西','2004-1-15','4','ls@qq.com','4002','李四','13112526334','FALSE','475013')
INSERT INTO Employee(Address,Birthday,DepartmentID,EmailAddress,EmployeeID,Name,PhoneNumber,Sex,Zip)
values('陕西','2004-1-16','4','wmz@qq.com','4003','王麻子','13112526335','FALSE','475014')
INSERT INTO Employee(Address,Birthday,DepartmentID,EmailAddress,EmployeeID,Name,PhoneNumber,Sex,Zip)
values('陕西','2004-1-17','4','xd@qq.com','4004','熊大','13112526336','FALSE','475015')
(3)将’1003’号员工的地址变为“陕西”。
update Employee
set Address='陕西'
where EmployeeID='1003';
(4)将研发部所有职员的Income涨500元。
update Salary
set Income=Income+500
where EmployeeID in
(
select salary.EmployeeID
from Salary,Employee
where Salary.EmployeeID=Employee.EmployeeID and DepartmentID='2'
);
(5)将‘2002’号员工的地址变为‘北京’,所属部门改为2。
update Employee
set Address='北京',DepartmentID='2'
where EmployeeID='2002';
(6)将Salary表中前3个元组的outcome属性值统一修改为1100。
UPDATE Salary
SET Outcome=1000
WHERE Salary.EmployeeID IN ('1001','1002','1003');
(7)将‘3001’号员工调入财务部。
UPDATE Employee
SET DepartmentId=1
WHERE EMployeeId='3001';
(8)在Departments表中新增一名为DepartmentTEL的属性列,并插入正确的数据信息。(DepartmentTEL为8位的部门电话)
ALTER TABLE departments ADD DepartmentTEL char(12);
(9)删除Departments表的‘Note’列。
ALTER TABLE departments DROP COLUMN Note;
(10)在Salary表中新增一名为Stime的属性列,Stime的值为插入时系统的时间数据,并将EmployeeID和Stime设置为主键。
ALTER TABLE salary ADD stime datetime;
ALTER TABLE salary ADD PRIMARY KEY (stime,employeeid);
(11)删除“王霞”的收入信息。
DELETE
FROM Salary
WHERE EmployeeID=3003;
错误点:
Error1:
错误原因:
创建聚集索引和非聚集索引的列是有要求的,以下类型无法创建索引:
binary、varbinary
ntext、text、image
varchar(max)、nvarchar(max)
uniqueidentifier
rowversion、timestamp
sql_variant
精度大于 18 位的 decimal 和 numeric
标量大于 2 的 datetimeoffset
CLR 类型(hierarchyid和空间类型)
Xml
设置为不为空:TER TABLE salary alter column stime datetime NOT NULL;
Error2:
删除的是一行而非一格