Assignment 1 « SQL »

Due: 11:59pm 02/14/2022 ET

SQL Joke


Setting Up


Getting the Stencil


You can click here to get the stencil code for Homework 1. Reference this guide for more information about Github and Github Classroom.

The data is located in the data folder. To ensure compatibility with the autograder, you should not modify the stencil unless instructed otherwise. For this assignment, please write each of your queries to its corresponding SQL file. Failing to do so may hinder with the autograder and result in a low grade.


SQLite


If you are working locally, you can check if SQLite is installed already by running sqlite3 -version in your terminal. You can refer to this guide or this guide to install SQLite and learn how to use SQLite on your machine. SQLite is installed on all department machines and by default on Mac. It can be accessed from the command line using sqlite3.

Running sqlite3 somedb.db from your terminal will launch an environment that will allow you to type your SQL queries directly into the terminal. You can exit this environment by pushing Ctrl+D or by typing .exit and pressing enter.

As a more explicit example, to open a sql environment where you can query the movies.db database, you can type:

$ sqlite3 movies.db

To execute a SQL statement that you have saved in a solution file, you can run the following command:

$ sqlite3 movies.db < sql_solutions.sql

For more information on using SQLite from the command line, see http://www.sqlite.org/sqlite.html. Additionally, we have provided very helpful hints for most of the problems; you should be able to use these as a starting point if you get stuck before looking up additional information online.

Additionally or alternatively, there are really powerful SQLite tools out there that you can use, especially if you prefer a more graphical way of interacting with SQLite. Check out the section below for more details.


Database


Some useful tools you can use to view the content in a database: SQLite Viewer (a web application) and SQLTools + SQLite packages (if you are developing on VS Code). Additionally, you can install and use SQLite extensions on your internet browser (e.g., SQLite Manager for Chrome or for Mozilla)



Part 1: Starting Off!

20 points


This part of the assignment builds off the exercises you completed in the lab. If you have not yet completed the lab, please do so before starting this assignment. There are some really useful hints and examples you can borrow from the lab for this assignment. The database and schema are described again below, but are the same from the lab.

In this assignment, you should use keywords in some of your queries. Please remember to check if there are any required keywords before implementing your solution for each task.

We have provided a database named people.db with the name, age, ID, and occupation of some Brown students and alumni. Here is the schema: 

people_main(ID INTEGER, name TEXT, occupation TEXT, age INTEGER)
people_likes(ID1 INTEGER, ID2 INTEGER)
people_friends(ID1 INTEGER, ID2 INTEGER)

In the people_main table, ID is a unique identifier for a particular student or alumni. name, occupation and age correspond to the person's first name, occupation and age.

In the people_friends table, each (ID1, ID2) pair indicates that the particular person with ID1 is friends with the person with ID2 (and vice versa). The friendship is mutual, and if (ID1, ID2) is in the table, it is guaranteed that (ID2, ID1) exists in the table.

In the people_likes table, each (ID1, ID2) pair indicates that the student or alumni with ID1 likes the person with ID2. The (ID1, ID2) pair in the table does not guarantee that the (ID2, ID1) pair also exists in the table.

Your job is to write SQL queries for the data being requested:

  1. (4 points) Write a SQL statement that returns the name and number of people that like each person. Results should be ordered by count (descending), and then by name (A-Z). Save the query to part1_problem1.sql

    Keyword(s): Use a LEFT JOIN!
    Hint: The following website is quite useful: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

  2. (4 points) Write a SQL statement that returns the two occupations that have the lowest count of popular people. Consider popular people as people who are liked by at least one other person. Return the two occupations and the counts. Results should be ordered by occupation (A-Z). Save the query to part1_problem2.sql

    Keyword(s):: The LIMIT statement will come in handy!

  3. (4 points) Write a SQL statement that returns the name and occupation of all people who have more than 3 friends. Results should be ordered by name (A-Z). Save the query to part1_problem3.sql

    Keyword(s): You'll need to take a look at the HAVING function.

  4. (4 points) Write a SQL statement that returns the distinct name and age of all people who are liked by anyone younger than them. Results should be ordered by name (A-Z). Save the query to part1_problem4.sql.

  5. (4 points) Write a SQL statement to find pairs (A, B) such that person A likes person B, but A is not friends with B. The query should return 4 columns: ID of person 1, name of person 1, ID of person 2 and name of person 2. Results should be ordered by ID1 (ascending), then ID2 (ascending). Save the query to part1_problem5.sql

    Keyword(s): You'll need to use the JOIN statement.


Time to join stuff!


SQL Joke



Part 2: Getting Harder!

60 points


For this part of the assignment, you will be using the TMDB Movie Dataset, which has been exported to the movies.db database. The database schema is as follows:

