sql challenge

gistfile1.txt

#What are the top 50 worst rated movies? 
#The results should include the movie title and rating and be sorted by the worst rating first.

SELECT title, rating FROM movies WHERE rating > 1 ORDER BY rating LIMIT 10;

#What movies do not have a rating? The results should include just the movie titles in sorted order.

SELECT title FROM movies WHERE rating IS NULL;

# What movies have the word "thrilling" in their synopsis? The results should just include the movie title.

SELECT title FROM movies WHERE synopsis LIKE '%thrilling%';

# What were the highest rated 'Science Fiction & Fantasy' movies released in the 80's? 
# The results should include the movie title, the year released, and rating sorted by highest rating first.

SELECT movies.title, movies.year, movies.rating, genres.name FROM movies JOIN genres 
ON movies.genre_id = genres.id WHERE movies.year > 1979 AND movies.year < 1990 AND 
genres.name = 'Science Fiction & Fantasy' ORDER BY rating DESC;

# What actors have starred as James Bond? The results should include the actor name, movie title, year released,
# and be sorted by year in ascending order (earliest year appears first).
SELECT actors.name, movies.title, movies.year FROM movies JOIN cast_members ON movies.id = cast_members.movie_id
JOIN actors ON cast_members.actor_id = actors.id
WHERE cast_members.character = 'James Bond' ORDER BY year;

# What movies has Julianne Moore starred in? The results should include the movie title, year released, and name of 
# the genre, sorted by genre first and then movie title.

SELECT genres.name, movies.title, movies.year FROM actors JOIN cast_members ON actors.id = cast_members.actor_id
JOIN movies ON cast_members.movie_id = movies.id JOIN genres ON movies.genre_id = genres.id WHERE actors.name = 'Julianne Moore';

# What were the five earliest horror movies and what studios produced them? Include the movie title, year released, and studio name 
# (if any) in the results sorted by year.

SELECT movies.title, movies.year, studios.name FROM studios JOIN movies ON studios.id = movies.studio_id JOIN genres ON movies.studio_id = genres.id 
WHERE genres.name = 'Horror' ORDER by year;

CREATE TABLE recipies (
  id serial,
  name varchar(150) NOT NULL,
  serving_size integer,
  cooking_time integer,
  directions text NOT NULL,
  created_at timestamp NOT NULL
);


CREATE TABLE ingridiants (
  id serial,
  recipies_id integer NOT NULL,
  ingridiants text
);

INSERT INTO ingridiants (recipies_id, ingridiants)
  VALUES (1, '4 green eggs 1/2 lb ham');

INSERT INTO ingridiants (recipies_id, ingridiants)
  VALUES (2, '3 large green tomatoes
2 eggs
1/2 cup milk
1/2 cup breadcrumbs
1 quart vegetable oil');

INSERT INTO ingridiants (recipies_id, ingridiants)
  VALUES (3, '2 oz gin
1 oz dry vermouth
(optional) lemon peel or olive');



INSERT INTO ingridiants (recipies_id, serving_size, directions, created_at)
  VALUES ('Martini', 1, '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', now());


INSERT INTO recipies (name, serving_size, directions, created_at)
  VALUES ('Martini', 1, '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', now());

INSERT INTO recipies (name, directions, created_at)
  VALUES ('Fried Green Tomatoes', '1. Slice the tomatoes 1/2 inch thick.
2. Whisk eggs and milk together.
3. Dip tomatoes in egg mixture and then bread crumbs.
4. Heat oil in a large skillet.
5. Fry the tomatoes in the oil.', now());

INSERT INTO recipies (name, serving_size, cooking_time, directions, created_at)
  VALUES ('Green Eggs & Ham', 2, 25, '1. Slice the tomatoes 1/2 inch thick.
2. Whisk eggs and milk together.
3. Dip tomatoes in egg mixture and then bread crumbs.
4. Heat oil in a large skillet.
5. Fry the tomatoes in the oil.', now());

UPDATE ingridiants SET ingridiants = '3 oz vodka
1 oz dry vermouth
(optional) lemon peel or olive' WHERE ingridiants = '2 oz gin
1 oz dry vermouth
(optional) lemon peel or olive';

DELETE FROM recipies WHERE id = 1;

DELETE FROM ingridiants WHERE id = 1;