Using SQLite3 from Python

In [1]:
import sqlite3

con = sqlite3.connect('moviedb_jb.db')
cur = con.cursor()

q = 'select * from movie'
cur.execute( q )

for row in cur.fetchall():
        print( row )
(56, 'Captain America-The First Avenger', 2011, 'Joe Johnson')
(1, 'Birdman', 2014, 'Alejandro González Iñárritu')
(2, 'Pulp Fiction', 1994, 'Quentin Tarantino')
(3000, 'Bee Movie', 2007, 'Steve Hickner')
(4, 'Jurassic Park', 1993, 'Steven Spielberg')
(5, 'The Lost World: Jurassic Park', 1997, 'Steven Spielberg')
(39, 'Jurassic Park III', 2001, 'Joe Johnston')
(41, 'Jurassic World', 2015, 'Colin Trevorrow')
(6, 'Frozen', 2013, 'Chris Buck Jennifer Lee')
(7, 'Lion', 2016, 'Garth Davis')
(70, 'The Shining', 1980, 'Stanley Kubrick')
(60, 'Zombieland', 2009, 'Ruben Fleischer')
(745, 'Cars', 2006, 'John Lasseter')
(71, '2001: A Space Odyssey', 1968, 'Stanley Kubrick')
(746, 'Slumdog Millionaire', 2008, 'Danny Boyle')
(96, 'Hidden Figures', 2016, 'Theodore Melfi')
(97, 'Batman: The Movie', 1966, 'Tim Burton')
(12, 'Get Out', 2017, 'Jordan Peele')
(98, 'Spotlight', 2016, 'Tom McCarthy')
(23, 'Amelie', 2001, 'Jean-Pierre Jeunet')
(42, 'Star Wars: Episode IV - A New Hope', 1977, 'George Lucas')
(900, 'Harry Potter and Sorcerers Stone', 2001, 'Chris Columbus')
(901, 'Harry Potter and Chamber of Secrets', 2002, 'Chris Columbus')
(902, 'Harry Potter and The Prisoner of Azkaban', 2004, 'Alfonso Cuaron')
(903, 'Harry Potter and The Goblet of Fire', 2005, 'Mike Newell')
(904, 'Harry Potter and The Order of the Phoenix', 2007, 'David Yates')
(905, 'Harry Potter and The Half-Blood Prince', 2009, 'David Yates')
(906, 'Harry Potter and The Deathly Hallows - Part 1', 2010, 'David Yates')
(907, 'Harry Potter and The Deathly Hallows - Part 2', 2011, 'David Yates')
(410, 'Mulan', 1998, 'Barry Cook')
(232, 'Moon', 2009, 'Duncan Jones')
(888, 'Twilight', 2008, 'Catherine Hardwicke')
(1199, 'Lala Land', 2017, 'Damien Chazelle')
(43, 'Star Wars: Episode V - The Empire Strikes Back', 1980, 'George Lucas')
(100, 'The Godfather', 1974, 'Francis Ford Coppola')
(133, 'Batman: Mask of the Phantasm', 1993, 'Bruce Timm')
(134, 'Teenage Mutant Ninja Turtles', 1990, 'Steve Barron')
(777, 'The Ant Bully', 1980, 'John A. Davis')
(222, 'Transformerz', 2007, 'Michael Bay')
(422, 'Transformers:Revenge of the Fallen', 2009, 'Michael Bay')
(233, 'Jungle Book', 2016, 'Jon Favreau')
(17, 'Fight Club', 1999, 'David Fincher')
(3003, 'Red Cliff', 2008, 'John Woo')
(466, 'The legend of 1900', 1998, 'Giuseppe Tornatore')
(72, 'The Village', 2004, 'M. Night Shyamalan')
(555, 'Gattaca', 1997, 'Andrew Niccol')
In [2]:
import pandas
pandas.set_option('display.max_rows', 200)
In [4]:
def run(q):
    cur.execute(q)
    return pandas.DataFrame(cur.fetchall(),columns=[d[0] for d in cur.description])  # just using Pandas for nice tabular display of results, nothing more
