-- The menu table contains a foreign key reference.  We could look up
-- the number of the primary key by hand, but in real life, we often
-- don't know the value of the primary key, so we need to write a
-- query to obtain it.  Instead of passing values to the INSERT
-- command, we can give it the results of a SELECT query.

-- Ignore the INSERT INTO line, and just run the SELECT query on its
-- own.  These are the values which will be inserted.  Next, run the
-- full command, and this data will be inserted.  Run
--   SELECT * FROM menu;
-- to confirm this.

-- With this select query, we have added our own columns to the result
-- set in addition to those in the menu_item_types table.  Also note
-- the use of AS to alias "menu_item_types" as "i".  This is simply a
-- form of shorthand to make larger queries remain readable (and save
-- typing!).

INSERT INTO menu (item,description,item_type,price)
  SELECT 'GOOSE GALANTINE' AS item,
         'A goose meat and pistachio nut terrine set on a cumberland sauce, served with french toast.' AS description,
         i.id AS item_type,
         4.80 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'PRAWN COCKTAIL' AS item,
         'Succulent prawns set on a chiffonade of lettuce, topped with a tangy marie-rose sauce, served with brown bread and butter.' AS description,
         i.id AS item_type,
         4.20 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'OVEN BAKED CIABATTA' AS item,
         'Half a garlic rubbed cibatta bread with avocado slices and ripe tomato, topped with either cheddar cheese or vegan redwood cheddar and finished in the oven.' AS description,
         i.id AS item_type,
         3.70 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'WARMED GOAT\'S CHEESE' AS item,
         'An individual cheese round, placed on a nest of rocket leaves, surrounded by a rich cranberry sauce.' AS description,
         i.id AS item_type,
         4.60 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'SMOKED SALMON AND CRAB TARTLET' AS item,
         'Accompanied by a crisp salad and a lemon, lime and dill mayonnaise.' AS description,
         i.id AS item_type,
         6.00 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'MIXED FRUIT SALSA' AS item,
         'Sun-drenched seasonal fruits bound in a rich mango sauce, accompanied by creme fraiche or vegan cream cheese and oatmeal biscuits.' AS description,
         i.id AS item_type,
         4.50 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'CHEF\'S HOMEMADE SOUP OF THE DAY' AS item,
         'A warming textured soup served with a crisp petit pan.' AS description,
         i.id AS item_type,
         4.00 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'MOULES MARINEIERE' AS item,
         'Tasty mussels cooked in their shells, with a white wine, garlic, and cream liquor sprinkled with a scattering of prawns.' AS description,
         i.id AS item_type,
         6.00 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'ASPARAGUS, PARMA HAM AND DUCK TOWER' AS item,
         'Tender asparagus spears wrapped in Parma ham, topped with shredded duck and surrounded by a rich plum sauce.' AS description,
         i.id AS item_type,
         5.20 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'SPINACH PASTA' AS item,
         'Pasta shells coated in spinach, olive oil, black pepper and nutmeg.' AS description,
         i.id AS item_type,
         3.80 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'KING PRAWNS' AS item,
         'Served on a bed of saffron infused rice, napped with garlic butter.' AS description,
         i.id AS item_type,
         4.60 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'DUET OF SMOKE LAMB AND CHICKEN' AS item,
         'Sliced lamb and chicken set on a nest of crisp raw vegetables, drizzled with a honey and redcurrant dressing.' AS description,
         i.id AS item_type,
         5.40 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'BRANDIED CHICKEN LIVERS' AS item,
         'Fried in butter, flamed in brandy and placed on an onion marmalade with a crisp salad.' AS description,
         i.id AS item_type,
         5.00 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'SKILLET OF MUSHROOMS' AS item,
         'Cooked in a sweet sherry and stilton cheese sauce.  (white redwood cheddar can be used)' AS description,
         i.id AS item_type,
         4.20 AS price
    FROM menu_item_types AS i WHERE (name = 'starter');

