Develop queries in oracle and provide its output

Assignment Help Database Management System

Reference no: EM13999658

Ben and Jerry is happy with your efforts and wants to extend your contract.

THEY WANT YOU TO INCPRPORATE THE FOLOWING INFORMATION IN YOUR DATABASE

In addition to the current three tables, ICECREAM, RECIPE and INGREDIENT, they have one additional table CUSTOMER that needs to be integrated in the database. Purpose is to keep track of customer's ice cream flavor preferences.

CUSTOMER (Cust_ID, Cust_name, year_born)

They want to link CUSTOMER to their database. Customer flavor preference is shown in table 2.

Following tables are from assignment 1

ICECREAM (Ice_cream_ID, Ice_cream_flavor, price, years_first_offered, sellling _status)

INGREDIENT( Ingredient_ID, Ingredient_name, cost)

RECIPE (Ice_cream_ID, ingredient_ID, quantity_used)

WHERE:

Ice_cream_ID is the internal Id given to an ice cream.

Ingredient_ID is the internal Id given to an ingredient

selling_staus is an internal control which keeps track of ice cream sales as high, low, medium or none. If no figures are available this field has no value.

Years_first_offered is the year that ice cream was first offered

quantity_used is the amount of ingredient used in a given ice cream.

Table 1: CUSTOMER

CUST_ID	CUST_NAME	Year_born
1	Harry, T	2002
2	Sally, P	1992
3	Lio, L	1998
4	Patel, P	2001
5	Roner,K	1978
6	Jackson, O	2002
7	Long, P	2001
8	Smith, G	1992
9	Harry, L	2002
10	Paner, K	1978
11	Dan, U	2010
12	Patel, M	2001

Table 2: CUSTOMER and their Flavor preference

Name	Flavor preference
Harry, T	Vanilla, Coconut
Sally, P	Almond, Vanilla, Cookie
Lio, L	Banana, Green Tea, Mint
Patel, P	Cherry, Coconut
Roner,K
Jackson, O	Cherry, Coconut
Long, P
Smith, G	Berry, Vanilla, Mint, Cookie, almond
Harry, L	Mint
Paner, K
Dan, U	Coconut, Vanilla, Cherry
Patel, M	Coconut

You are to perform the following using ORACLE available at UB:

PART A: create tables and load data

a) Create CUSTOMER table and create table that will link customer to their ice cream preferences. Make sure to include appropriate primary and foreign keys. You can use customer_ID and Ice_cream_id to link customer to flavors.

Part B Provide Table structure

Part C: Provide table contents

Part D: Develop queries in ORACLE and provide its output

All queries MUST be based SOLELY on the information provided and each question must use a SINGLE query. No views or separate queries, unless otherwise stated.

As always all queries should be data independent.

c) Answer the following queries in SQL:

1. Give the names of customers who are using ice creams that have cocoa as their ingredient.

2. Give the flavor of ice creams that were introduced before customer Roner, K was born

3. Ben and Jerry want to discontinue flavors that none of their current customer like. Give a list of those flavors.

4. Give the count of customers that like exactly two flavors.

5. Ben and Jerry are stating a new flavor, a mix of Vanilla, Cookie and almond. List the names of customers that like either of these flavors.

6. Give the names of flavors that cost more than $500 (total).

7. Give the names of customers that like both Cherry and Vanilla flavors. (note: it is NOT either/Or but AND) (hint: think of UNION, INTERSECT, MINUS)

8. Give the count of employees that do not have any flavor preference.

9. Get the number of customers that have same preference as Harry, L.

10. Give the total cost of each flavor.

BONUS:

BONUS: Related to Q9..Give the names of customers that have EXACTLY same preference as Patel, P. (note if customer Patel has two flavor preference, then we want names of customers who also prefer either or both of those flavors).

PART E:

Draw one complete ERD of all entities from assignment 1 and 2.

Attachment:- Assign 1.pdf

Reference no: EM13999658

Questions Cloud

Affect the observed elasticitys of substitution : How do legal restrictions on practice for nurses and physicians tend to affect the observed elasticity’s of substitution? Would elasticity tend to be higher if legal restrictions were removed? Would quality of care be affected?

How much the bond worth now : A 30 year bond has a principle amount of $1000 and a coupon rate of 5% per year, interest payments are paid semi-annually. If the maturity date from now is exactly 10 years and the current market rate for the same bond is 12% per year, compounded sem..

Justified in resorting to violence to achieve their goals : In reaction to the growing Western influence in China, a secret society known as the Boxers waged a violent uprising. How do reactionary movements, such as the Boxer Rebellion, begin? Are they ever justified in resorting to violence to achieve their ..

What is the magnitude of the angle : when the eagle swoops down, grabs the pigeon, and flies off. At the instant right before the attack, the eagle is flying toward the pigeon at an angle theta = 63.5 Degree below the horizontal, and a speed of 34.7 m/s. What is the speed of the eagl..

Develop queries in oracle and provide its output : Create CUSTOMER table and create table that will link customer to their ice cream preferences. Make sure to include appropriate primary and foreign keys.

What is the flux at the instant the current in the solenoid : What is the flux at the instant the current is 2.50 A in the solenoid? If the EMF in the 7 turn lop is 0.00952 V, at what rate is the current changing in the solenoid?

What frequency the ear is most sensitive : At what frequency the ear is most sensitive? Assume that the length of the external auditory canal is 2.5 cm and sound speed in air is 344 m/s.

What is the change in the charge on the positive plate : A 25 pF parallel-plate capacitor with an air gap between the plates is connected to a 100 V battery. A Teflon slab is then inserted between the plates, and completely fills the gap. What is the change in the charge on the positive plate when the T..

What is its kinetic energy at c : The ball moves on the circle from A to C under the influence of gravity alone. If the kinetic energy of the ball is 35 J at A, what is its kinetic energy at C?

User Account

All Pages