· 6 years ago · Oct 24, 2019, 06:00 AM
1drop 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