mySQL Queries through multiple tables

Question

My first post here. I have an assignment to deliver in mysql and I have found a difficulty in 2 questions.

First and foremost the tables of a database are the following:

departments (dep_no, dep_name)
dept_emp (emp_no, dept_no, from_date, to_date)
dept_manager (dept_no, emp_no, from_date, to_date)
employees (emp_no, birthdate, first_name, last_name, gender, hire_date)
salaries (emp_no, salary, from_date to_date)
titles (emp_no, title, form_date, to_date)

and the 2 questions that I am having difficulty on are those 2 :

Which department pays the highest salary currently and who is the department manager?
What is the current average salary of Marketing Department?

I would be really thankful for some help.
Cheers!

sorry my bad. I don't even know the command to do this. I am stuck... — Πρέντζας Ιωάννης, Jul 31 '15 at 00:24
Maybe you should start small and try a few tiny queries. Or, as harsh as it sounds, that class is not for you. At least put some effort in. — Rohit Gupta, Jul 31 '15 at 00:32

score 0 · Answer 1 · answered Jul 31 '15 at 00:28

0

Try this for the first one:

select d.dep_name AS department
,      CONCAT(e.first_name, e.last_name) AS department_manager
from salaries S
     inner join dept_emp de on s.emp_no = de.emp_no
     inner join departments d on de.dept_no = d.dept_no
     inner join dept_manager dm on de.dept_no = d.dept_no
     inner join employees e on dm.emp_no = e.emp_no
where s.salary = (select MAX(salary) from salaries)

And the second one (if Marketing Department is on table dept_manager):

select AVG(salary)
from   salaries S
       inner join dept_manager dm on s.emp_no = dm.emp_no

answered Jul 31 '15 at 00:28

Roman Marusyk

23,328
24
73
116

Thank you very much Megatron!!! Although it runs correctly to say which department has the highest salary, I get 24 rows instead of one (as I see into the database there should be only one.) – Πρέντζας Ιωάννης Jul 31 '15 at 07:43
Yes. you have to add which department you need. Please add to the second query `inner join departments d on dm.dept_no = d.dept_no where d.dept_name = 'Marketing'` – Roman Marusyk Jul 31 '15 at 08:35
I mean on t he first block of code. The second was fine :) – Πρέντζας Ιωάννης Jul 31 '15 at 08:41
thank you very much for helping and bein extremely friendly – Πρέντζας Ιωάννης Jul 31 '15 at 08:47
Can I ask you a question? Can be there a few departments which pays the highest salary? Then you will get more than one row – Roman Marusyk Jul 31 '15 at 08:51
Well it can be, but I have 24 rows saying "Sales Department" and some random names for the department manager. for some reason many entries take the value of the max salary even if they don't have this value registered – Πρέντζας Ιωάννης Jul 31 '15 at 08:55
In query is mistake in line 6. Please fix it : `inner join dept_manager dm on dm.dept_no = d.dept_no`. And if U can then show me result. Thx – Roman Marusyk Jul 31 '15 at 09:08
it's completely correct. thank you very much. a last question. if I want to filter my results in order to say who is the current department manager, where should I put the subquery to check the to_date field? – Πρέντζας Ιωάννης Jul 31 '15 at 09:25
I think here: `inner join dept_manager dm on de.dept_no = d.dept_no and dm.to_date = yourdate` – Roman Marusyk Jul 31 '15 at 10:46

score 0 · Answer 2 · answered Jul 31 '15 at 00:28

Which department pays the highest salary currently and who is the department manager?

select dept_no from
dept_emp where emp_no in
(select emp_no from salaries 
where salary = (select max(salary) from salaries) )

What is the current average salary of Marketing Department?

select avg(s.salary) as marketing_avg_salary
from salaries s join dept_emp de
on s.emp_no = de.emp_no
where de.dept_no = (select dept_no from departments where dept_name = 'Marketing')

mySQL Queries through multiple tables

2 Answers2