SELECT
    w.id,
    w.first_name,
    w.last_name,
    p.name AS preserve_name,
    c.country_code
FROM workers w
LEFT JOIN preserves p ON p.id = w.preserve_id
LEFT JOIN countries_preserves cp ON cp.preserve_id = p.id
LEFT JOIN countries c ON c.id = cp.country_id
WHERE w.salary > 5000 AND w.age < 50
ORDER BY c.country_code;

SELECT
    p.name,
    COUNT(w.id) AS armed_workers
FROM preserves p
LEFT JOIN workers w ON w.preserve_id = p.id
WHERE w.is_armed = 1
GROUP BY p.name
ORDER BY armed_workers DESC, p.name;

SELECT
    p.name,
    c.country_code,
    YEAR(p.established_on) AS founded_in
FROM
    preserves p
JOIN
    countries_preserves cp ON p.id = cp.preserve_id
JOIN
    countries c ON cp.country_id = c.id
WHERE
    MONTH(p.established_on) = 5
ORDER BY
    p.established_on ASC
LIMIT 5;

DELIMITER $$

CREATE FUNCTION udf_average_salary_by_position_name(position_name VARCHAR(40))
RETURNS DECIMAL(19,2)
DETERMINISTIC
BEGIN
    DECLARE avg_salary DECIMAL(19,2);

    SELECT AVG(salary)
    INTO avg_salary
    FROM workers w
    JOIN positions p ON w.position_id = p.id
    WHERE p.name = position_name;

    RETURN avg_salary;
END $$

DELIMITER ;

SELECT p.name, udf_average_salary_by_position_name('Forester') as position_average_salary FROM positions p
WHERE p.name = 'Forester';

DELIMITER $$

CREATE PROCEDURE udp_increase_salaries_by_country(country_name VARCHAR(40))
BEGIN
    DECLARE country_id INT;

    SELECT id INTO country_id FROM countries WHERE name = country_name;

    UPDATE workers w
    JOIN preserves p ON w.preserve_id = p.id
    JOIN countries_preserves cp ON p.id = cp.preserve_id
    SET w.salary = w.salary * 1.05
    WHERE cp.country_id = country_id;
END $$

DELIMITER ;

CALL increase_salaries_by_country("Germany");