product.sql
/* 1. Data items in the following order: productName, productType, description, size, sizeTypeId, quantity, price
2. Replace , , , and with the foreign key value in table productType, column type
3. Replace , and with the foreign key value in table sizeType, column size
*/
"steak", , "Butcher's Cut Filet Mignon", 10, , 30, 33.74
"steak", , "Butcher's Cut Top Sirloin", 5, , 50, 14.99
"steak", , "T-Bone Steak", 18, , 40, 33.33
"bison", , "Ground Bison", , , 20, 22.49
"turkey", , "Home-Style Roasted Turkey Breast", 1.8, , 45, 21.99
"turkey", , "Turkey Burgers", 8, , 30, 21.99
"ham", , "Spiral Sliced Ham", 9, , 15, 89.99
"pork", , "Breaded Pork Tenderloin", 4, , 25, 19.99
"salmon", , "Lemon Dill Salmon Fillets", 6, , 20, 8.79
"seabass", , "Chilean Sea Bass", 5, , 30, 19.99
"crab", , "King Crab Legs", , 2, 16, 109.99
"crab", , "Gourmet Crab Cakes", 4, , 26, 34.99
"lobster", , "Cold Water Lobster Tails", 5, , 35, 22.99
"bean", , "Green Beans", 6.5, , 45, 10.99
"broccoli", , "Steakhouse Broccoli and Cauliflower Gratin", 22, ,35, 12.99
"brussels sprouts", , "Brussels Sprouts with Bacon & Shallots", 9.5, , 35, 14.99
"corn", , "Steakhouse Creamed Corn", 20, , 40, 10.99
"mixed", , "Roasted Vegetable Medley", 7, , 50, 9.99
"asparagus", , "Asparagus Spears With Hollandaise Sauce", 12, , 30, 15.99
"potatoes", , "Potatoes au Gratin", 8, , 55, 14.99
"risotto", , "Pepper Jack Risotto Cakes", 8, , 25, 19.99
"rice", , "Harvest Rice Pilaf", 25, , 45, 14.99
"bread", , "Mini Baguettes with Garlic Butter", 4, , 35, 49.99
Query Description 1. Change to use the database frozenfood 2. Create a table named sizeType with the following attributes, data types, and constraints: a. ID, integer, 7 characters, not null, auto increment b. size, variable character, 20 characters, not null C. primary key is the ID field 3. Update table product, row with description as tiramisu classico so the size = '3 ounce' versus '3 oz 4. Insert into table sizeType, column size the distinct character portion of table product, column size using the SUBSTRING_INDEX function, the arguments passed should be column size, an explicit space and the value -1. Example SUBSTRING INDEX(
,',-1). 5. Alter table product, add column sizeTypeId as integer, 7 characters, not null 6. Update table product, set column sizeTypeId equal to the corresponding size in table size Type. In my size Type table I have the following: | 1 ounce | 2 pound | 3 count For example, all rows with size equal to size containing the text "ounce set size Typeld equal to 1 Hint: use the LIKE comparison operator 7. Alter table product add foreign key for column sizeTypeId references table sizeType, column ID 8. Update table product, set size equal to the number portion of the data in column size using the SUBSTRING_INDEX function, the arguments passed should be column size, an explicit space and the value 1. Example SUBSTRING_INDEX( 1). 9. Alter table product to modify column size to be decimal, 7 characters, 2 to the right of the decimal point, not null 10. Insert into table product the data in file product.sql 11. Generate an ER Diagram using MySQL Workbench, save as a .mwb file 12. Export database frozenfood using MySQL Workbench, save as a .sql file 13. Provide written source code in a sql file
