Question

An objective of this task is to implement SQL script that verifies the following logical consistency constraint imposed on the contents of a sample database. "All orders submitted after 30 April 2019 must not include discontinued products" Download a file solution1.sql and insert into the file the implementations of the following actions.

(1) First, the script inserts into a sample database information about a new order submitted today that includes two products. One of the products is discontinued while the other is not discontinued. You are allowed to examine the contents of a sample database to find out which products are discontinued and which products are not discontinued and later on apply INSERT statements to implement this step. All other information related to a new order is up to you.

(2) Next, the script creates a single column relational table MESSAGE to store variable size strings no longer than 500 characters.

(3) Next, the script inserts into a relational table MESSAGE information about the contents of a sample database that violate the following consistency constraint. "All orders submitted after 30 April 2019 must not include discontinued products" The script must list the outcomes of verification of the consistency constraint as a single column table with the following messages as the rows in the table. Order submitted on includes a discontinued product

For example, if order_id of an order submitted on 1 May 2019 is equal to 278 and product Chai is discontinued and the product is included into the order then verification of the consistency constraint must return the following message. Order 278 submitted on 2019-05-01 includes a discontinued product Chai Use a function CONCAT to create the messages like the one listed above.

(4) Finally, the script makes the contents of a relational table MESSAGE permanent and lists the contents of the table.

Answer 1

Answering first two parts as per standard:

1)INSERT INTO ORDERS(ID,CUSTOMER_NAME,ADDRESS,ORDER_VALUE,PRODUCT_STATUS)
VALUES (1, 'SAMPLE_NAME','SAMPLE_ADDRESS', 1000,'DISCONTINUED');

INSERT INTO ORDERS(ID,CUSTOMER_NAME,ADDRESS,ORDER_VALUE,PRODUCT_STATUS)
VALUES (2, 'SAMPLE_NAME','SAMPLE_ADDRESS', 2000,'AVAILABLE');

2)CREATE TABLE MESSAGE(

MESSAGE varchar(500)
);