求SQL语句 创建存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作

Question

创建存储过程,要求当一个员工的工作年份大于6年时将其转到经理办公室工作CREATE DATABASE YGGL
ON
(  NAME='YGGL_DATA',
      FILENAME='d:\YGGL.MDF',
      SIZE=5MB,
      MAXSIZE=50MB,
      FILEGROWTH=5%)
LOG ON
      (NAME='YGGL_Log',
       FiLENAME='d:\YGGL_Log.ldf',
       SIZE=5MB,
       MAXSIZE=10MB,
       FILEGROWTH=1MB)
      GOUSE YGGL
CREATE TABLE Employees
           (   EmployeeID char(6) primary key,
               Name char(10) NOT NULL,
               education char(4) not null,
               workyear tinyint null,
               Address varchar(50) NOT NULL,
               Zip Char(6) NULL,
               PhoneNumber char(12) NULL,  
               EmailAddress varchar(20) NULL,   
               DepartmentID char(3) NOT NULL,           
               Birthday datetime NOT NULL,
               Sex bit NOT NULL,
           )
           
create table departments
   ( departmentID char(3) primary key,
    departmentname varchar(30),
    Note text)
go
create table salary
    (
   employeeID char(6) primary key,
   inCome float,
   outCome float 
)
go
insert into employees values('000001','王林','大专',8,'中山路32-1-508','210003','3355668',null,'2','1956-1-23',1)
insert into employees values('010008','伍容华','本科',3,'北京东路100-2','210001','3321321',null,'1','1966-3-28',1)
insert into employees values('020010','王向荣','硕士',2,'四牌路10-10-108','210006','3792361',null,'1','1972-12-9',1)
insert into employees values('020018','李丽','大专',6,'中山东路102-2','210002','3416601','[email protected]','1','1950-7-30',0)
insert into employees values('102201','刘明','本科',3,'虎距路100-2','210013','3606608',null,'5','1962-10-18',1)
insert into employees values('102208','朱俊','硕士',2,'牌楼巷5-3-1806','210013','3606608','[email protected]','5','1962-10-18',1)
insert into employees values('108991','钟敏','硕士',4,'中山路108-3-105','210004','4808817','[email protected]','5','1955-09-28',0)
insert into employees values('111006','张石兵','本科',1,'解放路34-9-1-203','210010','4563418','[email protected]','5','1964-10-01',1)
insert into employees values('210678','林涛','大专',2,'中山北路247-2-303','210008','3467337',null,'3','1967-04-02',1)
insert into employees values('302566','李玉珉','本科',3,'热和路209-3','210018','8765991','[email protected]','4','1958-09-20',1)
insert into employees values('308759','叶凡','本科',2,'北京西路3-7-502','210001','3608901',null,'4','1968-11-18',1)
insert into employees values('504209','陈林琳','大专',5,'汉中路120-4-102','210002','4468158',null,'4','1959-09-03',0)insert into departments values('1','财务部',null)
insert into departments values('2','人力资源部',null)
insert into departments values('3','经理办公室',null)
insert into departments values('4','研发部',null)
insert into departments values('5','市场部',null)insert into salary values('000001',2100.8,123.09)
insert into salary values('010008',1582.62,88.03)
insert into salary values('102201',2569.88,185.65)
insert into salary values('111006',1987.01,79.58)
insert into salary values('504209',2066.15,108.0)
insert into salary values('302566',2980.7,210.2)
insert into salary values('108991',3259.98,281.52)
insert into salary values('020010',2860.00,198.0)
insert into salary values('020018',2347.68,180.0)
insert into salary values('308759',2351.98,199.08)
insert into salary values('210678',2240.00,121.0)
insert into salary values('102208',1980.00,100.0)

Answer 1

create proc pr_name
as
begin
   update Employees set DepartmentID =3 where workyear>6
end

Answer 2

update Employees set DepartmentID=(select DepartmentID from departments where departmentname='经理办公室') where workyear>6
select * from Employees
/*
EmployeeID Name       education workyear Address                                            Zip    PhoneNumber  EmailAddress         DepartmentID Birthday                Sex
---------- ---------- --------- -------- -------------------------------------------------- ------ ------------ -------------------- ------------ ----------------------- -----
000001     王林         大专        8        中山路32-1-508                                        210003 3355668      NULL                 3            1956-01-23 00:00:00.000 1
010008     伍容华        本科        3        北京东路100-2                                          210001 3321321      NULL                 1            1966-03-28 00:00:00.000 1
020010     王向荣        硕士        2        四牌路10-10-108                                       210006 3792361      NULL                 1            1972-12-09 00:00:00.000 1
020018     李丽         大专        6        中山东路102-2                                          210002 3416601      [email protected]        1            1950-07-30 00:00:00.000 0
102201     刘明         本科        3        虎距路100-2                                           210013 3606608      NULL                 5            1962-10-18 00:00:00.000 1
102208     朱俊         硕士        2        牌楼巷5-3-1806                                        210013 3606608      [email protected]      5            1962-10-18 00:00:00.000 1
108991     钟敏         硕士        4        中山路108-3-105                                       210004 4808817      [email protected]    5            1955-09-28 00:00:00.000 0
111006     张石兵        本科        1        解放路34-9-1-203                                      210010 4563418      [email protected]      5            1964-10-01 00:00:00.000 1
210678     林涛         大专        2        中山北路247-2-303                                      210008 3467337      NULL                 3            1967-04-02 00:00:00.000 1
302566     李玉珉        本科        3        热和路209-3                                           210018 8765991      [email protected] 4            1958-09-20 00:00:00.000 1
308759     叶凡         本科        2        北京西路3-7-502                                        210001 3608901      NULL                 4            1968-11-18 00:00:00.000 1
504209     陈林琳        大专        5        汉中路120-4-102                                       210002 4468158      NULL                 4            1959-09-03 00:00:00.000 0(12 行受影响)--满足条件的记录仅ID为 000001 的一条.
*/

Answer 3

create proc C
as
begin
update Employees set DepartmentID=
(select DepartmentID from departments where departmentname='经理办公室') 
where workyear>6
endselect EmployeeID,workyear,DepartmentID from Employees这是我写的，不过是错的，我不明白哪里错啦