gistfile1.txt

SELECT title, rating FROM movies ORDER BY rating LIMIT 50;

SELECT title FROM movies WHERE rating = 0 ORDER BY title;

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

SELECT movies.title, movies.year, movies.rating FROM movies JOIN genres ON movies.genre_id = genres.id WHERE genres.name = 'Science Fiction & Fantasy' AND year BETWEEN 1980 AND 1989 ORDER BY rating DESC;

SELECT actors.name, movies.title, movies.year FROM movies 
  JOIN cast_members ON movies.id = cast_members.movie_id
  JOIN actors ON actors.id = cast_members.actor_id 
WHERE cast_members.character = 'James Bond' 
ORDER BY movies.year;

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

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

CREATE TABLE ingredients (
  id serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  count integer,
  unit_type varchar(63),
  recipe_id integer NOT NULL
  );
  
CREATE TABLE recipes (
  id serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  yield integer,
  total_time integer,
  instruction text
  );
  
  INSERT INTO recipes (name, yield, total_time, instruction) 
  VALUES ('Green Eggs & Ham', 2, 25, '1. Cook the eggs.
2. Cook the ham.
3. Combine.');

  INSERT INTO recipes (name, instruction) 
  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.');

  INSERT INTO recipes (name, yield, instruction) 
  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.');


INSERT INTO ingredients (name, count, recipe_id) 
VALUES ('green eggs', 4, 4);

INSERT INTO ingredients (name, count, unit_type, recipe_id) 
VALUES ('ham', 8, 'ounces', 4);


INSERT INTO ingredients (name, count, recipe_id) 
VALUES ('large green tomatoes', 3, 2);

INSERT INTO ingredients (name, count, recipe_id) 
VALUES ('eggs', 2, 2);

INSERT INTO ingredients (name, count, unit_type, recipe_id) 
VALUES ('milk', 4, 'ounces', 2);

INSERT INTO ingredients (name, count, unit_type, recipe_id) 
VALUES ('breadcrumbs', 4, 'ounces', 2);

INSERT INTO ingredients (name, count, unit_type, recipe_id) 
VALUES ('vegetable oil', 32, 'ounces', 2);


INSERT INTO ingredients (name, count, unit_type, recipe_id) 
VALUES ('gin', 2, 'ounces', 5);

INSERT INTO ingredients (name, count, unit_type, recipe_id) 
VALUES ('dry vermouth', 1, 'ounces', 5);

INSERT INTO ingredients (name, recipe_id) 
VALUES ('(optional) lemon peel or olive',5);

UPDATE ingredients 
SET count = 3, name = 'vodka'
WHERE recipe_id = (select id from recipes where name = 'Martini') AND name = 'gin';


DELETE FROM ingredients WHERE recipe_id = (SELECT id FROM recipes WHERE name = 'Green Eggs & Ham');
DELETE FROM recipes WHERE name = 'Green Eggs & Ham';