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

