sql portfolio

01 November 2022
sql,
long

Here are the gists from my Khan Academy SQL portfolio

App Impersonator

Jump to section titled: App Impersonator

Impersonates friend and book freatures of goodreads.com with UPDATE and DELETE.

	/* What does the app's [goodreads.com] SQL look like? */

	CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, password TEXT);

	CREATE TABLE friends (id INTEGER PRIMARY KEY, user_id INTEGER, friend_id INTEGER);

	CREATE TABLE books (id INTEGER PRIMARY KEY, title INTEGER, year INTEGER, pages INTEGER);

	/* Standard shelves CASE read, reading, to read */
	CREATE TABLE book_status (id INTEGER PRIMARY KEY, user_id INTEGER, book_id INTEGER, status TEXT);

	INSERT INTO users (name, email, password)
	VALUES ("Izak", "izak@mail.com", "Izak!"),
	("Jonotan Leondor","jon@email.com","Jon!"),
	("George Miller","george@email.com","George!"),
	("Jacques Lacan","jacques@email.com","Jacques!"),
	("Slavoj Zizek","slavoj@email.com","Slavoj!")
	;
	INSERT INTO friends (user_id, friend_id)
	VALUES (1, 2),
	(1, 3),
	(2, 3),
	(1, 5),
	(4, 5)
	;
	INSERT INTO books (title, year, pages)
	VALUES ("Fahrenheit 451", 1953, 256),
	("Jane Eyre", 1847, 536),
	("1984", 1949, 328),
	("The Great Gatsby", 1925, 208),
	("Hamlet", 1603, 176)
	;
	INSERT INTO book_status (user_id, book_id, status)
	VALUES (1, 5, "read"),
	(2, 4, "reading"),
	(3, 3, "to read"),
	(4, 2, "read"),
	(5, 1, "reading")
	;

	/* Select user and friend */
	SELECT a.name, b.name FROM friends
	JOIN users a
	ON friends.friend_id = a.id
	JOIN users b
	ON friends.user_id = b.id;
	/* Select user, book, and status */
	SELECT users.name, books.title, book_status.status FROM book_status
	JOIN users
	ON users.id = book_status.user_id
	JOIN books
	ON books.id = book_status.book_id;

	/* Insert friends, books, status */
	INSERT INTO users (name, email, password)
	VALUES("Suzzane Collines", "sue@email.com", "Sue!");
	INSERT INTO books (title, year, pages)
	VALUES("100 Love Sonnets", 1959, 160);
	INSERT INTO friends (user_id, friend_id)
	VALUES (1, 6), (4, 6);
	INSERT INTO book_status (user_id, book_id, status)
	VALUES (6, 6, "read"), (2, 6, "reading");

	/* Select additional user and friend */
	SELECT a.name, b.name FROM friends
	JOIN users a
	ON friends.friend_id = a.id
	JOIN users b
	ON friends.user_id = b.id;
	/* Select additional user, book, and status */
	SELECT users.name, books.title, book_status.status FROM book_status
	JOIN users
	ON users.id = book_status.user_id
	JOIN books
	ON books.id = book_status.book_id;

	/* Update friends, status */
	BEGIN TRANSACTION;
	UPDATE friends SET friend_id = 2 WHERE id = 6;
	UPDATE book_status SET status = "read" WHERE id = 2;
	COMMIT;

	/* Select updated user and friend */
	SELECT a.name, b.name FROM friends
	JOIN users a
	ON friends.friend_id = a.id
	JOIN users b
	ON friends.user_id = b.id;
	/* Select updated user, book, and status */
	SELECT users.name, books.title, book_status.status FROM book_status
	JOIN users
	ON users.id = book_status.user_id
	JOIN books
	ON books.id = book_status.book_id;

	/* Update friends, status */
	BEGIN TRANSACTION;
	DELETE FROM friends WHERE id = 6;
	COMMIT;

	/* Select deleteed user and friend */
	SELECT a.name, b.name FROM friends
	JOIN users a
	ON friends.friend_id = a.id
	JOIN users b
	ON friends.user_id = b.id;

view raw appImpersonator.sql hosted with ❤ by GitHub

Book Store

Jump to section titled: Book Store

Stores books with title, author, year, and price.

	/*
	Author: Izak Schmidlkofer
	Date: 11/22/2022
	Description: Books store with book id, title, author, year, and price
	*/

	CREATE TABLE book_store (id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER, price INTEGER);

	INSERT INTO book_store VALUES (1, "To Kill A Mocking Bird", "Harper Lee", 1960, 12);
	INSERT INTO book_store VALUES (2, "The Great Gatsby", "F. Scott Fitzgerald", 1925, 8);
	INSERT INTO book_store VALUES (3, "Notes From Underground", "Fyodor Dostoevsky", 1864, 8);
	INSERT INTO book_store VALUES (4, "Pride and Prejudice", "Jane Austen", 1813, 12);
	INSERT INTO book_store VALUES (5, "Fahrenheit 451", "Ray Bradbury", 1953, 8);

	INSERT INTO book_store VALUES (6, "Great Expectations", "Charles Dickens", 1861, 15);
	INSERT INTO book_store VALUES (7, "A Tale of Two Cities", "Charles Dickens", 1859, 15);
	INSERT INTO book_store VALUES (8, "Romeo and Juliet", "William Shakespeare", 1597, 12);
	INSERT INTO book_store VALUES (9, "Macbeth", "William Shakespeare", 1623, 10);
	INSERT INTO book_store VALUES (10, "Hamlet", "William Shakespeare", 1601, 10);

	INSERT INTO book_store VALUES (11, "Leaves of Grass", "Walt Whitman", 1855, 15);
	INSERT INTO book_store VALUES (12, "Phänomenologie des Geistes", "G.W.F. Hegel", 1807, 18);
	INSERT INTO book_store VALUES (13, "The Bluest Eye", "Toni Morrison", 1970, 8);
	INSERT INTO book_store VALUES (14, "The Invisble Man", "Ralph Ellison", 1952, 12);
	INSERT INTO book_store VALUES (15, "The Importance of Being Earnest", "Oscar Wilde", 1895, 10);

	SELECT title, price FROM book_store ORDER BY price;
	SELECT AVG(price) from book_store;

