Where is this dataset from?
Played a bit with the set. Wonder if it's real or just generated.
Interestingly enough, the numbers are expectable.
While the average and the minimum salaries for M and F are pretty much the same (13 and 100$ per year difference), the maximum salary has 9400$ with 22 entries for male employees receiving the salary higher than any female employee in the company.
I know, it's historical data and for fair comparison, needs to be refined (avg, min or max for each person) - with a subquery? Or can it be solved within one?
One of the queries I used is:
SELECT DISTINCT
e.gender, COUNT(s.salary) AS avg_salary
FROM
employees e
JOIN
salaries s ON s.emp_no = e.emp_no
WHERE
s.salary > 148820
GROUP BY e.gender;
Hi Tetiana!
Thanks for reaching out.
Regarding the first question - the employees data has been fabricated so it is realistic but may provide results that do not seem quite believable nowadays (also because the dataset regards information data from the previous century and only the first few years of this millenium).
Regarding the second question - have you completed the section on using MySQL subqueries yet? It is quite an important one and I do believe that once you complete it (together with the exercises provided), you'll be able to try out the question you have on the database yourself. Also, please consider completing our Advanced SQL course as well, where window functions and common table expressions are taught. These are tools that can also compute average for specific parts of the dataset (windows or other subsets).
Of course, once you've done this, please don't hesitate to reply to this thread or post another question should you have further questions. Thank you.
Hope this helps.
Best,
Martin