kqxbTAC3

1drop database if exists dbBarang;
2
3create database dbBarang;
4
5use dbBarang;
6
7CREATE TABLE tblBarang
8(
9kodebarang varchar(5) primary key,
10namabarang varchar(255) not null,
11stok varchar(255),
12hargabeli decimal(7,0),
13hargajual decimal(7,0)
14);
15
16INSERT INTO tblBarang VALUES
17('1', 'Spicy Wing', '500', 34000, 35000),
18('10', 'Stikie', '250', 14000, 15000),
19('11', 'Spicy Wing', '500', 29000, 30000),
20('12', 'Spicy chick', '500', 22000, 23000),
21('13', 'Cheesy Brocoli', '500', 31000, 32000),
22('14', 'Crispy Crunch', '300', 12000, 13000),
23('15', 'Fried Chicken', '500', 20000, 21000),
24('16', 'Chicken Tofu', '500', 20000, 21000),
25('17', 'Nugget Dino', '500', 26000, 27000),
26('18', 'Pizza ABC', '500', 25000, 26000),
27('19', 'Happy Star', '500', 25000, 26000),
28('2', 'Super Stick', '500', 28000, 29000),
29('20', 'Action', '500', 25000, 26000),
30('21', 'Fiesta Shoestring', '500', 12000, 13000),
31('22', 'Golden Farm Shoestring', '500', 13000, 14000),
32('23', 'Fiesta Battercoated', '500', 15000, 16000),
33('24', 'Golden Farm Shoestring', '1000', 22000, 23000),
34('25', 'Fiesta Shoestring', '1000', 21000, 22000),
35('26', 'Fiesta Battercoated', '1000', 25000, 26000),
36('27', 'Pok-pok', '500', 25000, 26000),
37('28', 'Siomay', '180', 10500, 11500),
38('29', 'Kentang Jusfray', '450', 11000, 12000),
39('3', 'Cordon Bleu', '500', 36000, 37000),
40('30', 'Cheese 123', '500', 25000, 26000),
41('31', 'Shnitzel', '500', 26000, 27000),
42('32', 'Delistripe', '500', 28000, 29000),
43('33', 'Cheesy Lover', '500', 32000, 33000),
44('34', 'Baso Ayam Bawang', '10', 5500, 6500),
45('35', 'Keecho', '500', 28000, 29000),
46('36', 'Nugget', '250', 10000, 11000),
47('37', 'Nugget', '500', 19000, 20000),
48('38', 'Nugget', '1000', 36000, 37000),
49('39', 'Stick', '250', 10000, 11000),
50('4', 'Ebi Katsu', '500', 36000, 37000),
51('40', 'Nugget Coin', '250', 10000, 11000),
52('41', 'Nugget Coin', '500', 18000, 19000),
53('42', 'Nugget ABC', '250', 10000, 11000),
54('43', 'Nugget ABC', '500', 18000, 19000),
55('44', 'Sosis Ayam 15', '375', 9000, 10000),
56('45', 'Sosis Ayam 3', '75', 1500, 2500),
57('46', 'Sosis Sapi 15', '375', 12000, 13000),
58('47', 'Baso Ayam', '200', 6000, 7000),
59('48', 'Baso Ayam', '500', 14500, 15500),
60('5', 'Karage', '500', 24000, 25000),
61('6', 'Nugget', '500', 24000, 25000),
62('7', 'Stikie', '500', 24000, 25000),
63('8', 'Karage', '250', 14000, 15000),
64('9', 'Nugget', '250', 14000, 15000);
65
66CREATE TABLE tblTransMasuk
67(
68nomasuk varchar(10) primary key,
69tanggal timestamp not null default now()
70);
71
72INSERT INTO tblTransMasuk VALUES
73('M-020809-1', '2009-08-02'),
74('M-030809-1', '2009-08-03'),
75('M-030809-2', '2009-08-03'),
76('M-030809-3', '2009-08-03'),
77('M-040809-1', '2009-08-04'),
78('M-040809-2', '2009-08-04'),
79('M-040809-3', '2009-08-04'),
80('M-040809-4', '2009-08-04'),
81('M-040809-5', '2009-08-04'),
82('M-040809-6', '2009-08-04');
83
84CREATE TABLE tblTransKeluar
85(
86nokeluar varchar(10) primary key,
87tanggal timestamp not null default now()
88);
89
90INSERT INTO tblTransKeluar VALUES
91('K-020809-1', '2009-08-02'),
92('K-020809-2', '2009-08-02'),
93('K-020809-3', '2009-08-02'),
94('K-020809-4', '2009-08-02'),
95('K-030809-1', '2009-08-03'),
96('K-030809-2', '2009-08-03'),
97('K-030809-3', '2009-08-03'),
98('K-030809-4', '2009-08-03'),
99('K-040809-1', '2009-08-04'),
100('K-040809-2', '2009-08-04');
101
102CREATE TABLE tblRinciMasuk
103(
104nomasuk varchar(10) not null,
105kodebarang varchar(5) not null,
106hargabeli decimal(7,0),
107jumlah smallint default 1,
108foreign key(nomasuk) references tblTransMasuk(nomasuk),
109foreign key(kodebarang) references tblBarang(kodebarang)
110);
111
112INSERT INTO tblRinciMasuk VALUES
113('M-020809-1', '29', 11000, 65),
114('M-020809-1', '15', 20000, 90),
115('M-020809-1', '2', 28000, 89),
116('M-020809-1', '40', 10000, 86),
117('M-020809-1', '3', 36000, 71),
118('M-020809-1', '42', 10000, 90),
119('M-030809-1', '43', 18000, 53),
120('M-030809-1', '47', 6000, 69),
121('M-030809-1', '26', 25000, 89),
122('M-030809-1', '4', 36000, 80),
123('M-030809-1', '23', 15000, 65),
124('M-030809-1', '31', 26000, 83),
125('M-030809-1', '14', 12000, 64),
126('M-030809-1', '41', 18000, 92),
127('M-030809-1', '29', 11000, 100),
128('M-030809-1', '45', 1500, 62),
129('M-030809-1', '34', 5500, 100),
130('M-040809-1', '6', 24000, 101),
131('M-040809-1', '33', 32000, 51),
132('M-040809-1', '29', 11000, 55),
133('M-040809-1', '6', 24000, 91),
134('M-040809-1', '15', 20000, 52),
135('M-040809-1', '15', 20000, 69),
136('M-040809-2', '48', 14500, 70),
137('M-040809-2', '14', 12000, 58),
138('M-040809-2', '9', 14000, 83),
139('M-040809-2', '21', 12000, 71),
140('M-040809-2', '35', 28000, 67),
141('M-040809-2', '31', 26000, 61),
142('M-040809-2', '10', 14000, 80),
143('M-040809-2', '5', 24000, 73),
144('M-040809-2', '44', 9000, 63),
145('M-040809-2', '39', 10000, 69),
146('M-040809-3', '31', 26000, 82),
147('M-040809-3', '22', 13000, 55),
148('M-040809-3', '28', 10500, 85),
149('M-040809-3', '45', 1500, 93),
150('M-040809-3', '2', 28000, 78),
151('M-040809-3', '45', 1500, 72),
152('M-040809-3', '34', 5500, 76),
153('M-040809-3', '26', 25000, 74),
154('M-040809-3', '18', 25000, 71),
155('M-040809-3', '14', 12000, 53),
156('M-040809-4', '4', 36000, 70),
157('M-040809-4', '19', 25000, 75),
158('M-040809-4', '8', 14000, 74),
159('M-040809-4', '13', 31000, 82),
160('M-040809-4', '27', 25000, 58),
161('M-040809-4', '46', 12000, 83),
162('M-040809-4', '25', 21000, 70),
163('M-040809-4', '6', 24000, 90),
164('M-040809-4', '23', 15000, 88),
165('M-040809-4', '30', 25000, 92),
166('M-040809-4', '2', 28000, 61),
167('M-040809-5', '17', 26000, 57),
168('M-040809-5', '1', 34000, 77),
169('M-030809-2', '41', 18000, 54),
170('M-030809-2', '10', 14000, 85),
171('M-030809-2', '23', 15000, 68),
172('M-030809-2', '8', 14000, 86),
173('M-030809-2', '46', 12000, 77),
174('M-030809-2', '5', 24000, 89),
175('M-030809-3', '25', 21000, 61),
176('M-030809-3', '17', 26000, 55),
177('M-030809-3', '29', 11000, 59),
178('M-030809-3', '46', 12000, 55),
179('M-030809-3', '22', 13000, 64),
180('M-030809-3', '43', 18000, 88),
181('M-040809-6', '13', 31000, 55),
182('M-040809-6', '2', 28000, 66),
183('M-040809-6', '39', 10000, 65),
184('M-040809-6', '12', 22000, 75),
185('M-040809-6', '13', 31000, 67),
186('M-040809-6', '3', 36000, 75),
187('M-040809-6', '11', 29000, 94),
188('M-040809-6', '29', 11000, 89);
189
190CREATE TABLE tblRinciKeluar
191(
192nokeluar varchar(10) not null,
193kodebarang varchar(5) not null,
194hargajual decimal(7,0),
195jumlah smallint default 1,
196foreign key(nokeluar) references tblTransKeluar(nokeluar),
197foreign key(kodebarang) references tblBarang(kodebarang)
198);
199
200INSERT INTO tblRinciKeluar VALUES
201('K-020809-1', '26', 26000, 54),
202('K-020809-1', '30', 26000, 71),
203('K-020809-1', '46', 13000, 56),
204('K-020809-1', '44', 10000, 82),
205('K-030809-1', '23', 16000, 61),
206('K-030809-1', '48', 15500, 57),
207('K-040809-1', '26', 26000, 97),
208('K-040809-1', '26', 26000, 71),
209('K-040809-1', '41', 19000, 92),
210('K-040809-1', '32', 29000, 87),
211('K-020809-2', '24', 23000, 71),
212('K-020809-2', '33', 33000, 59),
213('K-020809-2', '20', 26000, 78),
214('K-020809-2', '39', 11000, 78),
215('K-030809-2', '11', 30000, 82),
216('K-030809-2', '24', 23000, 85),
217('K-030809-2', '43', 19000, 69),
218('K-030809-2', '15', 21000, 65),
219('K-030809-2', '7', 25000, 77),
220('K-030809-2', '11', 30000, 80),
221('K-030809-3', '23', 16000, 60),
222('K-030809-3', '33', 33000, 88),
223('K-030809-3', '29', 12000, 90),
224('K-030809-3', '8', 15000, 91),
225('K-030809-3', '10', 15000, 99),
226('K-030809-3', '3', 37000, 53),
227('K-030809-3', '38', 37000, 69),
228('K-030809-3', '22', 14000, 56),
229('K-030809-3', '6', 25000, 59),
230('K-030809-3', '2', 29000, 86),
231('K-030809-4', '10', 15000, 74),
232('K-030809-4', '36', 11000, 88),
233('K-030809-4', '19', 26000, 96),
234('K-030809-4', '36', 11000, 55),
235('K-030809-4', '30', 26000, 86),
236('K-030809-4', '1', 35000, 72),
237('K-030809-4', '19', 26000, 64),
238('K-020809-3', '33', 33000, 71),
239('K-020809-3', '35', 29000, 64),
240('K-020809-3', '17', 27000, 72),
241('K-020809-3', '45', 2500, 56),
242('K-020809-3', '31', 27000, 68),
243('K-020809-3', '5', 25000, 59),
244('K-020809-3', '4', 37000, 72),
245('K-020809-3', '46', 13000, 78),
246('K-020809-3', '24', 23000, 100),
247('K-040809-2', '19', 26000, 64),
248('K-040809-2', '24', 23000, 57),
249('K-040809-2', '25', 22000, 99),
250('K-040809-2', '27', 26000, 96),
251('K-040809-2', '32', 29000, 72),
252('K-020809-4', '36', 11000, 86),
253('K-020809-4', '24', 23000, 58);
254--1
255select tblBarang.namabarang,tblBarang.stok,tblBarang.hargabeli
256from tblBarang ORDER BY tblBarang.namabarang asc;
257-- 2
258select tblBarang.namabarang,tblBarang.stok,tblBarang.hargabeli
259from tblBarang
260where tblBarang.namabarang like 'A%' OR
261tblBarang.namabarang like 'B%' OR
262tblBarang.namabarang like 'E%' OR
263tblBarang.namabarang like 'F%' OR
264tblBarang.namabarang like 'P%';
265 ORDER BY tblBarang.namabarang asc;
266-- 3
267select tblBarang.namabarang,tblBarang.stok
268from tblBarang
269where tblBarang.namabarang not like '%sti%' and
270tblBarang.namabarang not like '%Nugget%';
271--4
272select tblBarang.namabarang,tblBarang.stok
273from tblBarang
274where tblBarang.stok <1000
275order by tblBarang.stok desc;
276--5
277select tblBarang.namabarang,tblBarang.stok
278from tblBarang
279where tblBarang.stok >250 and tblBarang.stok<400
280order by tblBarang.stok desc;
281-- 6
282select tblBarang.namabarang,tblBarang.stok,tblBarang.hargabeli,tblBarang.stok*tblBarang.hargabeli as 'modaldasar'
283from tblBarang
284order by modaldasar asc;
285-- 7
286select tblBarang.namabarang,tblBarang.stok,tblBarang.hargajual,tblBarang.stok*tblBarang.hargajual as 'penjualan'
287from tblBarang
288where tblBarang.namabarang like '%Sosis%' or
289 tblBarang.namabarang like '%Bakso%' or
290tblBarang.namabarang like '%Nugget%'
291order by penjualan desc;
292
293-- 8
294select tblBarang.namabarang, tblBarang.stok, tblBarang.hargabeli, tblBarang.hargajual, tblBarang.stok*tblBarang.hargabeli as 'modaldasar' ,
295       tblBarang.stok*tblBarang.hargajual as 'penjualan', (tblBarang.stok*tblBarang.hargajual)-(tblBarang.stok*tblBarang.hargabeli) as 'laba'
296from tblBarang
297order by laba desc;
298
299-- 9
300select tblBarang.namabarang, tblBarang.stok, tblBarang.hargabeli, tblBarang.hargajual, tblBarang.stok*tblBarang.hargabeli as 'modaldasar' ,
301       tblBarang.stok*tblBarang.hargajual as 'penjualan', (tblBarang.stok*tblBarang.hargajual)-(tblBarang.stok*tblBarang.hargabeli) as 'laba'
302from tblBarang
303where tblBarang.namabarang not like '%Sosis%' AND
304 tblBarang.namabarang not like '%Bakso%' AND
305tblBarang.namabarang not like '%Nugget%'
306order by laba desc;
307
308-- 10
309select tblBarang.namabarang, tblBarang.stok, tblBarang.hargabeli, tblBarang.hargajual,tblBarang.hargajual-tblBarang.hargabeli as 'selisih'
310from tblBarang
311where tblBarang.namabarang like '%Ayam%' or
312 tblBarang.namabarang like '%chick%';
313-- 11
314select  max(tblBarang.hargajual),min(tblBarang.hargabeli),max(tblBarang.hargabeli),max(tblBarang.hargajual),avg(tblBarang.stok)
315from tblBarang;
316
317--12
318select tblTransMasuk.nomasuk,
319tblTransMasuk.tanggal,
320concat(tblBarang.namabarang,'-',tblRinciMasuk.kodebarang) as'barang masuk',
321tblRinciMasuk.jumlah
322from tblBarang,tblRinciMasuk,tblTransMasuk
323where tblBarang.kodebarang=tblRinciMasuk.kodebarang
324AND tblTransMasuk.nomasuk=tblRinciMasuk.nomasuk;
325
326-- 13
327select tblTransMasuk.nomasuk,
328tblTransMasuk.tanggal,
329concat(tblBarang.namabarang,'-',tblRinciMasuk.kodebarang) as'barang masuk',
330tblRinciMasuk.jumlah
331from tblBarang,tblRinciMasuk,tblTransMasuk
332where tblBarang.kodebarang=tblRinciMasuk.kodebarang
333AND tblTransMasuk.nomasuk=tblRinciMasuk.nomasuk and
334right(tblTransMasuk.nomasuk,1)%2=0;
335
336-- 14
337select tblTransMasuk.nomasuk,
338tblTransMasuk.tanggal,
339concat(tblBarang.namabarang,'-',tblRinciMasuk.kodebarang) as'barang masuk',
340tblRinciMasuk.jumlah,tblRinciMasuk.hargabeli, (tblRinciMasuk.jumlah*tblRinciMasuk.hargabeli) as 'total'
341from tblBarang,tblRinciMasuk,tblTransMasuk
342where tblBarang.kodebarang=tblRinciMasuk.kodebarang
343AND tblTransMasuk.nomasuk=tblRinciMasuk.nomasuk;
344
345-- 15
346select tblTransMasuk.nomasuk,
347tblTransMasuk.tanggal,
348concat(tblBarang.namabarang,'-',tblRinciMasuk.kodebarang) as'barang masuk',
349tblRinciMasuk.jumlah,tblRinciMasuk.hargabeli, (tblRinciMasuk.jumlah*tblRinciMasuk.hargabeli) as total
350from tblBarang,tblRinciMasuk,tblTransMasuk
351where tblBarang.kodebarang=tblRinciMasuk.kodebarang
352AND tblTransMasuk.nomasuk=tblRinciMasuk.nomasuk and
353(tblRinciMasuk.jumlah*tblRinciMasuk.hargabeli)<=1000000;
354
355-- 16
356select tblRinciKeluar.nokeluar,tblBarang.namabarang
357from tblTransKeluar,tblBarang,tblRinciKeluar
358where tblRinciKeluar.nokeluar=tblTransKeluar.nokeluar AND
359tblBarang.kodebarang=tblRinciKeluar.kodebarang and
360(tblBarang.namabarang like '%Nugget%' or tblBarang.namabarang like '%baso%');
361
362-- 17
363select tblTransKeluar.nokeluar, SUM(tblRinciKeluar.hargajual) as 'grandtotal'
364from tblTransKeluar,tblRinciKeluar,tblBarang
365where tblRinciKeluar.nokeluar=tblTransKeluar.nokeluar AND
366tblBarang.kodebarang=tblRinciKeluar.kodebarang and (tblTransKeluar.nokeluar="K-020809-1");
367
368-- 18
369select tblTransKeluar.nokeluar, tblRinciKeluar.kodebarang,tblBarang.namabarang
370from tblTransKeluar,tblRinciKeluar,tblBarang
371where tblRinciKeluar.nokeluar=tblTransKeluar.nokeluar AND
372tblBarang.kodebarang=tblRinciKeluar.kodebarang and (tblRinciKeluar.kodebarang%2=1);
373
374-- 19
375select tblTransKeluar.nokeluar,tblBarang.namabarang
376from tblTransKeluar,tblRinciKeluar,tblBarang
377where tblRinciKeluar.nokeluar=tblTransKeluar.nokeluar AND
378tblBarang.kodebarang=tblRinciKeluar.kodebarang and(tblBarang.namabarang like'C%' or tblBarang.namabarang like'N%' or tblBarang.namabarang like'S%');
379-- 20
380select tblBarang.namabarang,tblRinciMasuk.hargabeli,tblRinciMasuk.jumlah,
381(tblRinciMasuk.jumlah*tblRinciMasuk.hargabeli) as 'total',tblTransMasuk.tanggal
382from tblBarang,tblRinciMasuk,tblTransMasuk
383where tblBarang.kodebarang=tblRinciMasuk.kodebarang
384AND tblTransMasuk.nomasuk=tblRinciMasuk.nomasuk And (tblTransMasuk.tanggal=' 2009-08-02' or tblTransMasuk.tanggal=' 2009-08-03');
385-- 21
386select tblBarang.namabarang,tblRinciMasuk.hargabeli,tblRinciMasuk.jumlah,
387(tblRinciMasuk.jumlah*tblRinciMasuk.hargabeli) as 'total',tblTransMasuk.tanggal
388from tblBarang,tblRinciMasuk,tblTransMasuk
389where tblBarang.kodebarang=tblRinciMasuk.kodebarang
390AND tblTransMasuk.nomasuk=tblRinciMasuk.nomasuk and tblRinciMasuk.jumlah>70 And (tblTransMasuk.tanggal=' 2009-08-02' or tblTransMasuk.tanggal=' 2009-08-03');