I am having some trouble with this for an assignment in Database. I am trying to create some queries for this homework:

Assignment 5: Data Definition language and Transaction in MySQL

This is what I am currently working on:

On June 3, 2019, customer ‘10010’ makes a payment of $100 in cash. The payment ID is 3428.

The query as it is as of this moment:

SELECT PAYMENT
INSERT INTO PAYMENT (PMT_ID,PMT_DATE,CUS_CODE,PMT_AMT,PMT_TYPE,PMT_DETAILS)
VALUES(3428,6-3-2019,10010,100.00,cash,account),
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE + 100
WHERE CUS_CODE=10010,
COMMIT

2. On May 11, 2019, customer ‘10012’ makes a credit purchase of one unit of product ‘11QER/31’ with a unit price of $110.00; the invoice total is $118.80. The invoice number is 10983, and this invoice has only one product line. [note: no payment has been made here]

The Query as of now:

BEGIN TRANSACTION;

INSERT INTO VOICE VALUES (10983, 10012, ’5-11-2019’),

INSERT INTO LINE VALUES (10983, 1, ‘11QER/31’, 1, 110.00),

UPDATE PRODUCT SET P_QOH=P_QOH-1,

WHERE P_CODE= ’11QER/31’

UPDATE CUSTOMER SET CUS_BALANCE= CUS_BALANCE+110.00,

WHERE CUS_CODE=10012,

SET CUS_DATELSTPUR=’05-11-2019’,

WHERE CUS_CODE=10012,

COMMIT;

3. On September 19, 2019, customer ‘10015’ makes a purchase of 2 units of product ’14- Q1/L3’ with a unit price of $16.00, and 1 unit of ‘23109-HB’ with unit price of $9.00; the invoice total is $44.28. The invoice number is 10988, and this invoice has only two product lines. The customer paid the full price in cash. The payment id is 3520.

The Query as of now:

BEGIN TRANSACTION

INSERT INTO VOICE VALUES (10988, 10015, ’9-19-2019’),

INSERT INTO LINE VALUES (10988, 2, ‘14Q1/L3’ and ‘23109-HB’, 2, 16.00),

UPDATE PRODUCT SET P_QOH=P_QOH-1,

WHERE P_CODE= ‘14-Q1/L31’ and ‘23109-HB’,

UPDATE CUSTOMER SET CUS_BALANCE= CUS_BALANCE-16.00 and 9.00,

WHERE CUS_CODE=10012,

SET CUS_DATELSTPUR=’9-19-2019’,

WHERE CUS_CODE=10012,

INSERT INTO PAYMENT VALUES (3520, ‘9-19-2019’, 10015, 16.00 and 9.00, CASH),

COMMIT;

One last thing from earlier in the assignment was this:

The Payment Table

Image attached to this.

This was the query from earlier and I had a hard time with the validation rule:

CREATE TABLE PAYMENT(

PMT_ID INT PRIMARY KEY,

PMT_DATE DATE NOT NULL,

CUS_CODE INT,

PMT_AMT CHAR(9)

PMT_TYPE TEXT

PMT_DETAILS TEXT,

FOREIGN KEY(CUS_CODE)REFERENCES CUSTOMER(CUS_CODE));

Any help on this would be appreciated because I am not sure what I am doing wrong with these queries.

Also, I hope this is the right subject to be asking a question here.

Thanks.

Transcribed Image Text:Create the PAYMENT table. Remember to save your query for this. See the screenshot below for detailed info. Field Name Data Type Description 8 PMT_ID |РMT_DATE CUS_CODE PMT AMT PMT TYPE AutoNumber Рayment ID Date/Time Payment Date Number Customer Code Number Payment Amount Payment Type: Cc,CASH,CHECK Payment details, bank, credit card type, acount#, etc. Тext PMT_DETAILS Text Field Properties General Lookup Field Size 50 Format Note: The PAYMENT table records any time a customer made a payment. Use PMT_ID INT instead of autonumber for PMT_ID. Add validation rule to the PMT TYPE so that it only allows three values [check the script on creating Customer table ®r the CHECK statement]. The Cus code is a foreign key referencing Customer table.

Question

I am having some trouble with this for an assignment in Database. I am trying to create some queries for this homework:

Assignment 5: Data Definition language and Transaction in MySQL

This is what I am currently working on:

On June 3, 2019, customer ‘10010’ makes a payment of $100 in cash. The payment ID is 3428.

The query as it is as of this moment:

SELECT PAYMENT
INSERT INTO PAYMENT (PMT_ID,PMT_DATE,CUS_CODE,PMT_AMT,PMT_TYPE,PMT_DETAILS)
VALUES(3428,6-3-2019,10010,100.00,cash,account),
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE + 100
WHERE CUS_CODE=10010,
COMMIT

2. On May 11, 2019, customer ‘10012’ makes a credit purchase of one unit of product ‘11QER/31’ with a unit price of $110.00; the invoice total is $118.80. The invoice number is 10983, and this invoice has only one product line. [note: no payment has been made here]

The Query as of now:

BEGIN TRANSACTION;

INSERT INTO VOICE VALUES (10983, 10012, ’5-11-2019’),

INSERT INTO LINE VALUES (10983, 1, ‘11QER/31’, 1, 110.00),

UPDATE PRODUCT SET P_QOH=P_QOH-1,

WHERE P_CODE= ’11QER/31’

UPDATE CUSTOMER SET CUS_BALANCE= CUS_BALANCE+110.00,

WHERE CUS_CODE=10012,

SET CUS_DATELSTPUR=’05-11-2019’,

WHERE CUS_CODE=10012,

COMMIT;

3. On September 19, 2019, customer ‘10015’ makes a purchase of 2 units of product ’14- Q1/L3’ with a unit price of $16.00, and 1 unit of ‘23109-HB’ with unit price of $9.00; the invoice total is $44.28. The invoice number is 10988, and this invoice has only two product lines. The customer paid the full price in cash. The payment id is 3520.

The Query as of now:

BEGIN TRANSACTION

INSERT INTO VOICE VALUES (10988, 10015, ’9-19-2019’),

INSERT INTO LINE VALUES (10988, 2, ‘14Q1/L3’ and ‘23109-HB’, 2, 16.00),

UPDATE PRODUCT SET P_QOH=P_QOH-1,

WHERE P_CODE= ‘14-Q1/L31’ and ‘23109-HB’,

UPDATE CUSTOMER SET CUS_BALANCE= CUS_BALANCE-16.00 and 9.00,

WHERE CUS_CODE=10012,

SET CUS_DATELSTPUR=’9-19-2019’,

WHERE CUS_CODE=10012,

INSERT INTO PAYMENT VALUES (3520, ‘9-19-2019’, 10015, 16.00 and 9.00, CASH),

COMMIT;