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,
I am having some trouble with this for an assignment in
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.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 5 images