Problem 3: Matchmaker, Matchmaker (100 pts)

Did you take 61A with the hope of finding a new group of friends? Well you're in luck! With all this data in hand, it's easy for us to find your perfect match. If two students want the same pet and have the same taste in music, they are clearly meant to be friends! In order to provide some more information for the potential pair to converse about, let's include the favorite colors of the two individuals as well!

In order to match up students, you will have to do a join on the students table with itself. When you do a join, SQLite will match every single row with every single other row, so make sure you do not match anyone with themselves, or match any given pair twice!

Important Note: When pairing the first and second person, make sure that the first person responded first (i.e., the first person has an earlier time). This is to ensure your output matches our tests.

Write a SQL query to create a table that has 4 columns:

• The shared preferred pet of the pair
• The shared favorite song of the pair
• The favorite color of the first person
• The favorite color of the second person

CREATE TABLE matchmaker AS
  SELECT "REPLACE THIS LINE WITH YOUR SOLUTION";

Hint: When joining table names where column names are the same, use dot notation to distinguish which columns are from which table: [table_name].[column name]. This sometimes may get verbose, so it’s stylistically better to give tables an alias using the AS keyword. The syntax for this is as follows:

SELECT <[alias1].[column_name1], [alias2].[column_name2]...>
    FROM <[table_name1] AS [alias1],[table_name2] AS [alias2]...> ...