米ハーバード大学 CS50：課題「マスコットキャラクターのDuckを盗んだ泥棒を発見せよ！」の回答例（Harvard CS50 Week 7 SQL − PSet 7: Fiftyville） 

sqlite> .tables
airports                  crime_scene_reports       people
atm_transactions          flights                   phone_calls
bank_accounts             interviews
courthouse_security_logs  passengers

-- Explore table schemas to understand what data is available and how tables connect oone another
sqlite> .schema airports
CREATE TABLE airports (
    id INTEGER,
    abbreviation TEXT,
    full_name TEXT,
    city TEXT,
    PRIMARY KEY(id)
);

sqlite> .schema crime_scene_reports
CREATE TABLE crime_scene_reports (
    id INTEGER,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    street TEXT,
    description TEXT,
    PRIMARY KEY(id)
);

sqlite> .schema people
CREATE TABLE people (
    id INTEGER,
    name TEXT,
    phone_number TEXT,
    passport_number INTEGER,
    license_plate TEXT,
    PRIMARY KEY(id)
);

sqlite> .schema atm_transactions
CREATE TABLE atm_transactions (
    id INTEGER,
    account_number INTEGER,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    atm_location TEXT,
    transaction_type TEXT,
    amount INTEGER,
    PRIMARY KEY(id)
);

sqlite> .schema flights
CREATE TABLE flights (
    id INTEGER,
    origin_airport_id INTEGER,
    destination_airport_id INTEGER,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    hour INTEGER,
    minute INTEGER,
    PRIMARY KEY(id),
    FOREIGN KEY(origin_airport_id) REFERENCES airports(id),
    FOREIGN KEY(destination_airport_id) REFERENCES airports(id)
);

sqlite> .schema phone_calls
CREATE TABLE phone_calls (
    id INTEGER,
    caller TEXT,
    receiver TEXT,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    duration INTEGER,
    PRIMARY KEY(id)
);

sqlite> .schema bank_accounts
CREATE TABLE bank_accounts (
    account_number INTEGER,
    person_id INTEGER,
    creation_year INTEGER,
    FOREIGN KEY(person_id) REFERENCES people(id)
);

sqlite> .schema interviews
CREATE TABLE interviews (
    id INTEGER,
    name TEXT,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    transcript TEXT,
    PRIMARY KEY(id)
);

sqlite> .schema courthouse_security_logs
CREATE TABLE courthouse_security_logs (
    id INTEGER,
    year INTEGER,
    month INTEGER,
    day INTEGER,
    hour INTEGER,
    minute INTEGER,
    activity TEXT,
    license_plate TEXT,
    PRIMARY KEY(id)
);

sqlite> .schema passengers
CREATE TABLE passengers (
    flight_id INTEGER,
    passport_number INTEGER,
    seat TEXT,
    FOREIGN KEY(flight_id) REFERENCES flights(id)
);

sqlite> SELECT description
FROM crime_scene_reports
WHERE month = 7
AND day = 28
AND street = "Chamberlin Street";
description
Theft of the CS50 duck took place at 10:15am at the Chamberlin Street courthouse. Interviews were conducted today with three witnesses who were present at the time — each of their interview transcripts mentions the courthouse.

-- key information, searching by the key word 'courthouse'
sqlite> SELECT * FROM interviews WHERE year = 2020 AND month = 7 AND day = 28 AND transcript LIKE '%courthouse%';
id | name | year | month | day | transcript
161 | Ruth | 2020 | 7 | 28 | Sometime within ten minutes of the theft, I saw the thief get into a car in the courthouse parking lot and drive away. If you have security footage from the courthouse parking lot, you might want to look for cars that left the parking lot in that time frame.
162 | Eugene | 2020 | 7 | 28 | I don't know the thief's name, but it was someone I recognized. Earlier this morning, before I arrived at the courthouse, I was walking by the ATM on Fifer Street and saw the thief there withdrawing some money.
163 | Raymond | 2020 | 7 | 28 | As the thief was leaving the courthouse, they called someone who talked to them for less than a minute. In the call, I heard the thief say that they were planning to take the earliest flight out of Fiftyville tomorrow. The thief then asked the person on the other end of the phone to purchase the flight ticket.

-- Get the name of the thief
SELECT name FROM people

-- Query security logs
WHERE people.license_plate IN (
    -- Get the license plates from the courthouse logs
    SELECT license_plate FROM courthouse_security_logs
    -- In the ten minute time frame (10:15 - 10:25)
    WHERE year = 2020 AND month = 7 AND day = 28 AND hour = 10 AND minute > 15 AND minute < 25
)

-- Query ATM transactions
AND people.id IN (
    -- Get person_id from the ATM transactions
    SELECT person_id FROM bank_accounts
    -- Let's join the bank account information so that we can grab the person_id
    JOIN atm_transactions ON atm_transactions.account_number = bank_accounts.account_number
    -- Transaction was on the day of the crime
    WHERE atm_transactions.year = 2020 AND atm_transactions.month = 7 AND atm_transactions.day = 28
    -- It was a withdrawal
    AND transaction_type = "withdraw"
    -- It occured on Fifer Street
    AND atm_transactions.atm_location = "Fifer Street"
)

-- Query calls
AND people.phone_number IN (
    -- Get the phone numbers from calls
    SELECT caller FROM phone_calls
    -- Date of the crime
    WHERE year = 2020 AND month = 7 AND day = 28
    -- Duration less than a minute
    AND duration < 60
)

-- Query first flight passenger list
AND people.passport_number IN (
    -- Get the passport numbers of passengers
    SELECT passport_number FROM passengers
    -- On the first flight
    WHERE flight_id IN (
        -- Get the id of the first flight of the next day
        SELECT id FROM flights WHERE year = 2020 AND month = 7 AND day = 29
        ORDER BY hour, minute ASC LIMIT 1
    )
);

-- Get the city name
SELECT city FROM airports
-- From the first flight of the day
WHERE id IN (
    SELECT destination_airport_id FROM flights WHERE year = 2020 AND month = 7 AND day = 29
    ORDER BY hour, minute ASC LIMIT 1
);

-- Get the accomplice's name
SELECT name FROM people
-- Using their phone number
WHERE phone_number IN (
    -- From the list of phone calls
    SELECT receiver FROM phone_calls
    -- On the date of the crime
    WHERE year = 2020 AND month = 7 AND day = 28
    -- And where the caller was our criminal
    AND caller = (
        -- Ernest is a prick
        SELECT phone_number FROM people WHERE name = "Ernest"
    )
    -- And to reduce the likelihood of getting more than one result, let's constrain it a little more
    AND duration < 60
);

The THIEF is: Ernest
The thief ESCAPED TO: London
The ACCOMPLICE is: Berthold