CS50 Problem Set 7#

1
SELECT name FROM songs;

1
SELECT name
2
FROM songs
3
ORDER BY tempo;

1
SELECT name
2
FROM songs
3
ORDER BY duration_ms DESC
4
LIMIT 5;

1
SELECT name
2
FROM songs
3
WHERE danceability > 0.75 AND energy > 0.75 AND valence > 0.75;

1
SELECT AVG(energy)
2
FROM songs;

1
SELECT name
2
FROM songs
3
WHERE artist_id = (
4
    SELECT id
5
    FROM artists
6
    WHERE name = 'Post Malone'
7
);

1
SELECT AVG(energy)
2
FROM songs
3
WHERE artist_id = (
4
    SELECT id
5
    FROM artists
6
    WHERE name = 'Drake'
7
);

1
SELECT name
2
FROM songs
3
WHERE name LIKE '%feat.%';

1
1. I will use the SQL to count the average energy, valence, and danceability of a person’s top 100 songs from the past year.
2

3
2. I should consider how many times they listen to each music.

1
-- 1. Titles of all movies from 2008
2
SELECT title
3
FROM movies
4
WHERE year = 2008;

1
-- 2. Birth year of Emma Stone
2
SELECT birth
3
FROM people
4
WHERE name = 'Emma Stone';

1
-- 3. Titles of all movies since 2018, in alphabetical order
2
SELECT title
3
FROM movies
4
WHERE year >= 2018
5
ORDER BY title ASC;

1
-- 4. Number of movies with a 10.0 rating
2
SELECT COUNT(rating)
3
FROM ratings
4
WHERE rating = 10.0

1
-- 5. Titles and years of all Harry Potter movies, in chronological order (title beginning with "Harry Potter and the ...")
2
SELECT title, year
3
FROM movies
4
WHERE title LIKE "Harry Potter and the%"
5
ORDER BY year;

1
-- 6. Average rating of movies in 2012
2
SELECT AVG(rating)
3
FROM ratings
4
WHERE movie_id IN (
5
    SELECT id FROM movies WHERE year = 2012
6
)

1
-- 7. All movies and ratings from 2010, in decreasing order by rating (alphabetical for those with same rating)
2
SELECT movies.title, ratings.rating
3
FROM movies
4
JOIN ratings
5
ON movies.id = ratings.movie_id AND movies.year = 2010 AND ratings.rating > 0
6
ORDER BY ratings.rating DESC, title ASC;

1
-- 8. Names of people who starred in Toy Story
2
SELECT name
3
FROM people
4
WHERE id IN(
5
    SELECT person_id
6
    FROM stars
7
    WHERE movie_id = (
8
        SELECT id
9
        FROM movies
10
        WHERE title = 'Toy Story'
11
    )
12
);

1
-- 9. Names of all people who starred in a movie released in 2004, ordered by birth year
2
SELECT id, name
3
FROM people
4
WHERE id IN(
5
    SELECT person_id
6
    FROM stars
7
    WHERE movie_id IN(
8
        SELECT id
9
        FROM movies
10
        WHERE year = 2004
11
    )
12
)
13
ORDER BY birth;

1
-- 10. Names of all directors who have directed a movie that got a rating of at least 9.0
2
SELECT name
3
FROM people
4
WHERE id IN (
5
    SELECT person_id
6
    FROM directors
7
    WHERE movie_id IN (
8
        SELECT movie_id
9
        FROM ratings
10
        WHERE rating >= 9.0
11
    )
12
);

1
-- 11. Titles of the five highest rated movies (in order) that Chadwick Boseman starred in, starting with the highest rated
2
SELECT title
3
FROM movies
4
JOIN ratings ON ratings.movie_id = movies.id
5
WHERE id IN (
6
    SELECT movie_id
7
    FROM stars
8
    WHERE person_id = (
9
        SELECT id
10
        FROM people
11
        WHERE name = 'Chadwick Boseman'
12
    )
13
)
14
ORDER BY ratings.rating DESC
15
LIMIT 5;

1
-- 12. Titles of all of movies in which both Jennifer Lawrence and Bradley Cooper starred
2
SELECT title
3
FROM movies
4
WHERE (
5
    id IN (
6
    SELECT movie_id
7
    FROM stars
8
    WHERE person_id IN (
9
        SELECT id
10
        FROM people
11
        WHERE name = 'Bradley Cooper'
12
        )
13
    )
14
)
15
AND (
16
    id IN (
17
    SELECT movie_id
18
    FROM stars
19
    WHERE person_id IN (
20
        SELECT id
21
        FROM people
22
        WHERE name = 'Jennifer Lawrence'
23
        )
24
    )
25
);

1
-- 13. Names of all people who starred in a movie in which Kevin Bacon also starred
2
SELECT name
3
FROM people
4
JOIN stars ON people.id = stars.person_id
5
JOIN movies ON movies.id = stars.movie_id
6
WHERE movies.id IN (
7
    SELECT movies.id
8
    FROM movies
9
    JOIN stars ON movies.id = stars.movie_id
10
    JOIN people ON stars.person_id = people.id
11
    WHERE people.name = 'Kevin Bacon' AND people.birth = 1958
12
)
13
AND people.name != 'Kevin Bacon';

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

