Harvard CS50 学习笔记（七）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 1
select name from songs;
-- 2
select name from songs order by tempo;
-- 3
select name from songs order by duration_ms desc limit 5;
-- 4
select name from songs where danceability > 0.75 and energy > 0.75 and valence > 0.75;
-- 5
select sum(energy) / count(*) from songs;
-- 6
select songs.name from songs join artists on songs.artist_id = artists.id where artists.name = 'Post Malone';
-- 7
select SUM(songs.energy) / count(songs.id) from songs join artists on songs.artist_id = artists.id where artists.name = 'Drake';
-- 8
select name from songs where name like '%feat%';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 1
select title from movies where year = 2008;
-- 2
select birth from people where name = 'Emma Stone';
-- 3
select title from movies where year >= 2018 order by title;
-- 4
select count(movie_id) from ratings where rating = 10.0;
-- 5
select title, year from movies where title like 'Harry Potter%' order by year;
-- 6
select sum(rating)/count(movie_id) from ratings join movies on ratings.movie_id = movies.id where movies.year = 2012;
-- 7
select movies.title, ratings.rating from ratings join movies on ratings.movie_id = movies.id where movies.year = 2010 order by ratings.rating desc, movies.title;
-- 8
select people.name from stars join movies on stars.movie_id = movies.id join people on stars.person_id = people.id where movies.title = 'Toy Story';
-- 9
select people.name from movies join stars on movies.id = stars.movie_id join people on stars.person_id = people.id where movies.year = 2004 order by people.birth;
-- 10
select distinct(people.name) from movies join ratings on movies.id = ratings.movie_id join directors on movies.id = directors.movie_id join people on directors.person_id = people.id where ratings.rating >= 9.0;
-- 11
select movies.title from movies join ratings on movies.id = ratings.movie_id join stars on movies.id = stars.movie_id join people on stars.person_id = people.id where people.name = 'Chadwick Boseman' order by ratings.rating desc limit 5;
-- 12
select movies.title from movies join stars on movies.id = stars.movie_id join people on stars.person_id = people.id where movies.id in (select stars.movie_id from stars join people on stars.person_id = people.id where people.name = 'Johnny Depp') and people.name = 'Helena Bonham Carter';
-- 13
select people.name from movies join stars on movies.id = stars.movie_id join people on stars.person_id = people.id where movies.title in (select movies.title from movies join stars on movies.id = stars.movie_id join people on stars.person_id = people.id where people.name = 'Kevin Bacon' and people.birth = 1958) and people.name != 'Kevin Bacon';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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

-- query crime_scene_reports
select * from crime_scene_reports where year = 2020 and month = 7 and day = 28 and street = 'Chamberlin Street';

-- query interviews
select * from interviews where year = 2020 and month = 7 and day = 28;

-- cars that left courthouse within 10 minutes
select * from courthouse_security_logs where year = 2020 and month = 7 and day = 28 and hour = 10 and minute >= 15 and minute <= 25 and activity = 'exit';

-- people whose car left courthouse within 10 minutes
select * from people where license_plate in (select license_plate from courthouse_security_logs where year = 2020 and month = 7 and day = 28 and hour = 10 and minute >= 15 and minute <= 25 and activity = 'exit');

--people who withdrawed money in that dat at Fifer Street
select * from people join bank_accounts on people.id = bank_accounts.person_id where bank_accounts.account_number in (select account_number from atm_transactions where year = 2020 and month = 7 and day = 28 and atm_location = 'Fifer Street' and transaction_type = 'withdraw');

--people who made a phone call at that day and duration is less than a minute
select * from people where phone_number in (select caller from phone_calls where year = 2020 and month = 7 and day = 28 and duration < 60);

-- people who took the earliest flight out of Fiftyville tomorrow
select * from people where passport_number in (select passport_number from passengers where flight_id in (select id from flights where origin_airport_id = (select id from airports where city = 'Fiftyville') and year = 2020 and month = 7 and day = 29 order by hour limit 1));

-- people whose car left courthouse within 10 minutes and withdrawed money in that dat at Fifer Street and made a phone call at that day and duration is less than a minute and took the earliest flight out of Fiftyville tomorrow
select a.name from (select * from people where license_plate in (select license_plate from courthouse_security_logs where year = 2020 and month = 7 and day = 28 and hour = 10 and minute >= 15 and minute <= 25 and activity = 'exit')) as a join (select * from people join bank_accounts on people.id = bank_accounts.person_id where bank_accounts.account_number in (select account_number from atm_transactions where year = 2020 and month = 7 and day = 28 and atm_location = 'Fifer Street' and transaction_type = 'withdraw')) as b on a.id = b.id join (select * from people where phone_number in (select caller from phone_calls where year = 2020 and month = 7 and day = 28 and duration < 60)) as c on b.id = c.id join (select id from people where passport_number in (select passport_number from passengers where flight_id in (select id from flights where origin_airport_id = (select id from airports where city = 'Fiftyville') and year = 2020 and month = 7 and day = 29 order by hour limit 1))) as d on c.id = d.id;