Lab 13 - SQL

intro to SQL...

Instructions: https://inst.eecs.berkeley.edu/~cs61a/su19/lab/lab13/

Solution: https://github.com/tomthestrom/cs61a/blob/master/lab/lab13/lab13.sql

Q1 - non-coding - what would SQL print?

Q2: Obedience

To warm-up, let's ask a simple question related to our data: Is there a correlation between whether students do as they're told and their favorite images of their instructors?

Write an SQL query to create a table that contains the columns seven (this column representing "obedience") and instructor (the image of the instructor students selected) from the students table.

Q2: Solution

CREATE TABLE obedience AS
  SELECT seven, instructor FROM students;

Q3: The Smallest Unique Positive Integer

Who successfully managed to guess the smallest unique positive integer value? Let's find out!

Write an SQL query to create a table with the columns time and smallest that we can inspect to determine what the smallest integer value is. In order to make it easier for us to inspect these values, use WHERE to restrict the answers to numbers greater than 2, ORDER BY to sort the numerical values, and LIMIT your result to the first 20 values that are greater than the number 2.

Q3: Solution

CREATE TABLE smallest_int AS
  SELECT time, smallest FROM students WHERE smallest > 2 ORDER BY smallest ASC LIMIT 20;

Q4: Matchmaker, Matchmaker

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!

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

• The shared preferred pet of the couple

• The shared favorite song of the couple

• The favorite color of the first person

• The favorite color of the second person

Q4: Solution

CREATE TABLE matchmaker AS
  SELECT f.pet, f.song, f.color, s.color FROM students f, students s
   WHERE f.pet = s.pet            
   AND f.song = s.song
   AND f.time < s.time; ;

Q5: The Smallest Unique Positive Integer

Who successfully managed to guess the smallest unique positive integer value? Let's find out!

Write an SQL query to create a table with the columns time and smallest which contains the timestamp for each submission that made a unique guess for the smallest unique positive integer - that is, only one person put that number for their guess of the smallest unique integer. Also include their guess in the output.

Q5: Solution

CREATE TABLE smallest_int_having AS
  SELECT time, smallest FROM students
  GROUP BY smallest
  HAVING COUNT(smallest) = 1;