DESCdrop database if exists dbBarang;

create database dbBarang;

use dbBarang;

CREATE TABLE tblBarang
(
kodebarang varchar(5) primary key,
namabarang varchar(255) NOT null,
stok int,
hargabeli decimal(7,0),
hargajual decimal(7,0)
);

INSERT INTO tblBarang VALUES
('1', 'Spicy Wing', '500', 34000, 35000),
('10', 'Stikie', '250', 14000, 15000),
('11', 'Spicy Wing', '500', 29000, 30000),
('12', 'Spicy chick', '500', 22000, 23000),
('13', 'Cheesy Brocoli', '500', 31000, 32000),
('14', 'Crispy Crunch', '300', 12000, 13000),
('15', 'Fried Chicken', '500', 20000, 21000),
('16', 'Chicken Tofu', '500', 20000, 21000),
('17', 'Nugget Dino', '500', 26000, 27000),
('18', 'Pizza ABC', '500', 25000, 26000),
('19', 'Happy Star', '500', 25000, 26000),
('2', 'Super Stick', '500', 28000, 29000),
('20', 'Action', '500', 25000, 26000),
('21', 'Fiesta Shoestring', '500', 12000, 13000),
('22', 'Golden Farm Shoestring', '500', 13000, 14000),
('23', 'Fiesta Battercoated', '500', 15000, 16000),
('24', 'Golden Farm Shoestring', '1000', 22000, 23000),
('25', 'Fiesta Shoestring', '1000', 21000, 22000),
('26', 'Fiesta Battercoated', '1000', 25000, 26000),
('27', 'Pok-pok', '500', 25000, 26000),
('28', 'Siomay', '180', 10500, 11500),
('29', 'Kentang Jusfray', '450', 11000, 12000),
('3', 'CORdon Bleu', '500', 36000, 37000),
('30', 'Cheese 123', '500', 25000, 26000),
('31', 'Shnitzel', '500', 26000, 27000),
('32', 'Delistripe', '500', 28000, 29000),
('33', 'Cheesy Lover', '500', 32000, 33000),
('34', 'Baso Ayam Bawang', '10', 5500, 6500),
('35', 'Keecho', '500', 28000, 29000),
('36', 'Nugget', '250', 10000, 11000),
('37', 'Nugget', '500', 19000, 20000),
('38', 'Nugget', '1000', 36000, 37000),
('39', 'Stick', '250', 10000, 11000),
('4', 'Ebi Katsu', '500', 36000, 37000),
('40', 'Nugget Coin', '250', 10000, 11000),
('41', 'Nugget Coin', '500', 18000, 19000),
('42', 'Nugget ABC', '250', 10000, 11000),
('43', 'Nugget ABC', '500', 18000, 19000),
('44', 'Sosis Ayam 15', '375', 9000, 10000),
('45', 'Sosis Ayam 3', '75', 1500, 2500),
('46', 'Sosis Sapi 15', '375', 12000, 13000),
('47', 'Baso Ayam', '200', 6000, 7000),
('48', 'Baso Ayam', '500', 14500, 15500),
('5', 'Karage', '500', 24000, 25000),
('6', 'Nugget', '500', 24000, 25000),
('7', 'Stikie', '500', 24000, 25000),
('8', 'Karage', '250', 14000, 15000),
('9', 'Nugget', '250', 14000, 15000);

CREATE TABLE tblTransMasuk
(
nomasuk varchar(10) primary key,
tanggal timestamp NOT null default now()
);

INSERT INTO tblTransMasuk VALUES
('M-020809-1', '2009-08-02'),
('M-030809-1', '2009-08-03'),
('M-030809-2', '2009-08-03'),
('M-030809-3', '2009-08-03'),
('M-040809-1', '2009-08-04'),
('M-040809-2', '2009-08-04'),
('M-040809-3', '2009-08-04'),
('M-040809-4', '2009-08-04'),
('M-040809-5', '2009-08-04'),
('M-040809-6', '2009-08-04');

CREATE TABLE tblTransKeluar
(
nokeluar varchar(10) primary key,
tanggal timestamp NOT null default now()
);

