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.
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.