After handling basic sql queries through Python, let us look at slightly more advanced SQL queries and their execution through Python's sqlite module. SQL concepts like nested queries, merges, join operations are some of the basic concepts among advanced sql queries. In order to execute queries and practice these concepts, we need to first load data. Most of these concepts involve more than one table, so we will load and work with data among two tables.
Fandango is an online ticketing platform for movies. In order to support user's ticket buying decision, Fandango has viewer reviews and ratings for movie listings. This Fandango data set consists of two files - Fandango scrape and Fandango score comparison. The Fandango scrape consists of the list of movies on Fandango and their ratings. The score comparison file consists of the movie name and compares Fandango's movie score with that of other movie review websites like rotten tomatoes and metacritic.
Connect to fandangodb.db and load the two data sets, fandango_scrape and fandango_score_comparison into two tables 'fscrape' and 'fscore' respectively.
import sqlite3
import csv
import pandas as pd
# Connecting to the database
fancon = sqlite3.connect(':memory:')
# Reading data into tables
scrapedf = pd.read_csv('https://raw.githubusercontent.com/colaberry/538data/master/fandango/fandango_scrape.csv')
scoredf = pd.read_csv('https://raw.githubusercontent.com/colaberry/538data/master/fandango/fandango_score_comparison.csv')
scrapedf.to_sql(name='fscrape',con=fancon,if_exists='append',index=False)
scoredf.to_sql(name='fscore',con=fancon,if_exists='append',index=False)
ref_tmp_var = False
ref_tmp_var = True
assert ref_tmp_var
In the above step we have loaded two files fandango_score_comparison and fandango_scrape into two dataframes. Let us verify the success of the loading operation by visualizing the dataframes.
Visualize the heads of both data frames created in the step above. Read and understand the nature of the data.
# Use .head(5) function on the dataframes to visualize first 5 rows of the dataframes.
# Use print function to print both the dataframes in a single step.
print(scrapedf.head(5),scoredf.head(5))
FILM STARS RATING VOTES 0 Fifty Shades of Grey (2015) 4.0 3.9 34846 1 Jurassic World (2015) 4.5 4.5 34390 2 American Sniper (2015) 5.0 4.8 34085 3 Furious 7 (2015) 5.0 4.8 33538 4 Inside Out (2015) 4.5 4.5 15749 FILM RottenTomatoes RottenTomatoes_User \ 0 Avengers: Age of Ultron (2015) 74 86 1 Cinderella (2015) 85 80 2 Ant-Man (2015) 80 90 3 Do You Believe? (2015) 18 84 4 Hot Tub Time Machine 2 (2015) 14 28 Metacritic Metacritic_User IMDB Fandango_Stars Fandango_Ratingvalue \ 0 66 7.1 7.8 5.0 4.5 1 67 7.5 7.1 5.0 4.5 2 64 8.1 7.8 5.0 4.5 3 22 4.7 5.4 5.0 4.5 4 29 3.4 5.1 3.5 3.0 RT_norm RT_user_norm ... IMDB_norm RT_norm_round \ 0 3.70 4.3 ... 3.90 3.5 1 4.25 4.0 ... 3.55 4.5 2 4.00 4.5 ... 3.90 4.0 3 0.90 4.2 ... 2.70 1.0 4 0.70 1.4 ... 2.55 0.5 RT_user_norm_round Metacritic_norm_round Metacritic_user_norm_round \ 0 4.5 3.5 3.5 1 4.0 3.5 4.0 2 4.5 3.0 4.0 3 4.0 1.0 2.5 4 1.5 1.5 1.5 IMDB_norm_round Metacritic_user_vote_count IMDB_user_vote_count \ 0 4.0 1330 271107 1 3.5 249 65709 2 4.0 627 103660 3 2.5 31 3136 4 2.5 88 19560 Fandango_votes Fandango_Difference 0 14846 0.5 1 12640 0.5 2 12055 0.5 3 1793 0.5 4 1021 0.5 [5 rows x 22 columns]
ref_tmp_var = False
try:
scrapedftest = pd.DataFrame([('Fifty Shades of Grey (2015)',4.0,3.9,34846),
('Jurassic World (2015)',4.5,4.5,34390),
('American Sniper (2015)',5.0,4.8,34085),
('Furious 7 (2015)',5.0,4.8,33538),
('Inside Out (2015)',4.5,4.5,15749)],
columns=['FILM','STARS','RATING','VOTES'])
scoredftest = pd.DataFrame([('Avengers: Age of Ultron (2015)',74,86,66,7.1,7.8,5.0,4.5,3.70,4.3,3.3,3.55,3.90,3.5,4.5,3.5,3.5,4.0,1330,271107,14846,0.5),
('Cinderella (2015)',85,80,67,7.5,7.1,5.0,4.5,4.25,4.0,3.35,3.75,3.55,4.5,4.0,3.5,4.0,3.5,249,65709,12640,0.5),
('Ant-Man (2015)',80,90,64,8.1,7.8,5.0,4.5,4.00,4.5,3.2,4.05,3.90,4.0,4.5,3.0,4.0,4.0,627,103660,12055,0.5),
('Do You Believe? (2015)',18,84,22,4.7,5.4,5.0,4.5,0.90,4.2,1.1,2.35,2.70,1.0,4.0,1.0,2.5,2.5,31,3136,1793,0.5),
('Hot Tub Time Machine 2 (2015)',14,28,29,3.4,5.1,3.5,3.0,0.70,1.4,1.45,1.7,2.55,0.5,1.5,1.5,1.5,2.5,88,19560,1021,0.5)],
columns=['FILM','RottenTomatoes','RottenTomatoes_User','Metacritic','Metacritic_User','IMDB','Fandango_Stars','Fandango_Ratingvalue','RT_norm','RT_user_norm','Metacritic_norm','Metacritic_user_nom','IMDB_norm','RT_norm_round','RT_user_norm_round','Metacritic_norm_round','Metacritic_user_norm_round','IMDB_norm_round','Metacritic_user_vote_count','IMDB_user_vote_count','Fandango_votes','Fandango_Difference'])
if scrapedftest.equals(scrapedf.head(5)) and scoredftest.equals(scoredf.head(5)):
ref_assert_var = True
ref_tmp_var = True
else:
ref_assert_var = False
print('Please follow the instructions given and use the same variables provided in the instructions.')
except Exception:
print('Please follow the instructions given and use the same variables provided in the instructions.')
assert ref_tmp_var
True
Let us understand a bit more about the data, before using SQL queries to analyze it.
Find the total number of rows in both the tables and print them out. You may use the dataframes to count the data.
# Use a simple print statement to print the number of rows
# Use the length method on the dataframes
print("Total number of records in fscrape table=%d and fscore table=%d"%(len(scrapedf),len(scoredf)))
ref_tmp_var = False
try:
var1=510
var2=146
if var1==len(scrapedf) and var2==len(scoredf):
ref_assert_var = True
ref_tmp_var = True
else:
ref_assert_var = False
print('Please follow the instructions given and use the same variables provided in the instructions.')
except Exception:
print('Please follow the instructions given and use the same variables provided in the instructions.')
assert ref_tmp_var
True
Union is a set theory concept which says that it is a collection of all unique elements of two or more sets. When performed among two tables say by specifying column name, it outputs the collection of all unique values that appear in the common column, of either or both tables. Intersection outputs the values that are appear in both tables. Note that when performed on same inputs, intersection will always be a subset of Union. Minus operation results in values/records, featuring in one table but not featuring in the other - i.e. A-B = A-(Intersection(A,B)).
Execute Union and Intersection operations on 'FILM' column of 'fscore' and 'fscrape' tables to understand the data better as well as understand the operations better.
fancur = fancon.cursor()
# Modify the code below
fancur.execute("SELECT...UNION...")
unionout = fancur.fetchall()
fancur.execute("...INTERSECT...")
intersectout = fancur.fetchall()
fancur.execute("...EXCEPT...")
exceptout = fancur.fetchall()
# 'MINUS' keyword is not supported in sqlite module at present. 'EXCEPT' keyword can be used as a substitute.
# The EXCEPT query returns the same output as MINUS.
fancur.execute("SELECT FILM FROM fscore UNION SELECT FILM FROM fscrape")
unionout = fancur.fetchall()
fancur.execute("SELECT FILM FROM fscore INTERSECT SELECT FILM FROM fscrape")
intersectout = fancur.fetchall()
fancur.execute("SELECT FILM FROM fscrape EXCEPT SELECT FILM FROM fscore")
exceptout = fancur.fetchall()
print(unionout[:5],intersectout[:5],exceptout[:5])
print("total number of rows in union=%d, total number of rows in intersection=%d, total number of rows in minus=%d"%(len(unionout),len(intersectout),len(exceptout)))
[(' Like Sunday, Like Rain (2015)',), ("'71 (2015)",), ('1001 Grams (2015)',), ('12 Golden Ducks (2015)',), ('24 Days (2015)',)] [("'71 (2015)",), ('5 Flights Up (2015)',), ('A Little Chaos (2015)',), ('A Most Violent Year (2014)',), ('About Elly (2015)',)] [(' Like Sunday, Like Rain (2015)',), ('1001 Grams (2015)',), ('12 Golden Ducks (2015)',), ('24 Days (2015)',), ('3 1/2 Minutes, Ten Bullets (2015)',)] total number of rows in union=510, total number of rows in intersection=145, total number of rows in minus=364
ref_tmp_var = False
try:
test = [(' Like Sunday, Like Rain (2015)',), ("'71 (2015)",), ('1001 Grams (2015)',), ('12 Golden Ducks (2015)',), ('24 Days (2015)',)]
test2 = [("'71 (2015)",), ('5 Flights Up (2015)',), ('A Little Chaos (2015)',), ('A Most Violent Year (2014)',), ('About Elly (2015)',)]
test3 = [(' Like Sunday, Like Rain (2015)',), ('1001 Grams (2015)',), ('12 Golden Ducks (2015)',), ('24 Days (2015)',), ('3 1/2 Minutes, Ten Bullets (2015)',)]
if test==unionout[:5] and len(unionout)==510 and test2==intersectout[:5] and len(intersectout)==145 and test3==exceptout[:5] and len(exceptout)==364:
ref_assert_var = True
ref_tmp_var = True
else:
ref_assert_var = False
print('Please follow the instructions given and use the same variables provided in the instructions.')
except Exception:
print('Please follow the instructions given and use the same variables provided in the instructions.')
assert ref_tmp_var
True
A Nested query, also referred to as sub-query, is a query within a query. This means that the innermost query is executed first and the output of that query serves as an input to the query which surrounds the embedded query.
From above analysis we can see that fscrape is a bigger list and fscore is a smaller list of movies. Let us retrieve complete Fandango information for all movies that have an IMDB score of more than 7.5
Retrieve FILM, STARS, RATING, VOTES for all movies that have an IMDB rating greater than 7.5 in the fscore table. Note: Do not use 'JOIN' operation to solve this exercise.
# Modify the code below
fancur.execute('SELECT...IN (SELECT...)')
fancur.execute('SELECT * FROM fscrape WHERE FILM IN (SELECT FILM FROM fscore WHERE IMDB>7.5)')
nestout = fancur.fetchall()
print(nestout[:5])
print("total number of rows=%d"%len(nestout))
[('Inside Out (2015)', 4.5, 4.5, 15749), ('Kingsman: The Secret Service (2015)', 4.5, 4.2, 15205), ('Avengers: Age of Ultron (2015)', 5.0, 4.5, 14846), ('Ant-Man (2015)', 5.0, 4.5, 12055), ('Mad Max: Fury Road (2015)', 4.5, 4.3, 10509)] total number of rows=27
ref_tmp_var = False
try:
test = [('Inside Out (2015)', 4.5, 4.5, 15749), ('Kingsman: The Secret Service (2015)', 4.5, 4.2, 15205), ('Avengers: Age of Ultron (2015)', 5.0, 4.5, 14846), ('Ant-Man (2015)', 5.0, 4.5, 12055), ('Mad Max: Fury Road (2015)', 4.5, 4.3, 10509)]
if test==nestout[:5] and len(nestout)==27:
ref_assert_var = True
ref_tmp_var = True
else:
ref_assert_var = False
print('Please follow the instructions given and use the same variables provided in the instructions.')
except Exception:
print('Please follow the instructions given and use the same variables provided in the instructions.')
assert ref_tmp_var
True
A join is an operation that is used to combine data from two tables, given that there is a common column in both of them. There are four types of joins:
Lets understand what they actually mean by executing these operations on the two tables.
Execute inner join operation on the 'fscore' and 'fscrape' tables. Note that FILM is the common column among both tables.
from tabulate import tabulate
fancur.execute('''SELECT...INNER JOIN...ON...''')
templist1 = fancur.fetchall()
headers1 = []
print(tabulate(templist1, headers1))
fancur.execute('''SELECT fscore.FILM, fscore.RottenTomatoes, fscore.Metacritic, fscore.IMDB, fscrape.RATING, fscrape.VOTES
FROM fscore
INNER JOIN fscrape ON fscore.FILM=fscrape.FILM''')
templist1 = fancur.fetchall()
headers1 = ['FILM','rottentomatoes','metacritic','Imdb','fandangorating','fandangovotes']
print(tabulate(templist1, headers1))
print(len(templist1))
FILM rottentomatoes metacritic Imdb fandangorating fandangovotes ---------------------------------------------------------------------- ---------------- ------------ ------ ---------------- --------------- Avengers: Age of Ultron (2015) 74 66 7.8 4.5 14846 Cinderella (2015) 85 67 7.1 4.5 12640 Ant-Man (2015) 80 64 7.8 4.5 12055 Do You Believe? (2015) 18 22 5.4 4.5 1793 Hot Tub Time Machine 2 (2015) 14 29 5.1 3 1021 The Water Diviner (2015) 63 50 7.2 4 397 Irrational Man (2015) 42 53 6.9 3.5 252 Top Five (2014) 86 81 6.5 3.5 3223 Shaun the Sheep Movie (2015) 99 81 7.4 4 896 Love & Mercy (2015) 89 80 7.8 4 864 Far From The Madding Crowd (2015) 84 71 7.2 4 804 Black Sea (2015) 82 62 6.4 3.5 218 Leviathan (2014) 99 92 7.7 3.5 64 Unbroken (2014) 51 59 7.2 4.1 9443 The Imitation Game (2014) 90 73 8.1 4.6 8055 Taken 3 (2015) 9 26 6.1 4.1 6757 Ted 2 (2015) 46 48 6.6 4.1 6437 Southpaw (2015) 59 57 7.8 4.6 5597 Night at the Museum: Secret of the Tomb (2014) 50 47 6.3 4.1 5445 Pixels (2015) 17 27 5.6 4.1 3886 McFarland, USA (2015) 79 60 7.5 4.6 3364 Insidious: Chapter 3 (2015) 59 52 6.3 4.1 3276 The Man From U.N.C.L.E. (2015) 68 55 7.6 4.1 2686 Run All Night (2015) 60 59 6.6 4.1 2066 Trainwreck (2015) 85 75 6.7 4.1 8381 Selma (2014) 99 89 7.5 4.6 7025 Ex Machina (2015) 92 78 7.7 4.1 3458 Still Alice (2015) 88 72 7.5 4.1 1258 Wild Tales (2014) 96 77 8.2 4.1 235 The End of the Tour (2015) 92 84 7.9 4.1 121 Red Army (2015) 96 82 7.7 4.1 54 When Marnie Was There (2015) 89 71 7.8 4.1 46 The Hunting Ground (2015) 92 77 7.5 4.1 42 The Boy Next Door (2015) 10 30 4.6 3.6 2800 Aloha (2015) 19 40 5.5 3.1 2284 The Loft (2015) 11 24 6.3 3.6 811 5 Flights Up (2015) 52 55 6.1 3.6 79 Welcome to Me (2015) 71 67 5.9 3.6 56 Saint Laurent (2015) 51 52 6.3 3.1 43 Maps to the Stars (2015) 60 67 6.3 3.1 35 I'll See You In My Dreams (2015) 94 75 6.9 3.6 281 Timbuktu (2015) 99 91 7.2 3.6 74 About Elly (2015) 97 87 8.2 3.6 43 The Diary of a Teenage Girl (2015) 95 87 7 3.6 38 Kingsman: The Secret Service (2015) 75 58 7.8 4.2 15205 Tomorrowland (2015) 50 60 6.6 3.7 8077 The Divergent Series: Insurgent (2015) 30 42 6.4 4.2 7123 Annie (2014) 27 33 5.2 4.2 6835 Fantastic Four (2015) 9 27 4 2.7 6288 Terminator Genisys (2015) 26 38 6.9 4.2 6272 Pitch Perfect 2 (2015) 67 63 6.7 4.2 4577 Entourage (2015) 32 38 7.1 4.2 4279 The Age of Adaline (2015) 54 51 7.3 4.2 3325 Hot Pursuit (2015) 8 31 4.9 3.7 2618 The DUFF (2015) 71 56 6.6 4.2 2273 Black or White (2015) 39 45 6.6 4.2 1862 Project Almanac (2015) 34 47 6.4 3.7 1834 Ricki and the Flash (2015) 64 54 6.2 3.7 1462 Seventh Son (2015) 12 30 5.5 3.2 1213 Mortdecai (2015) 12 27 5.5 3.2 1196 Unfinished Business (2015) 11 32 5.4 3.2 821 American Ultra (2015) 46 50 6.5 3.7 638 True Story (2015) 45 50 6.3 3.2 540 Child 44 (2015) 26 41 6.4 3.7 308 Dark Places (2015) 26 39 6.3 3.7 55 Birdman (2014) 92 88 7.9 3.7 4194 The Gift (2015) 93 77 7.6 3.7 2680 Unfriended (2015) 60 59 5.9 2.7 2507 Monkey Kingdom (2015) 94 72 7.3 4.2 701 Mr. Turner (2014) 98 94 6.9 3.2 290 Seymour: An Introduction (2015) 100 83 7.7 4.2 41 The Wrecking Crew (2015) 93 67 7.8 4.2 38 American Sniper (2015) 72 72 7.4 4.8 34085 Furious 7 (2015) 81 67 7.4 4.8 33538 The Hobbit: The Battle of the Five Armies (2014) 61 59 7.5 4.3 15337 San Andreas (2015) 50 43 6.5 4.3 9749 Straight Outta Compton (2015) 90 72 8.4 4.8 8096 Vacation (2015) 27 34 6.3 3.8 3815 Chappie (2015) 30 41 7 3.8 3642 Poltergeist (2015) 31 47 5 2.8 2704 Paper Towns (2015) 55 56 6.9 3.8 1750 Big Eyes (2014) 72 62 7 3.8 1501 Blackhat (2015) 34 51 5.4 2.8 1430 Self/less (2015) 20 34 6.6 3.8 1415 Sinister 2 (2015) 13 31 5.5 3.3 973 Little Boy (2015) 20 30 7.4 4.3 811 Me and Earl and The Dying Girl (2015) 81 74 8.2 4.3 624 Maggie (2015) 54 52 5.6 3.3 95 Mad Max: Fury Road (2015) 97 89 8.3 4.3 10509 Spy (2015) 93 75 7.3 4.3 9418 The SpongeBob Movie: Sponge Out of Water (2015) 78 62 6.1 3.3 4493 Paddington (2015) 98 77 7.2 4.3 4045 Dope (2015) 87 72 7.5 4.3 2195 What We Do in the Shadows (2015) 96 75 7.6 4.3 259 The Overnight (2015) 82 65 6.9 3.3 110 The Salt of the Earth (2015) 96 83 8.4 4.3 83 Song of the Sea (2014) 99 86 8.2 4.3 66 Fifty Shades of Grey (2015) 25 46 4.2 3.9 34846 Get Hard (2015) 29 34 6.1 3.9 5933 Focus (2015) 57 56 6.6 3.9 4933 Jupiter Ascending (2015) 26 40 5.5 3.4 4122 The Gallows (2015) 16 30 4.4 2.9 1896 The Second Best Exotic Marigold Hotel (2015) 62 51 6.6 3.9 1870 Strange Magic (2015) 17 25 5.7 3.4 1117 The Gunman (2015) 17 39 5.8 3.4 996 Hitman: Agent 47 (2015) 7 28 5.9 3.9 917 Cake (2015) 49 49 6.5 3.4 482 The Vatican Tapes (2015) 13 37 4.6 2.9 210 A Little Chaos (2015) 40 51 6.4 3.9 83 The 100-Year-Old Man Who Climbed Out the Window and Disappeared (2015) 67 58 7.1 3.9 63 Escobar: Paradise Lost (2015) 52 56 6.6 3.9 48 Into the Woods (2014) 71 69 6 3.4 13055 It Follows (2015) 96 83 6.9 2.9 2097 Inherent Vice (2014) 73 81 6.7 2.9 1078 A Most Violent Year (2014) 90 79 7.1 3.4 675 While We're Young (2015) 83 76 6.4 2.9 449 Clouds of Sils Maria (2015) 89 78 6.8 3.4 162 Testament of Youth (2015) 81 77 7.3 3.9 127 Infinitely Polar Bear (2015) 80 64 7.2 3.9 124 Phoenix (2015) 99 91 7.2 3.4 70 The Wolfpack (2015) 84 75 7.1 3.4 66 The Stanford Prison Experiment (2015) 84 68 7.1 3.9 51 Tangerine (2015) 95 86 7.4 3.9 36 Magic Mike XXL (2015) 62 60 6.3 4.4 9363 Home (2015) 45 55 6.7 4.4 7705 The Wedding Ringer (2015) 27 35 6.7 4.4 6506 Woman in Gold (2015) 52 51 7.4 4.4 2435 The Last Five Years (2015) 60 60 6 4.4 99 Amy (2015) 97 85 8 4.4 729 Jurassic World (2015) 71 59 7.3 4.5 34390 Minions (2015) 54 56 6.7 4 14998 Max (2015) 35 47 7 4.5 3412 Paul Blart: Mall Cop 2 (2015) 5 13 4.3 3.5 3054 The Longest Ride (2015) 31 33 7.2 4.5 2603 The Lazarus Effect (2015) 14 31 5.2 3 1651 The Woman In Black 2 Angel of Death (2015) 22 42 4.9 3 1333 Danny Collins (2015) 77 58 7.1 4 531 Spare Parts (2015) 52 50 7.2 4.5 450 Serena (2015) 18 36 5.4 3 50 Inside Out (2015) 98 94 8.6 4.5 15749 Mr. Holmes (2015) 87 67 7.4 4 1348 '71 (2015) 97 83 7.2 3.5 192 Two Days, One Night (2014) 97 89 7.4 3.5 118 Gett: The Trial of Viviane Amsalem (2015) 100 90 7.8 3.5 59 Kumiko, The Treasure Hunter (2015) 87 68 6.7 3.5 41 145
ref_tmp_var = False
try:
test = [('Avengers: Age of Ultron (2015)',74,66,7.8,4.5,14846),('Cinderella (2015)',85,67,7.1,4.5,12640),('Ant-Man (2015)',80,64,7.8,4.5,12055),('Do You Believe? (2015)',18,22,5.4,4.5,1793),('Hot Tub Time Machine 2 (2015)',14,29,5.1,3,1021)]
if test==templist1[:5] and len(templist1)==145:
ref_assert_var = True
ref_tmp_var = True
else:
ref_assert_var = False
print('Please follow the instructions given and use the same variables provided in the instructions.')
except Exception:
print('Please follow the instructions given and use the same variables provided in the instructions.')
assert ref_tmp_var
True
Now that we have seen the results of an 'inner join' operation, Let's replicate the same for a 'left join'.
Execute a left join operation on the 'fscore' and 'fscrape' tables. Note that FILM is the common column among both tables.
fancur.execute()
templist2 =
headers2 = []
fancur.execute('''SELECT fscore.FILM, fscore.RottenTomatoes, fscore.Metacritic, fscore.IMDB, fscrape.RATING, fscrape.VOTES
FROM fscore
LEFT JOIN fscrape ON fscore.FILM=fscrape.FILM''')
templist2 = fancur.fetchall()
headers2 = ['FILM','rottentomatoes','metacritic','Imdb','fandangorating','fandangovotes']
print(tabulate(templist2, headers2))
print(len(templist2))
FILM rottentomatoes metacritic Imdb fandangorating fandangovotes ---------------------------------------------------------------------- ---------------- ------------ ------ ---------------- --------------- Avengers: Age of Ultron (2015) 74 66 7.8 4.5 14846 Cinderella (2015) 85 67 7.1 4.5 12640 Ant-Man (2015) 80 64 7.8 4.5 12055 Do You Believe? (2015) 18 22 5.4 4.5 1793 Hot Tub Time Machine 2 (2015) 14 29 5.1 3 1021 The Water Diviner (2015) 63 50 7.2 4 397 Irrational Man (2015) 42 53 6.9 3.5 252 Top Five (2014) 86 81 6.5 3.5 3223 Shaun the Sheep Movie (2015) 99 81 7.4 4 896 Love & Mercy (2015) 89 80 7.8 4 864 Far From The Madding Crowd (2015) 84 71 7.2 4 804 Black Sea (2015) 82 62 6.4 3.5 218 Leviathan (2014) 99 92 7.7 3.5 64 Unbroken (2014) 51 59 7.2 4.1 9443 The Imitation Game (2014) 90 73 8.1 4.6 8055 Taken 3 (2015) 9 26 6.1 4.1 6757 Ted 2 (2015) 46 48 6.6 4.1 6437 Southpaw (2015) 59 57 7.8 4.6 5597 Night at the Museum: Secret of the Tomb (2014) 50 47 6.3 4.1 5445 Pixels (2015) 17 27 5.6 4.1 3886 McFarland, USA (2015) 79 60 7.5 4.6 3364 Insidious: Chapter 3 (2015) 59 52 6.3 4.1 3276 The Man From U.N.C.L.E. (2015) 68 55 7.6 4.1 2686 Run All Night (2015) 60 59 6.6 4.1 2066 Trainwreck (2015) 85 75 6.7 4.1 8381 Selma (2014) 99 89 7.5 4.6 7025 Ex Machina (2015) 92 78 7.7 4.1 3458 Still Alice (2015) 88 72 7.5 4.1 1258 Wild Tales (2014) 96 77 8.2 4.1 235 The End of the Tour (2015) 92 84 7.9 4.1 121 Red Army (2015) 96 82 7.7 4.1 54 When Marnie Was There (2015) 89 71 7.8 4.1 46 The Hunting Ground (2015) 92 77 7.5 4.1 42 The Boy Next Door (2015) 10 30 4.6 3.6 2800 Aloha (2015) 19 40 5.5 3.1 2284 The Loft (2015) 11 24 6.3 3.6 811 5 Flights Up (2015) 52 55 6.1 3.6 79 Welcome to Me (2015) 71 67 5.9 3.6 56 Saint Laurent (2015) 51 52 6.3 3.1 43 Maps to the Stars (2015) 60 67 6.3 3.1 35 I'll See You In My Dreams (2015) 94 75 6.9 3.6 281 Timbuktu (2015) 99 91 7.2 3.6 74 About Elly (2015) 97 87 8.2 3.6 43 The Diary of a Teenage Girl (2015) 95 87 7 3.6 38 Kingsman: The Secret Service (2015) 75 58 7.8 4.2 15205 Tomorrowland (2015) 50 60 6.6 3.7 8077 The Divergent Series: Insurgent (2015) 30 42 6.4 4.2 7123 Annie (2014) 27 33 5.2 4.2 6835 Fantastic Four (2015) 9 27 4 2.7 6288 Terminator Genisys (2015) 26 38 6.9 4.2 6272 Pitch Perfect 2 (2015) 67 63 6.7 4.2 4577 Entourage (2015) 32 38 7.1 4.2 4279 The Age of Adaline (2015) 54 51 7.3 4.2 3325 Hot Pursuit (2015) 8 31 4.9 3.7 2618 The DUFF (2015) 71 56 6.6 4.2 2273 Black or White (2015) 39 45 6.6 4.2 1862 Project Almanac (2015) 34 47 6.4 3.7 1834 Ricki and the Flash (2015) 64 54 6.2 3.7 1462 Seventh Son (2015) 12 30 5.5 3.2 1213 Mortdecai (2015) 12 27 5.5 3.2 1196 Unfinished Business (2015) 11 32 5.4 3.2 821 American Ultra (2015) 46 50 6.5 3.7 638 True Story (2015) 45 50 6.3 3.2 540 Child 44 (2015) 26 41 6.4 3.7 308 Dark Places (2015) 26 39 6.3 3.7 55 Birdman (2014) 92 88 7.9 3.7 4194 The Gift (2015) 93 77 7.6 3.7 2680 Unfriended (2015) 60 59 5.9 2.7 2507 Monkey Kingdom (2015) 94 72 7.3 4.2 701 Mr. Turner (2014) 98 94 6.9 3.2 290 Seymour: An Introduction (2015) 100 83 7.7 4.2 41 The Wrecking Crew (2015) 93 67 7.8 4.2 38 American Sniper (2015) 72 72 7.4 4.8 34085 Furious 7 (2015) 81 67 7.4 4.8 33538 The Hobbit: The Battle of the Five Armies (2014) 61 59 7.5 4.3 15337 San Andreas (2015) 50 43 6.5 4.3 9749 Straight Outta Compton (2015) 90 72 8.4 4.8 8096 Vacation (2015) 27 34 6.3 3.8 3815 Chappie (2015) 30 41 7 3.8 3642 Poltergeist (2015) 31 47 5 2.8 2704 Paper Towns (2015) 55 56 6.9 3.8 1750 Big Eyes (2014) 72 62 7 3.8 1501 Blackhat (2015) 34 51 5.4 2.8 1430 Self/less (2015) 20 34 6.6 3.8 1415 Sinister 2 (2015) 13 31 5.5 3.3 973 Little Boy (2015) 20 30 7.4 4.3 811 Me and Earl and The Dying Girl (2015) 81 74 8.2 4.3 624 Maggie (2015) 54 52 5.6 3.3 95 Mad Max: Fury Road (2015) 97 89 8.3 4.3 10509 Spy (2015) 93 75 7.3 4.3 9418 The SpongeBob Movie: Sponge Out of Water (2015) 78 62 6.1 3.3 4493 Paddington (2015) 98 77 7.2 4.3 4045 Dope (2015) 87 72 7.5 4.3 2195 What We Do in the Shadows (2015) 96 75 7.6 4.3 259 The Overnight (2015) 82 65 6.9 3.3 110 The Salt of the Earth (2015) 96 83 8.4 4.3 83 Song of the Sea (2014) 99 86 8.2 4.3 66 Fifty Shades of Grey (2015) 25 46 4.2 3.9 34846 Get Hard (2015) 29 34 6.1 3.9 5933 Focus (2015) 57 56 6.6 3.9 4933 Jupiter Ascending (2015) 26 40 5.5 3.4 4122 The Gallows (2015) 16 30 4.4 2.9 1896 The Second Best Exotic Marigold Hotel (2015) 62 51 6.6 3.9 1870 Strange Magic (2015) 17 25 5.7 3.4 1117 The Gunman (2015) 17 39 5.8 3.4 996 Hitman: Agent 47 (2015) 7 28 5.9 3.9 917 Cake (2015) 49 49 6.5 3.4 482 The Vatican Tapes (2015) 13 37 4.6 2.9 210 A Little Chaos (2015) 40 51 6.4 3.9 83 The 100-Year-Old Man Who Climbed Out the Window and Disappeared (2015) 67 58 7.1 3.9 63 Escobar: Paradise Lost (2015) 52 56 6.6 3.9 48 Into the Woods (2014) 71 69 6 3.4 13055 It Follows (2015) 96 83 6.9 2.9 2097 Inherent Vice (2014) 73 81 6.7 2.9 1078 A Most Violent Year (2014) 90 79 7.1 3.4 675 While We're Young (2015) 83 76 6.4 2.9 449 Clouds of Sils Maria (2015) 89 78 6.8 3.4 162 Testament of Youth (2015) 81 77 7.3 3.9 127 Infinitely Polar Bear (2015) 80 64 7.2 3.9 124 Phoenix (2015) 99 91 7.2 3.4 70 The Wolfpack (2015) 84 75 7.1 3.4 66 The Stanford Prison Experiment (2015) 84 68 7.1 3.9 51 Tangerine (2015) 95 86 7.4 3.9 36 Magic Mike XXL (2015) 62 60 6.3 4.4 9363 Home (2015) 45 55 6.7 4.4 7705 The Wedding Ringer (2015) 27 35 6.7 4.4 6506 Woman in Gold (2015) 52 51 7.4 4.4 2435 The Last Five Years (2015) 60 60 6 4.4 99 Mission: Impossible – Rogue Nation (2015) 92 75 7.8 Amy (2015) 97 85 8 4.4 729 Jurassic World (2015) 71 59 7.3 4.5 34390 Minions (2015) 54 56 6.7 4 14998 Max (2015) 35 47 7 4.5 3412 Paul Blart: Mall Cop 2 (2015) 5 13 4.3 3.5 3054 The Longest Ride (2015) 31 33 7.2 4.5 2603 The Lazarus Effect (2015) 14 31 5.2 3 1651 The Woman In Black 2 Angel of Death (2015) 22 42 4.9 3 1333 Danny Collins (2015) 77 58 7.1 4 531 Spare Parts (2015) 52 50 7.2 4.5 450 Serena (2015) 18 36 5.4 3 50 Inside Out (2015) 98 94 8.6 4.5 15749 Mr. Holmes (2015) 87 67 7.4 4 1348 '71 (2015) 97 83 7.2 3.5 192 Two Days, One Night (2014) 97 89 7.4 3.5 118 Gett: The Trial of Viviane Amsalem (2015) 100 90 7.8 3.5 59 Kumiko, The Treasure Hunter (2015) 87 68 6.7 3.5 41 146
ref_tmp_var = False
try:
test = [('Avengers: Age of Ultron (2015)',74,66,7.8,4.5,14846),('Cinderella (2015)',85,67,7.1,4.5,12640),('Ant-Man (2015)',80,64,7.8,4.5,12055),('Do You Believe? (2015)',18,22,5.4,4.5,1793),('Hot Tub Time Machine 2 (2015)',14,29,5.1,3,1021)]
if test==templist2[:5] and len(templist2)==146:
ref_assert_var = True
ref_tmp_var = True
else:
ref_assert_var = False
print('Please follow the instructions given and use the same variables provided in the instructions.')
except Exception:
print('Please follow the instructions given and use the same variables provided in the instructions.')
assert ref_tmp_var
True
From above code we can see that INNER JOIN and LEFT JOIN produce almost the same output. This is because the fscore table is a subset of fscrape table.
As of now, RIGHT OUTER JOIN and FULL OUTER JOIN are not supported by the sqlite module.
Stored procedures, triggers and materialized views are some of the advanced SQL concepts. A stored procedure is a set of instructions/SQL queries which performs a logical action on the database server and is executed on a recurring basis, much like a standard operating procedure. A trigger is a type of stored procedure, which is set to automatically execute when a certain event occurs in the database (Eg. Say a record in a specific table is updated or deleted). A view is the output of a sql query which is presented in the console, but not stored on disk. When this output is stored on disk, in the form of a structure, in order to retrieve it frequently (instead of having to run the query again), it is called a materialized view.
Some additional reading on the above topics: Stored procedures: https://en.wikipedia.org/wiki/Stored_procedure, https://www.tutorialspoint.com/t_sql/t_sql_stored_procedures.htm
Triggers: https://www.tutorialspoint.com/plsql/plsql_triggers.htm
Materialized views: https://en.wikipedia.org/wiki/Materialized_view, http://www.postgresqltutorial.com/postgresql-materialized-views/
The above concepts are not supported by sqlite module.
ref_tmp_var = False
ref_tmp_var = True
assert ref_tmp_var