Question

*Part II:Smith has decided to track customers and what s/he is orderingthe most. In order to do this,create a fifth base entitytothe database (i.e., table)called OrderNumber. For this table, Smith wants to be able to have a unique order number(each time the customer visitsthe restaurant), the customer number, and what food the customer ordered(assume the customer only orders one itemeach visit).As the database administrator,Smith is allowing you to setup the table, relationship(s), and ensure that at least 10 records are entered. Avoid using the same customer for each all 10 records.Smith would like the database administrator to create 3queries based on the following criteria:

•Determine that most popular food item orderedby all customers. (Display the food name and number of items it was ordered—sort the food name in ascendingorder).

•Which customer visited the restaurantthe most. (Display the customer’s name, address, phone and email address).

•Smith would like you to include one query that uses the average function. You get to elect how this query is implemented but ensure that the OrderNumber table is used.

Answer 1

Answer..-

Step 1 : Create a table OrderNumber which has 3 columns ('OrderNo' 'CustomerNo' 'Food Name')

create table OrderNumber('OrderNo' INT NOT NULL PRIMARY KEY,

'CustomerNo' INT,

'Food Name' VARCHAR(25));

Step 2 : Create Another table Having Information about every customer.

create table Customer('CustomerNo' INT,

'Name' VARCHAR(25),

'Address' VARCHAR(25),

'PhoneNo' INT(10),

'E-mail ID' VARCHAR(50));

Step 3 : Enter Details in Table OrderNumber .

For Ex. , INSERT INTO OrderNumber VALUES(1,1,'Masala Dosa');

INSERT INTO OrderNumber VALUES(2,2,'Idli');

Step 4 : Enter Details in Table Customer.

For Ex. , INSERT INTO Customer VALUES(1,'Rajesh','Delhi', 1234567890,'xyz@abc.com' );

INSERT INTO Customer VALUES(2,'Mahesh','Amritsar', 9876541230,'pqr@abc.com' );

Step 5 : Enter the Details of all the customers as above

QUERY 1 : Determine that most popular food item ordered by all customers. (Display the food name and number of items it was ordered—sort the food name in ascending order).

ANSWER: SELECT Food_Name,COUNT(*)

FROM OrderNumber

GROUP BY Food_Name

ORDER BY Food_Name ASC;

QUERY 2 : Which customer visited the restaurant the most. (Display the customer’s name, address, phone and email address).

ANSWER: SELECT *

FROM Customer

WHERE CustomerNo IN (SELECT DISTINCT CustomerNo

FROM OrderNumber

GROUP BY CustomerNo

ORDER BY Count(*)

DESC LIMIT 1);

QUERY 3: Smith would like you to include one query that uses the average function. You get to elect how this query is implemented but ensure that the OrderNumber table is used.

ANSWER: As OrderNumber Table does not contain a column for which calculation of average makes any sense but for only to show how Average(AVG) function is used in database I have calculated the average of OrderNo (which does not any sense). So the Query is as follows -

SELECT AVG(CustomerNo) FROM OrderNumber;