MvH0Pn2v

1DESCdrop 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 int,
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
255--nomOR 0
256SELECT namabarang,stok,hargabeli
257FROM tblBarang
258ORDER BY namabarang ASC hargabeli DESC;
259
260--nomOR1
261SELECT namabarang,stok,hargabeli
262FROM tblBarang
263WHERE namabarang LIKE 'A%' OR  namabarang LIKE 'B%' OR namabarang LIKE 'E%'OR namabarang LIKE 'F%' OR namabarang LIKE 'P%'
264ORDER by namabarang ASC;
265 --nomOR2
266 SELECT namabarang,stok,hargabeli
267 FROM tblBarang
268 WHERE namabarang NOT LIKE '%NUGGET%' OR NOT LIKE '%Sti%'
269 ORDER BY namabarang ASC;
270
271-- nomor 3