amazon sql model DD up -- Dolly Parton

amazon.sql

--I chose to make DVD a format instead
--of the primary noun


CREATE TABLE movies_and_tv (
  	id INTEGER PRIMARY KEY,
	name TEXT,
	year INTEGER,
	rating TEXT
);

-- the code would involve some criteria
-- for which formats to prioritize
CREATE TABLE movie_items (
 	id INTEGER PRIMARY KEY,
 	format TEXT,
 	price REAL,
 	movies_and_tv_id INTEGER
);

CREATE TABLE actors (
	id INTEGER PRIMARY KEY,
	name TEXT
);

-- many to many relationship
CREATE TABLE join_movies_actors (
	id INTEGER PRIMARY KEY,
	movies_and_tv_id INTEGER,
	actors_id INTEGER
);

-- this table can be queried for
-- average stars and total number
-- of reviews for a particular movie
CREATE TABLE reviews (
	id INTEGER PRIMARY KEY,
	author TEXT,
	stars INTEGER,
	content TEXT,
	movies_and_tv_id INTEGER
);

-- with the last table this table can be 
-- queried for most helpful positive review
-- and most helpful negative review
CREATE TABLE reviews_of_reviews (
	id INTEGER PRIMARY KEY,
	was_helpful TEXT,
	reviews_id INTEGER
);

--not filled below
CREATE TABLE customers (
	id INTEGER PRIMARY KEY,
	name TEXT,
	credit_card INTEGER
);

--not filled below
CREATE TABLE purchases (
	id INTEGER PRIMARY KEY,
	movie_items_id INTEGER,
	time_stamp TEXT,
	customer_id INTEGER
);

--not filled below
CREATE TABLE page_views (
	id INTEGER PRIMARY KEY,
	movie_items_id INTEGER,
	time_stamp TEXT,
	customer_id INTEGER
);

-- creates join table for purchased together
-- using criteria WHERE
-- |purchase.timestamp - purchase.timestamp| < 1 week
-- AND purchase.customer_id == purchase.customer_id
-- (something like that)
CREATE TABLE join_purchases (
	id INTEGER PRIMARY KEY,
	purchase1_movie_item_movie_id INTEGER,
	purchase2_movie_item_movie_id INTEGER
);

-- creates join table for purchased after viewing
-- using criteria WHERE
-- purchase.timestamp - view.timestamp < 1 hour
-- AND purchase.customer_id == view.customer_id
-- (something like that)
CREATE TABLE join_views_purchases (
	id INTEGER PRIMARY KEY,
	purchase_movie_item_movie_id INTEGER,
	view_movie_item_movie_id INTEGER
);





-------------------BEGIN DATA----------------------------



INSERT INTO movies_and_tv (id, name, year, rating)
	VALUES 
	(1, "The Chronicles of Narnia", 1111, "PG"),
	(2, "Home Alone", 1111, "PG"),
	(3, "Matrix Reloaded", 1111, "PG-13"),
	(4, "Dune", 1111, "PG-13"),
	(5, "Ghost-Busters", 1111, "PG"),
	(6, "Bill and Ted's Excellent Adventure", 1111, "PG"),
	(7, "The Crucible", 1111, "PG"),
	(8, "Harry Potter", 1111, "PG"),
	(9, "Lord of the Rings", 1111, "PG"),
	(10, "Sin City", 1111, "PG");

INSERT INTO movie_items (id, format, price, movies_and_tv_id)
	VALUES
	(1, "dvd", 21.79, 1),
	(2, "instant_rent", 2.99, 1),
	(3, "instant_buy", 4.99, 1),
	(4, "dvd", 19.79, 2),
	(5, "instant_rent", 2.99, 2),
	(6, "instant_buy", 4.99, 2),
	(7, "dvd", 19.79, 3),
	(8, "instant_rent", 2.99, 3),
	(9, "instant_buy", 4.99, 3),
	(10, "dvd", 19.79, 4),
	(11, "instant_rent", 2.99, 4),
	(12, "instant_buy", 4.99, 4),
	(13, "new_dvd", 9.79, 7),
	(14, "used_dvd", .25, 7),
	(15, "used_dvd", .99, 9),
	(16, "new_dvd", 5.39, 7),
	(17, "used_dvd", .25, 7),
	(18, "used_dvd", .99, 9),
	(19, "dvd", 21.79, 10),
	(20, "instant_rent", 2.99, 10),
	(21, "instant_buy", 4.99, 9),
	(22, "dvd", 19.79, 8),
	(23, "instant_rent", 2.99, 8),
	(24, "instant_buy", 4.99, 8),
	(25, "dvd", 19.79, 7),
	(26, "instant_rent", 2.99, 7),
	(27, "instant_buy", 4.99, 7),
	(28, "dvd", 19.79, 6),
	(29, "instant_rent", 2.99, 6),
	(30, "instant_buy", 4.99, 6),
	(31, "dvd", 19.79, 5),
	(32, "instant_rent", 2.99, 5),
	(33, "instant_buy", 4.99, 5),
	(34, "new_dvd", 6.79, 5),
	(35, "used_dvd", .25, 1),
	(36, "used_dvd", 1.99, 3),
	(37, "new_dvd", 5.39, 4),
	(38, "used_dvd", .25, 6),
	(39, "used_dvd", .99, 8),
	(40, "new_dvd", 11.79, 10),
	(41, "used_dvd", .75, 2),
	(42, "used_dvd", .99, 2),
	(43, "new_dvd", 5.39, 10),
	(44, "used_dvd", .25, 4),
	(45, "used_dvd", .99, 1),
	(46, "instant_buy", 4.99, 10),
	(47, "dvd", 19.79, 9),
	(48, "instant_rent", 2.99, 9);