view raw bookStore.sql hosted with ❤ by GitHub

Data Dig

Jump to section titled: Data Dig

Uses KA movie database to generate basic statistic.

	/* Put your data in here and query it! */
	/* 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);

	/What are average, max, and min values in the data?/
	SELECT title, MAX(Worldwide), MAX(Domestic), MAX(Overseas) FROM topmovies;
	SELECT title, MIN(Worldwide), MIN(Domestic), MIN(Overseas) FROM topmovies;
	SELECT AVG(Worldwide), AVG(Domestic), AVG(Overseas) FROM topmovies;

	/What about those numbers per category in the data (using HAVING)?/
	SELECT studio, AVG(Worldwide) AS avg_worldwide FROM topmovies
	GROUP BY studio
	HAVING avg_worldwide > 863
	;
	SELECT studio, AVG(Domestic) AS avg_domestic FROM topmovies
	GROUP BY studio
	HAVING avg_domestic > 307
	;
	SELECT studio, AVG(Overseas) AS avg_overseas FROM topmovies
	GROUP BY studio
	HAVING avg_overseas > 556
	;

	/What ways are there to group the data values that don’t exist yet (using CASE)?/
	SELECT title, worldwide,
	CASE
	WHEN Worldwide > 863 THEN "above average"
	ELSE "below average"
	END as "avg_worldwide"
	FROM topmovies;
	SELECT COUNT(*),
	CASE
	WHEN Worldwide > 863 THEN "above average"
	ELSE "below average"
	END as "avg_worldwide"
	FROM topmovies
	GROUP BY avg_worldwide;

	SELECT COUNT(*),
	CASE
	WHEN Studio IN (SELECT Studio FROM topmovies GROUP BY Studio HAVING AVG(Worldwide) > 863) THEN "above average studio"
	ELSE "below average studio"
	END as "avg_worldwide"
	FROM topmovies
	GROUP BY avg_worldwide;

	/What interesting ways are there to filter the data (using AND/OR)?/
	SELECT COUNT(*),
	CASE
	WHEN Studio IN (SELECT Studio FROM topmovies GROUP BY Studio HAVING AVG(Worldwide) > 863) AND Worldwide > 863 THEN "above average studio and film"
	WHEN Worldwide > 863 THEN "above average film"
	ELSE "below average film"
	END as "avg_worldwide"
	FROM topmovies
	GROUP BY avg_worldwide;

	SELECT title, Domestic, Overseas FROM topmovies
	WHERE Domestic < 307 AND Overseas > 555
	ORDER BY Overseas;

view raw dataDig.sql hosted with ❤ by GitHub

Famous People

Jump to section titled: Famous People

Uses JOIN for relational structures in data gathered manually.

	/* Create table about the people and what they do here */
	CREATE TABLE famous_people (id INTEGER PRIMARY KEY,
	fullname TEXT, birth_year INTEGER);

	INSERT INTO famous_people (fullname, birth_year)
	VALUES ("G.W.F Hegel", 1770),
	("Karl Marx", 1818),
	("Friedrich Nietzsche", 1844),
	("Michel Foucault", 1926),
	("Louis Althusser", 1918)
	;

	/* Who they influenced */
	CREATE TABLE influenced (id INTEGER PRIMARY KEY,
	person_id INTEGER, influenced_id INTEGER);

	INSERT INTO influenced (person_id, influenced_id)
	VALUES (1, 2),
	(1, 3),
	(3, 4),
	(2, 5)
	;

	/* What they accomplished */
	CREATE TABLE accomplishments (id INTEGER PRIMARY KEY, accomplishment TEXT, person_id INTEGER);

	INSERT INTO accomplishments (accomplishment, person_id)
	VALUES ("Phenomenology of Spirit", 1),
	("Philosophy of Right", 1),
	("The Science of Logic", 1),
	("Communist Manifesto", 2),
	("Das Kapital", 2),
	("On the Geneology of Morals", 3),
	("Thus Spoke Zarathustra", 3),
	("The Order of Things", 4),
	("The Archeology of Knowledge", 4),
	("On Ideology", 5),
	("Reading Capital", 5)
	;


	SELECT a.fullname, b.fullname FROM influenced
	JOIN famous_people a
	ON influenced.person_id = a.id
	JOIN famous_people b
	ON influenced.influenced_id = b.id;

	SELECT famous_people.fullname, accomplishments.accomplishment FROM famous_people
	JOIN accomplishments
	ON famous_people.id = accomplishments.person_id;

view raw gistfile1.txt hosted with ❤ by GitHub

← Previous
beyond stokes theorem
Next →
musician website

sql portfolio

App Impersonator#

Book Store#

Data Dig#

Famous People#

App Impersonator

Book Store

Data Dig

Famous People