CREATE DATABASE Restoran;

USE Restoran;

CREATE TABLE Customer(
             ID_Customer VARCHAR(5) PRIMARY KEY,
			 Nama_Depan VARCHAR(30),
			 Nama_Belakang VARCHAR(30),
			 Alamat VARCHAR(30),
			 Email VARCHAR(30),
			 No_Telepon VARCHAR(30)
);

CREATE TABLE Employee(
             ID_Employee VARCHAR(5) PRIMARY KEY,
			 Nama_Depan VARCHAR(30),
			 Nama_Belakang VARCHAR(30),
			 Jabatan VARCHAR(30),
			 Alamat VARCHAR(30),
			 Email VARCHAR(30),
			 No_Telepon VARCHAR(30),
			 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),
			 Transaction_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_Telepon)
VALUES ('C0001', 'Santa', 'Monica', '232 Database Annx', 'santa@live.com', '083-221-8762'),
       ('C0002', 'Sil', 'Eighty', '80 Downhill Building', 'sil@gmail.com', '082-123-0938'),
	   ('C0003', 'Chandra', 'Nalaar', '191 Magic Land', 'chandra@yahoo.com', '089-382-8371'),
	   ('C0004', 'Gideon', 'Zura', '202 Index Annex', 'gideon@live.com', '084-829-3882'),
	   ('C0005', 'Strygwyr', NULL, '147 Bloody Annex', 'strygwyr@gmail.com', '081-826-2372'),
	   ('C0006', 'Mogul', 'Kahn', '182 Ex Building', 'mogul@yahoo.com', '085-562-7782'),
	   ('C0007', 'John', 'Cena', '92 Smack Land', 'john@live.com', '086-829-9283'),
	   ('C0008', 'Budi', NULL, '141 Wonder Land', 'budi@gmail.com', '087-927-8263'),
	   ('C0009', 'Immelman', NULL, '220 Plane Building', 'immelman@yahoo.com', '088-283-9372'),
	   ('C0010', 'Datboi', NULL, '31 Frog Annex', 'datboi@gmail.com', '081-028-0373');

INSERT INTO Employee(ID_Employee, Nama_Depan, Nama_Belakang, Jabatan, Alamat, Email, No_Telepon, Gaji)
VALUES ('E0001', 'Sax', 'Fucelli', 'Manager', '04 Numero Quatro', 'sax@yahoo.com', '084-4444-4444', 10000000),
       ('E0002', 'Smitty', 'Werbenmanjensen', 'Customer Service', '01 Under Sea', 'smitty@gmail.com', '081-111-1111', 5000000),
	   ('E0003', 'Maximillion', 'Pegasus', 'Cashier', '18 Duelist Kingdom', 'maximillion@live.com', '083-171-1377', 2000000),
	   ('E0004', 'Oktavia', 'Seckendorff', 'Chef', '70 Under World', 'oktavia@yahoo.com', '085-120-8753', 3000000),
	   ('E0005', 'Frejya', 'Wion', 'Chef', '05 Walkure Space', 'frejya@gmail.com', '088-237-6718', 3500000),
	   ('E0006', 'Won', NULL, 'Chef', '23 Harvest Moon', 'won@live.com', '089-671-0283', 3750000),
	   ('E0007', 'Barney', NULL, 'Cashier', '45 Dino Street', 'barney@yahoo.com', '087-332-9716', 2500000);

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 Kecap', 'Makanan', 'P0001'),
       ('M0002', 'Soda Mentos', 'Minuman', 'P0002'),
	   ('M0003', 'Es Krim Panas', 'Dessert', 'P0003'),
	   ('M0004', 'Ayam Om', 'Makanan', 'P0002'),
	   ('M0005', 'Teh Pedas', 'Minuman', 'P0001'),
	   ('M0006', 'Giga Pudding', 'Dessert', 'P0002'),
	   ('M0007', 'Nasi UMN', 'Makanan', 'P0003'),
	   ('M0008', 'Kopi Setarbak', 'Minuman', 'P0003'),
	   ('M0009', 'Kue Rebus', 'Dessert', 'P0001'),
	   ('M0010', 'Gudetama', 'Dessert', 'P0001');

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

SELECT CASE
      WHEN CONCAT(Nama_Depan,' ', Nama_Belakang) IS NULL
	  THEN Nama_Depan
	  ELSE CONCAT(Nama_Depan,' ', Nama_Belakang)
	  END AS 'Nama Customer'
FROM Customer
ORDER BY 1;