Top 100 Movies

top_movies.sql

/* Source: http://www.boxofficemojo.com/alltime/world/ */
CREATE TABLE topmovies(
   Rank        INTEGER,
   Title       TEXT,
   Studio      TEXT,
   Worldwide   REAL,
   Domestic    REAL,
   DomesticPct REAL,
   Overseas    REAL,
   OverseasPct REAL,
   Year        INTEGER
);

INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (1,'Avatar','Fox',2788.00,760.50,0.27,2027.50,0.73,2009);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (2,'Titanic','Par.',2186.80,658.70,0.30,1528.10,0.70,1997);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (3,'Marvel''s The Avengers','BV',1518.60,623.40,0.41,895.20,0.59,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (4,'Harry Potter and the Deathly Hallows Part 2','WB',1341.50,381.00,0.28,960.50,0.72,2011);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (5,'Furious 7','Uni.',1322.20,321.20,0.24,1001.00,0.76,2015);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (6,'Frozen','BV',1274.20,400.70,0.31,873.50,0.69,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (7,'Iron Man 3','BV',1215.40,409.00,0.34,806.40,0.66,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (8,'Transformers: Dark of the Moon','P/DW',1123.80,352.40,0.31,771.40,0.69,2011);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (9,'The Lord of the Rings: The Return of the King','NL',1119.90,377.80,0.34,742.10,0.66,2003);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (10,'Skyfall','Sony',1108.60,304.40,0.28,804.20,0.73,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (11,'Transformers: Age of Extinction','Par.',1091.40,245.40,0.23,846.00,0.78,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (12,'The Dark Knight Rises','WB',1084.40,448.10,0.41,636.30,0.59,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (13,'Pirates of the Caribbean: Dead Man''s Chest','BV',1066.20,423.30,0.40,642.90,0.60,2006);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (14,'Toy Story 3','BV',1063.20,415.00,0.39,648.20,0.61,2010);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (15,'Pirates of the Caribbean: On Stranger Tides','BV',1045.70,241.10,0.23,804.60,0.77,2011);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (16,'Jurassic Park','Uni.',1029.20,402.50,0.39,626.70,0.61,1993);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (17,'Star Wars: Episode I - The Phantom Menace','Fox',1027.00,474.50,0.46,552.50,0.54,1999);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (18,'Alice in Wonderland (2010)','BV',1025.50,334.20,0.33,691.30,0.67,2010);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (19,'The Hobbit: An Unexpected Journey','WB',1017.00,303.00,0.30,714.00,0.70,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (20,'The Dark Knight','WB',1004.60,534.90,0.53,469.70,0.47,2008);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (21,'The Lion King','BV',987.50,422.80,0.43,564.70,0.57,1994);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (22,'Harry Potter and the Sorcerer''s Stone','WB',974.80,317.60,0.33,657.20,0.67,2001);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (23,'Despicable Me 2','Uni.',970.80,368.10,0.38,602.70,0.62,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (24,'Pirates of the Caribbean: At World''s End','BV',963.40,309.40,0.32,654.00,0.68,2007);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (25,'The Hobbit: The Desolation of Smaug','WB',960.40,258.40,0.27,702.00,0.73,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (26,'Harry Potter and the Deathly Hallows Part 1','WB',960.30,296.00,0.31,664.30,0.69,2010);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (27,'The Hobbit: The Battle of the Five Armies','WB',955.10,255.10,0.27,700.00,0.73,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (28,'Harry Potter and the Order of the Phoenix','WB',939.90,292.00,0.31,647.90,0.69,2007);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (29,'Finding Nemo','BV',936.70,380.80,0.41,555.90,0.59,2003);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (30,'Harry Potter and the Half-Blood Prince','WB',934.40,302.00,0.32,632.50,0.68,2009);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (31,'The Lord of the Rings: The Two Towers','NL',926.00,342.60,0.37,583.50,0.63,2002);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (32,'Shrek 2','DW',919.80,441.20,0.48,478.60,0.52,2004);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (33,'Harry Potter and the Goblet of Fire','WB',896.90,290.00,0.32,606.90,0.68,2005);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (34,'Spider-Man 3','Sony',890.90,336.50,0.38,554.30,0.62,2007);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (35,'Ice Age: Dawn of the Dinosaurs','Fox',886.70,196.60,0.22,690.10,0.78,2009);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (36,'Harry Potter and the Chamber of Secrets','WB',879.00,262.00,0.30,617.00,0.70,2002);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (37,'Ice Age: Continental Drift','Fox',877.20,161.30,0.18,715.90,0.82,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (38,'The Lord of the Rings: The Fellowship of the Ring','NL',871.50,315.50,0.36,556.00,0.64,2001);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (39,'The Hunger Games: Catching Fire','LGF',864.90,424.70,0.49,440.20,0.51,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (40,'Star Wars: Episode III - Revenge of the Sith','Fox',848.80,380.30,0.45,468.50,0.55,2005);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (41,'Transformers: Revenge of the Fallen','P/DW',836.30,402.10,0.48,434.20,0.52,2009);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (42,'The Twilight Saga: Breaking Dawn Part 2','LG/S',829.70,292.30,0.35,537.40,0.65,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (43,'Inception','WB',825.50,292.60,0.35,533.00,0.65,2010);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (44,'Spider-Man','Sony',821.70,403.70,0.49,418.00,0.51,2002);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (45,'Independence Day','Fox',817.40,306.20,0.38,511.20,0.63,1996);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (46,'Shrek the Third','P/DW',799.00,322.70,0.40,476.20,0.60,2007);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (47,'Harry Potter and the Prisoner of Azkaban','WB',796.70,249.50,0.31,547.10,0.69,2004);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (48,'E.T.: The Extra-Terrestrial','Uni.',792.90,435.10,0.55,357.80,0.45,1982);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (49,'Fast & Furious 6','Uni.',788.70,238.70,0.30,550.00,0.70,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (50,'Indiana Jones and the Kingdom of the Crystal Skull','Par.',786.60,317.10,0.40,469.50,0.60,2008);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (51,'Spider-Man 2','Sony',783.80,373.60,0.48,410.20,0.52,2004);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (52,'Star Wars','Fox',775.40,461.00,0.60,314.40,0.41,1977);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (53,'Guardians of the Galaxy','BV',774.20,333.20,0.43,441.00,0.57,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (54,'2012','Sony',769.70,166.10,0.22,603.60,0.78,2009);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (55,'Maleficent','BV',758.40,241.40,0.32,517.00,0.68,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (56,'The Da Vinci Code','Sony',758.20,217.50,0.29,540.70,0.71,2006);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (57,'The Amazing Spider-Man','Sony',757.90,262.00,0.35,495.90,0.65,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (58,'Shrek Forever After','P/DW',752.60,238.70,0.32,513.90,0.68,2010);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (59,'The Hunger Games: Mockingjay - Part 1','LGF',752.10,337.10,0.45,415.00,0.55,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (60,'X-Men: Days of Future Past','Fox',748.10,233.90,0.31,514.20,0.69,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (61,'Madagascar 3: Europe''s Most Wanted','P/DW',746.90,216.40,0.29,530.50,0.71,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (62,'The Chronicles of Narnia: The Lion, the Witch and the Wardrobe','BV',745.00,291.70,0.39,453.30,0.61,2005);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (63,'Monsters University','BV',743.60,268.50,0.36,475.10,0.64,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (64,'The Matrix Reloaded','WB',742.10,281.60,0.38,460.60,0.62,2003);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (65,'Up','BV',731.30,293.00,0.40,438.30,0.60,2009);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (66,'Gravity','WB',716.40,274.10,0.38,442.30,0.62,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (67,'Captain America: The Winter Soldier','BV',714.80,259.80,0.36,455.00,0.64,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (68,'The Twilight Saga: Breaking Dawn Part 1','Sum.',712.20,281.30,0.40,430.90,0.61,2011);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (69,'The Twilight Saga: New Moon','Sum.',709.80,296.60,0.42,413.20,0.58,2009);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (70,'Transformers','P/DW',709.70,319.20,0.45,390.50,0.55,2007);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (71,'The Amazing Spider-Man 2','Sony',709.00,202.90,0.29,506.10,0.71,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (72,'Dawn of the Planet of the Apes','Fox',708.80,208.50,0.29,500.30,0.71,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (73,'The Twilight Saga: Eclipse','Sum.',698.50,300.50,0.43,398.00,0.57,2010);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (74,'Mission: Impossible - Ghost Protocol','Par.',694.70,209.40,0.30,485.30,0.70,2011);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (75,'The Hunger Games','LGF',691.20,408.00,0.59,283.20,0.41,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (76,'Forrest Gump','Par.',677.90,330.30,0.49,347.70,0.51,1994);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (77,'The Sixth Sense','BV',672.80,293.50,0.44,379.30,0.56,1999);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (78,'Interstellar','Par.',672.70,188.00,0.28,484.70,0.72,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (79,'Man of Steel','WB',668.00,291.00,0.44,377.00,0.56,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (80,'Kung Fu Panda 2','P/DW',665.70,165.20,0.25,500.40,0.75,2011);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (81,'Ice Age: The Meltdown','Fox',660.90,195.30,0.30,465.60,0.70,2006);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (82,'Pirates of the Caribbean: The Curse of the Black Pearl','BV',654.30,305.40,0.47,348.90,0.53,2003);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (83,'Big Hero 6','BV',652.00,222.40,0.34,429.60,0.66,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (84,'Star Wars: Episode II - Attack of the Clones','Fox',649.40,310.70,0.48,338.70,0.52,2002);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (85,'Thor: The Dark World','BV',644.80,206.40,0.32,438.40,0.68,2013);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (86,'Kung Fu Panda','P/DW',631.70,215.40,0.34,416.30,0.66,2008);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (87,'The Incredibles','BV',631.40,261.40,0.41,370.00,0.59,2004);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (88,'Fast Five','Uni.',626.10,209.80,0.34,416.30,0.67,2011);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (89,'Hancock','Sony',624.40,227.90,0.37,396.40,0.64,2008);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (90,'MIB 3','Sony',624.00,179.00,0.29,445.00,0.71,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (91,'Iron Man 2','Par.',623.90,312.40,0.50,311.50,0.50,2010);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (92,'Ratatouille','BV',623.70,206.40,0.33,417.30,0.67,2007);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (93,'How to Train Your Dragon 2','Fox',618.90,177.00,0.29,441.90,0.71,2014);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (94,'The Lost World: Jurassic Park','Uni.',618.60,229.10,0.37,389.60,0.63,1997);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (95,'The Passion of the Christ','NM',611.90,370.80,0.61,241.10,0.39,2004);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (96,'Mamma Mia!','Uni.',609.80,144.10,0.24,465.70,0.76,2008);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (97,'Life of Pi','Fox',609.00,125.00,0.21,484.00,0.80,2012);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (98,'Madagascar: Escape 2 Africa','P/DW',603.90,180.00,0.30,423.90,0.70,2008);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (99,'Casino Royale','Sony',599.00,167.40,0.28,431.60,0.72,2006);
INSERT INTO topmovies(Rank,Title,Studio,Worldwide,Domestic,DomesticPct,Overseas,OverseasPct,Year) VALUES (100,'Tangled','BV',591.80,200.80,0.34,391.00,0.66,2010);

I've seen a few people have problems with this data set when trying, for example, to find the average worldwide takings, since the values are text. I don't know much SQL, so don't know if there is a good solution for this. There seems to be a MONEY type, but I don't think it's implemented on KA (?).

petercollingridge
you mean that?:
SELECT AVG(CAST(SUBSTR(domestic_money, 2) AS REAL)) AS AVG_MONEY FROM top_movies;

@petercollingridge

AVG(domestic_money) doesn't work because of the $ in front. The fact that the data type is TEXT won't cause an error--look at AVG(year)--that works just fine.

That being said, AVG(SUBSTR(DOMESTIC_MONEY,2)) is all you need.

Even if a workaround works for the exercise, I don't want to encourage my new hires to learn that an optimistic substr is OK in a production system.

I find that the worldwide figure data for Avatar, Titanic and Furious 7 has some problems. The integer I get is always 2 for Avatar, please help.

Used Replace function to replace the comma, the substring function to start from the second position to omit the $ sign, and finally the convert function to convert to decimal data type.

select convert(substring(replace(worldwide,',',''),2),decimal(6,2)) as worldwide_gross from top_movies;

I noticed some carrots at the end of some of the year cells. What do those mean?

@abatt0ir: The source (http://www.boxofficemojo.com/alltime/world/) has notes that go with the chart. One of them explains the carot:
"^ Indicates the movie made its gross over multiple releases."

This is a really good example of data cleansing requirements for my students though they'd have almost no chance of understanding how to do this from the course content covered so far.

In order to return an average worldwide gross value figure (assuming in millions of $US) from the dataset provided that is correct as per data on 7Feb16 ($862.84) and formatted with a dollar sign at the front, you'd need to do the following.
SELECT '$' || ROUND(AVG(CAST(SUBSTR(REPLACE(worldwide,',',''),2) AS REAL)),2) AS AVG_WorldWide_Gross_in_Million_USD FROM top_movies WHERE CAST(SUBSTR(REPLACE(worldwide,',',''),2) AS REAL) != 0;

Thanks to all who have provided feedback in the comments above.

Is it better practice to just Find and replace in notepad all the $ or use the cast and all that ?

Why the first raw duplicates column names? Also it looks that starting with domestic_percent it doesn't match.

I was trying on khan academy to use this data for the SQL "Data dig" project but ran into a weird result after running this sql statement:

/* show how much money the Pirates series made */
select "All Pirates Series", SUM(CAST(replace(trim(worldwide,'$'),',','') as REAL))
as "$ Total WorldWide" from top_movies where title like 'Pirates%';

Instead of the expected value of 3729.6, the select returned 3729.6000000000003 . I cannot even guess why at this point. I tried using FLOA type as well in case it makes any diff. I even tried an extra trim around the replace statement in case there were extra spaces messing things up. What am I missing? (thanks in advance)

In order to play around with this data set on Khan Academy, use the following style of code rather than simply using the provided column name:
CAST(REPLACE(SUBSTR(worldwide,2),',','')AS real)

• Here I have taken the column 'worldwide' and started considering the values only from the second position using SUBSTR so that dollar signs are ignored.

• Then I have used REPLACE to basically replace the commas with blanks.

• Finally I have changed datatype from string to real using CAST.

Hope this helps!

Why is it not possible for me to use TOP keyword in the editor? I have found the keyword LIMIT to be working but TOP gives a syntax error :(

Since within the raw data, there are some years that end up with '^', which will cause duplicate value when remove the '^' by SUBSTR, here comes my question, if want to make a query to select those years that total yearly worldwide gross higher than some numbers such as 1000, how to do it. my solution seems can't remove the duplicated year.

SELECT DISTINCT REPLACE(year,'^','') AS Year, ROUND(SUM(REPLACE(SUBSTR(worldwide,2),',',''))) AS sum_world_gross FROM top_movies WHERE worldwide !='Worldwide' GROUP BY Year HAVING ROUND(SUM(REPLACE(SUBSTR(worldwide,2),',',''))) > 1000;

The result is weird, for example, there are 2 2008 with different sum_world_gross

In order to play around with this data set on Khan Academy, use the following style of code rather than simply using the provided column name:
CAST(REPLACE(SUBSTR(worldwide,2),',','')AS real)

• Here I have taken the column 'worldwide' and started considering the values only from the second position using SUBSTR so that dollar signs are ignored.
• Then I have used REPLACE to basically replace the commas with blanks.
• Finally I have changed datatype from string to real using CAST.

Hope this helps!

It did. Thanks. A shame this wasn't included in the actual course (so far anyway).

What's this CAST(REPLACE(SUBSTR())) about? Is this even covered in the tutorial? where do I put this.

Hi,
Is there some reason why all variables are "TEXT", when there was possibility of INTEGER or DECIMAL?

Thank you

Hi,
i have same problem but i find new solution:

SELECT * FROM top_movies;
SELECT studio, AVG(worldwide) as avg_worldwide FROM top_movies group by studio ;/*(bad response)*/
SELECT studio, AVG(replace(worldwide,'$','')) as avg_worldwide FROM top_movies group by studio ;/*(bad response, i check in excel: avg all valueBV=871,1)*/
SELECT studio, AVG(substr(worldwide, 2 , 10)) as avg_worldwide FROM top_movies group by studio;/*(bad response)*/
SELECT studio, AVG(substr(worldwide, 2 , 10)) as avg_worldwide FROM top_movies group by studio,worldwide;/*(i try group by studio and worldwide and i found inaccuracy of values from response)*/

UPDATE top_movies SET worldwide = replace(worldwide,',','');
UPDATE top_movies SET worldwide = replace(worldwide,'$','');

SELECT studio, AVG(worldwide) as avg_worldwide FROM top_movies group by studio ;/*heureka it's good response*/

Hope this helps!

Hi,
What I did was to copy all the data in a txt and replace all the ´$´ with ´´ and then used that info.

I love Khan Academy but they have a tendency to challenge you with tests that require advanced methods they don't teach; offering this table as an option for one of their projects really takes the cake. Also, none of the suggestions in this thread work for said project.

@aaronhhelton Thanks for the feedback! You're right, this dataset has a lot of value that needs cleaning. I'll look for a new data set that's more appropriate for the project level.
(And sorry I missed so much conversation on this gist - y'all came up with some great approaches to cleaning it)

Here are some additional data sets for the project:
Pokemon - https://gist.github.com/pamelafox/26b9357c6120d3d0d27c2b90c950e8c6
Superbowls - https://gist.github.com/pamelafox/3d8ab901def3577a8bfc881f6255d3f6
NBA players of the week - https://gist.github.com/pamelafox/6cda1115e2bcbc43e62cf4ff9d4158c7
NASA astronauts - https://gist.github.com/pamelafox/b98e03caca7d1ec14394a90ec1512cff

I have also updated this gist with cleaned numeric data.

Cool!

Good! It works fine.