In [5]:
run(q)
Out[5]:
mID title year director
0 56 Captain America-The First Avenger 2011 Joe Johnson
1 1 Birdman 2014 Alejandro González Iñárritu
2 2 Pulp Fiction 1994 Quentin Tarantino
3 3000 Bee Movie 2007 Steve Hickner
4 4 Jurassic Park 1993 Steven Spielberg
5 5 The Lost World: Jurassic Park 1997 Steven Spielberg
6 39 Jurassic Park III 2001 Joe Johnston
7 41 Jurassic World 2015 Colin Trevorrow
8 6 Frozen 2013 Chris Buck Jennifer Lee
9 7 Lion 2016 Garth Davis
10 70 The Shining 1980 Stanley Kubrick
11 60 Zombieland 2009 Ruben Fleischer
12 745 Cars 2006 John Lasseter
13 71 2001: A Space Odyssey 1968 Stanley Kubrick
14 746 Slumdog Millionaire 2008 Danny Boyle
15 96 Hidden Figures 2016 Theodore Melfi
16 97 Batman: The Movie 1966 Tim Burton
17 12 Get Out 2017 Jordan Peele
18 98 Spotlight 2016 Tom McCarthy
19 23 Amelie 2001 Jean-Pierre Jeunet
20 42 Star Wars: Episode IV - A New Hope 1977 George Lucas
21 900 Harry Potter and Sorcerers Stone 2001 Chris Columbus
22 901 Harry Potter and Chamber of Secrets 2002 Chris Columbus
23 902 Harry Potter and The Prisoner of Azkaban 2004 Alfonso Cuaron
24 903 Harry Potter and The Goblet of Fire 2005 Mike Newell
25 904 Harry Potter and The Order of the Phoenix 2007 David Yates
26 905 Harry Potter and The Half-Blood Prince 2009 David Yates
27 906 Harry Potter and The Deathly Hallows - Part 1 2010 David Yates
28 907 Harry Potter and The Deathly Hallows - Part 2 2011 David Yates
29 410 Mulan 1998 Barry Cook
30 232 Moon 2009 Duncan Jones
31 888 Twilight 2008 Catherine Hardwicke
32 1199 Lala Land 2017 Damien Chazelle
33 43 Star Wars: Episode V - The Empire Strikes Back 1980 George Lucas
34 100 The Godfather 1974 Francis Ford Coppola
35 133 Batman: Mask of the Phantasm 1993 Bruce Timm
36 134 Teenage Mutant Ninja Turtles 1990 Steve Barron
37 777 The Ant Bully 1980 John A. Davis
38 222 Transformerz 2007 Michael Bay
39 422 Transformers:Revenge of the Fallen 2009 Michael Bay
40 233 Jungle Book 2016 Jon Favreau
41 17 Fight Club 1999 David Fincher
42 3003 Red Cliff 2008 John Woo
43 466 The legend of 1900 1998 Giuseppe Tornatore
44 72 The Village 2004 M. Night Shyamalan
45 555 Gattaca 1997 Andrew Niccol
In [6]:
q = 'select * from movie,rating,reviewer on movie.mid=rating.mid and rating.rid=reviewer.rid'
run(q)
Out[6]:
mID title year director rID mID stars ratingDate rID name
0 56 Captain America-The First Avenger 2011 Joe Johnson 17 56 4.8 2017-03-07 17 Adhish
1 1 Birdman 2014 Alejandro González Iñárritu 100 1 5.0 2017-03-07 100 John Ringland
2 3003 Red Cliff 2008 John Woo 666 3003 5.0 2017-03-07 666 Jonathan Boatrite
3 133 Batman: Mask of the Phantasm 1993 Bruce Timm 999 133 5.0 2017-03-07 999 Jonathan Lottes
4 134 Teenage Mutant Ninja Turtles 1990 Steve Barron 999 134 5.0 2017-03-07 999 Jonathan Lottes
5 5 The Lost World: Jurassic Park 1997 Steven Spielberg 999 5 2.0 2017-03-07 999 Jonathan Lottes
6 422 Transformers:Revenge of the Fallen 2009 Michael Bay 999 422 1.0 2017-03-07 999 Jonathan Lottes
7 41 Jurassic World 2015 Colin Trevorrow 999 41 5.0 2017-03-07 999 Jonathan Lottes
8 2 Pulp Fiction 1994 Quentin Tarantino 120 2 4.0 2017-03-17 120 Edward Crow
9 4 Jurassic Park 1993 Steven Spielberg 2 4 5.0 2017-03-07 2 Anthony Taboni
10 5 The Lost World: Jurassic Park 1997 Steven Spielberg 2 5 1.0 2017-03-07 2 Anthony Taboni
11 39 Jurassic Park III 2001 Joe Johnston 2 39 3.0 2017-03-07 2 Anthony Taboni
12 410 Mulan 1998 Barry Cook 2 410 3.0 2017-03-07 2 Anthony Taboni
13 888 Twilight 2008 Catherine Hardwicke 20 888 5.0 2017-03-07 20 Anna Kwietniewski
14 42 Star Wars: Episode IV - A New Hope 1977 George Lucas 20 42 0.0 2017-03-07 20 Anna Kwietniewski
15 100 The Godfather 1974 Francis Ford Coppola 115 100 4.0 2017-03-07 115 Hui Duan
16 232 Moon 2009 Duncan Jones 536 232 4.0 2017-03-07 536 Mia Sette
17 555 Gattaca 1997 Andrew Niccol 555 555 5.0 2017-03-07 555 Andrew Forthman
18 42 Star Wars: Episode IV - A New Hope 1977 George Lucas 555 42 5.0 2017-03-07 555 Andrew Forthman
19 4 Jurassic Park 1993 Steven Spielberg 555 4 3.0 2017-03-07 555 Andrew Forthman
20 2 Pulp Fiction 1994 Quentin Tarantino 555 2 5.0 2017-03-07 555 Andrew Forthman
21 745 Cars 2006 John Lasseter 555 745 1.0 2017-03-07 555 Andrew Forthman
22 134 Teenage Mutant Ninja Turtles 1990 Steve Barron 555 134 5.0 2017-03-07 555 Andrew Forthman
23 422 Transformers:Revenge of the Fallen 2009 Michael Bay 555 422 0.0 2017-03-07 555 Andrew Forthman
24 41 Jurassic World 2015 Colin Trevorrow 97 41 5.0 2017-03-07 97 Daniel Seaberg
25 42 Star Wars: Episode IV - A New Hope 1977 George Lucas 97 42 4.0 2017-03-07 97 Daniel Seaberg
26 23 Amelie 2001 Jean-Pierre Jeunet 24 23 4.0 2017-03-07 24 Krithika Krishnan
27 72 The Village 2004 M. Night Shyamalan 164 72 5.0 2017-03-07 164 Megan Tuskes
28 900 Harry Potter and Sorcerers Stone 2001 Chris Columbus 496 900 4.0 2017-03-17 496 Adi
29 56 Captain America-The First Avenger 2011 Joe Johnson 419 56 1.0 2017-03-17 419 Samuel Siegart
30 745 Cars 2006 John Lasseter 425 745 3.0 2017-03-07 425 Maggie
31 746 Slumdog Millionaire 2008 Danny Boyle 425 746 5.0 2017-03-07 425 Maggie
32 70 The Shining 1980 Stanley Kubrick 13 70 4.0 2017-03-07 13 Meg Arnold
33 60 Zombieland 2009 Ruben Fleischer 13 60 4.0 2017-03-07 13 Meg Arnold
34 901 Harry Potter and Chamber of Secrets 2002 Chris Columbus 13 901 4.0 2017-03-07 13 Meg Arnold
35 3000 Bee Movie 2007 Steve Hickner 419 3000 5.0 2017-03-07 419 Samuel Siegart
36 43 Star Wars: Episode V - The Empire Strikes Back 1980 George Lucas 666 43 4.3 2017-03-07 666 Jonathan Boatrite
37 71 2001: A Space Odyssey 1968 Stanley Kubrick 410 71 5.0 2017-03-07 410 Robert Kazmierczak
38 42 Star Wars: Episode IV - A New Hope 1977 George Lucas 410 42 5.0 2017-03-07 410 Robert Kazmierczak
39 71 2001: A Space Odyssey 1968 Stanley Kubrick 419 71 4.0 2017-03-07 419 Samuel Siegart
40 97 Batman: The Movie 1966 Tim Burton 3 97 1.0 2017-03-07 3 Anonymous Chupacabera
41 96 Hidden Figures 2016 Theodore Melfi 3 96 5.0 2017-03-07 3 Anonymous Chupacabera
42 98 Spotlight 2016 Tom McCarthy 199 98 5.0 2017-03-07 199 Xuli Shen
43 12 Get Out 2017 Jordan Peele 101 12 5.0 2017-03-07 101 Dwayne The Rock Johnson
44 70 The Shining 1980 Stanley Kubrick 542 70 5.0 2017-03-07 542 Kasparov
45 12 Get Out 2017 Jordan Peele 466 12 5.0 2017-03-07 466 Yiqing Zhong
46 6 Frozen 2013 Chris Buck Jennifer Lee 67 6 5.0 2017-03-07 67 Aishani Bhalla
47 17 Fight Club 1999 David Fincher 17 17 5.0 2017-03-07 17 Adhish
48 7 Lion 2016 Garth Davis 67 7 5.0 2017-03-07 67 Aishani Bhalla
49 96 Hidden Figures 2016 Theodore Melfi 67 96 5.0 2017-03-07 67 Aishani Bhalla
50 5 The Lost World: Jurassic Park 1997 Steven Spielberg 67 5 2.0 2017-03-07 67 Aishani Bhalla
51 466 The legend of 1900 1998 Giuseppe Tornatore 444 466 5.0 2017-03-07 444 Prashant Shekhar
52 900 Harry Potter and Sorcerers Stone 2001 Chris Columbus 100 900 4.8 2017-03-16 100 John Ringland
53 900 Harry Potter and Sorcerers Stone 2001 Chris Columbus 666 900 5.0 2017-03-16 666 Jonathan Boatrite
54 902 Harry Potter and The Prisoner of Azkaban 2004 Alfonso Cuaron 666 902 3.8 2017-03-16 666 Jonathan Boatrite
55 902 Harry Potter and The Prisoner of Azkaban 2004 Alfonso Cuaron 100 902 4.8 2017-03-16 100 John Ringland
56 901 Harry Potter and Chamber of Secrets 2002 Chris Columbus 100 901 3.5 2017-03-16 100 John Ringland
57 906 Harry Potter and The Deathly Hallows - Part 1 2010 David Yates 100 906 2.0 2017-03-16 100 John Ringland
58 43 Star Wars: Episode V - The Empire Strikes Back 1980 George Lucas 666 43 1.0 2017-03-16 666 Jonathan Boatrite
59 901 Harry Potter and Chamber of Secrets 2002 Chris Columbus 666 901 4.1 2017-03-07 666 Jonathan Boatrite
60 905 Harry Potter and The Half-Blood Prince 2009 David Yates 100 905 4.0 2017-03-07 100 John Ringland
61 903 Harry Potter and The Goblet of Fire 2005 Mike Newell 100 903 4.0 2017-03-07 100 John Ringland
62 3000 Bee Movie 2007 Steve Hickner 100 3000 4.9 2017-03-07 100 John Ringland
63 904 Harry Potter and The Order of the Phoenix 2007 David Yates 666 904 4.3 2017-03-07 666 Jonathan Boatrite
64 71 2001: A Space Odyssey 1968 Stanley Kubrick 100 71 2.2 2017-03-07 100 John Ringland
65 42 Star Wars: Episode IV - A New Hope 1977 George Lucas 100 42 2.5 2017-03-07 100 John Ringland

