Page 336 - IT 402 v2.0 class 10
P. 336

et us create the OFFI   table with the following fields and records:

               S_ID        ITEM_NAME              DATE_OF_PURCHASE                  COST         DISCOUNT
               S001      Paper Clip                     2019-01-04                   20               5
               S002      Pencil                         2020-03-22                   10               5
               S003      Sticky Note                    2019-12-09                   45               7
               S004      Diary                          2016-12-30                   120              9
               S005      Pen                            2019-11-17                   30               8

        The query used to create the above table is:

           CRE AT E  T AB L E  O F F I CE  (
             S_ I D  V ARCH AR( 1 0 ) ,
             I T E M _ NAM E  V ARCH AR( 2 5 ) ,
             D AT E _ O F _ P U RCH ASE  D AT E ,
             CO ST  D O U B L E ,
             D I SCO U NT  D O U B L E ,
             ) ;
        Now, inserts the desired records using the following commands:

           I NSE RT  I NT O  O F F I CE  V AL U E S ( ' S0 0 1 ' ,  ' P ap er  Clip ' ,  ' 2 0 1 9 - 0 1 - 0 4 ' ,  2 0 ,  5 ) ;
           I NSE RT  I NT O  O F F I CE  V AL U E S ( ' S0 0 2 ' ,  ' P encil' ,  ' 2 0 2 0 - 0 3 - 2 2 ' ,  1 0 ,  5 ) ;

           I NSE RT  I NT O  O F F I CE  V AL U E S ( ' S0 0 3 ' ,  ' Stick y  Note' ,  ' 2 0 1 9 - 1 2 - 0 9 ' ,  4 5 ,  7 ) ;
           I NSE RT  I NT O  O F F I CE  V AL U E S ( ' S0 0 4 ' ,  ' D iar y ' ,  ' 2 0 1 6 - 1 2 - 3 0 ' ,  1 2 0 ,  9 ) ;
           I NSE RT  I NT O  O F F I CE  V AL U E S ( ' S0 0 5 ' ,  ' P en' ,  ' 2 0 1 9 - 1 1 - 1 7 ' ,  3 0 ,  8 ) ;
        Now  use the S M   function to calculate the total discount of the items having cost greater than   .  he
        following command is used to do so:

           SE L E CT  SU M ( D I SCO U NT )  F RO M  O F F I CE  W H E RE  CO ST >2 0 ;

                                                 + - - - - - - - - - - - - - - - +
                                                 |  SU M ( D I SCO U NT )  |
                                                 + - - - - - - - - - - - - - - - +
                                                 |             2 4  |
                                                 + - - - - - - - - - - - - - - - +

        Now  find the number of items have discount more than 5.  he following command is used to do so:
           SE L E CT  CO U NT ( * )  F RO M  O F F I CE  W H E RE  D I SCO U NT  > 5 ;
                                                    + - - - - - - - - - - +
                                                    |  CO U NT ( * )  |
                                                    + - - - - - - - - - - +
                                                    |         3  |
                                                    + - - - - - - - - - - +
        Now  find the average cost of all the items.  he following command is used to do so:
           SE L E CT  AV G ( CO ST )  F RO M  O F F I CE ;
                                                   + - - - - - - - - - - - +
                                                   |  AV G ( CO ST )  |
                                                   + - - - - - - - - - - - +
                                                   |         4 5  |
                                                   + - - - - - - - - - - - +





         334       Touchpad Information Technology-X
   331   332   333   334   335   336   337   338   339   340   341