search

mikeizbicki / cmc-csci143

big data course materials
40 stars 76 forks source link

Midterm #471

Closed mikeizbicki closed 8 months ago

mikeizbicki commented 8 months ago

Due Date:

Sunday, 17 March at midnight. The midterm cannot be submitted late.

Collaboration Policy:

You may not collaborate with any human on this assignment.

You are allowed to reference arbitrary non-human resources created before the midterm was assigned. In particular, you are encouraged to reference:

  1. Postgres,
  2. your previous assignments,
  3. the class notes,
  4. github issues,
  5. online documentation,
  6. and AI assistants like ChatGPT.

Instructions:

A link to the repo with your midterm will be posted here around 1pm today.

The file sql/midterm.sql contains 4 problems.

Write SQL code that answers each of those four problems.

Upload your completed midterm.sql file to sakai.

mikeizbicki commented 8 months ago

The midterm is posted at https://github.com/mikeizbicki/csci143-midterm.

Recall that the no-collaboration policy means that you shouldn't post anything to the github issues at this point. If you have specific questions, you can email me.

mikeizbicki commented 8 months ago

This message is to clarify the prompt for problem 2. For reference, the original prompt states:

/* PROBLEM 2:
*
* Management wants to hire a family-friendly actor to do a commercial,
* and so they want to know which family-friendly actors generate the most revenue.
*
* Write a SQL query that:
* Lists the first and last names of all actors who have appeared in movies in the "Family" category,
* but that have never appeared in movies in the "Horror" category.
* For each actor, you should also list the total amount that customers have paid to rent films that the actor has been in.
* Order the results so that actors generating the most revenue are at the top.
*/

I want to clarify the following line in particular:

For each actor, you should also list the total amount that customers have paid to rent films that the actor has been in

The final amount that your SELECT query outputs should include the revenue generated from all films that the actor has been in regardless of category. It is incorrect to include only films in the 'Family' category.