SHARE
TWEET

SQLearning(UPDATE,DELETE,JOIN,IN,etc)

akevintg Mar 26th, 2015 661 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*LINK .bak :https://www.dropbox.com/s/3id5mtbsjxhocmz/create.bak?dl=0
  2. RightClick Database>Restore Database>Device>Source>Device>Add>open.bak>ok*/
  3.  
  4. -- USE SBD
  5. INSERT INTO MsPaymentType VALUES
  6. ('PT001','Kredit'),
  7. ('PT002','Cash'),
  8. ('PT003','Debit')
  9.  
  10. INSERT INTO MsShipmentType VALUES
  11. ('ST001','Express','100000'),
  12. ('ST002','Regular','50000'),
  13. ('ST003','Economy','25000')
  14.  
  15. INSERT INTO MsCity VALUES
  16. ('CT001','Jakarta'),
  17. ('CT002','Bandung'),
  18. ('CT003','Bekasi'),
  19. ('CT004','Kerawang'),
  20. ('CT005','Purwakarta'),
  21. ('CT006','Cibinong'),
  22. ('CT007','Sukabumi'),
  23. ('CT008','Semarang'),
  24. ('CT009','Klaten'),
  25. ('CT010','Kudus'),
  26. ('CT011','Cilacap'),
  27. ('CT012','Surabaya'),
  28. ('CT013','Gresik'),
  29. ('CT014','Blitar'),
  30. ('CT015','Kediri'),
  31. ('CT016','Malang'),
  32. ('CT017','Tangerang'),
  33. ('CT018','Serang'),
  34. ('CT019','Cilegon'),
  35. ('CT020','Rangkas Bitung'),
  36. ('CT021','Menteng'),
  37. ('CT022','Yogyakarta'),
  38. ('CT023','Wates'),
  39. ('CT024','Pandeglang'),
  40. ('CT025','Wonosari')
  41.  
  42. INSERT INTO MsDestination VALUES
  43. ('DT001','CT001','CT002','10000'),
  44. ('DT002','CT002','CT003','20000'),
  45. ('DT003','CT004','CT005','30000'),
  46. ('DT004','CT006','CT007','40000'),
  47. ('DT005','CT008','CT009','50000'),
  48. ('DT006','CT010','CT011','60000'),
  49. ('DT007','CT012','CT013','70000'),
  50. ('DT008','CT014','CT015','80000'),
  51. ('DT009','CT016','CT017','90000'),
  52. ('DT010','CT018','CT019','15000'),
  53. ('DT011','CT020','CT021','16000'),
  54. ('DT012','CT022','CT003','17000'),
  55. ('DT013','CT024','CT025','18000'),
  56. ('DT014','CT001','CT003','19000'),
  57. ('DT015','CT005','CT007','20000'),
  58. ('DT016','CT009','CT010','22000'),
  59. ('DT017','CT013','CT015','23000'),
  60. ('DT018','CT017','CT019','24000'),
  61. ('DT019','CT021','CT023','25000'),
  62. ('DT020','CT025','CT002','30000'),
  63. ('DT021','CT024','CT003','32000'),
  64. ('DT022','CT023','CT004','33000'),
  65. ('DT023','CT022','CT005','34000'),
  66. ('DT024','CT018','CT006','35000'),
  67. ('DT025','CT011','CT007','45000')
  68.  
  69. INSERT INTO MsStaff VALUES
  70. ('SF001','Jean Karunadewi Wahab','1990-05-11','Female','Wahidin Raya Street No. 1','Jean@gmail.com',5000000),
  71. ('SF002','Netty Setiawan','1992-01-15','Female','Juanda Raya Street No. 19','Netty@yahoo.com',6000000),
  72. ('SF003','Edward Tabianto','1987-05-18','Male','Gatot Subroto Street No. 18','Edward@ymail.com',7000000),
  73. ('SF004','Kelvin Chandra','1990-05-12','Male','Achmad Yani Street No 9','Kelvin@yahoo.com',8000000),
  74. ('SF005','Leo Andika','1980-03-1','Male','Gunung Sahari Raya Street','Leo@gmail.com',9000000),
  75. ('SF006','Febrian Jiuwira','1982-02-21','Male','Purnawarman Street No. 99','Febrian@ymail.com',10000000),
  76. ('SF007','Robert Trisnadi','1991-03-1','Male','Letnan Sari Street No. 8','Robert@yahoo.com',11000000),
  77. ('SF008','Yara Kanigara','1990-01-12','Male','Kalibata Street No. 7','Yara@gmail.com',12000000),
  78. ('SF009','Handy Tedja Sukmana','1988-09-11','Male','Selamat Jaya Street No. 5','Handy@ymail.com',13000000),
  79. ('SF010','Faustina','1987-05-12','Female','Kebun Raya Street No. 9','Faustina@ymail.com',14000000)
  80.  
  81.  
  82. INSERT INTO HeaderShipment VALUES
  83. ('SH001','SF001','DT001','PT001','ST003','Shirley Halim Ng','Taman Ratu Street No. 2','5','2011-11-1'),
  84. ('SH002','SF002','DT002','PT002','ST002','Tri Fennia Lesmana','Kopi Street No. 9','1','2014-10-3'),
  85. ('SH003','SF003','DT003','PT003','ST001','Merianti','Setiabudi Raya Street No. 8','3','2014-10-2'),
  86. ('SH004','SF004','DT004','PT001','ST001','Sukianti','Kemanggisan Street No. 7','4','2014-11-5'),
  87. ('SH005','SF005','DT005','PT002','ST003','Ellys','TPI Sari Street No. 6','5','2014-11-2'),
  88. ('SH006','SF006','DT006','PT001','ST003','Kenrick Satrio','     Muara Karang Street No. 5','7','2014-11-2'),
  89. ('SH007','SF007','DT007','PT002','ST002','Andri','Taman Ancol Street No. 3','8','2014-11-9'),
  90. ('SH008','SF008','DT008','PT003','ST001','Hadi Setiawan','Tangerang Jaya Street No. 3','1','2014-11-3'),
  91. ('SH009','SF009','DT009','PT001','ST001','Denny','Teluk Gong Street No. 2','1','2014-11-29'),
  92. ('SH010','SF010','DT025','PT001','ST001','Erick Kurniawan','Taman Ratu Street No. 10','2','2014-10-15'),
  93. ('SH011','SF001','DT024','PT002','ST002','Vincent','Kerayaan Jaya Street No. 9','3','2014-9-13'),
  94. ('SH012','SF002','DT023','PT001','ST001','Antony Budianto','Solo Makmur Street No. 3','11','2014-9-19'),
  95. ('SH013','SF003','DT022','PT001','ST002','Kharisma Pribadi','Sukabumi Street No. 1','15','2014-9-10'),
  96. ('SH014','SF004','DT021','PT002','ST002','Yoedi Hariadi Kurniawan','Bekasi Jati Street No. 2','35','2014-8-5'),
  97. ('SH015','SF005','DT020','PT002','ST003','Michael Reynaldo Phangtriastu','Sari Ratu Street No. 3','25','2014-8-1'),
  98. ('SH016','SF006','DT011','PT003','ST003','Calvindoro Satyagraha','Daan Mogot Street No. 1','100','2014-8-15'),
  99. ('SH017','SF007','DT013','PT003','ST003','Tata Panca Putra','Alam Sutra Street No. 2','7','2014-7-3'),
  100. ('SH018','SF001','DT015','PT002','ST002','Harris Kristanto','Singkawang Street No. 3','11','2014-7-8'),
  101. ('SH019','SF001','DT017','PT002','ST002','Joni Pohan','Setia Jaya Street No. 1','15','2014-7-5'),
  102. ('SH020','SF002','DT019','PT003','ST001','Arie Purnama','Sudirman Street No. 2','25','2014-11-30')
  103.  
  104. /*Insert these following data!
  105. (insert, getdate)
  106. SH021   SF005   DT001   PT001   ST001   Indra   Kapuas Street No. 12    3       Current Date
  107. */
  108.  
  109. INSERT INTO HeaderShipment
  110. VALUES('SH021','SF005','DT001','PT001','ST001','Indra','Kapuas Street No.12',3,GETDATE())
  111.  
  112. SELECT *
  113. FROM HeaderShipment
  114.  
  115. /*Insert this following data!
  116. (insert, round, rand)
  117. SF011   Effendy         1992-10-1       Male    Tanggerang City Street no 88    Effendy@yahoo.com       Obtained from random result with range between 3000000 and 5000000
  118. */
  119.  
  120. INSERT INTO MsStaff
  121. VALUES ('SF011','Effendy','1992/10/1','Male','Tanggerang City Street No 88','Effendy@yahoo.com',ROUND(RAND()*(5000000-3000000)+3000000,2))
  122.  
  123. SELECT *
  124. FROM MsStaff
  125.  
  126. /*Change StaffGender by replacing the gender with the first character of StaffGender on MsStaff table. Then display all data on MsStaff table.
  127. (update, left)
  128. */
  129.  
  130. UPDATE MsStaff
  131. SET StaffGender=LEFT(StaffGender,1)
  132.  
  133. SELECT *
  134. FROM MsStaff
  135.  
  136. /*Change the StaffEmail into ‘Edward@binus.edu’ and StaffSalary into the current StaffSalary added by 800000 on MsStaff table for every staff
  137. whose id is ‘SF003’. Then display all data on MsStaff table.
  138. (update, like)
  139. */
  140.  
  141. UPDATE MsStaff
  142. SET StaffSalary=StaffSalary+800000, StaffEmail='Edward@binus.edu'
  143. WHERE StaffID LIKE 'SF003'
  144.  
  145. SELECT *
  146. FROM MsStaff
  147.  
  148. /*Change the StaffName into the first name of StaffName on MsStaff table for every staff who had handled the shipment on 30th day. Then display
  149. all data on MsStaff table.
  150. (update, substring, charindex, in, day)
  151. */
  152.  
  153. UPDATE MsStaff
  154. SET StaffName=SUBSTRING(StaffName,1,CHARINDEX(' ',StaffName)-1)
  155. WHERE StaffID IN(
  156.         SELECT StaffID
  157.         FROM HeaderShipment
  158.         WHERE DAY(ShipmentDate)=30
  159. )
  160.  
  161. SELECT *
  162. FROM MsStaff
  163.  
  164. /*Change StaffName by adding ‘Ms. ’ in front of it on MsStaff table for every staff with id ‘SF001’,’SF002’,’SF010’. Then display all data on MsStaff table.
  165. (update, in)
  166. */
  167.  
  168. UPDATE MsStaff
  169. SET StaffName='Ms. '+StaffName
  170. WHERE StaffID IN('SF001','SF002','SF010')
  171.  
  172. SELECT * FROM
  173. MsStaff
  174.  
  175. /*Change ReceiverAddress into ‘Bogor Raya Street No. 3’ on HeaderShipment table for every shipment that been served by staff whose name is ‘Kelvin Chandra’ and
  176. the transaction occurred on 5th day. Then display all data on HeaderShipment table.
  177. (update, in, like, day)
  178. */
  179.  
  180. UPDATE HeaderShipment
  181. SET ReceiverAddress='Bogor Raya Street No. 3'
  182. WHERE DAY(ShipmentDate)=5 AND
  183.         StaffID IN (
  184.                 SELECT StaffID
  185.                 FROM MsStaff
  186.                 WHERE StaffName LIKE 'Kelvin Chandra'
  187.         )
  188.  
  189. SELECT *
  190. FROM HeaderShipment
  191.  
  192. /*Delete data on HeaderShipment table for every transaction which handled by the staff whose name has two words. Then display all data on HeaderShipment table.
  193. (delete, join, like, not like)
  194. */
  195.  
  196. SELECT *
  197. FROM MsStaff
  198.  
  199. DELETE HS FROM HeaderShipment HS JOIN MsStaff MS ON MS.StaffID=HS.StaffID
  200. WHERE StaffName LIKE '% %' AND StaffName NOT LIKE '% % %'
  201.  
  202. SELECT *
  203. FROM HeaderShipment
  204.  
  205. /*Delete data on MsDestination table for every destination that the length of CityFromName (obtained from CityName of CityFrom) is lower than 10 characters.
  206. Then display all data on MsDestination table.
  207. (delete, len)
  208. */
  209.  
  210. SELECT *
  211. FROM MsDestination
  212.  
  213. SELECT *
  214. FROM MsCity
  215.  
  216. DELETE FROM MsDestination
  217. WHERE CityFromID IN(
  218.         SELECT CityID
  219.         FROM MsCity
  220.         WHERE LEN(CityName)<10
  221. )
  222.  
  223. SELECT *
  224. FROM MsDestination
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top