INSERT INTO menu (item,item_type,price)
  SELECT 'SIDE SALAD' AS item,
         i.id AS item_type,
         2.00 AS price
    FROM menu_item_types AS i WHERE (name = 'side order');

INSERT INTO menu (item,item_type,price)
  SELECT 'CHIPS' AS item,
         i.id AS item_type,
         1.60 AS price
    FROM menu_item_types AS i WHERE (name = 'side order');

INSERT INTO menu (item,item_type,price)
  SELECT 'ONION RINGS' AS item,
         i.id AS item_type,
         1.20 AS price
    FROM menu_item_types AS i WHERE (name = 'side order');

INSERT INTO menu (item,item_type,price)
  SELECT 'GARLIC BREAD' AS item,
         i.id AS item_type,
         1.40 AS price
    FROM menu_item_types AS i WHERE (name = 'side order');

INSERT INTO menu (item,item_type,price)
  SELECT 'GARLIC BREAD WITH CHEESE' AS item,
         i.id AS item_type,
         1.60 AS price
    FROM menu_item_types AS i WHERE (name = 'side order');

INSERT INTO menu (item,item_type,price)
  SELECT 'BREAD ROLL & BUTTER' AS item,
         i.id AS item_type,
         1.00 AS price
    FROM menu_item_types AS i WHERE (name = 'side order');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'STEAK AND MUSHROOM PIE' AS item,
         'Topped with homemade shortcrust pastry, with chips and vegetables.' AS description,
         i.id AS item_type,
         12.00 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'CHICKEN AND VEGETABLE STIR FRY' AS item,
         'Served with savoury rice.' AS description,
         i.id AS item_type,
         9.60 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'COTTAGE CHEESE AND PINEAPPLE' AS item,
         'Complemented with a generous seasonal salad.' AS description,
         i.id AS item_type,
         7.80 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'VEGETABLE PANCAKES' AS item,
         'Tender vegetables, bound in a rich cheese and cream sauce, enveloped in a seasoned pancake and gratinated in the oven. (white redwood cheddar and vegan cream can be used)' AS description,
         i.id AS item_type,
         7.90 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'CHICKEN MADRAS' AS item,
         'Medium hot curry with savoury rice, naan bread and chutney.' AS description,
         i.id AS item_type,
         9.80 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'WARMED CIABATTA' AS item,
         'Covered in cream cheese and strips of smoke salmon.' AS description,
         i.id AS item_type,
         8.00 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'HOMEMADE LASAGNE' AS item,
         'Freshly prepared by chef.  Served with a mixed salad and a crusty roll.' AS description,
         i.id AS item_type,
         9.00 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'PASTA SHELLS' AS item,
         'Cooked in a mixed herb, plum tomato and garlic sauce topped with parmesan shavings.' AS description,
         i.id AS item_type,
         8.70 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'FISHERMAN\'S PIE' AS item,
         'Flaked haddock and prawns in chef\'s own recipe, topped with potato and cheese.' AS description,
         i.id AS item_type,
         9.00 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'CHICKEN KIEV' AS item,
         'Succulent chicken breast filled with a garlic and parsley butter, coated in a natural breadcrumb casing.  Served with chips and a salad garnish.' AS description,
         i.id AS item_type,
         9.70 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'MEDALLIONS OF FILLET STEAK' AS item,
         'Set on a bed of wild mushrooms, topped with a rich madeira sauce.' AS description,
         i.id AS item_type,
         10.40 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'OVEN BAKED BREAST OF DUCK' AS item,
         'Nestled on a spring cabbage, onion and beansprout stir fry, drizzled with an orange and lemon reduction.' AS description,
         i.id AS item_type,
         12.50 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'BROCCOLI, MUSHROOM AND ASPARAGUS BAKE' AS item,
         'Cooked in a white wine and vegan cream sauce, baked with a sage and onion crust.' AS description,
         i.id AS item_type,
         8.30 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'PANFRIED BREAST OF CHICKEN' AS item,
         'Filled with ripe brie, wrapped in smoked bacon and set on a red wine reduction.' AS description,
         i.id AS item_type,
         10.40 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'PANFRIED PLAICE FILLETS' AS item,
         'Napped with a fresh parsley and lemon dressing' AS description,
         i.id AS item_type,
         10.90 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,item_type,price)
  SELECT 'WHOLE BAKED CATCH OF THE DAY' AS item,
         i.id AS item_type,
         12.40 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'DUET OF LAMB AND DUCK' AS item,
         'Set on a potato rosti, floating on a redcurrant and claret jus.' AS description,
         i.id AS item_type,
         9.40 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'MARINATED VENISON STEAK' AS item,
         'Napped with a juniper berry and gin sauce.' AS description,
         i.id AS item_type,
         14.50 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'BAKED PORK FILLET' AS item,
         'Coated with a sweet cider and apple sauce.' AS description,
         i.id AS item_type,
         9.40 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'RACK OF LAMB' AS item,
         'Set on  a rich mint and onion jus.' AS description,
         i.id AS item_type,
         9.90 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'SEARED TUNA STEAK' AS item,
         'Covered with a cherry tomato and chilli pepper butter.' AS description,
         i.id AS item_type,
         8.50 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'CHICKEN PRINCESS' AS item,
         'Tender chicken breast, poached in a white wine and cream sauce, topped with asparagus spears.' AS description,
         i.id AS item_type,
         9.50 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,description,item_type,price)
  SELECT 'DEEP FRIED WHOLETAIL SCAMPI' AS item,
         'Served with chips and salad.' AS description,
         i.id AS item_type,
         9.80 AS price
    FROM menu_item_types AS i WHERE (name = 'main course');

