SQL

SQL TABLE : SUBSCRIPTION, PRODUCT, SPECIFICATION

create table SUBSCRIPTION (
  MSISDN int(13),
  PRODUCT_ID int(7),
  EFFECTIVE_DATE date&time,
  EXPIRED_DATE date&time,
  PRIMARY KEY (PRODUCT_ID));

create table PRODUCT (
  PRODUCT_ID int(7),
  PRODUCT_NAME varchar(30),
  FOREIGN KEY (PRODUCT_ID));
  
create table SPECIFICATION (
  PRODUCT_ID int(7),
  PRICE int(8),
  FOREIGN KEY (PRODUCT_ID));
  

SQL TABLE : SUBSCRIPTION, PRODUCT, SPECIFICATION

create database test_coderbyte;
use test_coderbyte;
create table SUBSCRIPTION (
  MSISDN int(13),
  PRODUCT_ID int(7) PRIMARY KEY AUTO_INCREMENT,
  EFFECTIVE_DATE TIMESTAMP,
  EXPRIRED_DATE TIMESTAMP);
  
insert into SUBSCRIPTION (MSISDN, PRODUCT_ID, EFFECTIVE_DATE, EXPIRED_DATE) value
  (6287889918951, 8212362, '2020-11-19 09:56:58', '2020-12-19 09:56:51'),
  (6287884073148, 8211036, '2020-11-19 08:52:19', '2020-11-22 08:52:17'),
  (6287775663617, 8211310, '2020-11-19 08:38:04', '2020-12-19 00:00:00'),
  (6287884073148, 8211036, '2020-11-18 21:04:28', '2020-11-21 21:04:26');
  
create table PRODUCT (
  PRODUCT_ID int(7) FOREIGN KEY,
  PRODUCT_NAME varchar(30));
 
insert into PRODUCT (PRODUCT_ID, PRODUCT_NAME) value 
  (8211036, "Keluarga 10GB, 3hr, 10rb"),
  (8211310, "BIZ Pro"),
  (8212226, "XTRA Combo 5GB+5GB, 54.000"),
  (8212362, "Xtra Combo Lite 21GB, 30hr");
  
create table SPECIFICATION (
  PRODUCT_ID int(7) FOREIGN KEY,
  PRICE int(8));
  
insert into SPECIFICATION (PRODUCT_ID, PRICE) value 
  (8211036, 10000),
  (8211310, 149000),
  (8212226, 54000),
  (8212362, 105000);