SQL!Q!!!!

createdb recipes

CREATE TABLE recipes (
  id SERIAL NOT NULL ,
  name VARCHAR(125) NOT NULL ,
  yields VARCHAR(50),
  total_time VARCHAR(150),
  directions TEXT NOT NULL
);

CREATE TABLE quantity (
  recipe_id INTEGER NOT NULL ,
  quantity VARCHAR(50) NOT NULL,
  ingredients_id INTEGER NOT NULL
);



CREATE TABLE ingredients (
  id INTEGER NOT NULL,
  description TEXT NOT NULL
);


INSERT INTO recipes (name, yields, total_time, directions)
VALUES ('Green Eggs & Ham',
  '2 Servings',
  '25 minutes',
   '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, yields, directions)
VALUES ('Martini', '1 Serving', '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( description)
  VALUES( 'green eggs' );
INSERT INTO ingredients( description)
  VALUES( 'ham' );
INSERT INTO ingredients( description)
  VALUES( 'large green tomatoes' );
INSERT INTO ingredients( description)
  VALUES( 'eggs' );
INSERT INTO ingredients( description)
  VALUES( 'milk' );
INSERT INTO ingredients( description)
  VALUES( 'breadcrumbs' );


INSERT INTO ingredients( description)
  VALUES( 'vegetable oil' );
INSERT INTO ingredients( description)
  VALUES( 'gin' );
INSERT INTO ingredients( description)
  VALUES( 'dry vermouth' );
INSERT INTO ingredients( description)
  VALUES( 'lemon peel or olive' );

INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (1,4,1);
INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (1,'1/2 lb',2);
 INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (2,3,3);
 INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (2,2,4);
 INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (2,'1/2 cup',5);
 INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (2,'1/2 cup',6);
 INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (2,'1 quart',7);
  INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (3,'2 oz',8);
  INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (3,'1 oz',9);
  INSERT INTO quantity (recipe_id, quantity, ingredients_id)
 VALUES (3,'(optional)',10);


#UPDATE

UPDATE quantity SET quantity = '3 oz' WHERE quantity = '2 oz';
UPDATE ingredients SET description ='vodka' WHERE description = 'gin';

# DELETE

DELETE FROM quantity WHERE ingredients_id IN (select id from ingredients where description in ('green eggs', 'ham'));

delete from ingredients where description in ('green eggs', 'ham');

delete from recipes where name = 'Green Eggs & Ham';