Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 61 of 79

Table Creation and Data Manipulation Commands — Question 7

Back to all questions
7
Question

Question 7

Write Query statements for following transaction : (Consider tables of question 12)

  1. Increase price of all products by 10%.
  2. List the details of all orders whose payment is pending as per increased price.
  3. Decrease prices by 10% for all those products for which orders were placed 10 months before.
Answer

The following tables are considered :

Orders (OrdNo, Ord_date, ProdNo#, Qty) 
Product (ProdNo, Descp, Price)
Payment (OrdNo, Pment)

1.

UPDATE product
SET price = (price * 0.1) + price ;

2.

SELECT *
FROM Orders
JOIN Payment ON Orders.OrdNo = Payment.OrdNo
WHERE Payment.Pment = 'Pending';

3.

UPDATE Product
SET Price = Price - (Price * 0.1)
WHERE ProdNo IN (
    SELECT ProdNo#
    FROM Orders
    WHERE YEAR(Ord_date) = YEAR(CURDATE()) - 1 
    AND MONTH(Ord_date) = MONTH(CURDATE()) - 10
);