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?

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd