(2022年) CS50 week7

其實之前就已經修完 CS50 了，不過因應 2022 年將課堂作業的系統
從 c9.io 換到 github codespace + vscode 整合
打算來重溫一下裡面的作業，並暖身一下 C 語言的手感

由於已經有許多前輩分享課程與作業的心得
加上官網教材的投影片、重點筆記、教學影片都非常完整
故不再做詳細的紀錄，專注在作業 (Lab, Problem sets) 上面

https://cs50.harvard.edu/x/2022/weeks/7/

---

Lab6： Songs

(點擊展開) 1.sql

-- write a SQL query to list the names of all songs in the database.
SELECT name FROM songs;

(點擊展開) 2.sql

-- write a SQL query to list the names of all songs in increasing order of tempo
SELECT name FROM songs ORDER BY tempo;

(點擊展開) 3.sql

-- write a SQL query to list the names of the top 5 longest songs, in descending order of length
SELECT name FROM songs ORDER BY duration_ms DESC LIMIT 5;

(點擊展開) 4.sql

-- write a SQL query that lists the names of any songs that have danceability, energy, and valence greater than 0.75.
SELECT name
FROM songs
WHERE
  danceability > 0.75
  AND energy > 0.75
  AND valence > 0.75;

(點擊展開) 5.sql

-- write a SQL query that returns the average energy of all the songs
SELECT AVG(energy) FROM songs;

(點擊展開) 6.sql

-- write a SQL query that lists the names of songs that are by Post Malone
SELECT name
FROM songs
where
  artist_id = (SELECT id FROM artists WHERE name == "Post Malone");

(點擊展開) 7.sql

-- write a SQL query that returns the average energy of songs that are by Drake
SELECT avg(energy)
FROM songs
WHERE
  artist_id = (SELECT id FROM artists WHERE name == "Drake");

(點擊展開) 8.sql

-- write a SQL query that lists the names of the songs that feature other artists
SELECT name FROM songs WHERE name LIKE "%feat.%";

–

驗證

---

作業： Movies

(點擊展開) 1.sql

-- write a SQL query to list the titles of all movies released in 2008.
SELECT title FROM movies WHERE year = 2008;

(點擊展開) 2.sql

-- write a SQL query to determine the birth year of Emma Stone.
SELECT birth FROM people WHERE name = "Emma Stone";

(點擊展開) 3.sql

-- write a SQL query to list the titles of all movies with a release date on or after 2018, in alphabetical order.
SELECT title
FROM movies
WHERE year >= 2018
ORDER BY title;

(點擊展開) 4.sql

--  write a SQL query to determine the number of movies with an IMDb rating of 10.0.
SELECT count(movies.id)
FROM movies
    JOIN ratings
    ON movies.id = ratings.movie_id
    AND ratings.rating = 10;

(點擊展開) 5.sql

-- write a SQL query to list the titles and release years of all Harry Potter movies, in chronological order.
SELECT title, year
FROM movies
WHERE
    title LIKE 'Harry Potter%'
ORDER BY year;

(點擊展開) 6.sql

-- write a SQL query to determine the average rating of all movies released in 2012.
SELECT avg(rating)
FROM ratings
    JOIN movies
    ON movies.id = ratings.movie_id
where movies.year=2012;

(點擊展開) 7.sql

-- write a SQL query to list all movies released in 2010 and their ratings, in descending order by rating. For movies with the same rating, order them alphabetically by title.
SELECT
    ratings.rating, movies.title
FROM movies
    JOIN ratings
    ON movies.id = ratings.movie_id
WHERE movies.year = 2010
ORDER BY
    ratings.rating DESC,
    movies.title ASC;

(點擊展開) 8.sql

-- write a SQL query to list the names of all people who starred in Toy Story.
SELECT name
FROM people
    JOIN stars ON stars.person_id = people.id
    JOIN movies ON stars.movie_id = movies.id
where movies.title = "Toy Story";

(點擊展開) 9.sql

-- write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.
SELECT DISTINCT name
FROM people
    JOIN stars ON stars.person_id = people.id
    JOIN movies ON stars.movie_id = movies.id
    JOIN ratings ON ratings.movie_id = movies.id
WHERE movies.year = 2004
ORDER BY people.birth;

(點擊展開) 10.sql

-- write a SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.
SELECT name
FROM people
    JOIN directors ON directors.person_id = people.id
    JOIN movies ON directors.movie_id = movies.id
    JOIN ratings ON ratings.movie_id = movies.id
WHERE ratings.rating >= 9;

(點擊展開) 11.sql

-- write a SQL query to list the titles of the five highest rated movies (in order) that Chadwick Boseman starred in, starting with the highest rated.
SELECT movies.title
FROM movies
    JOIN stars ON stars.movie_id = movies.id
    JOIN people ON stars.person_id = people.id
    JOIN ratings ON ratings.movie_id = movies.id
WHERE people.name = "Chadwick Boseman"
ORDER BY ratings.rating DESC
LIMIT 5;

(點擊展開) 12.sql

-- write a SQL query to list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred.
SELECT title
FROM movies
    JOIN stars ON stars.movie_id = movies.id
    JOIN people ON stars.person_id = people.id
WHERE
    people.name = "Johnny Depp"
    AND title in (
        SELECT title
        FROM movies
            JOIN stars ON stars.movie_id=movies.id
            JOIN people ON stars.person_id=people.id
        WHERE people.name = "Helena Bonham Carter"
    );

(點擊展開) 13.sql

-- write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred.
SELECT name
FROM people
    JOIN stars ON stars.person_id = people.id
    JOIN movies ON stars.movie_id = movies.id
WHERE
    movies.id IN (
        SELECT movies.id FROM movies
            JOIN people ON stars.person_id = people.id
            JOIN stars ON stars.movie_id = movies.id
        WHERE
            people.name = "Kevin Bacon"
            AND people.birth = 1958
        )
    AND people.name != "Kevin Bacon";

–

驗證

---

作業： Fiftyville

(點擊展開) log.sql

-- Keep a log of any SQL queries you execute as you solve the mystery.

-- Checking the description of the crime happened at the given location and time.
SELECT description
  FROM crime_scene_reports
 WHERE year = 2021
   AND month = 7
   AND day = 28
   AND street = 'Humphrey Street';

-- Two incidents happened that day. Only one is related to theft. Other is related to littering.

-- The witnesses could be the accomplice. So, finding the names of the witnesses from the interviews table. Also, checking their interviews' transcripts.
SELECT name, transcript
  FROM interviews
 WHERE year = 2021
   AND month = 7
   AND day = 28;

-- Found two transcripts with the name- Eugene, so checking how many Eugenes are present in the 'people' table.
SELECT name
  FROM people
 WHERE name = 'Eugene';
-- Found out that there is only one Eugene. So, proceeding accordingly.

-- Findling the names of the 3 witnesses from the list of names of people who gave interviews on July 28, 2021. Crime report said that the witnesses mentioned "bakery" in their transcripts. Also, ordering the results alphabetically by the names of witnesses.
SELECT name, transcript
  FROM interviews
 WHERE year = 2021
   AND month = 7
   AND day = 28
   AND transcript LIKE '%bakery%'
 ORDER BY name;
-- Witnesses are- Eugene, Raymond, and Ruth.


-- Eugene gave clues- Thief was withdrawing money from the ATM on Leggett Street. So, checking the account number of the person who did that transaction.
SELECT account_number, amount
  FROM atm_transactions
 WHERE year = 2021
   AND month = 7
   AND day = 28
   AND atm_location = 'Leggett Street'
   AND transaction_type = 'withdraw';
-- Finding the names associated with the corresponding account numbers. Putting these names in the 'Suspect List'
SELECT name, atm_transactions.amount
  FROM people
  JOIN bank_accounts
    ON people.id = bank_accounts.person_id
  JOIN atm_transactions
    ON bank_accounts.account_number = atm_transactions.account_number
 WHERE atm_transactions.year = 2021
   AND atm_transactions.month = 7
   AND atm_transactions.day = 28
   AND atm_transactions.atm_location = 'Leggett Street'
   AND atm_transactions.transaction_type = 'withdraw';


