Question

Use MySQL Workbench to do the two task, for each task, run and then take a screenshot.

Task 1:

Example: You want to keep track of all the customers that live in area 615 and are issued more than 1 invoice. It has a long query!! How about putting it in the view.

CREATE VIEW CUSTOMERS_OF_615 AS SELECT CUSTOMER.CUS_CODE, COUNT(INV_NUMBER) AS INVOICE_COUNT FROM CUSTOMER JOIN INVOICE

USING (CUS_CODE)

WHERE CUSTOMER.CUS_AREACODE = 615

GROUP BY CUSTOMER.CUS_CODE HAVING INVOICE_COUNT >1

A VIEW can be fetched similar to a table.

SELECT * FROM CUSTOMERS_OF_615

Question: Create a View that displays each vendor’s contact information with the number of products they provide, take a screenshot!

Task 2:

Question:

Create a Trigger that will log changes in the Customer table after any update to a new table named as Customer_modified.

Test the Trigger using the query below:

UPDATE CUSTOMER SET CUS_INITIAL="ASD", CUS_AREACODE="123",

CUS_PHONE='1234', CUS_BALANCE='656' WHERE CUS_CODE=10011

Answer 1

TASK1:
CREATE VIEW CUSTOMERS_OF_615 AS SELECT CUSTOMER.CUS_CODE, COUNT(INV_NUMBER) AS INVOICE_COUNT FROM CUSTOMER JOIN INVOICE

USING (CUS_CODE)

WHERE CUSTOMER.CUS_AREACODE = 615

GROUP BY CUSTOMER.CUS_CODE HAVING INVOICE_COUNT >1

EXPLANATION: Yes, you are doing in right way