SQL How to create DML scripts to update table Customers and set Customer_Status_
ID: 3806078 • Letter: S
Question
SQL
How to create DML scripts to update table Customers and set Customer_Status_Id to the correct value for each of the customers?
Data model for Customer and customer_status:
-here's how do Customer_status table looks like:
Inactive Customers => Have 0 orders
Active Customers => Have between 1 and 3 orders
Very Active Customers => Have 4 or more orders
-here's how many orders that each customer placed:
CUSTOMERS CUSTOMER ID NUMERIC CUSTOMER FIRST NAME VARCHAR (50 BYTE) CUSTOMER LAST NAME VARCHAR (50 BYTE) CUSTOMER ADDRESS VARCHAR 255 BYTE) CUSTOMER CITY VARCHAR (50 BYTE) O CUSTOMER STATE CHAR BYTE) CUSTOMER ZIP VARCHAR 20 BYTE) CUSTOMER PHONE VARCHAR 30 BYTE) CUSTOMER FAX F Customer Status Id nteger CUSTOMERS PK (CUSTOMER ID) Customer Customer Status Customer Status Id integer CustomeryStatus name VARCHAR Customer Status PK (Customer Status.Explanation / Answer
In the above problem, the name of table with Customer_id and orders placed is not given. So considering it as Orders table, the DML query for the problem will be:
UPDATE Customers SET Customer_Status_Id= 1
WHERE CUSTOMER_ID in ( SELECT CUSTOMER_ID from Orders where TOTAL_ORDER = 0)
AND SET Customer_Status_Id= 2
WHERE CUSTOMER_ID in ( SELECT CUSTOMER_ID from Orders where TOTAL_ORDER BETWEEN 1 AND 3)
AND SET Customer_Status_Id= 3
WHERE CUSTOMER_ID in ( SELECT CUSTOMER_ID from Orders where TOTAL_ORDER >=4);
Also we need to first set Status Id in Customer_Status Table to maintain Referential Integrity constraint as the Customer_Status_Id of Customers table is foreign key while Customer_Status_Id is primary key of Customer_Status table.
Query for setting Status ID in Customer_Status table is:
UPDATE Customer_Status SET Customer_Status_Id= 1
WHERE Customer_Status_name= 'Inactive Customers'
AND SET Customer_Status_Id= 2
WHERE Customer_Status_name= 'Active Customers'
AND SET Customer_Status_Id= 3
WHERE Customer_Status_name= 'very Active Customers' ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.