Question

Hello, just need some help with a few MS-SQL queries. I asked these questions recently and recieved multiple errors when trying them out.

Questions:

This is the database that the queries should be written about: Shopltems itemlD itemDescription pnice markup CustomerPurchases CustomerVisits dateVisit ? customerID customerlD dateVisit itemiD visitDuration qty totalDue Customers LcustomerlD firstName lastName mainPhoneNo secondaryPhoneNo email addressLinel addressLine2 isp Profiles t profilelD parentProfilelD profileDescription earlybird assertive CustomerProfiles customerlD profilelD comment

Previous answer to Task 2 that didnt work:

Answer 1

It would have been easy if you could have shared the DB details also. Anyway, For TASK 2: you can use the following query

UPDATE Customers set TotalVisits=(select count(CustomerID) from CustomerVisits where Customers.CustomerID=Customervisits.CustomerID group by CustomerID);

I have tested the query on a different database and it is working as you want.

Task 4: Trigger should work something like this:

It needs to automatically update the customers totalvisits column on the basis an update,delete and insertion.

You should look for spelling mistakes if any in the below code because we can't test without the DB here.

create or replace trigger total_visits on CustomerVisits
after INSERT or DELETE or UPDATE as
begin
UPDATE Customers set TotalVisits=(select count(CustomerID)
from CustomerVisits where Customers.CustomerID=customervisits.CustomerID
group by CustomerID);
end;
/

Similarly, Task for totalspent can be completed.