INSERT INTO tblTransKeluar VALUES
('K-020809-1', '2009-08-02'),
('K-020809-2', '2009-08-02'),
('K-020809-3', '2009-08-02'),
('K-020809-4', '2009-08-02'),
('K-030809-1', '2009-08-03'),
('K-030809-2', '2009-08-03'),
('K-030809-3', '2009-08-03'),
('K-030809-4', '2009-08-03'),
('K-040809-1', '2009-08-04'),
('K-040809-2', '2009-08-04');

CREATE TABLE tblRinciMasuk
(
nomasuk varchar(10) NOT null,
kodebarang varchar(5) NOT null,
hargabeli decimal(7,0),
jumlah smallint default 1,
fOReign key(nomasuk) references tblTransMasuk(nomasuk),
fOReign key(kodebarang) references tblBarang(kodebarang)
);

INSERT INTO tblRinciMasuk VALUES
('M-020809-1', '29', 11000, 65),
('M-020809-1', '15', 20000, 90),
('M-020809-1', '2', 28000, 89),
('M-020809-1', '40', 10000, 86),
('M-020809-1', '3', 36000, 71),
('M-020809-1', '42', 10000, 90),
('M-030809-1', '43', 18000, 53),
('M-030809-1', '47', 6000, 69),
('M-030809-1', '26', 25000, 89),
('M-030809-1', '4', 36000, 80),
('M-030809-1', '23', 15000, 65),
('M-030809-1', '31', 26000, 83),
('M-030809-1', '14', 12000, 64),
('M-030809-1', '41', 18000, 92),
('M-030809-1', '29', 11000, 100),
('M-030809-1', '45', 1500, 62),
('M-030809-1', '34', 5500, 100),
('M-040809-1', '6', 24000, 101),
('M-040809-1', '33', 32000, 51),
('M-040809-1', '29', 11000, 55),
('M-040809-1', '6', 24000, 91),
('M-040809-1', '15', 20000, 52),
('M-040809-1', '15', 20000, 69),
('M-040809-2', '48', 14500, 70),
('M-040809-2', '14', 12000, 58),
('M-040809-2', '9', 14000, 83),
('M-040809-2', '21', 12000, 71),
('M-040809-2', '35', 28000, 67),
('M-040809-2', '31', 26000, 61),
('M-040809-2', '10', 14000, 80),
('M-040809-2', '5', 24000, 73),
('M-040809-2', '44', 9000, 63),
('M-040809-2', '39', 10000, 69),
('M-040809-3', '31', 26000, 82),
('M-040809-3', '22', 13000, 55),
('M-040809-3', '28', 10500, 85),
('M-040809-3', '45', 1500, 93),
('M-040809-3', '2', 28000, 78),
('M-040809-3', '45', 1500, 72),
('M-040809-3', '34', 5500, 76),
('M-040809-3', '26', 25000, 74),
('M-040809-3', '18', 25000, 71),
('M-040809-3', '14', 12000, 53),
('M-040809-4', '4', 36000, 70),
('M-040809-4', '19', 25000, 75),
('M-040809-4', '8', 14000, 74),
('M-040809-4', '13', 31000, 82),
('M-040809-4', '27', 25000, 58),
('M-040809-4', '46', 12000, 83),
('M-040809-4', '25', 21000, 70),
('M-040809-4', '6', 24000, 90),
('M-040809-4', '23', 15000, 88),
('M-040809-4', '30', 25000, 92),
('M-040809-4', '2', 28000, 61),
('M-040809-5', '17', 26000, 57),
('M-040809-5', '1', 34000, 77),
('M-030809-2', '41', 18000, 54),
('M-030809-2', '10', 14000, 85),
('M-030809-2', '23', 15000, 68),
('M-030809-2', '8', 14000, 86),
('M-030809-2', '46', 12000, 77),
('M-030809-2', '5', 24000, 89),
('M-030809-3', '25', 21000, 61),
('M-030809-3', '17', 26000, 55),
('M-030809-3', '29', 11000, 59),
('M-030809-3', '46', 12000, 55),
('M-030809-3', '22', 13000, 64),
('M-030809-3', '43', 18000, 88),
('M-040809-6', '13', 31000, 55),
('M-040809-6', '2', 28000, 66),
('M-040809-6', '39', 10000, 65),
('M-040809-6', '12', 22000, 75),
('M-040809-6', '13', 31000, 67),
('M-040809-6', '3', 36000, 75),
('M-040809-6', '11', 29000, 94),
('M-040809-6', '29', 11000, 89);

