MySQL joining tables and functions

Question

I'm trying to answer these questions but I'm not understanding the whole joining and functions part of MySQL. Can someone show me or explain these to me?

this is the link to the employee database we are using - https://github.com/datacharmer/test_db

1. I want to know how many employees with each title were born after 1965-01-01.
2. I want to know the average salary per title.
3. How much money was spent on salary for the marketing department between the years 1990 and 1992?

This is what I have so far for each one.

1.

select count(title) as "Number Of Employees", title from titles GROUP BY title LIMIT 20;

SELECT d.dept_name as "Department", avg(s.salary) as "Average Salary" from departments d
INNER JOIN dept_emp de on de.dept_no = d.dept_no
INNER JOIN salaries s on s.emp_no = de.emp_no
GROUP BY d.dept_name;

3. and this one seems like it's just those two put together so I completely don't understand it.

Answer 1

1. Join with the employee table so you can get the employee's date of birth.

SELECT t.title, COUNT(*) AS "Number of Employees"
FROM titles AS t
JOIN employees AS e ON e.emp_no = t.emp_no
WHERE e.birth_date > '1965-01-01'
GROUP BY t.title

2. You need to get the most recent salary for each employee and average that. And you have to join with the titles table so you can average by title.

SELECT t.title, AVG(salary)
FROM titles AS t
JOIN employees AS e ON e.emp_no = t.emp_no
JOIN (
    -- subquery to get latest salary for each employee
    -- See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?noredirect=1&lq=1
    SELECT s.emp_no, s.salary
    FROM salaries AS s
    JOIN (
        SELECT emp_no, MAX(from_date) AS max_date
        FROM salaries
        GROUP BY emp_no
    ) AS ms ON s.emp_no = ms.emp_no AND s.from_date = ms.from_date
) AS s ON e.emp_no = t.emp_no
GROUP BY t.title

3. I'm not even sure what the third question means. Does it mean the total salaries for all employees during those years? This seems incredibly complex for a beginner exercise, since you have to deal with different start/end dates for employees, and changing salaries during that period. I'm not even sure how to do that in a single query.