{"id":795,"date":"2013-07-10T10:50:00","date_gmt":"2013-07-10T03:50:00","guid":{"rendered":"http:\/\/blog.kusumoto.co\/?p=795"},"modified":"2013-07-11T08:38:24","modified_gmt":"2013-07-11T01:38:24","slug":"lab-database-1072556","status":"publish","type":"post","link":"https:\/\/oldblog.kusumotolab.com\/?p=795","title":{"rendered":"Lab Database 10\/7\/2556"},"content":{"rendered":"<h2>Preparing Database<\/h2>\n<pre lang=\"SQL\">\/* Create Database and use Database *\/\r\ncreate database Northwind\r\nuse Northwind\r\n\/* Create Table *\/\r\ncreate table Orders (\r\nord_id int primary key,\r\nemp_id varchar(7),\r\ncus_id varchar(7),\r\nshipName varchar(30),\r\nfreight money,\r\nqty int,\r\norderDate datetime,\r\nshipedDate datetime\r\n);\r\n\r\n\/* Insert Data *\/\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10249, 'e0003', 'c0031' , 'North\/South', 60.00, 24, '10\/03\/1997 00:00:00', '10\/31\/1997 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10250, 'e0023', 'c0005' ,'The Cracker Box' ,155.33, 17 ,'10\/13\/1997 00:00:00' ,'11\/24\/1997 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10251, 'e0021', 'c0157', 'Bon app', 108.00, 11, '01\/15\/1998 00:00:00', '02\/12\/1998 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10252, 'e0011', 'c1019' ,'Around the Horn', 90.75, 15, '03\/16\/1998 00:00:00', '04\/27\/1998 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10253, 'e0011', 'c1021', 'Eastern Connection', 215.21, 21, '04\/09\/1998 00:00:00', '05\/07\/1998 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10254, 'e0017', 'c0733', 'The Cracker Box', 107.75, 14, '09\/18\/1996 00:00:00', '10\/16\/1996 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10255, 'e0008', 'c0112', 'QUICK-Stop' ,135.33, 21, '08\/08\/1997 00:00:00', '09\/05\/1997 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10256, 'e0021', 'c0107', 'Bon app', 231.15, 16, '11\/28\/1997 00:00:00', '12\/26\/1997 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10257, 'e0003', 'c0052', 'Bon app', 132.25, 9, '03\/04\/1998 00:00:00', '04\/01\/1998 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10258, 'e0005', 'c0050', 'QUICK-Stop', 178.67, 12, '11\/27\/1996 00:00:00', '12\/25\/1996 00:00:00')\r\ninsert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10259, 'e0017', 'c0015', 'Eastern Connection', 210.45 ,76, '04\/15\/1997 00:00:00', '05\/13\/1997 00:00:00')\r\n\/* Select Data to show *\/\r\nselect * from Orders;<\/pre>\n<h2>Homework<\/h2>\n<pre lang=\"SQL\">\r\n\/*1*\/\r\nselect *,right(cast(ord_id as varchar(10)),5) as '\u0e2a\u0e34\u0e19\u0e04\u0e49\u0e32\u0e02\u0e2d\u0e07\u0e25\u0e39\u0e01\u0e04\u0e49\u0e32\u0e17\u0e35\u0e48\u0e21\u0e35 5' from Orders where ord_id like '%5%'\r\n\/*2*\/\r\nselect shipName,Replace(shipName,'Around the Horn','Around the World') from Orders\r\n\/*3*\/\r\nselect shipedDate,datepart(day,(dateadd(day,5,shipedDate)))as addday from Orders\r\n\/*4*\/\r\nselect * ,Len(shipName) as '\u0e04\u0e27\u0e32\u0e21\u0e22\u0e32\u0e27\u0e0a\u0e37\u0e48\u0e2d', Ceiling(freight) as '\u0e1b\u0e31\u0e14\u0e17\u0e28\u0e19\u0e34\u0e22\u0e21' from Orders Where freight>155\r\n\/*5*\/\r\nselect * from Orders where ord_id>10252\r\n\/*6*\/\r\nselect * from Orders where Cast(orderDate as varchar(30)) like '%1998%'\r\n\/*7*\/\r\nselect *,Square(qty) as '\u0e23\u0e32\u0e04\u0e32\u0e22\u0e01\u0e01\u0e33\u0e25\u0e31\u0e07 2' from Orders Where Square(qty) BETWEEN 120 and 350\r\n\/*8*\/\r\nselect shipName,DateDiff(week,orderDate,shipedDate) as '\u0e08\u0e33\u0e19\u0e27\u0e19\u0e2a\u0e31\u0e1b\u0e14\u0e32\u0e2b\u0e4c' from Orders Where DateDiff(week,orderDate,shipedDate)>=4\r\n\/*9*\/\r\nSELECT SERVERPROPERTY('edition'),GetDate()\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Preparing Database \/* Create Database and use Database *\/ create database Northwind use Northwind \/* Create Table *\/ create table Orders ( ord_id int primary key, emp_id varchar(7), cus_id varchar(7), shipName varchar(30), freight money, qty int, orderDate datetime, shipedDate datetime ); \/* Insert Data *\/ insert into Orders (ord_id,emp_id,cus_id,shipName,freight,qty,orderDate,shipedDate) values (10249, &#8216;e0003&#8217;, &#8216;c0031&#8217; , &#8216;North\/South&#8217;, [&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],"tags":[],"class_list":["post-795","post","type-post","status-publish","format-standard","hentry","category-homework","category-mssql"],"jetpack_publicize_connections":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3OMEb-cP","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/posts\/795","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=795"}],"version-history":[{"count":12,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/posts\/795\/revisions"}],"predecessor-version":[{"id":807,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=\/wp\/v2\/posts\/795\/revisions\/807"}],"wp:attachment":[{"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=795"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=795"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oldblog.kusumotolab.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=795"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}