CREATE TABLE tblRinciKeluar
(
nokeluar varchar(10) NOT null,
kodebarang varchar(5) NOT null,
hargajual decimal(7,0),
jumlah smallint default 1,
fOReign key(nokeluar) references tblTransKeluar(nokeluar),
fOReign key(kodebarang) references tblBarang(kodebarang)
);

INSERT INTO tblRinciKeluar VALUES
('K-020809-1', '26', 26000, 54),
('K-020809-1', '30', 26000, 71),
('K-020809-1', '46', 13000, 56),
('K-020809-1', '44', 10000, 82),
('K-030809-1', '23', 16000, 61),
('K-030809-1', '48', 15500, 57),
('K-040809-1', '26', 26000, 97),
('K-040809-1', '26', 26000, 71),
('K-040809-1', '41', 19000, 92),
('K-040809-1', '32', 29000, 87),
('K-020809-2', '24', 23000, 71),
('K-020809-2', '33', 33000, 59),
('K-020809-2', '20', 26000, 78),
('K-020809-2', '39', 11000, 78),
('K-030809-2', '11', 30000, 82),
('K-030809-2', '24', 23000, 85),
('K-030809-2', '43', 19000, 69),
('K-030809-2', '15', 21000, 65),
('K-030809-2', '7', 25000, 77),
('K-030809-2', '11', 30000, 80),
('K-030809-3', '23', 16000, 60),
('K-030809-3', '33', 33000, 88),
('K-030809-3', '29', 12000, 90),
('K-030809-3', '8', 15000, 91),
('K-030809-3', '10', 15000, 99),
('K-030809-3', '3', 37000, 53),
('K-030809-3', '38', 37000, 69),
('K-030809-3', '22', 14000, 56),
('K-030809-3', '6', 25000, 59),
('K-030809-3', '2', 29000, 86),
('K-030809-4', '10', 15000, 74),
('K-030809-4', '36', 11000, 88),
('K-030809-4', '19', 26000, 96),
('K-030809-4', '36', 11000, 55),
('K-030809-4', '30', 26000, 86),
('K-030809-4', '1', 35000, 72),
('K-030809-4', '19', 26000, 64),
('K-020809-3', '33', 33000, 71),
('K-020809-3', '35', 29000, 64),
('K-020809-3', '17', 27000, 72),
('K-020809-3', '45', 2500, 56),
('K-020809-3', '31', 27000, 68),
('K-020809-3', '5', 25000, 59),
('K-020809-3', '4', 37000, 72),
('K-020809-3', '46', 13000, 78),
('K-020809-3', '24', 23000, 100),
('K-040809-2', '19', 26000, 64),
('K-040809-2', '24', 23000, 57),
('K-040809-2', '25', 22000, 99),
('K-040809-2', '27', 26000, 96),
('K-040809-2', '32', 29000, 72),
('K-020809-4', '36', 11000, 86),
('K-020809-4', '24', 23000, 58);

--nomOR 0
SELECT namabarang,stok,hargabeli
FROM tblBarang
ORDER BY namabarang ASC hargabeli DESC;

--nomOR1
SELECT namabarang,stok,hargabeli
FROM tblBarang
WHERE namabarang LIKE 'A%' OR  namabarang LIKE 'B%' OR namabarang LIKE 'E%'OR namabarang LIKE 'F%' OR namabarang LIKE 'P%'
ORDER by namabarang ASC;
 --nomOR2
 SELECT namabarang,stok,hargabeli
 FROM tblBarang
 WHERE namabarang NOT LIKE '%NUGGET%' OR NOT LIKE '%Sti%'
 ORDER BY namabarang ASC;

-- nomor 3