SQL Challenge - Movie queries and recipe database

recipe_database.txt

CREATE TABLE recipes (
id serial,
name varchar(255),
serving_size varchar(200),
total_time varchar(40),
created_at timestamp NOT NULL,
directions text NOT NULL,
);
 
CREATE TABLE ingredients ( 
ingredient_id serial,  
ingredient_name varchar(255) NOT NULL
);
 
INSERT INTO ingredients(ingredient_name) VALUES ('green_eggs');
INSERT INTO ingredients(ingredient_name) VALUES ('ham');
INSERT INTO ingredients(ingredient_name) VALUES ('large green tomatoes');
INSERT INTO ingredients(ingredient_name) VALUES ('eggs');
INSERT INTO ingredients(ingredient_name) VALUES ('milk');
INSERT INTO ingredients(ingredient_name) VALUES ('breadcrumbs');
INSERT INTO ingredients(ingredient_name) VALUES ('vegetable oil');
INSERT INTO ingredients(ingredient_name) VALUES ('gin');
INSERT INTO ingredients(ingredient_name) VALUES ('dry vermouth');
INSERT INTO ingredients(ingredient_name) VALUES ('lemon peel');
INSERT INTO ingredients(ingredient_name) VALUES ('olive');
 
CREATE TABLE recipe_ingredients_list (
id serial,
recipe_id integer NOT NULL,
ingredient_id integer NOT NULL,
ingredient_amount varchar(55) NOT NULL
);
 
INSERT INTO recipes (name, serving_size, total_time, directions)
VALUES ('Green Eggs & Ham','Yields 2 servings', '25 minutes', 'Directions:
1. Cook the eggs.
2. Cook the ham.
3. Combine');
 
INSERT INTO recipes (name, directions)
VALUES ('Fried Green Tomatoes', 'Directions:
1. Slice the tomatoes 1/2 inch thick.
2. Whisk eggs and milk together.
3. Dip the tomatoes in egg mixture and then bread crumbs.
4. Heat oil in a large skillet.
5. Fry the tomatoes in the oil.');
 
INSERT INTO recipes (name, serving_size, directions)
VALUES ('Martini', 'Yields 1 serving', 'Directions:
1. Pour all ingredients into mixing glass with ice cubes.
2. Stir well.
3. Strain in chilled martini cocktail glass.
4. Squeeze oil from lemon peel onto the drink, or garnish with olive.');
 
 
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (1, 1, '4');
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (1, 2, '1/2 lbs.');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (4, 3, '3 large');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (4, 4, '2');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (4, 5, '1/2 cup');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (4, 6, '1/2 cup');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (4, 7, '1 quart');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (5, 9, '2 oz');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (5, 10, '1 oz');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (5, 11, 'optional');
 
INSERT INTO recipe_ingredients_list (recipe_id, ingredient_id, ingredient_amount)
VALUES (5, 12, 'optional');
 
 
 
DELETE FROM recipes WHERE name = 'Green Eggs & Ham';
DELETE FROM recipe_ingredients_list WHERE recipe_id = 2;

sql_queries.txt

SELECT movies.title, movies.rating FROM movies ORDER BY movies.rating LIMIT 50;
SELECT movies.title, movies.rating FROM movies WHERE movies.rating IS NULL;
SELECT movies.title FROM movies WHERE movies.synopsis ILIKE '%thrilling%';
 
SELECT movies.title, movies.year, movies.rating FROM movies                                                     WHERE movies.year BETWEEN 1980 AND 1989 AND movies.genre_id = 17 
ORDER BY movies.rating DESC;
 
 
SELECT cast_members.character, movies.title, actors.name, movies.year FROM cast_members
JOIN movies ON cast_members.movie_id = movies.id
RIGHT OUTER JOIN actors ON cast_members.actor_id = actors.id        
WHERE cast_members.character ILIKE '%james bond%'
ORDER BY movies.year;
 
SELECT movies.title, movies.year, genres.name, cast_members.character FROM cast_members
JOIN movies ON movies.id = cast_members.movie_id
JOIN genres ON genres.id = movies.genre_id
JOIN actors ON cast_members.actor_id = actors.id
WHERE actors.name ILIKE '%julianne moore%';
 
SELECT movies.title, movies.year, studios.name FROM movies
JOIN genres ON movies.genre_id = genres.id
LEFT OUTER JOIN studios ON movies.studio_id = studios.id
WHERE genres.name ILIKE '%horror%'
ORDER BY movies.year LIMIT 5;