-- Raymond gave clues-- As leaving the bakery, they called a person and talked for less than a minute. They asked the person on the other end of the call to buy a flight ticket of the earliest flight on July 29, 2021.
-- First finding the information about the aiport in Fiftyville which would be the origin of the flight of the thief.
SELECT abbreviation, full_name, city
  FROM airports
 WHERE city = 'Fiftyville';
-- Finding the flights on July 29 from Fiftyville airport, and ordering them by time.
SELECT flights.id, full_name, city, flights.hour, flights.minute
  FROM airports
  JOIN flights
    ON airports.id = flights.destination_airport_id
 WHERE flights.origin_airport_id =
       (SELECT id
          FROM airports
         WHERE city = 'Fiftyville')
   AND flights.year = 2021
   AND flights.month = 7
   AND flights.day = 29
 ORDER BY flights.hour, flights.minute;
-- First flight comes out to be at 8:20 to LaGuardia Airport in New York City (Flight id- 36). This could be the place where the thief went to.
-- Checking the list of passengers in that flight. Putting them all in 'Suspect List'. Ordering the names according to their passport numbers.
SELECT passengers.flight_id, name, passengers.passport_number, passengers.seat
  FROM people
  JOIN passengers
    ON people.passport_number = passengers.passport_number
  JOIN flights
    ON passengers.flight_id = flights.id
 WHERE flights.year = 2021
   AND flights.month = 7
   AND flights.day = 29
   AND flights.hour = 8
   AND flights.minute = 20
 ORDER BY passengers.passport_number;
-- Checking the phone call records to find the person who bought the tickets.
-- Firstly, checking the possible names of the caller, and putting these names in the 'Suspect List'. Ordering them according to the durations of the calls.
SELECT name, phone_calls.duration
  FROM people
  JOIN phone_calls
    ON people.phone_number = phone_calls.caller
 WHERE phone_calls.year = 2021
   AND phone_calls.month = 7
   AND phone_calls.day = 28
   AND phone_calls.duration <= 60
 ORDER BY phone_calls.duration;
-- Secondly, checking the possible names of the call-receiver. Ordering them according to the durations of the calls.
SELECT name, phone_calls.duration
  FROM people
  JOIN phone_calls
    ON people.phone_number = phone_calls.receiver
 WHERE phone_calls.year = 2021
   AND phone_calls.month = 7
   AND phone_calls.day = 28
   AND phone_calls.duration <= 60
   ORDER BY phone_calls.duration;


-- Ruth gave clues- The thief drove away in a car from the bakery, within 10 minutes from the theft. SO, checking the license plates of cars within that time frame. Then, checking out the names of those cars' owners. They could be suspects.
SELECT name, bakery_security_logs.hour, bakery_security_logs.minute
  FROM people
  JOIN bakery_security_logs
    ON people.license_plate = bakery_security_logs.license_plate
 WHERE bakery_security_logs.year = 2021
   AND bakery_security_logs.month = 7
   AND bakery_security_logs.day = 28
   AND bakery_security_logs.activity = 'exit'
   AND bakery_security_logs.hour = 10
   AND bakery_security_logs.minute >= 15
   AND bakery_security_logs.minute <= 25
 ORDER BY bakery_security_logs.minute;


-- Bruce must the thief as he is present in all the 4 lists- List of passengers, list of people who did the specific atm transactions, list of people who called, and list of people who drove away in cars from the bakery.
-- He must have escaped to the New York City, as he took the New York City flight.
-- Robin must be the accomplice who purchased the flight ticket, and helped Bruce escape to the New York City.

(點擊展開) answer.txt

The THIEF is: Bruce
The city the thief ESCAPED TO: New York City
The ACCOMPLICE is: Robin

–

驗證