pset 7

houses

Implement a program to import student data into a database, and then produce class rosters.

$ python import.py characters.csv
$ python roster.py Gryffindor

Lavender Brown, born 1979
Colin Creevey, born 1981
Seamus Finnigan, born 1979
Hermione Jean Granger, born 1979
Neville Longbottom, born 1980
Parvati Patil, born 1979
Harry James Potter, born 1980
Dean Thomas, born 1980
Romilda Vane, born 1981
Ginevra Molly Weasley, born 1981
Ronald Bilius Weasley, born 1980

characters.csv
import.py
roster.py
students.db

characters.csv example:

name,house,birth
Adelaide Murton,Slytherin,1982
Adrian Pucey,Slytherin,1977
Anthony Goldstein,Ravenclaw,1980
Blaise Zabini,Slytherin,1979

# TODO
import sys
import csv
from cs50 import SQL

if len(sys.argv) != 2:
    print("Usage: python import.py spreadsheet.csv")
    sys.exit(1)

db = SQL("sqlite:///students.db")
with open(sys.argv[1], 'r') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        name = row["name"].split()
        first = name[0]
        middle = name[1] if len(name) == 3 else None
        last = name[2] if len(name) == 3 else name[1]
        db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)",
                   first, middle, last, row["house"], row["birth"])

# TODO
from cs50 import SQL
import sys

if len(sys.argv) != 2:
    print("Usage: python roster.py Housename")
    sys.exit(1)

db = SQL("sqlite:///students.db")
lst = db.execute("SELECT * FROM students WHERE house = ? ORDER BY last, first", sys.argv[1])
for row in lst:
    print(row["first"], end=' ')
    if row["middle"] != None:
        print(row["middle"], end=' ')
    print(row["last"] + ', ' + "born", row["birth"])

movies

Write SQL queries to answer questions about a database of movies.

1.sql
2.sql
3.sql
4.sql
5.sql
6.sql
7.sql
8.sql
9.sql
10.sql
11.sql
12.sql
13.sql
movies.db

-- 1.sql
SELECT title
FROM movies
WHERE year = 2008;

-- 2.sql
SELECT birth
FROM people
WHERE name = "Emma Stone";

-- 3.sql
SELECT title
FROM movies
WHERE year >= 2018
ORDER BY title;

-- 4.sql
SELECT COUNT(movie_id)
FROM ratings
WHERE rating = 10.0;

-- 5.sql
SELECT title, year
FROM movies
WHERE title LIKE "Harry Potter%"
ORDER BY year;

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

-- 7.sql
SELECT movies.title, ratings.rating
FROM ratings JOIN movies
ON ratings.movie_id=movies.id
WHERE movies.year = 2010 AND ratings.rating IS NOT NULL
ORDER BY ratings.rating DESC, movies.title ASC;

-- 8.sql
SELECT people.name
FROM people JOIN stars
ON people.id=stars.person_id
WHERE stars.movie_id IN
(SELECT id
FROM movies
WHERE title = "Toy Story");

-- 9.sql
SELECT DISTINCT people.name
FROM people JOIN stars
ON people.id=stars.person_id
WHERE stars.movie_id IN
(SELECT id
FROM movies
WHERE year = 2004)
ORDER BY people.birth;

-- 10.sql
SELECT people.name
FROM people JOIN directors
ON people.id=directors.person_id
WHERE directors.movie_id IN
(SELECT movie_id
FROM ratings
WHERE rating >= 9.0);

-- 11.sql
SELECT movies.title
FROM movies JOIN ratings
ON movies.id=ratings.movie_id
WHERE ratings.movie_id IN
(SELECT stars.movie_id
FROM stars JOIN people
ON stars.person_id=people.id
WHERE people.name = "Chadwick Boseman")
ORDER BY ratings.rating DESC
LIMIT 5;

-- 12.sql
SELECT movies.title
FROM movies JOIN stars
ON movies.id=stars.movie_id
WHERE stars.person_id = (SELECT id FROM people WHERE name = "Johnny Depp")
OR stars.person_id = (SELECT id FROM people WHERE name = "Helena Bonham Carter")
GROUP BY movies.id
HAVING COUNT(movies.id) > 1;

-- 13.sql
SELECT people.name
FROM people JOIN stars
ON people.id=stars.person_id
WHERE stars.movie_id IN (SELECT stars.movie_id
FROM stars JOIN people
ON stars.person_id=people.id
WHERE people.name = "Kevin Bacon" AND people.birth = 1958)
AND NOT people.name = "Kevin Bacon";