gistfile1.txt

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

2. SELECT movies.title, movies.rating FROM movies WHERE movies.rating is  NULL;

3. SELECT movies.title FROM movies WHERE movies.synopsis LIKE '%thrilling%’;

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

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

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

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

######################################

CREATE TABLE recipes (
  id serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  servings integer,
  cooking_time integer,
  directions varchar(1000)
);

CREATE TABLE ingredient (
  id serial,
  ingredient_name varchar(255) NOT NULL,
  count integer,
  recipe_id integer NOT NULL,
  unit_type varchar(100)
);

INSERT INTO recipes (name, servings, cooking_time, directions)
VALUES ('Green Eggs & Ham', 2, 25, '1. Cook the eggs.
2. Cook the ham. 3. Combine.');

INSERT INTO recipes (name, directions)
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, servings, directions)
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 ingredient (ingredient_name, count, recipe_id)
VALUES ('green eggs', 4, 1);

INSERT INTO ingredient (ingredient_name, count, recipe_id, unit_type)
VALUES ('ham', 1, 1, 'lb');

INSERT INTO ingredient (ingredient_name, count, recipe_id)
VALUES ('large green tomatoes', 3, 2);

INSERT INTO ingredient (ingredient_name, count, recipe_id, unit_type)
VALUES ('gin', 2, 3, 'oz');

INSERT INTO ingredient (ingredient_name, count, recipe_id, unit_type)
VALUES ('vermouth', 1, 3, 'oz');

UPDATE ingredient
SET count = 3, ingredient_name = 'vodka'
WHERE ingredient_name = 'gin';

DELETE FROM ingredient WHERE recipe_id = 1;

DELETE FROM recipes WHERE id = 1;