Page 232 - Informatics_Practices_Fliipbook_Class12
P. 232

Ans. i.    +------------------+---------------+
                 | Manufacturer     | Avg(UPrice)   |
                 +------------------+---------------+
                 | Washing Powder   | 120.000000    |
                 | Tooth Paste      | 59.500000     |
                 | Soap             | 33.780000     |
                 | Shampoo          | 274.400000    |
                 +------------------+---------------+
              ii.   +---------------+
                 | Manufacturer  |
                 +---------------+
                 | Surf           |
                 | Colgate        |
                 | Lux            |
                 | Pepsodant     |
                 | Dove           |
                 +---------------+
              iii.   +-----------------------+
                 | COUNT(DISTINCT PName) |
                 +-----------------------+
                 | 4                     |
                 +-----------------------+
              iv.   +------------------+------------------+------------------+
                 | PName             | MAX(UPrice)       | MIN(UPrice)      |
                 +------------------+------------------+------------------+
                 | Washing Powder   | 120              | 120              |
                 | Tooth Paste      | 65               | 54               |
                 | Soap             | 43               | 25               |
                 | Shampoo          | 274              | 274              |
                 +------------------+------------------+------------------+
           4.  Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

              a.  Add a new column Discount in the INVENTORY table.
              b.  Set appropriate discount values for all cars keeping in mind the following:
                 i.   No discount is available on the LXI model.

                 ii.  VXI model gives a 10% discount.
                 iii. A 12% discount is given on cars other than LXI model and VXI model.
              c.  Display the name of the costliest car with fuel type “Petrol”.

              d.  Calculate the average discount and total discount available on Car4.
              e.  List the total number of cars having no discount.

         Ans.  a.  ALTER TABLE Inventory ADD Discount DECIMAL(10,2);
              b.  i.    UPDATE Inventory SET Discount=0 WHERE model="LXI";

                 ii.   UPDATE Inventory SET Discount=0.10*price WHERE model="VXI";
                 iii.  UPDATE Inventory SET Discount=0.12*price WHERE model NOT IN ("LXI","VXI");

              c.  SELECT CarName FROM Inventory WHERE Price = (SELECT MAX(Price)FROM Inventory

              d.  SELECT AVG(Discount),SUM(Discount) FROM Inventory WHERE carname="car4";
              e.  SELECT COUNT(*) FROM Inventory WHERE Discount=0;






          218  Touchpad Informatics Practices-XII
   227   228   229   230   231   232   233   234   235   236   237