What is the average movie rating by year?

In [9]:
q = 'select year,avg(stars) from movie, rating on movie.mid=rating.mid group by movie.year'
run(q)
Out[9]:
year avg(stars)
0 1966 1.000000
1 1968 3.733333
2 1974 4.000000
3 1977 3.300000
4 1980 3.575000
5 1990 5.000000
6 1993 4.333333
7 1994 4.666667
8 1997 2.500000
9 1998 4.000000
10 1999 5.000000
11 2001 4.160000
12 2002 3.866667
13 2004 4.533333
14 2005 4.000000
15 2006 2.000000
16 2007 4.733333
17 2008 5.000000
18 2009 2.600000
19 2010 2.000000
20 2011 2.900000
21 2013 5.000000
22 2014 5.000000
23 2015 5.000000
24 2016 5.000000
25 2017 5.000000

What movies were released before 1990?

In [10]:
q= 'select title, year from movie where year <=1990 order by year'
run(q)
Out[10]:
title year
0 Batman: The Movie 1966
1 2001: A Space Odyssey 1968
2 The Godfather 1974
3 Star Wars: Episode IV - A New Hope 1977
4 The Shining 1980
5 Star Wars: Episode V - The Empire Strikes Back 1980
6 The Ant Bully 1980
7 Teenage Mutant Ninja Turtles 1990

