Can someone explain this code for me? It outputs the difference between each presenter (PNRFNAME) salary (Salary_yearly)
SELECT P2.PNRFNAME ,P1.PNRFNAME AS "pnrfname" , P2.SALARY_YEARLY - P1.SALARY_YEARLY AS "salary difference"
FROM PRESENTERS P2
JOIN PRESENTERS P1
ON P2.PNRFNAME < P1.PNRFNAME
INSERT INTO PRESENTERS(PRESENTERID, PNRLNAME, PNRFNAME, AGE,YEARS, SALARY_YEARLY)
It maps each Presenter to each other Presenter and tells you how much different their salaries are. It uses a self-join to map presenters with each other.
ON P2.PNRFNAME < P1.PNRFNAME join condition is perhaps unusual, but it works. That join ensures that you will not get symmetric comparisons such as Jason vs Tiff and Tiff vs Jason on separate rows. This is because given any two names, both of them are not equal to each other but only one of them is the first in alphanumeric order. If you used the clause
NOT P2.PNRFNAME = P1.PNRFNAME, you would get the Jason vs Tiff and Tiff vs Jason doubling I mentioned.