<jupyter_start><jupyter_text>Creating the ceremonies table<jupyter_code>years_hosts = [(2010, "<NAME>"),
               (2009, "<NAME>"),
               (2008, "<NAME>"),
               (2007, "<NAME>"),
               (2006, "<NAME>"),
               (2005, "<NAME>"),
               (2004, "<NAME>"),
               (2003, "<NAME>"),
               (2002, "<NAME>"),
               (2001, "<NAME>"),
               (2000, "<NAME>"),
            ]

create_query = "CREATE TABLE ceremonies ( \
                id integer PRIMARY KEY, \
                Year integer, \
                Host text);"

conn.execute(create_query)

insert_query = "INSERT INTO ceremonies (Year, Host) VALUES (?, ?);"

conn.executemany(insert_query, years_hosts)
for row in conn.execute("SELECT * FROM ceremonies;"):
    print(row)<jupyter_output>(1, 2010, u'<NAME>')
(2, 2009, u'<NAME>')
(3, 2008, u'<NAME>')
(4, 2007, u'Ellen DeGeneres')
(5, 2006, u'<NAME>')
(6, 2005, u'<NAME>')
(7, 2004, u'<NAME>')
(8, 2003, u'<NAME>')
(9, 2002, u'<NAME>')
(10, 2001, u'<NAME>')
(11, 2000, u'<NAME>')<jupyter_text>Foreign key constraints<jupyter_code>conn.execute("PRAGMA foreign_keys = ON;")<jupyter_output><empty_output><jupyter_text>Setting up one-to-many<jupyter_code>query = """SELECT ceremonies.id, nominations.category, nominations.nominee, nominations.movie, nominations.character, nominations."won?"
FROM nominations
INNER JOIN ceremonies ON
nominations.year == ceremonies.year
;"""

results = conn.execute(query).fetchall()

for row in results:
    print(row)
create_query = """CREATE TABLE nominations_two (
                  id integer PRIMARY KEY, 
                  category text, 
                  nominee text, 
                  movie text,
                  character text,
                  won integer,
                  ceremony_id integer, 
                  FOREIGN KEY(ceremony_id) REFERENCES ceremony(id)
                  );
"""

conn.execute(create_query)
insert_query = "INSERT INTO nominations_two (ceremony_id, category, nominee, movie, character, won) VALUES (?, ?, ?, ?, ?, ?);"

conn.executemany(insert_query, results)
for row in conn.execute("SELECT * FROM nominations_two LIMIT 5;"):
    print(row)
# Drop and rename table

conn.execute("DROP TABLE nominations;")
conn.execute("ALTER TABLE nominations_two RENAME TO nominations;")
conn.execute("SELECT * FROM nominations LIMIT 5;").fetchall()<jupyter_output><empty_output><jupyter_text>Creating a join table<jupyter_code>conn.execute("""CREATE TABLE movies (
id integer PRIMARY KEY, 
movie text);"""
            )

conn.execute("""CREATE TABLE actors (
id integer PRIMARY KEY, 
actor text);""")

conn.execute("""CREATE TABLE movies_actors (
id integer PRIMARY KEY, 
movie_id integer, 
actor_id integer, 
FOREIGN KEY(movie_id) REFERENCES movie(id), 
FOREIGN KEY(actor_id) REFERENCES actor(id));""")<jupyter_output><empty_output><jupyter_text>Insert records into join tables<jupyter_code>movies = conn.execute("SELECT DISTINCT movie FROM nominations").fetchall()

print(movies)

insert_query = "INSERT INTO movies (movie) VALUES (?);"

conn.executemany(insert_query, movies)

conn.execute("SELECT * FROM movies LIMIT 10;").fetchall()
actors = conn.execute("SELECT DISTINCT nominee FROM nominations").fetchall()

print(actors)

insert_query = "INSERT INTO actors (actor) VALUES (?);"

conn.executemany(insert_query, actors)

conn.execute("SELECT * FROM actors LIMIT 10;").fetchall()
movie_actor = conn.execute("""
SELECT movies.id, actors.id 
FROM movies, actors, nominations
WHERE movies.movie = nominations.movie
AND actors.actor = nominations.nominee""").fetchall()

# 220 rows
print(movie_actor)

insert_query = "INSERT INTO movies_actors (movie_id, actor_id) VALUES (?, ?);"

conn.executemany(insert_query, movie_actor)

conn.execute("SELECT * FROM movies_actors LIMIT 10;").fetchall()
conn.close()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup