0

So, I have recently started studying data bases in Uni, MySQL. I am practicing some queries with different stuff that the professor gave us.

One of the questions i have found is as follows:

Show the Name and Wage of the employee supervisor and the average wage of the employees that he is supervising. Arrange the data by name and the average wage in an ascending order. (I'm working on the newest version of the MySQL Workbench with the accompanying local host server)

There is only one table, the employee table. With a foreign key supervisor ID that references the employee IDs which are primary keys.

This is the solution to the query:

SELECT SUP.name, SUP.wage, AVG(EMP.wage) AS AVG_WAGE
FROM employee EMP, employee SUP
WHERE SUP.employee_ID = EMP.supervisor_ID
GROUP BY SUP.name
ORDER BY AVG(EMP.wage) ASC;

I'm wondering, what is the name of the thing that he used here where he like... split the employee table into 2 virtual tables for the sake of the query?

I didn't know this is possible at first so I was doing this:

SELECT name, wage, AVG(wage) AS AVG_WAGE
FROM employee
WHERE emp_ID IN (SELECT sup_ID FROM employee)
GROUP BY name, wage, sup_ID IN (SELECT emp_ID FROM employee)
ORDER BY AVG(wage);

This shows the supervisors and their wages but doesn't show the average wage of the employees that each supervisor supervises and just displays the wage of the supervisors again with a bunch of zeroes at the end.

Thank you!

I'm sorry if I'm not very clear in my wording since I'm not studying databases in English and English is not my first language. Thus it isn't easy to find info on google about a bit specific things like this where I don't know how to word questions.

10
  • 2
    Tell your prof that there's an explicit, better readable and understandable join for decades now... ... FROM employee EMP INNER JOIN employee SUP ON SUP.employee_ID = EMP.supervisor_ID GROUP BY SUP.name ... – sticky bit Dec 28 '20 at 2:28
  • 1
    Is your teacher... (ahem...) getting on in years? You should be using ANSI JOIN syntax as mentioned above by @stickybit. Furthermore, MySQL is by far and away the absolute worst of the major servers on which to be learning about SQL and databases - you should recommend to your teacher that your class use PostgreSQL or possibly SQL Express Edition or Oracle XE, all of which are more standards compliant than MySQL! – Vérace Dec 28 '20 at 7:49
  • @Vérace well it is dated course, this was an old practice sheet i found from like 2011 or something XD He probably would have done it differently now :D Thank you for your info :D – Cewu00 Dec 28 '20 at 10:10
  • @stickybit its an old practice paper, i dug it out of the website. :D so thats why its dated, probably (sheet from 2011) Than you for the info :D I wasn't very clear on the origins of the question XD – Cewu00 Dec 28 '20 at 10:12
  • ANSI JOIN syntax is around for approx. 30 years - it should have been used in 2011! p.s. (forgot earlier) - welcome to the forum! :-) – Vérace Dec 28 '20 at 10:34
1

I just read your examples closer and understand your question better now. Your second example that you were doing uses a subquery but the first example your professor was doing is called an implicit inner join. It's just a shorthand way to write FROM employee EMP INNER JOIN employee SUP ON SUP.employee_ID = EMP.supervisor_ID like @stickybit commented.

2
  • 1
    Thank you! I understand :) – Cewu00 Dec 28 '20 at 10:05
  • @Cewu00 Great, no problem! – J.D. Dec 28 '20 at 13:22
1

I'm wondering, what is the name of the thing that he used here where he like... split the employee table into 2 virtual tables for the sake of the query?

In this query, the author uses 2 copies of the source table. 2 absolutely independent copies. He gives unique aliases (temporary names) to each copy - this allows us to distinguish from which copy of the base table each column is taken.

So in the WHERE clause, he attaches the matched row taken from the copy EMP to the row from the copy SUP (or backwards - the binding is symmetric). The row attached from EMP does not affect the same row from SUP - it will be joined to some another row from EMP if a matched row exists.

You may imagine that for this query we make 2 temporary copies of the source table (with absolutely the same structures and data) and give them the names EMP and SUP, then we join them in the query.

1
  • Thank you for your input! :D – Cewu00 Dec 28 '20 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.