Reference no: EM132195761

Objectives:

• Learn to implement advance SQL statements
• To practise joining tables for retrieving data
• To practise the use of relational set operators to merge data

Activities

Task

1) Create a new database called Food_Beverages and import the fileFood_Beverages.sql.

2) Draw an ER diagram to represent thedatabase.

3) Use this new database to implement the SQL statements to complete the following:

a) In a following SELECT statement, how many rows will there be in the output? Explain how you have derived at this number. Describe how you can confirm the number iscorrect.

SELECT P.ProductID, C.CategoryID FROM Product AS P, Category AS C;

b) Are all the rows in the output of (a) meaningful? How can you modify that SELECT statement so that the rows in the two tables are logically joined and every row in the output ismeaningful?

c) Display the ID and the shipped date of every order along with the ID and name of the company in charge of shippingit.

d) Show the name and the quantity of the product(s) being listed in eachorder.

e) List the full name and the title of the person which each employee reports to. The ID and last name of the employee should be included in theoutput.

f) Display the ID and contact person of the supplier of the discontinued products. The ID of the product and category ID it belongs to should be included in theoutput.

g) Show the average unit price of the products in each category. The name of each category should be included in theoutput.

h) List the name and title of every customer contact person from Berlin, followed by the full name and title of the employees based in London. The city should be included in the output. (hint: use a relational setoperator)

i) Show the orders which have more than 5 products listed in them? The Order ID and the number of products list in it should be in theoutput.

j) What is the largest discount given to a product beingordered?