Does the movie involve dinosaurs?

In [12]:
q='select * from movie where movie.title like "Jur%"'
run(q)
Out[12]:
mID title year director
0 4 Jurassic Park 1993 Steven Spielberg
1 39 Jurassic Park III 2001 Joe Johnston
2 41 Jurassic World 2015 Colin Trevorrow

How many different directors are there?

In [16]:
q='select count(distinct director) from movie'
run(q)
Out[16]:
count(distinct director)
0 38

Movies between 2008 and 2013?

In [17]:
q = 'select * from movie where year between 2008 and 2013 order by year'
run(q)
Out[17]:
mID title year director
0 746 Slumdog Millionaire 2008 Danny Boyle
1 888 Twilight 2008 Catherine Hardwicke
2 3003 Red Cliff 2008 John Woo
3 60 Zombieland 2009 Ruben Fleischer
4 905 Harry Potter and The Half-Blood Prince 2009 David Yates
5 232 Moon 2009 Duncan Jones
6 422 Transformers:Revenge of the Fallen 2009 Michael Bay
7 906 Harry Potter and The Deathly Hallows - Part 1 2010 David Yates
8 56 Captain America-The First Avenger 2011 Joe Johnson
9 907 Harry Potter and The Deathly Hallows - Part 2 2011 David Yates
10 6 Frozen 2013 Chris Buck Jennifer Lee