movies(budget INTEGER, homepage TEXT, id INTEGER, original_language TEXT, original_title TEXT, overview TEXT, popularity REAL, release_date TEXT, revenue REAL, runtime INTEGER, status TEXT, tagline TEXT, title TEXT, vote_average REAL, vote_count INTEGER)
scores(review TEXT, min_score INTEGER, max_score INTEGER)

We encourage you to use the WITH operator, which lets you divide your query into separate queries. As an example, we can define a subquery and use it in another query as follows (there is also an example in the lab!):

WITH subquery AS (
SELECT
  original_title, vote_average
FROM
  movies
)

SELECT
  original_title
FROM
  subquery
);

  1. (10 points) Write a SQL query to find the original_title, budget and release_date of the movie "John Carter" and append to that the movie original_title, budget and release_date of the movie that was released 9 days after "John Carter".

    You can add days to a particular day by using the date function. For example, in order to add 3 days to to '2012-07-16', you can use date('2012-07-16', '+3 days')
    Keyword(s): The UNION statement should come in handy.

  2. (10 points) Write a SQL query to count the number of movies that start with "The", end with a "2" or contain the word "shark". Your query should be case insensitive and return one column with one entry. You should return a single value.

    3. Keyword(s): You may want to look into CASE statements and the LIKE operator. (Lab!)

  3. (10 points) Write a SQL query to select the original_title of all movies and a column where there is a 1 if there exists another movie that has the same vote average and the same runtime as that movie, and a 0 otherwise. Results should be ordered by original_title (A-Z).

    4. Keyword(s): You may want to look into the EXISTS operator.
    Hint: Think about possible edge cases.

  4. (10 points) Write a SQL query that returns the original_title, vote_average and review of every movie. The reviews depends on the vote_average as described described in the scores table. For example, movies with a vote average between 2 and 3.9 (inclusive) are reviewed as 'poor', whereas movies with a vote average between 9 and 10 (inclusive) are reviewed as 'excellent'. If a movie is reviewed as 'awful' or 'poor' then original_title should read 'do not watch'. Results should be ordered by id (ascending). For example, the output should have the following format: 
    'Snow White'   | 8.7 | 'great'
'Toy Story'    | 9.3 | 'must see'
'do not watch' | 2.3 | 'poor'

    Keywords(s): Look into the BETWEEN statement and how it can be used in a JOIN.
    Hint: Do not modify the current database by using UPDATE. Take a look at the CASE operation example from the lab.

  5. (10 points) Write a SQL query that finds the original_title, release_date and revenue of all the movies whose revenue exceeded the average revenue of all the movies released on the same day (including itself). Results should be ordered by release_date (ascending), and then revenue (descending).
    Keywords(s): You'll find the GROUP BY, AVG, and JOIN operations helpful!

  6. (10 points) Write a SQL query that, for each original_language that has more than 2 movies , finds the number of movies that were reviewed as 'poor' and the number of movies that were reviewed as 'good'.

    Like in the 4th question, you will need to look at the scores table to see what the review categories are and use the vote_average field of a movie to determine which review category it falls under. Your query should return 3 columns (original_language, num_poor which is the number of 'poor' movies for that language, and num_good which should be the number of 'good' movies for the language). Your results should be ordered by number of 'good' movies (descending) and then number of 'poor' movies (ascending). Remember to only include languages that have more than 2 movies!

    Keywords(s): The JOIN statement will come in handy!
    Hint: Refer to the examples from the lab!



Part 3: Optimization

10 points


We have provided you with the athletes.db database, although querying it is not necessary at all. The schema is as follows: 

school_athletes(ID INTEGER, name TEXT, school TEXT, performance_score INTEGER, division TEXT)

For the query below, explain why the given query might not be the most efficient way to accomplish the task. Write out an optimized version of the query in writeup.txt. Explain what steps you took to optimize it and why your version would be more efficient.

  1. (6 points) The SQL query to optimize is as follows: 

    SELECT ID, name
      FROM school_athletes AS athletes
      WHERE school = 'Brown' and performance_score > (
        SELECT AVG(performance_score)
        FROM school_athletes
        WHERE division = athletes.division
      );

  2. (4 points) Consider two tables. Table A is very long with 1 billion rows and 5 columns. Table B is very wide with 1000 rows and 10,000 columns. If we were to join the two tables and want to make sure the join is performant, how should we best filter the tables? Assume that we can select from each table and then join the results. Specifically, state the table in which we should use WHERE heavily and the table in which we should be careful about what values we use in our SELECT.



Handing In


After finishing the assignment (and any assignment in the future), run python3 zip_assignment.py in the command line from your assignment directory, and fix any issues brought up by the script.

After the script has been run successfully, you should find the file sql-submission-1951A.zip in your assignment directory. Please submit this zip file on Gradescope under the respective assignment. (If you have not signed up for Gradescope already, please refer to this guide.)



Credits


Made with ♥ by Jens and Esteban, updated for Spring 2022 by Aakansha Mathur.

Movie Database from: https://www.kaggle.com/tmdb/tmdb-movie-metadata