API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 24th, 2019
132
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.60 KB | None | 0 0
raw download clone embed print report
  1. drop database if exists dbBarang;
  2. create database dbBarang;
  3. use dbBarang;
  4. CREATE TABLE tblBarang(
  5. kodebarang varchar(5) primary key,
  6. namabarang varchar(255) not null,
  7. stok int,
  8. hargabeli decimal(7,0),
  9. hargajual decimal(7,0)
  10. );
  11.  
  12. INSERT INTO tblBarang VALUES
  13. ('1', 'Spicy Wing', '500', 34000, 35000),
  14. ('10', 'Stikie', '250', 14000, 15000),
  15. ('11', 'Spicy Wing', '500', 29000, 30000),
  16. ('12', 'Spicy chick', '500', 22000, 23000),
  17. ('13', 'Cheesy Brocoli', '500', 31000, 32000),
  18. ('14', 'Crispy Crunch', '300', 12000, 13000),
  19. ('15', 'Fried Chicken', '500', 20000, 21000),
  20. ('16', 'Chicken Tofu', '500', 20000, 21000),
  21. ('17', 'Nugget Dino', '500', 26000, 27000),
  22. ('18', 'Pizza ABC', '500', 25000, 26000),
  23. ('19', 'Happy Star', '500', 25000, 26000),
  24. ('2', 'Super Stick', '500', 28000, 29000),
  25. ('20', 'Action', '500', 25000, 26000),
  26. ('21', 'Fiesta Shoestring', '500', 12000, 13000),
  27. ('22', 'Golden Farm Shoestring', '500', 13000, 14000),
  28. ('23', 'Fiesta Battercoated', '500', 15000, 16000),
  29. ('24', 'Golden Farm Shoestring', '1000', 22000, 23000),
  30. ('25', 'Fiesta Shoestring', '1000', 21000, 22000),
  31. ('26', 'Fiesta Battercoated', '1000', 25000, 26000),
  32. ('27', 'Pok-pok', '500', 25000, 26000),
  33. ('28', 'Siomay', '180', 10500, 11500),
  34. ('29', 'Kentang Jusfray', '450', 11000, 12000),
  35. ('3', 'Cordon Bleu', '500', 36000, 37000),
  36. ('30', 'Cheese 123', '500', 25000, 26000),
  37. ('31', 'Shnitzel', '500', 26000, 27000),
  38. ('32', 'Delistripe', '500', 28000, 29000),
  39. ('33', 'Cheesy Lover', '500', 32000, 33000),
  40. ('34', 'Baso Ayam Bawang', '10', 5500, 6500),
  41. ('35', 'Keecho', '500', 28000, 29000),
  42. ('36', 'Nugget', '250', 10000, 11000),
  43. ('37', 'Nugget', '500', 19000, 20000),
  44. ('38', 'Nugget', '1000', 36000, 37000),
  45. ('39', 'Stick', '250', 10000, 11000),
  46. ('4', 'Ebi Katsu', '500', 36000, 37000),
  47. ('40', 'Nugget Coin', '250', 10000, 11000),
  48. ('41', 'Nugget Coin', '500', 18000, 19000),
  49. ('42', 'Nugget ABC', '250', 10000, 11000),
  50. ('43', 'Nugget ABC', '500', 18000, 19000),
  51. ('44', 'Sosis Ayam 15', '375', 9000, 10000),
  52. ('45', 'Sosis Ayam 3', '75', 1500, 2500),
  53. ('46', 'Sosis Sapi 15', '375', 12000, 13000),
  54. ('47', 'Baso Ayam', '200', 6000, 7000),
  55. ('48', 'Baso Ayam', '500', 14500, 15500),
  56. ('5', 'Karage', '500', 24000, 25000),
  57. ('6', 'Nugget', '500', 24000, 25000),
  58. ('7', 'Stikie', '500', 24000, 25000),
  59. ('8', 'Karage', '250', 14000, 15000),
  60. ('9', 'Nugget', '250', 14000, 15000);
  61.  
  62. CREATE TABLE tblTransMasuk(
  63. nomasuk varchar(10) primary key,
  64. tanggal timestamp not null default now()
  65. );
  66.  
  67. INSERT INTO tblTransMasuk VALUES
  68. ('M-020809-1', '2009-08-02'),
  69. ('M-030809-1', '2009-08-03'),
  70. ('M-030809-2', '2009-08-03'),
  71. ('M-030809-3', '2009-08-03'),
  72. ('M-040809-1', '2009-08-04'),
  73. ('M-040809-2', '2009-08-04'),
  74. ('M-040809-3', '2009-08-04'),
  75. ('M-040809-4', '2009-08-04'),
  76. ('M-040809-5', '2009-08-04'),
  77. ('M-040809-6', '2009-08-04');
  78.  
  79. CREATE TABLE tblTransKeluar(
  80. nokeluar varchar(10) primary key,
  81. tanggal timestamp not null default now()
  82. );
  83.  
  84. INSERT INTO tblTransKeluar VALUES
  85. ('K-020809-1', '2009-08-02'),
  86. ('K-020809-2', '2009-08-02'),
  87. ('K-020809-3', '2009-08-02'),
  88. ('K-020809-4', '2009-08-02'),
  89. ('K-030809-1', '2009-08-03'),
  90. ('K-030809-2', '2009-08-03'),
  91. ('K-030809-3', '2009-08-03'),
  92. ('K-030809-4', '2009-08-03'),
  93. ('K-040809-1', '2009-08-04'),
  94. ('K-040809-2', '2009-08-04');
  95.  
  96. CREATE TABLE tblRinciMasuk(
  97. nomasuk varchar(10) not null,
  98. kodebarang varchar(5) not null,
  99. hargabeli decimal(7,0),
  100. jumlah smallint default 1,
  101. foreign key(nomasuk) references tblTransMasuk(nomasuk),
  102. foreign key(kodebarang) references tblBarang(kodebarang)
  103. );
  104.  
  105. INSERT INTO tblRinciMasuk VALUES
  106. ('M-020809-1', '29', 11000, 65),
  107. ('M-020809-1', '15', 20000, 90),
  108. ('M-020809-1', '2', 28000, 89),
  109. ('M-020809-1', '40', 10000, 86),
  110. ('M-020809-1', '3', 36000, 71),
  111. ('M-020809-1', '42', 10000, 90),
  112. ('M-030809-1', '43', 18000, 53),
  113. ('M-030809-1', '47', 6000, 69),
  114. ('M-030809-1', '26', 25000, 89),
  115. ('M-030809-1', '4', 36000, 80),
  116. ('M-030809-1', '23', 15000, 65),
  117. ('M-030809-1', '31', 26000, 83),
  118. ('M-030809-1', '14', 12000, 64),
  119. ('M-030809-1', '41', 18000, 92),
  120. ('M-030809-1', '29', 11000, 100),
  121. ('M-030809-1', '45', 1500, 62),
  122. ('M-030809-1', '34', 5500, 100),
  123. ('M-040809-1', '6', 24000, 101),
  124. ('M-040809-1', '33', 32000, 51),
  125. ('M-040809-1', '29', 11000, 55),
  126. ('M-040809-1', '6', 24000, 91),
  127. ('M-040809-1', '15', 20000, 52),
  128. ('M-040809-1', '15', 20000, 69),
  129. ('M-040809-2', '48', 14500, 70),
  130. ('M-040809-2', '14', 12000, 58),
  131. ('M-040809-2', '9', 14000, 83),
  132. ('M-040809-2', '21', 12000, 71),
  133. ('M-040809-2', '35', 28000, 67),
  134. ('M-040809-2', '31', 26000, 61),
  135. ('M-040809-2', '10', 14000, 80),
  136. ('M-040809-2', '5', 24000, 73),
  137. ('M-040809-2', '44', 9000, 63),
  138. ('M-040809-2', '39', 10000, 69),
  139. ('M-040809-3', '31', 26000, 82),
  140. ('M-040809-3', '22', 13000, 55),
  141. ('M-040809-3', '28', 10500, 85),
  142. ('M-040809-3', '45', 1500, 93),
  143. ('M-040809-3', '2', 28000, 78),
  144. ('M-040809-3', '45', 1500, 72),
  145. ('M-040809-3', '34', 5500, 76),
  146. ('M-040809-3', '26', 25000, 74),
  147. ('M-040809-3', '18', 25000, 71),
  148. ('M-040809-3', '14', 12000, 53),
  149. ('M-040809-4', '4', 36000, 70),
  150. ('M-040809-4', '19', 25000, 75),
  151. ('M-040809-4', '8', 14000, 74),
  152. ('M-040809-4', '13', 31000, 82),
  153. ('M-040809-4', '27', 25000, 58),
  154. ('M-040809-4', '46', 12000, 83),
  155. ('M-040809-4', '25', 21000, 70),
  156. ('M-040809-4', '6', 24000, 90),
  157. ('M-040809-4', '23', 15000, 88),
  158. ('M-040809-4', '30', 25000, 92),
  159. ('M-040809-4', '2', 28000, 61),
  160. ('M-040809-5', '17', 26000, 57),
  161. ('M-040809-5', '1', 34000, 77),
  162. ('M-030809-2', '41', 18000, 54),
  163. ('M-030809-2', '10', 14000, 85),
  164. ('M-030809-2', '23', 15000, 68),
  165. ('M-030809-2', '8', 14000, 86),
  166. ('M-030809-2', '46', 12000, 77),
  167. ('M-030809-2', '5', 24000, 89),
  168. ('M-030809-3', '25', 21000, 61),
  169. ('M-030809-3', '17', 26000, 55),
  170. ('M-030809-3', '29', 11000, 59),
  171. ('M-030809-3', '46', 12000, 55),
  172. ('M-030809-3', '22', 13000, 64),
  173. ('M-030809-3', '43', 18000, 88),
  174. ('M-040809-6', '13', 31000, 55),
  175. ('M-040809-6', '2', 28000, 66),
  176. ('M-040809-6', '39', 10000, 65),
  177. ('M-040809-6', '12', 22000, 75),
  178. ('M-040809-6', '13', 31000, 67),
  179. ('M-040809-6', '3', 36000, 75),
  180. ('M-040809-6', '11', 29000, 94),
  181. ('M-040809-6', '29', 11000, 89);
  182.  
  183. CREATE TABLE tblRinciKeluar(
  184. nokeluar varchar(10) not null,
  185. kodebarang varchar(5) not null,
  186. hargajual decimal(7,0),
  187. jumlah smallint default 1,
  188. foreign key(nokeluar) references tblTransKeluar(nokeluar),
  189. foreign key(kodebarang) references tblBarang(kodebarang)
  190. );
  191.  
  192. INSERT INTO tblRinciKeluar VALUES
  193. ('K-020809-1', '26', 26000, 54),
  194. ('K-020809-1', '30', 26000, 71),
  195. ('K-020809-1', '46', 13000, 56),
  196. ('K-020809-1', '44', 10000, 82),
  197. ('K-030809-1', '23', 16000, 61),
  198. ('K-030809-1', '48', 15500, 57),
  199. ('K-040809-1', '26', 26000, 97),
  200. ('K-040809-1', '26', 26000, 71),
  201. ('K-040809-1', '41', 19000, 92),
  202. ('K-040809-1', '32', 29000, 87),
  203. ('K-020809-2', '24', 23000, 71),
  204. ('K-020809-2', '33', 33000, 59),
  205. ('K-020809-2', '20', 26000, 78),
  206. ('K-020809-2', '39', 11000, 78),
  207. ('K-030809-2', '11', 30000, 82),
  208. ('K-030809-2', '24', 23000, 85),
  209. ('K-030809-2', '43', 19000, 69),
  210. ('K-030809-2', '15', 21000, 65),
  211. ('K-030809-2', '7', 25000, 77),
  212. ('K-030809-2', '11', 30000, 80),
  213. ('K-030809-3', '23', 16000, 60),
  214. ('K-030809-3', '33', 33000, 88),
  215. ('K-030809-3', '29', 12000, 90),
  216. ('K-030809-3', '8', 15000, 91),
  217. ('K-030809-3', '10', 15000, 99),
  218. ('K-030809-3', '3', 37000, 53),
  219. ('K-030809-3', '38', 37000, 69),
  220. ('K-030809-3', '22', 14000, 56),
  221. ('K-030809-3', '6', 25000, 59),
  222. ('K-030809-3', '2', 29000, 86),
  223. ('K-030809-4', '10', 15000, 74),
  224. ('K-030809-4', '36', 11000, 88),
  225. ('K-030809-4', '19', 26000, 96),
  226. ('K-030809-4', '36', 11000, 55),
  227. ('K-030809-4', '30', 26000, 86),
  228. ('K-030809-4', '1', 35000, 72),
  229. ('K-030809-4', '19', 26000, 64),
  230. ('K-020809-3', '33', 33000, 71),
  231. ('K-020809-3', '35', 29000, 64),
  232. ('K-020809-3', '17', 27000, 72),
  233. ('K-020809-3', '45', 2500, 56),
  234. ('K-020809-3', '31', 27000, 68),
  235. ('K-020809-3', '5', 25000, 59),
  236. ('K-020809-3', '4', 37000, 72),
  237. ('K-020809-3', '46', 13000, 78),
  238. ('K-020809-3', '24', 23000, 100),
  239. ('K-040809-2', '19', 26000, 64),
  240. ('K-040809-2', '24', 23000, 57),
  241. ('K-040809-2', '25', 22000, 99),
  242. ('K-040809-2', '27', 26000, 96),
  243. ('K-040809-2', '32', 29000, 72),
  244. ('K-020809-4', '36', 11000, 86),
  245. ('K-020809-4', '24', 23000, 58);
  246.  
  247. -- SELECT * FROM tblMenu;
  248. -- SELECT * FROM tblBarang;
  249. -- SELECT * FROM tblPemesan;
  250. -- SELECT * FROM tblPesanan;
  251. -- SELECT * FROM tblRinciMasuk;
  252. -- SELECT * FROM tblTransMasuk;
  253. -- SELECT * FROM tblRinciKeluar;
  254. -- SELECT * FROM tblTransKeluar;
  255.  
  256. /*Nomor 0*/
  257. select namabarang,stok,hargabeli from tblBarang order by namabarang ASC, hargabeli DESC;
  258. /*Nomor 1*/
  259. select namabarang,stok,hargabeli from tblBarang
  260. where (namabarang LIKE 'A%')OR(namabarang LIKE 'B%')OR(namabarang LIKE 'E%')OR(namabarang LIKE 'F%')
  261. OR(namabarang LIKE 'P%')
  262. order by namabarang ASC,hargabeli DESC;
  263. /*Nomor 2*/
  264. select namabarang,stok,hargabeli from tblBarang
  265. where namabarang not like '%Sti%'AND namabarang not like '%Nugget%'
  266. order by namabarang ASC,hargabeli DESC;
  267. /*Nomor 3*/
  268. select namabarang,stok from tblBarang
  269. where stok<1000
  270. order by namabarang DESC;
  271. /*Nomor 4*/
  272. select namabarang,stok from tblBarang
  273. where stok>=250 AND stok<=400
  274. order by stok ASC;
  275. /*Nomor 5*/
  276. select namabarang as 'NAMA BARANG',stok as 'JUMLAH STOK',hargabeli as 'HARGA BELI',stok*hargabeli as 'MODAL DASAR'
  277. from tblBarang
  278. order by stok*hargabeli ASC;
  279. /*Nomor 6*/
  280. select namabarang as 'NAMA BARANG',stok as 'JUMLAH STOK',hargabeli as 'HARGA BELI',stok*hargajual as 'HASIL PENJUALAN'
  281. from tblBarang
  282. where namabarang like '%Baso%' or namabarang like '%Nugget%' or namabarang like '%Sosis%'
  283. order by stok*hargajual DESC;
  284. /*nomor 7*/
  285. select namabarang as 'NAMA BARANG',stok as 'JUMLAH STOK',hargabeli as 'HARGA BELI',hargajual as 'HARGA JUAL',stok*hargajual as 'HASIL PENJUALAN',stok*hargabeli as 'MODAL DASAR',stok*hargajual-stok*hargabeli as 'LABA'
  286. from tblBarang
  287. order by stok*hargajual-stok*hargabeli DESC;
  288. /*Nomor 8*/
  289. select namabarang as 'NAMA BARANG',stok as 'JUMLAH STOK',hargabeli as 'HARGA BELI',hargajual as 'HARGA JUAL',stok*hargajual as 'HASIL PENJUALAN',stok*hargabeli as 'MODAL DASAR',stok*hargajual-stok*hargabeli as 'LABA'
  290. from tblBarang
  291. where namabarang not like '%Baso%'AND namabarang not like '%Sosis%' AND namabarang not like '%Nugget%'
  292. order by namabarang ASC;
  293. /*Nomor 9*/
  294. select namabarang as 'Nama Barang',stok as 'STOK',hargabeli as 'Harga Beli',hargajual as 'Harga Jual',hargajual-hargabeli as 'Selisih'
  295. from tblBarang
  296. where namabarang like '%Ayam%' OR namabarang like '%Chick%';
  297. /*Nomor 10*/
  298. select MAX(hargajual)as 'harga jual paling tinggi',MIN(hargajual) as 'harga jual paling rendah',MAX(hargabeli) as 'harga beli paling tinggi',
  299. MIN(hargabeli) as 'harga beli paling rendah', AVG(stok) as 'stok rata-rata'
  300. from tblBarang;
  301. -- Nomor 11
  302. SELECT tblTransMasuk.nomasuk as 'Nomor Nota Masuk', tblTransMasuk.tanggal as 'Tanggal Nota', concat(tblBarang.namabarang,'-','[kode:',tblRinciMasuk.kodebarang,']') as 'Barang Masuk', tblRinciMasuk.jumlah as 'Jumlah'
  303. FROM tblTransMasuk,tblBarang,tblRinciMasuk
  304. WHERE (tblTransMasuk.nomasuk = tblRinciMasuk.nomasuk AND tblBarang.kodebarang = tblRinciMasuk.kodebarang);
  305. -- Nomor 12
  306. SELECT tblTransMasuk.nomasuk as 'Nomor Nota Masuk', tblTransMasuk.tanggal as 'Tanggal Nota', concat(tblBarang.namabarang,'-','[kode:',tblRinciMasuk.kodebarang,']') as 'Barang Masuk', tblRinciMasuk.jumlah as 'Jumlah'
  307. FROM tblTransMasuk,tblBarang,tblRinciMasuk
  308. WHERE (tblTransMasuk.nomasuk = tblRinciMasuk.nomasuk AND tblBarang.kodebarang = tblRinciMasuk.kodebarang) AND RIGHT(tblTransMasuk.nomasuk,1)%2 = 0;
  309. -- Nomor 13
  310. SELECT
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!