How many movies after 1993 made the list?

In [18]:
q='select count(year) from movie where year>1993'
run(q)
Out[18]:
count(year)
0 36

What is the average rating of all of the movies?

In [24]:
q = 'select title, avg(stars) as Average from movie, rating on movie.mid=rating.mid group by movie.mid order by Average desc, title'
run(q)
Out[24]:
title Average
0 Batman: Mask of the Phantasm 5.000000
1 Birdman 5.000000
2 Fight Club 5.000000
3 Frozen 5.000000
4 Gattaca 5.000000
5 Get Out 5.000000
6 Hidden Figures 5.000000
7 Jurassic World 5.000000
8 Lion 5.000000
9 Red Cliff 5.000000
10 Slumdog Millionaire 5.000000
11 Spotlight 5.000000
12 Teenage Mutant Ninja Turtles 5.000000
13 The Village 5.000000
14 The legend of 1900 5.000000
15 Twilight 5.000000
16 Bee Movie 4.950000
17 Pulp Fiction 4.666667
18 Harry Potter and Sorcerers Stone 4.600000
19 The Shining 4.500000
20 Harry Potter and The Order of the Phoenix 4.300000
21 Harry Potter and The Prisoner of Azkaban 4.300000
22 Amelie 4.000000
23 Harry Potter and The Goblet of Fire 4.000000
24 Harry Potter and The Half-Blood Prince 4.000000
25 Jurassic Park 4.000000
26 Moon 4.000000
27 The Godfather 4.000000
28 Zombieland 4.000000
29 Harry Potter and Chamber of Secrets 3.866667
30 2001: A Space Odyssey 3.733333
31 Star Wars: Episode IV - A New Hope 3.300000
32 Jurassic Park III 3.000000
33 Mulan 3.000000
34 Captain America-The First Avenger 2.900000
35 Star Wars: Episode V - The Empire Strikes Back 2.650000
36 Cars 2.000000
37 Harry Potter and The Deathly Hallows - Part 1 2.000000
38 The Lost World: Jurassic Park 1.666667
39 Batman: The Movie 1.000000
40 Transformers:Revenge of the Fallen 0.500000