INSERT INTO actors (id, name)
	VALUES
	(1, "Wynona Ryder"),
	(2, "Keanu Reeves"),
	(3, "Sting"),
	(4, "Dan Akroyd"),
	(5, "Daniel RadCliffe"),
	(6, "Kristen Stewart"),
	(7, "McCauley Culkin"),
	(8, "Liv Tyler"),
	(9, "Elijah Wood"),
	(10, "Liam Neeson"),
	(11, "Tilda Swinton");

INSERT INTO join_movies_actors (id, movies_and_tv_id, actors_id)
	VALUES
	(1, 2, 7),
	(2, 8, 6),
	(3, 8, 5),
	(4, 1, 10),
	(5, 1, 11),
	(6, 3, 2),
	(7, 4, 3),
	(8, 5, 4),
	(9, 6, 2),
	(10, 7, 1),
	(11, 9, 8),
	(12, 9, 9),
	(13, 10, 9);


INSERT INTO reviews (id, author, stars, content, movies_and_tv_id)
	VALUES
	(1, "keanu reeves", 5, "totally awesome acting!!!", 6),
	(2, "roger", 1, "best movie i've seen since the human centipede", 1),
	(3, "christian science review", 1, "where's the button for ban this sacriligous shite??", 5),
	(4, "some guy with nothing to do", 4, "this is what earth will be like soon if people don't stop having babies! ps wtf Sting?", 4),
	(5, "ebert", 1, "two thumbs way up your ass!!", 1),
	(6, "a real wizard", 2, "that's not how you cast an obliviate charm", 8),
	(7, "meathead666", 5, "great movie to watch with a chick! that scene with the dogs eating elija woodes is fucking GROSS she'll be all hiding her face in your biceps! giggadygiggady!!", 10);

INSERT INTO reviews_of_reviews (id, was_helpful, reviews_id)
	VALUES
	(1, "yes", 2),
	(2, "no", 1),
	(3, "no", 7),
	(4, "yes", 7),
	(5, "yes", 7),
	(6, "yes", 7),
	(7, "yes", 3),
	(8, "no", 5),
	(9, "no", 5),
	(10, "yes", 6),
	(11, "no", 1),
	(12, "no", 1),
	(13, "yes", 1),
	(14, "no", 4);





---------------- BEGIN SQL QUERIES -------------------


---------3. "which dvd is the most expensive?"--------



SELECT * FROM movies_and_tv, movie_items WHERE
	movies_and_tv.id = movie_items.movies_and_tv_id AND
	movie_items.price = (SELECT MAX(price) FROM movie_items);



---------4. "What is the total cost of any 2?"--------



SELECT SUM(price) FROM movie_items, movies_and_tv WHERE
	movie_items.format = "dvd" AND
	movie_items.movies_and_tv_id = movies_and_tv.id AND
	(movies_and_tv.name = "The Crucible" OR movies_and_tv.name = "Dune");



---------5. "Return just the titles for all the DVD's in the database."--------


SELECT name FROM movies_and_tv;



---------8."Select a DVD, and then select all its product reviews."--------


SELECT * FROM reviews WHERE movies_and_tv_id = 
	(SELECT id FROM movies_and_tv WHERE name = "Bill and Ted's Excellent Adventure");



---------9. "Select a product review, and then select all the product reviews that correspond to the first product reviews DVD."-------


SELECT reviews.* FROM reviews WHERE 
	reviews.movies_and_tv_id = 
	(SELECT movies_and_tv_id FROM reviews WHERE reviews.id = 2);