{"id":822,"date":"2013-07-17T10:31:01","date_gmt":"2013-07-17T03:31:01","guid":{"rendered":"http:\/\/blog.kusumoto.co\/?p=822"},"modified":"2013-07-17T10:31:01","modified_gmt":"2013-07-17T03:31:01","slug":"lab-database-1772556","status":"publish","type":"post","link":"https:\/\/oldblog.kusumotolab.com\/?p=822","title":{"rendered":"Lab Database 17\/7\/2556"},"content":{"rendered":"<h1>Preparing Database and table<\/h1>\n<pre lang=\"sql\">\r\ncreate table product (\r\nprod_id varchar(6) primary key,\r\nprod_name varchar(30),\r\nsup_id varchar(6),\r\ncat_id int,\r\nprice money,\r\ninStock int,\r\nonOrder int,\r\nreOrder int\r\n)\r\n<\/pre>\n<h1>File Data &#8216;import.txt&#8217;<\/h1>\n<pre>\r\np0001,Tofu,s0031,2,23.00,33,7,10\r\np0002,Seaweed,s0031,2,6.00,24,11,12\r\np0003,Biscuits Almond,s0005,3,55.00,17,3,8\r\np0004,Ipoh coffee,s0005,1,108.00,11,6,5\r\np0005,Earl grey tea,s0011,1,90.00,15,5,7\r\np0006,Herbal Tea,s0011,1,25.00,21,1,17\r\np0007,Sparkle,s0033,5,107.00,14,3,9\r\np0008,Hershey's,s0112,3,35.00,21,5,12\r\np0009,Seaweed,s0107,2,28.00,16,3,\r\np0010,Fruit jellies,s0052,3,32.00,9,0,6\r\np0011,Syoss Shampoo,s0050,5,78.00,12,2,8\r\np0012,Drink Water,s0021,1,20.00,76,32,28\r\n<\/pre>\n<h1>Import data script<\/h1>\n<pre lang=\"SQL\">\r\nBULK INSERT product\r\nFROM 'c:\\import.txt'\r\nWITH\r\n(\r\nFIELDTERMINATOR = ',',\r\nROWTERMINATOR = '\\n'\r\n)\r\n<\/pre>\n<h1>Homework<\/h1>\n<pre lang=\"SQl\">\r\n\/*1*\/\r\nselect sum(inStock) as 'InStock' \r\nfrom product \r\nwhere sup_id='s0011'\r\n\/*2*\/\r\nselect cat_id,sum(onOrder) as 'Sum Order' \r\nfrom product \r\ngroup by cat_id \r\nhaving avg(inStock) > 10 \r\norder by sum(onOrder) DESC;\r\n\/*3*\/\r\nselect cat_id,max(onOrder) as 'MAX ORDER' \r\nfrom product \r\ngroup by cat_id\r\n\/*4*\/\r\nselect max(price) as 'MAX S0031' , min(price) as 'MIN S0031' \r\nfrom product \r\nwhere sup_id='s0031'\r\n\/*5*\/\r\nselect inStock from product where price > 80;\r\n\/*6*\/\r\nselect cat_id,avg(onOrder) as 'Average onOrder' \r\nfrom product \r\ngroup by cat_id \r\norder by avg(onOrder) ASC\r\n\/*7*\/\r\nselect cat_id,max(onOrder) as 'Max Order' \r\nfrom product \r\ngroup by cat_id \r\nhaving cat_id!=3\r\n\/*8*\/\r\nselect sup_id,avg(onOrder) as 'Average onOrder' ,avg(inStock) as 'Average inStock' \r\nfrom product \r\ngroup by sup_id \r\norder by avg(inStock) DESC \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Preparing Database and table create table product ( prod_id varchar(6) primary key, prod_name varchar(30), sup_id varchar(6), cat_id int, price money, inStock int, onOrder int, reOrder int ) File Data &#8216;import.txt&#8217; p0001,Tofu,s0031,2,23.00,33,7,10 p0002,Seaweed,s0031,2,6.00,24,11,12 p0003,Biscuits Almond,s0005,3,55.00,17,3,8 p0004,Ipoh coffee,s0005,1,108.00,11,6,5 p0005,Earl grey tea,s0011,1,90.00,15,5,7 p0006,Herbal Tea,s0011,1,25.00,21,1,17 p0007,Sparkle,s0033,5,107.00,14,3,9 p0008,Hershey&#8217;s,s0112,3,35.00,21,5,12 p0009,Seaweed,s0107,2,28.00,16,3, p0010,Fruit jellies,s0052,3,32.00,9,0,6 p0011,Syoss Shampoo,s0050,5,78.00,12,2,8 p0012,Drink Water,s0021,1,20.00,76,32,28 Import data script BULK INSERT [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[54,60,9],"tags":[],"class_list":["post-822","post","type-post","status-publish","format-standard","hentry","category-homework","category-mssql","category-private"],"jetpack_publicize_connections":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3OMEb-dg","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/posts\/822","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=822"}],"version-history":[{"count":5,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/posts\/822\/revisions"}],"predecessor-version":[{"id":827,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/posts\/822\/revisions\/827"}],"wp:attachment":[{"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=822"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=822"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=822"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}