What is the average title length?

In [29]:
q = 'select min(length(title)) as Min, avg(length(title)) as Avg, max(length(title)) as Max from movie'
In [30]:
run(q)
Out[30]:
Min Avg Max
0 4 18.978261 46

Which 90s movies had greater than 4 stars?

In [35]:
q = '''
select title, year, avg(stars) as Average from movie,rating on movie.mid=rating.mid 
where year >= 1990 and year <= 1999 
group by movie.mid
having Average>=4
'''
run(q)
Out[35]:
title year Average
0 Pulp Fiction 1994 4.666667
1 Jurassic Park 1993 4.000000
2 Fight Club 1999 5.000000
3 Batman: Mask of the Phantasm 1993 5.000000
4 Teenage Mutant Ninja Turtles 1990 5.000000
5 The legend of 1900 1998 5.000000
6 Gattaca 1997 5.000000

lowest rated movie

In [36]:
q='select year, min(stars) from movie, rating on movie.mid=rating.mid'
run(q)
Out[36]:
year min(stars)
0 1977 0
In [37]:
q='select min(stars) from movie, rating on movie.mid=rating.mid'
run(q)
Out[37]:
min(stars)
0 0
In [39]:
q='select title, stars from movie, rating on movie.mid=rating.mid where stars=3'
run(q)
Out[39]:
title stars
0 Jurassic Park III 3
1 Mulan 3
2 Jurassic Park 3
3 Cars 3
In [40]:
q='select title, stars from movie, rating on movie.mid=rating.mid where stars=(select min(stars) from movie, rating on movie.mid=rating.mid)'
run(q)
Out[40]:
title stars
0 Star Wars: Episode IV - A New Hope 0
1 Transformers:Revenge of the Fallen 0
In [41]:
q='select * from Movie m natural join Rating r where r.stars in (select min(stars) from Rating)'
run(q)
Out[41]:
mID title year director rID stars ratingDate
0 42 Star Wars: Episode IV - A New Hope 1977 George Lucas 20 0 2017-03-07
1 422 Transformers:Revenge of the Fallen 2009 Michael Bay 555 0 2017-03-07

What are the range of years of movies reviewed?

In [43]:
q='select min(year), max(year) from movie'
run(q)
Out[43]:
min(year) max(year)
0 1966 2017
In [47]:
q='select distinct mID from rating'
run(q);
In [48]:
q='select min(year),max(year) from movie where mID in (select distinct mID from rating)'
run(q)
Out[48]:
min(year) max(year)
0 1966 2017
In [50]:
#Which is the highest rated harry potter movie
q = '''select title,max(avgstars)
from
(select title, avg(stars) as avgstars
from movie m, rating r
on m.mid=r.mid
where title like "Harry Potter%"
group by m.mid)'''
run(q)
Out[50]:
title max(avgstars)
0 Harry Potter and Sorcerers Stone 4.6
In [54]:
q0 = '''select title, avg(stars) as avgstars
from movie m, rating r
on m.mid=r.mid
where title like "Harry Potter%"
group by m.mid'''

q1 = 'select title,max(avgstars) from (' + q0 + ')'

run(q1)
Out[54]:
title max(avgstars)
0 Harry Potter and Sorcerers Stone 4.6

How many movies did each director direct?

