SELECT Concat(a.first_name, ' ', a.last_name) AS NAME,
       b.salary
FROM   employees a,
       salaries b
WHERE  a.emp_no = b.emp_no
       AND b.to_date = '9999-01-01'
       AND b.salary < (SELECT Avg(salary)
                       FROM   salaries
                       WHERE  to_date = '9999-01-01');

SELECT Min(avg_salary)
FROM   (SELECT Round(Avg(a.salary)) AS avg_salary
        FROM   salaries a,
               titles b
        WHERE  a.emp_no = b.emp_no
               AND a.to_date = '9999-01-01'
               AND b.to_date = '9999-01-01'
        GROUP  BY b.title) a;

SELECT a.first_name,
       b.salary
FROM   employees a,
       salaries b
WHERE  a.emp_no = b.emp_no
       AND b.to_date = '9999-01-01'
       AND ( a.emp_no, b.salary ) IN (SELECT emp_no,
                                             salary
                                      -- 'in(sub_query)' <-> ='any(sub_query)'
                                      FROM   salaries
                                      WHERE  to_date = '9999-01-01'
                                             AND salary >= 50000);