---
# System prepended metadata

title: Untitled

---

q1:

select 
    a.days as Days, b1.emp_id as Emp_id, b1.name as Name, b1.status as Status
from TableB1  b1 inner join TableA a on b1.emp_id = a.emp_id
order by b1.emp_id;

################################################################

q2:

update TableB2
set stutas = '離職員工'
where emp_id = '9001' and name = 'Cat';

select name, emp_id, status from TableB2;

################################################################

q3:

delete TableB2 where emp_id = '9101' and name = 'Jason';

################################################################

q4:

insert into TableB1(`name`, emp_id, `status`) values ('Cindy', '6002', '新進職員');
insert into TableA(`days`, emp_id) values (35, '6002');

################################################################

q5:

select 
    a.days as Days, b1.emp_id as Emp_id, b1.name as Name, b1.status as Status
from TableB1  b1 inner join TableA a on b1.emp_id = a.emp_id
union
select 
    a.days as Days, b2.emp_id as Emp_id, b2.name as Name, b2.status as Status
from TableB2  b2 inner join TableA a on b1.emp_id = a.emp_id
order by Days;

################################################################

q6:

create procedure dbo.DeleteUserByEmp_id
    @deleted_emp_id char(10)
as
begin
    if exists (select * from TableB1 where emp_id = @deleted_emp_id)
        delete TableB1 where emp_id = @deleted_emp_id
    else 
        delete TableB2 where emp_id = @deleted_emp_id
    
    delete TAbleA where emp_id = @deleted_emp_id

    commit;
end