CREATE database restauran;

USE restauran;

CREATE TABLE customer(
	id_customer VARCHAR(5) PRIMARY KEY,
	nama_depan VARCHAR(30),
	nama_belakang VARCHAR(20),
	alamat VARCHAR(30),
	email VARCHAR(30),
	no_telp VARCHAR(15)
);

CREATE TABLE employee(
	id_employee VARCHAR(5) PRIMARY KEY,
	nama_depan VARCHAR(30),
	nama_belakang VARCHAR(20),
	jabatan VARCHAR(30),
	alamat VARCHAR(30),
	email VARCHAR(30),
	no_telp VARCHAR(15),
	gaji int
);

CREATE TABLE paket (
	id_paket VARCHAR(5) PRIMARY KEY,
	harga_paket int
);


CREATE TABLE menu(
	id_menu VARCHAR(5) PRIMARY KEY,
	nama_menu VARCHAR(30),
	jenis_menu VARCHAR(20),
	id_paket VARCHAR(5),
	FOREIGN KEY (id_paket) REFERENCES paket(id_paket)
);

CREATE TABLE transaction (
	id_customer VARCHAR(5),
	id_paket VARCHAR(5),
	id_employee VARCHAR(5),
	trans_date DATE,
	PRIMARY KEY (id_customer,id_paket,id_employee),
	FOREIGN KEY (id_customer) REFERENCES customer(id_customer),
	FOREIGN KEY (id_paket) REFERENCES paket(id_paket),
	FOREIGN KEY (id_employee) REFERENCES employee(id_employee)
);

INSERT INTO customer(id_customer,nama_depan,nama_belakang,alamat,email,no_telp)
VALUES ('C0001','Michael','Oswin','125 Lugh Land','[email protected]','745-387-2874'),
	   ('C0002','Anna','Jeane','163 Lolita land','[email protected]','982-397-4862'),
	   ('C0003','Raymond',NULL,'287 Chucky Surub','[email protected]','917-376-4672'),
	   ('C0004','Theodeore','Mulia','287 Index Annex','[email protected]','122-187-1111'),
	   ('C0005','Niki','Emerssan','297 Cult Annex','niki|@yahoo.com','198-292-2223'),
	   ('C0006','Christine','Liviani','275 Love Annex','[email protected]','000-1982836'),
	   ('C0007','Hermawan',NULL,'212 Tall Building','[email protected]','192-168-1001'),
	   ('C0008','Agung',NULL,'293 Short Building','[email protected]','192-168-1993'),
	   ('C0009','Samuel','Sandoro','206 Oppa Annex','[email protected]','192-168-2973'),
	   ('C0010','Kennard','Alcander','202 Brother Annex','[email protected]','198-782-3864');


INSERT INTO employee(id_employee,nama_depan,nama_belakang,jabatan,alamat,email,no_telp,gaji)
VALUES ('E0001','Anthony',NULL,'Manager','70 Cool Guy','[email protected]','123-456-4565', 5000000),
       ('E0002','Akino','Archilles','Chef','21 Kino Kino', '[email protected]', '354-124-5786', 3500000),
       ('E0003','Kevin','Alexander','Cashier','34 Nishinoya', '[email protected]','098-123-7832', 3000000),
       ('E0004','Ivy','Marcia','Chef','06 Wall Street','[email protected]','846-732-8427',3500000),
       ('E0005','Martha','Saphia','Customer Service','78 Ruby Fort','[email protected]','718-298-2873',3200000),
       ('E0006','Nofiandy',NULL,'Cashier','03 Smooth Hair','[email protected]','516-927-9266',3250000),
       ('E0007', 'Octavany',NULL,'Cashier','23 Relaxing Cafe','[email protected]','653-876-1928',3150000);

INSERT INTO paket(id_paket, harga_paket)
VALUES ('P0001',3000000),
       ('P0002',2500000),
       ('P0003',2000000);

INSERT INTO Menu(id_menu, nama_menu, jenis_menu, id_paket)
VALUES ('M0001','Nasi Goreng Cinta', 'Makanan', 'P0001'),
        ('M0002','Ayam Bumbu PHP','Makanan','P0002'),
        ('M0003','Soto Daging CPP','Makanan','P0003'),
        ('M0004','Siomay Pak BoBi','Makanan','P0001'),
        ('M0005','Blue Integer','Minuman','P0001'),
        ('M0006','Mocha Float','Dessert','P0003'),
        ('M0007','Choco Banana Split','Dessert','P0002'),
        ('M0008','Es Teh Panas','Minuman','P0002'),
        ('M0009','Nasi Micin','Makanan','P0002'),
        ('M0010','Sapi Chabe','Makanan','P0003');

INSERT INTO transaction(id_customer,id_paket,id_employee,trans_date)
VALUES ('C0001','P0003','E0002','2016-02-25'),
	   ('C0002','P0002','E0001','2016-03-12'),
	   ('C0003','P0002','E0002','2016-04-10'),
	   ('C0004','P0001','E0004','2016-06-28'),
	   ('C0005','P0002','E0001','2016-03-30'),
	   ('C0006','P0003','E0003','2016-03-10'),
	   ('C0007','P0001','E0005','2016-03-01'),
	   ('C0008','P0002','E0002','2016-08-30'),
	   ('C0009','P0003','E0002','2016-11-02'),
	   ('C0010','P0001','E0005','2016-08-18');

SELECT* FROM menu;

SELECT nama_depan,nama_belakang
FROM employee
WHERE jabatan = 'cashier';