Thank you guys for your help. I have been fiddling around with what you guys have recommended but unfortunately I still have been unable to produce the correct results.
Here is the full database schema
And this is how I am trying to get it to output
Just took a quick look at it. I haven't done SQL in a while and can't quite remember all keywords and the syntax and features…
The essential information you want is all in Works_On. There's no natural join with employee, employee, works_on. To get the pairs you need to join works_on with itself.
Select A.ESSN, B.ESSN
From Works_On A, Works_On B
Where A.PNO = B.PNO AND A.ESSN < B.ESSN
That gives you the pairs of people as SSNs. If the same two people work together in multiple projects you can use Select Distinct. (if you want to return the PNO too, you can select it here as well)
Then all that's left to do is join it with the employee table to get the names. It's possible to do this with nested queries, temporary tables, or as one query in which you join employee, works_on, works_on, employee.
WITH Pairs AS (Select A.ESSN AS EA, B.ESSN AS EB
From Works_On A, Works_On B
Where A.PNO = B.PNO AND A.ESSN < B.ESSN)
Select E1.Fname, E1.Lname, E2.Fname, E2.Lname
From Employee As E1, Pairs, Employee E2
Where E1.SSN = Pairs.EA AND Pairs.EB = E2.SSN
That should do it (in theory; haven't tried it and might have some syntax errors); If WITH AS doesn't work with your database that feature might work differently there and the easiest (though less clean) solution is to just do everything in one query.