Fill This Form To Receive Instant Help
Homework answers / question archive / SQL Skills Evaluation Question 1-3: The following restaurant menu items are in a table called restaurant_menu: MenuID Meal Item Cost CaloriesID Calories 1 Breakfast Oatmeal 5 2 300 2 Breakfast Omelette 7 1 600 3 Lunch Clam Chowder 10 3 650 4 Lunch Steak 12 5 700 5 Lunch Burger 9 7 700 6 Dinner Clam Chowder 10 3 650 7 Dinner Steak 12 5 700 8 Dinner Burger 9 7 700 9 Dessert Apple Pie 6 4 450 Chocolate 10 Dessert Cake 5 6 550 The following table shows the calorie information on a variety of restaurant menu items in a table called restaurant_calories: CaloriesID Calories Calories_From_Fat 1 500 300 2 300 75 3 650 250 4 450 280 5 700 300 6 550 350 7 700 350 8 750 300 9 600 250 10 900 300 Write one query that d oes the f ollowing: Lists the unique B reakfast, Lunch, D inner items on the m enu in reverse alphabetical order To the right of the items, show the number of times each item appears on t he menu Write one query that d oes the f ollowing: Pulls the m eal, item, cost, c alories_from_fat information In addition to pulling the columns a bove, generate a new column farthest on t he right called h ealth_kpi that uses the following logic: if calories <= 350 then the KPI is ‘Healthy’, if calories between 351 and 599 then the KPI is ‘Fair’, if calories >= 600, then the KPI is ‘Unhealthy’ Write one query that d oes the f ollowing: a
SQL Skills Evaluation
Question 1-3:
The following restaurant menu items are in a table called restaurant_menu:
MenuID
|
Meal
|
Item
|
Cost
|
CaloriesID
|
Calories
|
1
|
Breakfast
|
Oatmeal
|
5
|
2
|
300
|
2
|
Breakfast
|
Omelette
|
7
|
1
|
600
|
3
|
Lunch
|
Clam Chowder
|
10
|
3
|
650
|
4
|
Lunch
|
Steak
|
12
|
5
|
700
|
5
|
Lunch
|
Burger
|
9
|
7
|
700
|
6
|
Dinner
|
Clam Chowder
|
10
|
3
|
650
|
7
|
Dinner
|
Steak
|
12
|
5
|
700
|
8
|
Dinner
|
Burger
|
9
|
7
|
700
|
9
|
Dessert
|
Apple Pie
|
6
|
4
|
450
|
|
|
Chocolate |
|
|
|
10
|
Dessert
|
Cake
|
5
|
6
|
550
|
The following table shows the calorie information on a variety of restaurant menu items in a table called restaurant_calories:
CaloriesID
|
Calories
|
Calories_From_Fat
|
1
|
500
|
300
|
2
|
300
|
75
|
3
|
650
|
250
|
4
|
450
|
280
|
5
|
700
|
300
|
6
|
550
|
350
|
7
|
700
|
350
|
8
|
750
|
300
|
9
|
600
|
250
|
10
|
900
|
300
|
a. Displays only the list of items t hat appear only once on the menu
average_meal_cost farthest to the right, which gives the overall average cost of all items associated with a meal. For example, the first 2 rows would show 6 in the new column.
Each table has 5 rows per column. The column consists of the values of 1,2,3,4,5.
Hence both tables look exactly like this:
col 1 |
1 |
2 |
3 |
4 |
5 |
Write a query that joins every row of table1 to every row of table2.
After writing the query, how many total rows will there be, assuming there is no filtering?
CREATE TEMPORARY FUNCTION M YSTERY_FUNCTION(start_date D ATE, end_date D ATE) AS
(
(S ELECT
CASE
WHEN s tart_date I S NULL OR end_date I S NULL THEN NULL
ELSE C OUNT( days) * (C ASE WHEN s tart_date <= end_date T HEN 1 E LSE - 1 END) END
F ROM U NNEST( G ENERATE_DATE_ARRAY( start_date, end_date, I NTERVAL
CASE WHEN s tart_date <= end_date T HEN 1 E LSE - 1 E ND DAY) ) A S d ays
W HERE d ays N OT IN ( S ELECT d ays F ROM d ays_list)
A ND E XTRACT( D AYOFWEEK FROM days) N OT IN ( 1, 7))
);