So here is what I have tried:
FROM STUDENT A, STUDENT B
Here is the STUDENT table columns:
STUDENT_ID primary key
It seems to work. But according to the practice exam, it is worth 15 points so I am not sure if it is totally right. What if there more that two students have the same fname and lname? How would I write it or would this work for that too?
To avoid the Cartesian product (which is what you get with those multiple
FROM clauses) and the duplicate rows that would create, I'd use an
SELECT a.STUDENT_ID, a.FNAME, a.LNAME FROM STUDENT a WHERE EXISTS ( SELECT 1 FROM STUDENT b WHERE a.FNAME = b.FNAME AND a.LNAME = b.LNAME AND a.STUDENT_ID <> b.STUDENT_ID );
You would also benefit greatly by having an index on both
LNAME but I doubt that's going to be relevant to your practice exam.
What you have will technically work. However, the number of rows returned per matched student will equal
n - 1, where
n is the number of students who have the same first and last name as the matched student.
To address this, use a
DISTINCT clause in your query.
SELECT DISTINCT A.STUDENT_ID FROM STUDENT A, STUDENT B WHERE A.FNAME=B.FNAME AND A.LNAME=B.LNAME AND A.STUDENT_ID!=B.STUDENT_ID;