Page 231 - Informatics_Practices_Fliipbook_Class12
P. 231

Ans.  a.  8
                  b.  123.23, 340
                  c.  21
                  d.  1979 11 26 November
                  e.  IND ence
                  f.  form actices
               3.  Consider the following table named “Product”, showing details of products being sold in a grocery shop.
                  PCode             PName            UPrice           Manufacture
                  P01               Washing Powder   120              Surf

                  P02               Tooth Paste      54               Colgate
                  P03               Soap             25               Lux
                  P04               Tooth Paste      65               Pepsodent
                  P05               Soap             38               Dove

                  P06               Shampoo          245              Dove
                  a.  Write SQL queries for the following:
                  i.  Create the table Product with appropriate data types and constraints.
                  ii.  Identify the primary key in Product.
                  iii.  List the Product Code, Product name and price in descending order of their product name. If PName is the same then
                    display the data in ascending order of price.
                  iv. Add a new column Discount to the table Product.
                  v.   Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where the
                    UPrice is more than 100, otherwise, the discount will be 0.
                  vi. Increase the price by 12 per cent for all the products manufactured by Dove.
                  vii. Display the total number of products manufactured by each manufacturer.

             Ans.  i.  CREATE TABLE product

                        (PCode CHAR(3) PRIMARY KEY,
                        Pname VARCHAR(25) NOT NULL,
                        Uprice DECIMAL(10,2),
                        Manufacturer VARCHAR(30));
                  ii.  PCode
                  iii. SELECT pcode,pname,uprice FROM product ORDER BY pname DESC,uprice ASC;
                  iv. ALTER TABLE product ADD discount DECIMAL(8,2);
                  v. UPDATE product SET discount=0.10*uprice WHERE uprice >100;

                  vi. UPDATE product SET uprice = uprice+0.12*uprice WHERE manufacturer="Dove";
               b.  Write the output(s) produced by executing the following queries on the basis of the information given above in the table
                  Product:
                  i.  SELECT PName, Average(UPrice) FROM Product GROUP BY Pname;
                  ii. SELECT DISTINCT Manufacturer FROM Product;
                  iii. SELECT COUNT(DISTINCT PName) FROM Notes Product;
                  iv.  SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;





                                                                                     SQL: Working with Two Tables  217
   226   227   228   229   230   231   232   233   234   235   236