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','oswin@yahoo.com','745-387-2874'),
	   ('C0002','Anna','Jeane','163 Lolita land','anna@gmail.com','982-397-4862'),
	   ('C0003','Raymond',NULL,'287 Chucky Surub','raymond@gmail.com','917-376-4672'),
	   ('C0004','Theodeore','Mulia','287 Index Annex','mulia@gmai.com','122-187-1111'),
	   ('C0005','Niki','Emerssan','297 Cult Annex','niki|@yahoo.com','198-292-2223'),
	   ('C0006','Christine','Liviani','275 Love Annex','liviani@yahoo.com','000-1982836'),
	   ('C0007','Hermawan',NULL,'212 Tall Building','hermawan@yahoo.com','192-168-1001'),
	   ('C0008','Agung',NULL,'293 Short Building','agung@yahoo.com','192-168-1993'),
	   ('C0009','Samuel','Sandoro','206 Oppa Annex','sam@live.com','192-168-2973'),
	   ('C0010','Kennard','Alcander','202 Brother Annex','ken@live.com','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','anthony@umn.ac.id','123-456-4565', 5000000),
       ('E0002','Akino','Archilles','Chef','21 Kino Kino', 'akino@student.com', '354-124-5786', 3500000),
       ('E0003','Kevin','Alexander','Cashier','34 Nishinoya', 'alex@yahoo.com','098-123-7832', 3000000),
       ('E0004','Ivy','Marcia','Chef','06 Wall Street','ivy@yahoo.com','846-732-8427',3500000),
       ('E0005','Martha','Saphia','Customer Service','78 Ruby Fort','martha@umn.ac.id','718-298-2873',3200000),
       ('E0006','Nofiandy',NULL,'Cashier','03 Smooth Hair','nofiandy@gmail.com','516-927-9266',3250000),
       ('E0007', 'Octavany',NULL,'Cashier','23 Relaxing Cafe','octa@iseng.com','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';