Brief

• Due date: 11:59pm 02/04/2020
• Stencil: cs1951a_install sql
• Data: /course/cs1951a/pub/sql/stencil/data/
• Handin: cs1951a_handin sql
• Files to submit: Part 1 SQL Files, Part 2 SQL Files, writeup.txt

---

---

Setting Up

The cs1951a_install sql script installs all the stencil code you will need to your cs1951a course directory, which is ~/course/cs1951a/.

To get the data files, you can either navigate to /course/cs1951a/pub/sql/stencil/data/ and copy them, or you can run:

cp -r /course/cs1951a/pub/sql/stencil/data ~/course/cs1951a/sql/

---

Part 0: Intro to Data Ethics

10 points

Throughout the course, we will be learning techniques that allow us to better understand and work with Big Data. These tools are extremely powerful and can be used in a myriad of ways, spanning from collecting user data online to creating predictive machine learning models. As a result, many professionals have felt the need to create ethical standards that all data scientists should follow. For example, the ACM (Association for Computing Machinery), which is the world's largest education and scientific computing society, recently updated their Code of Ethics to address recent advances in technology that have allowed computing to spread to all aspects of life. Read the following articles and respond to the questions in writeup.txt.

Articles

• Should Data Scientists adhere to a Hippocratic Oath?
• ACM Code of Ethics

Questions

1. (3 points) How do you think data is currently being used (both in industry and research)? What are positive and negative applications of data science? (3-4 sentences total)
2. (4 points) Do you think that a universal code of ethics is necessary to create for data scientists? Why or why not? Who should be responsible for upholding data standards - the government, tech companies, users, etc? Should the government create legislation to hold them accountable? (4-5 sentences total)
3. (3 points) What do you like or dislike about the ACM code of ethics? What are areas that you would like to change or improve? (4-5 sentences total)

---

SQLite

SQLite is installed on all department machines. 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.

---

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 first half of the lab, please do so before starting this assignment. The database and schema are described again below, but are the same from the lab.

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

   Hint: Use a LEFT JOIN… 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

   Hint: 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

   Hint: 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

   You can do this problem without using two SELECT statements, but doing so makes sure that all your answers are distinct. Also, future problems will require using two SELECT statements, so practicing here isn't a bad idea!

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

   Time to join stuff!

---

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:

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" (This movie should also be a Disney movie!).

   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')

Hint: 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.

Hint: You may want to look into CASE statements and the LIKE operator.

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).

Hint: You may want to look into the EXISTS operator. Additionally, 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'
   

   Hint: Look into the BETWEEN statement and how it can be used in a join.

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).



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!

---

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, team 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 team = athletes.team
         ); 

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

The assignment directory present in your course directory, which is

~/course/cs1951a/sql

would be the one used to hand in the solution files. Please make sure that this directory contains the following:

• Part 1 SQL Files (5 in total)
• Part 2 SQL Files (6 in total)
• writeup.txt

Once you are sure of all the files, please run the handin script cs1951a_handin sql to turn in your assignment.

Please do not hand in the data files!

---

Credits

Made with ♥ by Jens and Esteban, updated for Spring 2020 by Mounika

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