INSERT INTO menu (item,item_type,price)
  SELECT 'RASPBERRY MERINGUE' AS item,
         i.id AS item_type,
         4.00 AS price
    FROM menu_item_types AS i WHERE (name = 'dessert');

INSERT INTO menu (item,item_type,price)
  SELECT 'STRAWBERRY CHEESECAKE' AS item,
         i.id AS item_type,
         4.00 AS price
    FROM menu_item_types AS i WHERE (name = 'dessert');

INSERT INTO menu (item,item_type,price)
  SELECT 'CRÈME BRULÉE' AS item,
         i.id AS item_type,
         4.00 AS price
    FROM menu_item_types AS i WHERE (name = 'dessert');

INSERT INTO menu (item,item_type,price)
  SELECT 'CHOCOLATE FUDGE CAKE' AS item,
         i.id AS item_type,
         4.00 AS price
    FROM menu_item_types AS i WHERE (name = 'dessert');

INSERT INTO menu (item,item_type,price)
  SELECT 'HAZELNUL AND ORANGE ROULADE' AS item,
         i.id AS item_type,
         4.00 AS price
    FROM menu_item_types AS i WHERE (name = 'dessert');

INSERT INTO menu (item,item_type,price)
  SELECT 'TEA' AS item,
         i.id AS item_type,
         1.50 AS price
    FROM menu_item_types AS i WHERE (name = 'beverage');

INSERT INTO menu (item,item_type,price)
  SELECT 'COFFEE' AS item,
         i.id AS item_type,
         1.70 AS price
    FROM menu_item_types AS i WHERE (name = 'beverage');

INSERT INTO menu (item,item_type,price)
  SELECT 'HOUSE WHITE WINE' AS item,
         i.id AS item_type,
         2.00 AS price
    FROM menu_item_types AS i WHERE (name = 'beverage');

INSERT INTO menu (item,item_type,price)
  SELECT 'HOUSE RED WINE' AS item,
         i.id AS item_type,
         2.00 AS price
    FROM menu_item_types AS i WHERE (name = 'beverage');