Good job! Of course, you can combine conditions in an IF statement with boolean operators: AND, OR, and NOT. You can also use all comparison operators you know from SQL: <, >, <=, >=, =, etc.
The number of personal days for most employees is 20. However, employees from IT departments (department IDs: 2, 3, and 4) hired before 2016 get 30 personal days:
CREATE FUNCTION get_personal_days(emp_id integer)
RETURNS integer AS $$
DECLARE
start_year integer;
dep_id integer;
BEGIN
start_year := (SELECT EXTRACT(year FROM start_date) FROM person WHERE person_id = emp_id);
dep_id := (SELECT department_id FROM person WHERE person_id = emp_id);
IF dep_id IN (2, 3, 4) AND start_year < 2016 THEN
RETURN 30;
ELSE
RETURN 20;
END IF;
END;
$$ LANGUAGE plpgsql;
Write a function named get_bonus(emp_id integer) which computes the bonus for an employee and returns it as a decimal.
For employees in IT departments (departments IDs: 2, 3, and 4), the bonus is:
For other departments, the bonus is:
