Questions & AnswersComputer Science

SQL QUERY Thank you.   Compose a set of queries to manipulate and...

Question
Answered step-by-step
Asked by PresidentOpossum3673 on coursehero.com

SQL QUERY Thank you.   Compose a set of queries to manipulate and...

SQL QUERY

Thank you.

 

Compose a set of queries to manipulate and ask questions of the za database. This database contains a total of five tables:

pizza: A simple list of pizzas with no additional information.
 

SELECT * FROM PIZZA

------------+
| pizza_name |
+------------+
| Canadian   |
| Donair     |
| Greek      |
| Hawaiian   |
| Vegetarian |
+------------+
supplier: A list of ingredient suppliers with location and reliability data.
 

SELECT * FROM SUPPLIER

+-------------+-------------------------+-----------+----------+-------------+
| supplier_id | supplier_name           | city      | province | reliability |
+-------------+-------------------------+-----------+----------+-------------+
|           1 | Bobs Lactose and Donair | Halifax   | NS       |           3 |
|           2 | Everything But Meat     | Vancouver | BC       |           4 |
|           3 | Eventual Arrival Maybe  | Arnprior  | ON       |           1 |
|           4 | Expensive but FAST      | Fergus    | ON       |           5 |
|           5 | Overly Specialized      | Penticton | BC       |           4 |
+-------------+-------------------------+-----------+-


ingredient_info: A list of ingredients with nutritional information.
 

SELECT * FROM INGREDIENT_INFO

+------------------+-----------+-------+--------+---------------+
| ingredient_name  | sodium_mg | fat_g | carb_g | calories_100g |
+------------------+-----------+-------+--------+---------------+
| Artichoke        |        94 |   0.2 |    5.0 |            47 |
| Bacon            |      1450 |  42.0 |    1.0 |            42 |
| Banana peppers   |       100 |   0.4 |    3.6 |             8 |
| Black olives     |       720 |  15.0 |    6.0 |            80 |
| Donair meat      |       900 |  15.0 |    1.0 |           170 |
| Dough            |       600 |   8.0 |   40.0 |           200 |
| Feta cheese      |       350 |  25.0 |    2.0 |           300 |
| Green peppers    |         2 |   0.0 |    4.0 |            15 |
| Ground beef      |        75 |  18.0 |    0.0 |           250 |
| Ham              |      1000 |  11.0 |    2.0 |           110 |
| Jalapeno peppers |         3 |   0.4 |    4.1 |            29 |
| Mozzarella       |       180 |   6.0 |    1.0 |            80 |
| Mushrooms        |         5 |   0.0 |    1.0 |             5 |
| Olive oil        |         0 |  14.0 |    0.0 |           120 |
| Onion            |         5 |   0.0 |    9.0 |            40 |
| Pepperoni        |       650 |  13.0 |    1.0 |           140 |
| Pineapple        |         1 |   0.0 |   22.0 |            85 |
| Sauce            |       400 |   0.5 |    7.0 |            40 |
| Shrimp           |       119 |   1.0 |    0.0 |            99 |
| Tomato           |        10 |   0.0 |    3.0 |            15 |
+------------------+-----------+-------+--------+---------------
ingredient_supplier: A list of which suppliers provide which ingredients, with associated prices and an indication of whether the ingredient is currently in stock.
 

SELECT * FROM INGREDIENT_SUPPLIER

supplier_id | ingredient_name | ingredient_price_per_unit | ingredient_in_stock |
+-------------+-----------------+---------------------------+---------------------+
|           1 | Donair meat     |                      3.00 |                   1 |
|           1 | Dough           |                      0.30 |                   1 |
|           1 | Feta cheese     |                      3.75 |                   1 |
|           1 | Ham             |                      2.25 |                   1 |
|           1 | Mozzarella      |                      2.55 |                   1 |
|           2 | Black olives    |                      0.75 |                   1 |
|           2 | Dough           |                      0.30 |                   1 |
|           2 | Feta cheese     |                      3.30 |                   1 |
|           2 | Green peppers   |                      0.90 |                   1 |
|           2 | Mozzarella      |                      1.95 |                   1 |
|           2 | Mushrooms       |                      1.20 |                   1 |
|           2 | Olive oil       |                      1.65 |                   1 |
|           2 | Onion           |                      0.45 |                   1 |
|           2 | Pineapple       |                      1.35 |                   0 |
|           2 | Sauce           |                      0.30 |                   1 |
|           2 | Tomato          |                      1.20 |                   1 |
|           3 | Bacon           |                      1.50 |                   0 |
|           3 | Black olives    |                      0.60 |                   1 |
|           3 | Dough           |                      0.27 |                   1 |
|           3 | Feta cheese     |                      3.60 |                   0 |
|           3 | Green peppers   |                      0.68 |                   1 |
|           3 | Ham             |                      2.40 |                   0 |
|           3 | Mozzarella      |                      2.25 |                   1 |
|           3 | Mushrooms       |                      1.35 |                   0 |
|           3 | Olive oil       |                      1.80 |                   1 |
|           3 | Onion           |                      0.53 |                   1 |
|           3 | Pepperoni       |                      3.30 |                   1 |
|           3 | Sauce           |                      0.30 |                   1 |
|           3 | Tomato          |                      1.28 |                   1 |
|           4 | Feta cheese     |                      4.50 |                   0 |
|           4 | Ham             |                      3.15 |                   1 |
|           4 | Mozzarella      |                      2.93 |                   1 |
|           4 | Pineapple       |                      2.10 |                   0 |
|           5 | Donair meat     |                      2.70 |                   0 |
|           5 | Pineapple       |                      1.50 |                   1 |
+-------------+-----------------+---------------------------+-
pizza_ingredient: An association of pizzas and ingredients.
 

SELECT * FROM PIZZA_INGREDIENT

+------------+-----------------+
| pizza_type | ingredient_name |
+------------+-----------------+
| Canadian   | Bacon           |
| Greek      | Black olives    |
| Vegetarian | Black olives    |
| Donair     | Donair meat     |
| Canadian   | Dough           |
| Donair     | Dough           |
| Greek      | Dough           |
| Hawaiian   | Dough           |
| Vegetarian | Dough           |
| Greek      | Feta cheese     |
| Vegetarian | Green peppers   |
| Hawaiian   | Ham             |
| Canadian   | Mozzarella      |
| Donair     | Mozzarella      |
| Greek      | Mozzarella      |
| Hawaiian   | Mozzarella      |
| Vegetarian | Mozzarella      |
| Vegetarian | Mushrooms       |
| Canadian   | Olive oil       |
| Donair     | Olive oil       |
| Greek      | Olive oil       |
| Hawaiian   | Olive oil       |
| Vegetarian | Olive oil       |
| Donair     | Onion           |
| Hawaiian   | Pineapple       |
| Canadian   | Sauce           |
| Donair     | Sauce           |
| Greek      | Sauce           |
| Hawaiian   | Sauce           |
| Vegetarian | Sauce           |
| Canadian   | Tomato          |
| Donair     | Tomato          |
| Greek      | Tomato          |
+------------+-----------------+

Here is the internal view of the database as constructed through the "Reverse Engineer" function in MySQL:Notes about attributes:
Most attributes should be self explanatory. A couple deserve extra explanation:

  • supplier.reliability is a reliability rating for each supplier, from 1 (awful) to 5 (highly reliable).
  • ingredient_supplier.ingredient_in_stock is effectively a Boolean value, where 1 = in stock and 0 = out of stock.
  • pizza.pizza_name and pizza_ingredient.pizza_type contain the same information even though their names are different.


 

 

Answer & Explanation

Solved by verified expert
Answered by Arsh_Gupta on coursehero.com

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet.

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac,
CliffsNotes Logo

Unlock access to this and over
10,000 step-by-step explanations

Unlock Explanation

Have an account? Log In

<p>sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet.</p>sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac,

Step-by-step explanation

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectu

 

sectetur adipiscin

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec fa
 

 

sectetur adipiscing elit. N

sectetur adipisci
sectetur a
sectetur adipiscing elit
 

 

 

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. D

 

sectetur adipiscing elit.

 

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisi
sectetur adipiscing elit
sectetur adipiscing elit. Na
sectetur adipiscing elit
sectetur adipiscing elit. Na
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec
 

 

sectetur adipiscing elit. Nam lacinia p

 

sectetur adipiscing elit. Nam laci
sectetur adipiscing el
sectetur adipiscing elit. Nam l
sectetur adipiscing elit. Nam
 

 

 

 

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie

 

sectet
sectetur adipiscing e
sectetur adipiscing elit. Nam lacinia p
sect
sect
sectetur adip
sectetur adipiscing el
sectetur adipiscing elit. N
sectetur adipiscing elit. Nam lacinia pulvinar tortor nec
sectetur ad
sectetur adipiscing elit. Nam
sectetur ad
sectetur adipiscing elit. Nam lac
sectetur
sectetur adipiscing elit. Nam lacinia pulvinar tor
sectetur adi
sectetur adipiscing elit. Nam lacinia p
sectetur adipis
sectetur adipiscing elit. Nam
sectetur
sectetur
sectetur adipiscing
sectetur
sectetur adipiscing elit.
 

 

sectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congu

Get unstuck with a CliffsNotes subscription

Example CliffsNotes Question and Answer
Unlock every step-by-step explanation, download literature note PDFs, plus more.Get Access

Related Q&A