--query1
select "name",employee_code from instructor where id_desig in
(
  select id_desig from designation where desig_name like 'Asst. Professor'
) and id_dept in
(
  select id_dept from department where dept_code like 'CSE'
);




--query2

select * from teaches;
select * from course;
select * from instructor;
select * from designation;

select id_instructor , count(credit) AS total_course_load from course  join teaches using(course_id) where id_instructor in
(
  select id_instructor from instructor
)
and id_desig in
(
  select id_desig from designation where desig_name like 'Asst. Professor'
)and id_dept in
(
  select id_dept from department where dept_code like 'CSE'
) group by id_instructor;


--query3

select * from designation;

update designation set
salary = case
when desig_name = 'Lecturer' then 0.5*(select salary from designation where desig_name = 'professor')
when desig_name = 'Asst. Professor' then 0.75*(select salary from designation where desig_name = 'professor')
else salary
end;