Run the following script to populate the table INSERT INTO dbo.Menu SELECT
Question:
- Run the following script to populate the table
INSERT INTO dbo.Menu
SELECT 'Big Mac','Hamburger',1.25,3.24,1015,5000,15853
union
SELECT 'Quarter Pounder / Cheese','Hamburger',1.15,3.24,1000,4589,16095
union
SELECT 'Half Pounder / Cheese','Hamburger',1.35,3.50,500,3500,12589
union
SELECT 'Whopper','Hamburger',1.55,3.99,989,4253,13000
union
SELECT 'Kobe Cheeseburger','Hamburger',2.25,5.25,350,1500,5000
union
SELECT 'Grilled Stuffed Burrito','Burrito',.75,5.00,2000,7528,17896
union
SELECT 'Bean Burrito','Burrito',.50,1.00,1750,7000,18853
union
SELECT '7 layer Burrito','Burrito',.78,2.50,350,1000,2563
union
SELECT 'Dorrito Burrito','Burrito',.85,1.50,600,2052,9857
union
SELECT 'Turkey and Cheese Sub','Sub Sandwich',1.75,5.50,1115,7878,16853
union
SELECT 'Philly Cheese Steak Sub','Sub Sandwich',2.50,6.00,726,2785,8000
union
SELECT 'Tuna Sub','Sub Sandwich',1.25,4.50,825,3214,13523
union
SELECT 'Meatball Sub','Sub Sandwich',1.95,6.50,987,4023,15287
union
SELECT 'Italian Sub','Sub Sandwich',2.25,7.00,625,1253,11111
union
SELECT '3 Cheese Sub','Sub Sandwich',.25,6.00,815,3000,11853
- ItemID has a primary key with an Identity (1000,1)
- ItemType does not allow nulls
- CostToMake should always be greater than 0
- Retrieve all Burritos and sort by Price
- Retrieve all items that Cost more than $1.00 to make and sort by WeeklySales
- What's the sum of total profit by ItemType
- Retrieve Total Weekly Sales by ItemType of only items with more than 3000 weekly Sales. Sort by Total Weekly Sales descending.
- Find out the profit made Weekly, Monthly and Yearly on Big Mac's
- Retrieve the ItemType has more than $20,000 in Monthly Sales
- Retrieve the ItemType that had the best Profit from MonthlySales
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel