Question

Question 5: Sevens

Let's take a look at data from both of our tables, students and checkboxes, to find out if students that got the number 7 assigned to them also chose '7' for the obedience question. Specifically, we want to look at the students that fulfill the below conditions and see if they also chose '7' in the question that asked students to choose the number 7 (column seven in students).

• reported that their favorite number (column number in students) was 7
• have 'True' in column '7' in checkboxes, meaning they got assigned the number 7.

In order to examine rows from both the students and the checkboxes 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 checkboxes 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 checkboxes table.

You should get the following output:

sqlite> SELECT * FROM sevens;
7

CREATE TABLE sevens as

-- REPLACE THIS LINE SELECT 'YOUR CODE HERE';

Answer 1

This is the required query :

CREATE TABLE sevens as
SELECT a.seven FROM students as a, checkboxes as b WHERE a.time = b.time AND a.number="7" AND b.'7' = "true"

(Take a look at a.time = b.time , this is used to only compare the value for same student because time is the unique identifier for the student in both the tables 'students' and checkboxes)

**If you have any query , please feel free to comment with details.
**Happy learning :)