Q. Given the following tables:
Orders (OrdNo, Ord_date, ProdNo#, Qty)
Product (ProdNo, Descp, Price)
Payment (OrdNo, Pment)
Write a query to delete all those records from table Orders whose complete payment has been made.
Answer :-
DELETE FROM Orders
WHERE Orders.OrdNo = Payment.OrdNo and Orders.ProdNo=Product.ProdNo
AND Payment.Pment = Orders.Qty * Product.Price ;
WHERE Orders.OrdNo = Payment.OrdNo and Orders.ProdNo=Product.ProdNo
AND Payment.Pment = Orders.Qty * Product.Price ;
Explaination :-
At first, I have to make a condition to linking the order, payment, and product table then make another condition that will check whether is payment done by the customer is equal to the price of the product * quantity.
can u make it simpler?
ReplyDeleteNo.
DeleteSigma male...
Deletehello thnk u
ReplyDeletePlz can you explain from where order how you write this query ?
ReplyDeleteAt first i have make condition to link order and payment table then make another condition that will check 'is payment done by customer is equal to price of product * quantity.
DeleteWhat was the need for second condition?
ReplyDeleteWhich condition?
DeleteWill the Product No matter here?
ReplyDeleteNo.
Deletei am writing the query but it is showing error
ReplyDeleteI think you have write wrong spelling. So, please correct it then tell me.
Deletedelete from orders natural join payment where pment is not null; ?
ReplyDeleteRight.
DeleteDELETE FROM Orders
ReplyDeleteWHERE Orders.OrdNo = Payment.OrdNo and Orders.ProdNo=Product.ProdNo
AND Payment.Pment = Orders.Qty * Product.Price ;
Yes.
Deleteone day before computer science exam!....who else is here??
ReplyDeleteAll the best 👍👍👍👍
DeleteThanks, this cleared my doubts!
ReplyDeleteWelcome : )
DeleteThis will never work. I tried doing this by making demo tables and MySQL cannot identify any of the columns from the different tables referred like "tablename.columnname" despite the 3 tables (orders, product, payment) being in the same database.
ReplyDeleteThe "tablename.columnname" only works in select statements where you mention what table the table alias in the "tablename.columnname" refers to. Say, "SELECT tablename.columnname" FROM tablename" then in this syntax, MySQL would understand what table is the column name in.
May you please share screenshot.
DeleteIs it a good question for exam?
ReplyDeleteSeems so!
ReplyDeletePost a Comment
You can help us by Clicking on ads. ^_^
Please do not send spam comment : )