6
\$\begingroup\$

This is sourced from the Stanford Coursera self study DB class SQL quizzes.

Students at your hometown high school have decided to organize their social network using databases. So far, they have collected information about sixteen students in four grades, 9-12. Here's the schema:

Highschooler (ID, name, grade) 

English: There is a high school student with unique ID and a given first name in a certain grade.

Friend (ID1, ID2) 

English: The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123).

Likes (ID1, ID2) 

English: The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also present.

DB is here

Prompt: Find the number of students who are either friends with Cassandra or are friends of friends of Cassandra. Do not count Cassandra, even though technically she is a friend of a friend.

My answer (which works) is below, but I am wondering if there is a more succinct way of accomplishing the same results. Any feedback would be appreciated.

select count(*)-1 from 
(
select id2
from friend f, highschooler a
where a.name='Cassandra'
and a.id=f.id1
union
select id2
from friend f, highschooler a
where a.id=f.id1
and f.id1 in ( select id2
from friend f, highschooler a
where a.name='Cassandra'
and a.id=f.id1 ) )
\$\endgroup\$
3
\$\begingroup\$

Style

  • Indentation would make your query more readable.
  • Table names in MySQL may be case sensitive, depending on the underlying filesystem. Therefore, it is safest to write the query using identifiers with the same case as stated in the problem.
  • SQL keywords are conventionally written in ALL CAPS, though some programmers object to that convention.
  • Single-letter names for table aliases are cryptic. While I can somewhat understand friend f, I have a harder time accepting highschooler a.

Formulation

  • UNION, short for UNION DISTINCT, automatically deduplicates the result set. If possible, prefer UNION ALL for efficiency. However, in this case, you do need deduplication for the correct answer.
  • The two halves of the union seem repetitive.
  • Subtracting one from the final count is weird and risky. I suggesting filtering out Cassandra herself from the result set.

Here's how I would write it:

SELECT count(Friend.ID1)
    FROM Highschooler AS Cassandra, Friend
    WHERE
        Cassandra.name = 'Cassandra'
        AND (
            Friend.ID2 = Cassandra.ID
            OR Friend.ID2 IN (
                SELECT Friend.ID1
                    FROM Friend
                    WHERE Friend.ID2 = Cassandra.ID
            )
        )
        AND Friend.ID1 <> Cassandra.ID;
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Your count should probably be COUNT. Also, I think aliasing Highschooler to Cassandra is a little odd. It just seems more natural to say WHERE Highschooler.Name = 'Cassandra' than the alternative you suggested. The alias thing is a pretty personal thing though. \$\endgroup\$ – PenutReaper Sep 15 '14 at 15:19

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.