Problem 4: Sevens (100 pts)

Let's take a look at data from both of our tables, students and numbers, to find out if students that really like the number 7 also chose '7' for the obedience question. Specifically, we want to look at the students that fulfill the below conditions:

Conditions:

• reported that their favorite number (column number in students) was 7
• have "True" in column "7" in numbers, meaning they checked the number 7 during the survey

In order to examine rows from both the students and the numbers table, we will need to perform a join.

How would you specify the WHERE clause to make the SELECT statement only consider rows in the joined table whose values all correspond to the same student? If you find that your output is massive and overwhelming, then you are probably missing the necessary condition in your WHERE clause to ensure this.

Note: The columns in the numbers table are strings with the associated number, so you must put quotes around the column name to refer to it. For example if you alias the table as a, to get the column to see if a student checked 9001, you must write a."9001".

Write a SQL query to create a table with just the column seven from students, filtering first for students who said their favorite number (column number) was 7 in the students table and who checked the box for seven (column "7") in the numbers table.

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

The first 10 lines of this table should be:

sqlite> SELECT * FROM sevens LIMIT 10;
seven
7
7
7
7