Why Choose Us?
0% AI Guarantee
Human-written only.
24/7 Support
Anytime, anywhere.
Plagiarism Free
100% Original.
Expert Tutors
Masters & PhDs.
100% Confidential
Your privacy matters.
On-Time Delivery
Never miss a deadline.
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
|
- 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. Displays only the list of items t hat appear only once on the menu
- Write one query that displays the meal and Item columns. Add a new column called
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.
- You are given 2 tables, each called table1, t able2. Each table consists of 1 column, c ol1.
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?
- What does the following SQL U DF d o?
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))
);
- Your company sells c omplicated machinery, m uch of which is shipped from different locations in pieces to be assembled at a local distribution center. Each order may be a part of multiple shipments, and each shipment may contain pieces of multiple orders. For example, if someone in Texas buys a tractor, the wheels, chassis, and engine may be shipped separately, from separate locations, and they may each be bundled with parts for a grain bin in the same shipments. M odel this relationship in two ways: One, for a transactional system (like an OLTP), and another for an analytic system, such as an OLAP. Y ou do not need to consider the relationships that these processes may have to entities such as customers, locations, etc. -- the only entities you should care about are orders and shipments.
Expert Solution
PFA
Archived Solution
You have full access to this solution. To save a copy with all formatting and attachments, use the button below.
For ready-to-submit work, please order a fresh solution below.