In [55]:
q = 'select director, count(director) as number from movie, rating on movie.mid = rating.mid group by director order by number desc'
run(q)
Out[55]:
director number
0 George Lucas 7
1 Chris Columbus 6
2 Stanley Kubrick 5
3 Steven Spielberg 5
4 David Yates 3
5 Quentin Tarantino 3
6 Alfonso Cuaron 2
7 Colin Trevorrow 2
8 Joe Johnson 2
9 John Lasseter 2
10 Jordan Peele 2
11 Michael Bay 2
12 Steve Barron 2
13 Steve Hickner 2
14 Theodore Melfi 2
15 Alejandro González Iñárritu 1
16 Andrew Niccol 1
17 Barry Cook 1
18 Bruce Timm 1
19 Catherine Hardwicke 1
20 Chris Buck Jennifer Lee 1
21 Danny Boyle 1
22 David Fincher 1
23 Duncan Jones 1
24 Francis Ford Coppola 1
25 Garth Davis 1
26 Giuseppe Tornatore 1
27 Jean-Pierre Jeunet 1
28 Joe Johnston 1
29 John Woo 1
30 M. Night Shyamalan 1
31 Mike Newell 1
32 Ruben Fleischer 1
33 Tim Burton 1
34 Tom McCarthy 1
In [56]:
Who reviewed the most movies?
Object `movies` not found.
In [ ]:
Who reviewed the most movies
In [58]:
q = "select max(cnt), name from(select count(*) as cnt, reviewer.name from rating natural join reviewer group by rating.rid)"
run(q)
Out[58]:
max(cnt) name
0 10 John Ringland
In [60]:
q0 = "select count(*) as cnt, reviewer.name from rating natural join reviewer group by rating.rid"
q1 = "select max(cnt), name from (" + q0+ ")"
run(q1)
Out[60]:
max(cnt) name
0 10 John Ringland

Catch multiples if exist

Which years have the most reviews?

In [68]:
q0 = 'select year, count(mid) as freq from rating natural join movie group by year'
maxfreq = 'select max(freq) from (' + q0 + ')' 
q1 = 'select year, freq from (' + q0 + ') where freq in (' + maxfreq + ')'

print(q1)
run(q1)
select year, freq from (select year, count(mid) as freq from rating natural join movie group by year) where freq in (select max(freq) from (select year, count(mid) as freq from rating natural join movie group by year))
Out[68]:
year freq
0 1977 5
1 2001 5
2 2009 5

Check if a reviewer didnt review anything

In [61]:
q='select name,rid from Reviewer where rid not in(select rid from Rating)'
run(q)
Out[61]:
name rID
0 Sanjeevani Choudhery 184
1 batman897 132
2 Sakar Sawarkar 155
3 Hedy Chen 29

What are all pairs of reviewers that did not review any of the same movies?

In [71]:
allpairs = 'select r1.rid, r2.rid from reviewer r1, reviewer r2 where r1.rid<r2.rid'
run(allpairs);
In [72]:
q1 = 'select reviewer.rid,rating.mid from reviewer,rating on reviewer.rid=rating.rid'
run(q1)
Out[72]:
rID mID
0 17 56
1 100 1
2 666 3003
3 999 133
4 999 134
5 999 5
6 999 422
7 999 41
8 120 2
9 2 4
10 2 5
11 2 39
12 2 410
13 20 888
14 20 42
15 115 100
16 536 232
17 555 555
18 555 42
19 555 4
20 555 2
21 555 745
22 555 134
23 555 422
24 97 41
25 97 42
26 24 23
27 164 72
28 496 900
29 419 56
30 425 745
31 425 746
32 13 70
33 13 60
34 13 901
35 419 3000
36 666 43
37 410 71
38 410 42
39 419 71
40 3 97
41 3 96
42 199 98
43 101 12
44 542 70
45 466 12
46 67 6
47 17 17
48 67 7
49 67 96
50 67 5
51 444 466
52 100 900
53 666 900
54 666 902
55 100 902
56 100 901
57 100 906
58 666 43
59 666 901
60 100 905
61 100 903
62 100 3000
63 666 904
64 100 71
65 100 42

TODO: lookup common table expressions in SQLite (like creating a view but local to current query)

In [ ]: