CMU 15-445/645 Homework1

CMU 15-445/645 Homework1

Q2 [5 points] (q2_sci_fi):

Find the 10 Sci-Fi works with the longest runtimes.

Details: Print the title of the work, the premiere date, and the runtime. The column listing the runtime should be suffixed with the string " (mins)", for example, if the runtime_mins value is 12, you should output 12 (mins). Note a work is Sci-Fi even if it is categorized in multiple genres, as long as Sci-Fi is one of the genres.
Your first row should look like this: Cicak-Man 2: Planet Hitam|2008|999 (mins)

Answer

sqlite> SELECT title, premiered, runtime_minutes || ' (mins)' as runtime 
   ...> FROM titles
   ...> JOIN akas ON akas.title_id = titles.title_id 
   ...> WHERE genres LIKE '%Sci-Fi%' 
   ...> ORDER BY runtime_minutes DESC 
   ...> LIMIT 10;

Q3 [5 points] (q3_oldest_people):

Determine the oldest people in the dataset who were born in or after 1900. You should assume that a person without a known death year is still alive.

Details: Print the name and age of each person. People should be ordered by a compound value of their age and secondly their name in alphabetical order. Return the first 20 results.
Your output should have the format: NAME|AGE

Answer:

sqlite> SELECT name || '|' || (CASE WHEN died IS NULL THEN strftime('%Y', 'now') ELSE died END - born) AS name_age
   ...> FROM people
   ...> WHERE born >= 1900
   ...> ORDER BY (CASE WHEN died IS NULL THEN strftime('%Y', 'now') ELSE died END - born), name
   ...> LIMIT 20;

Q4 [10 points] (q4_crew_appears_most):

Find the people who appear most frequently as crew members.

Details: Print the names and number of appearances of the 20 people with the most crew appearances ordered by their number of appearances in a descending fashion.
Your output should look like this: NAME|NUM_APPEARANCES

Answer:

sqlite> SELECT p.name, COUNT(*) AS num_appearances
   ...> FROM crew c
   ...> JOIN people p ON c.person_id = p.person_id
   ...> GROUP BY c.person_id
   ...> ORDER BY num_appearances DESC
   ...> LIMIT 20;

We should join the "people" and "crew" table firstly, then group by person_id, which corresponding to name in the joined table, finally chose the name and num_appearances

Q5 [10 points] (q5_decade_ratings):

Compute intersting statistics on the ratings of content on a per-decade basis.

Details: Get the average rating (rounded to two decimal places), top rating, min rating, and the number of releases in each decade. Exclude titles which have not been premiered (i.e. where premiered is NULL). Print the relevant decade in a fancier format by constructing a string that looks like this: 1990s. Order the decades first by their average rating in a descending fashion and secondly by the decade, ascending, to break ties.
Your output should have the format: DECADE|AVG_RATING|TOP_RATING|MIN_RATING|NUM_RELEASES

Answer:

SELECT 
  substr(t.premiered, 1, 3) || '0s' AS DECADE,
  ROUND(AVG(r.rating), 2) AS AVG_RATING,
  MAX(r.rating) AS TOP_RATING,
  MIN(r.rating) AS MIN_RATING,
  COUNT(*) AS NUM_RELEASES
FROM 
  titles t 
  JOIN ratings r ON t.title_id = r.title_id 
WHERE 
  t.premiered IS NOT NULL
GROUP BY 
  DECADE 
ORDER BY 
  AVG_RATING DESC, 
  DECADE ASC;

Q6 [10 points] (q6_cruiseing_altitude):

Determine the most popular works with a person who has "Cruise" in their name and is born in 1962.

Details: Get the works with the most votes that have a person in the crew with "Cruise" in their name who was born in 1962. Return both the name of the work and the number of votes and only list the top 10 results in order from most to least votes. Make sure your output is formatted as follows: Top Gun|408389

Answer:

SELECT titles.primary_title, ratings.votes 
FROM titles
JOIN ratings ON titles.title_id = ratings.title_id
JOIN crew ON titles.title_id = crew.title_id
JOIN people ON titles.title_id = crew.title_id AND crew.person_id = people.person_id
WHERE people.name LIKE '%Cruise%' AND people.born = 1962 
ORDER BY ratings.votes DESC 
LIMIT 10;

Q7 [15 points] (q7_year_of_thieves):

List the number of works that premiered in the same year that "Army of Thieves" premiered.

Details: Print only the total number of works. The answer should include "Army of Thieves" itself. For this question, determine distinct works by their title_id, not their names.

Answer:

SELECT COUNT(DISTINCT akas.title_id)
FROM titles
JOIN akas ON akas.title_id = titles.title_id
WHERE titles.premiered = (
  SELECT premiered
  FROM titles
  JOIN akas ON akas.title_id = titles.title_id
  WHERE akas.title = "Army of Thieves"
  );

We use the second query to get the permierd-time of the work "Army of Thieves".

Q8 [15 points] (q8_kidman_colleagues):

List the all the different actors and actresses who have starred in a work with Nicole Kidman (born in 1967).

Details: Print only the names of the actors and actresses in alphabetical order. The answer should include Nicole Kidman herself. Each name should only appear once in the output.
Note: As mentioned in the schema, when considering the role of an individual on the crew, refer to the field category. The roles "actor" and "actress" are different and should be accounted for as such.

Answer:

SELECT DISTINCT(people.name)
FROM people
JOIN crew ON crew.person_id = people.person_id
WHERE (crew.category = "actor" OR crew.category = "actress") AND crew.title_id IN (
    SELECT titles.title_id
    FROM titles
    JOIN crew ON titles.title_id = crew.title_id
    JOIN people ON crew.person_id = people.person_id
    WHERE people.name = "Nicole Kidman" AND people.born = 1967
)
ORDER BY people.name;

It's similiar with the Q7, we first use a query to get the works that Nicole Kidman attend, and then found the people cooperate with her.