1
-- Keep a log of any SQL queries you execute as you solve the mystery.
2
-- Get the description of the crime scene reports on that day and the street
3
SELECT description FROM crime_scene_reports
4
    WHERE year = 2025 AND month = 7 AND day = 28 AND street = 'Humphrey Street';
5
-- 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.
6
-- Littering took place at 16:36. No known witnesses.
7

8
--Get the transcript from the reporters
9
SELECT transcript FROM interviews WHERE year = 2025 AND month = 7 AND day = 28 AND transcript LIKE "%bakery%";
10
-- Sometime within ten minutes of the theft, I saw the thief get into a car in the bakery parking lot and drive away. If you have security footage from the bakery parking lot, you might want to look for cars that left the parking lot in that time frame.
11
-- I don't know the thief's name, but it was someone I recognized. Earlier this morning, before I arrived at Emma's bakery, I was walking by the ATM on Leggett Street and saw the thief there withdrawing some money.
12
-- As the thief was leaving the bakery, 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.
13

14
SELECT bakery_security_logs.activity, bakery_security_logs.license_plate, people.name FROM bakery_security_logs
15
JOIN people ON bakery_security_logs.license_plate = people.license_plate
16
WHERE bakery_security_logs.year = 2025
17
AND bakery_security_logs.month = 7
18
AND bakery_security_logs.day = 28
19
AND bakery_security_logs.hour = 10
20
AND bakery_security_logs.minute >= 15
21
AND bakery_security_logs.minute <= 25;
22
/*
23
+----------+---------------+---------+
24
| activity | license_plate |  name   |
25
+----------+---------------+---------+
26
| exit     | 5P2BI95       | Vanessa |
27
| exit     | 94KL13X       | Bruce   | *
28
| exit     | 6P58WS2       | Barry   |
29
| exit     | 4328GD8       | Luca    |
30
| exit     | G412CB7       | Sofia   |
31
| exit     | L93JTIZ       | Iman    |
32
| exit     | 322W7JE       | Diana   | *
33
| exit     | 0NTHK55       | Kelsey  |
34
+----------+---------------+---------+
35
*/
36

37
SELECT people.name FROM people
38
JOIN bank_accounts ON bank_accounts.person_id = people.id
39
JOIN atm_transactions ON atm_transactions.account_number = bank_accounts.account_number
40
WHERE atm_transactions.year = 2025
41
AND atm_transactions.month = 7
42
AND atm_transactions.day = 28
43
AND atm_transactions.atm_location = 'Leggett Street'
44
AND atm_transactions.transaction_type = 'withdraw';
45
/*
46
+---------+
47
|  name   |
48
+---------+
49
| Bruce   | *
50
| Diana   | *
51
| Brooke  |
52
| Kenny   |
53
| Iman    |
54
| Luca    |
55
| Taylor  |
56
| Benista |
57
+---------+
58
*/
59

60
UPDATE phone_calls
61
SET receiver = people.name
62
FROM people
63
WHERE phone_calls.receiver = people.phone_number;
64

65
UPDATE phone_calls
66
SET caller = people.name
67
FROM people
68
WHERE phone_calls.caller = people.phone_number;
69

70
SELECT caller, receiver FROM phone_calls
71
WHERE year = 2025
72
AND month = 7
73
AND day = 28
74
AND duration < 60;
75
/*
76
+---------+------------+
77
| caller  |  receiver  |
78
+---------+------------+
79
| Sofia   | Jack       |
80
| Kelsey  | Larry      |
81
| Bruce   | Robin      | *
82
| Kelsey  | Melissa    |
83
| Taylor  | James      |
84
| Diana   | Philip     | *
85
| Carina  | Jacqueline |
86
| Kenny   | Doris      |
87
| Benista | Anna       |
88
+---------+------------+
89
*/
90

91
SELECT id, origin_airport_id, destination_airport_id FROM flights
92
WHERE year = 2025
93
AND month = 7
94
AND day = 29
95
AND origin_airport_id = (
96
    SELECT id FROM airports
97
    WHERE city = 'Fiftyville'
98
)
99
ORDER BY flights.hour ASC;
100
/*
101
+----+-------------------+------------------------+
102
| id | origin_airport_id | destination_airport_id |
103
+----+-------------------+------------------------+
104
| 36 | 8                 | 4                      | *
105
| 43 | 8                 | 1                      |
106
| 23 | 8                 | 11                     |
107
| 53 | 8                 | 9                      |
108
| 18 | 8                 | 6                      |
109
+----+-------------------+------------------------+
110
*/
111

112
SELECT city FROM airports
113
WHERE id = 4;
114
/*
115
+---------------+
116
|     city      |
117
+---------------+
118
| New York City |
119
+---------------+
120
*/
121

122
SELECT name FROM people
123
WHERE people.passport_number IN (
124
    SELECT passengers.passport_number from passengers
125
    WHERE flight_id = 36
126
);
127
/*
128
+--------+
129
|  name  |
130
+--------+
131
| Kenny  |
132
| Sofia  |
133
| Taylor |
134
| Luca   |
135
| Kelsey |
136
| Edward |
137
| Bruce  | *
138
| Doris  |
139
+--------+
140
*/