【cs50】lab7 & problem set7

（1）lab7 songs

sqlite3 songs.db

　　1）list the names of all songs in the database

SELECT name FROM  songs;

　　2）list names of all songs in increasing order of tempo

SELECT name FROM songs ORDER BY tempo;

　　3） 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） 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）returns the average energy of all the songs.

SELECT AVG(energy) FROM songs;

　　6） lists the names of songs that are by Post Malone.

SELECT name FROM songs
WHERE artist_id IN
(SELECT id FROM artists WHERE name = "Post Malone");

　　7）returns the average energy of songs that are by Drake.

SELECT AVG(energy) FROM songs
WHERE artist_id IN
(SELECT id FROM artists WHERE name="Drake");

　　8）lists the names of the songs that feature other artists.

SELECT name FROM songs WHERE name LIKE "%feats.%";

（2）problem set7 movies

　　1） list the titles of all movies released in 2008

SELECT title FROM movies WHERE year = 2008;

　　2） determine the birth year of Emma Stone

SELECT birth FROM people WHERE name LIKE "Emma Stone";

　　3） 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） determine the number of movies with an IMDb rating of 10.0

SELECT COUNT(rating) FROM ratings WHERE rating=10.0;

　　5） 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）determine the average rating of all movies released in 2012.

SELECT AVG(rating) FROM ratings
JOIN movies ON ratings.movie_id = movies.id
WHERE year = 2012;

　　7） 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 title,rating FROM ratings
JOIN movies
ON ratings.movie_id = movies.id
WHERE year = 2010
ORDER BY rating DESC,title

　　8）list the names of all people who starred in Toy Story

SELECT name FROM people
JOIN stars ON people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE title LIKE "Toy Story";

　　9）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 people.id = stars.person_id
JOIN movies ON stars.movie_id = movies.id
WHERE year = 2004
ORDER BY birth;

　　10） list the names of all people who have directed a movie that received a rating of at least 9.0.

SELECT DISTINCT(name) FROM people
JOIN movies ON stars.movie_id = movies.id
JOIN stars ON (people.id = stars.person_id AND movies.id = stars.movie_id)
JOIN ratings ON movies.id = ratings.movies_id
WHERE rating >=9.0;

　　11）list the titles of the five highest rated movies (in order) that Chadwick Boseman starred in, starting with the highest rated.

SELECT DISTINCT(name) FROM people
JOIN movies ON stars.movie_id = movies.id
JOIN stars ON (people.id = stars.person_id AND movies.id = stars.movie_id)
JOIN ratings ON movies.id = ratings.movies_id
WHERE name LIKE "Chadwick Boseman"
ORDER BY rating DESC
LIMIT 5

　　12）list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred.

SELECT title FROM movies,stars,people
WHERE movies.id = stars.movie_id
AND stars.person_id = people.id
AND name LIKE "Johnny Depp"
AND title IN
(SELECT title FROM movies,stars,people
    WHERE movies.id = stars.movie_id
    AND stars.person_id = people.id
    AND name LIKE "Helena Bonham Carter");

　　13）list the names of all people who starred in a movie in which Kevin Bacon also starred.

SELECT DISTINCT(name) FROM stars,people
WHERE stars.person_id = people.id
AND name != "Kevin Bacon"
AND movie_id IN
(SELECT movie_id FROM stars,people
WHERE stars.person_id = people.id
AND name = "Kevin Bacon"
AND birth = 1958
);

(2)fiftyville

-- Keep a log of any SQL queries you execute as you solve the mystery.
sqlite3 fiftyville.db
.tables
.schema crime_scene_reports

-- 查看当天犯罪记录
SELECT * FROM crime_scene_reports
WHERE year = 2021
AND month = 7
AND day = 28
AND street = 'Humphrey Street';

--Theft of the CS50 duck took place at 10:15am at the Humphrey Street bakery.
--Interviews were conducted today with three witnesses who were present at the time – each of their interview transcripts mentions the bakery.
--Littering took place at 16:36.No known witnesses
SELECT * FROM interviews
WHERE year = 2021
AND month = 7
AND day = 28
AND transcript LIKE "%thief%" ;

--cars bakery ten minutes
--ATM on Leggett street ,withdrawing some money
--take the earliest flight out of Fiftyville tomorrow / call someone / purchase the flight ticket /<1 minute

--check car
SELECT * FROM bakery_security_logs
WHERE year = 2021
AND month = 7
AND day = 28;
--L68E5I0 -> 8:25 ENTRANCE,8:34 exit

--check atm
SELECT * FROM atm_transactions
WHERE year = 2021
AND month = 7
AND day = 28
AND atm_location LIKE "Leggett street"
AND transaction_type LIKE "withdraw";

--check airport
SELECT * FROM airports WHERE city LIKE "fiftyville":
-- abbreviation CSF; full_name = Fiftyville Regional Airport

--check flights
SELECT * FROM flights
WHERE origin_airport_id = 8
AND year = 2021
AND month = 7
AND day = 29
--the ealiest : 2021/7/29/8/20 from 8 to 4  flights id = 36

--check airport
SELECT * FROM airports WHERE id = 4;
-- LaGuardia Airport in New York City

--check phone_calls
SELECT * FROM phone_calls
WHERE year = 2021
AND month = 7
AND day = 28
AND duration < 60;

--bank
SELECT name FROM people,bank_accounts,atm_transactions
WHERE people.id = bank_accounts.person_id
AND bank_accounts.account_number = atm_transactions.account_number
AND month = 7
AND day = 28
AND atm_location LIKE "Leggett street"
AND transaction_type LIKE "withdraw";
--name : Bruce Diana Brooke Kenny Iman Luca Taylor Benista

--phone
SELECT name FROM people WHERE phone_number IN
(SELECT caller FROM phone_calls WHERE year = 2021 AND month = 7 AND day = 28 AND duration < 60);
--name : Kenny Sofia Benista Taylor Diana Kelsey Bruce Carina

--airport
SELECT name FROM people WHERE passport_number IN
(SELECT passport_number FROM passengers WHERE flight_id = 36);
--name:Kenny Sofia Taylor Luca Kelsey Edward Bruce Doris

--car
SELECT name FROM people WHERE license_plate IN
(SELECT license_plate FROM bakery_security_logs
WHERE year = 2021
AND month = 7
AND day = 28
AND hour = 10
AND minute > 15
AND minute <=25
AND activity = "exit");
--name:Vanessa Barry Iman Sofia Luca Diana Kelsey Bruce


--answer the phone
SELECT name FROM people WHERE phone_number IN
(SELECT receiver FROM phone_calls WHERE caller IN
(SELECT phone_number FROM people WHERE name LIKE "Bruce")
AND month = 7 AND day = 28 AND duration < 60
);
--name:Robin


-- call the phone :Bruce
-- answer the phone :Robin