In [1]:
import sqlite3
conn = sqlite3.connect('movie.db')
cur = conn.cursor()
In [2]:
cur.execute('''SELECT genre
FROM film
WHERE title="Titanic"''')
for row in cur.fetchall():
print(row[0])
In [3]:
cur.execute('''SELECT id, firstname, name
FROM person
WHERE name='Jolie'
AND firstname='Angelina' ''')
for row in cur.fetchall():
print(row[0], row[1], row[2])
In [4]:
cur.execute('''SELECT title
FROM film
WHERE year=2010
AND genre LIKE "%Action%"''')
for row in cur.fetchall():
print(row[0])
In [5]:
cur.execute('''SELECT name
FROM cinema
WHERE city="Bonn"''')
for row in cur.fetchall():
print(row[0])
In [6]:
cur.execute('''SELECT firstname, name
FROM person, participation
WHERE participation.person = person.id
AND function="actor"''')
for row in cur.fetchall()[:10]:
print(row[0], row[1])
In [7]:
cur.execute('''SELECT firstname, name
FROM person, participation, film
WHERE participation.person = person.id
AND participation.function="director"
AND participation.film = film.id
AND film.title="Titanic"''')
for row in cur.fetchall():
print(row[0], row[1])
As the dates in the SHOW table are randomly chosen between 1980 and 2016, every cinema shows ever film only once in this time and there are only cinemas from Bonn and Cologne in the cinema table there are not to many matches.
In [8]:
cur.execute('''SELECT cinema.name, cinema.city
FROM cinema, show, film
WHERE show.date > '1999-'
AND film.title="Inferno"
AND show.film = film.id
AND show.cinema = cinema.id''')
for row in cur.fetchall():
print(row[0], row[1])
In [9]:
cur.execute('''SELECT person.firstname, person.name
FROM person, participation, film
WHERE film.title = "Inferno"
AND participation.film = film.id
AND participation.person = person.id''')
for row in cur.fetchall():
print(row[0], row[1])
In [10]:
conn.close()