Kettle: create_sampledata_mysql

samrv發表於2024-08-23
MySql
USE sampledate ;

 CREATE TABLE sampledata.QUADRANT_ACTUALS(REGION VARCHAR(50) NOT NULL,DEPARTMENT VARCHAR(50) NOT NULL,POSITIONTITLE VARCHAR(50) NOT NULL,ACTUAL DECIMAL(18,4),BUDGET DECIMAL(18,4),VARIANCE DECIMAL(18,4))
; CREATE TABLE sampledata.DEPARTMENT_MANAGERS(REGION VARCHAR(50) NOT NULL,MANAGER_NAME VARCHAR(50) NOT NULL,EMAIL VARCHAR(50))
; CREATE TABLE sampledata.CUSTOMERS(CUSTOMERNUMBER INTEGER NOT NULL PRIMARY KEY,CUSTOMERNAME VARCHAR(50) NOT NULL,CONTACTLASTNAME VARCHAR(50) NOT NULL,CONTACTFIRSTNAME VARCHAR(50) NOT NULL,PHONE VARCHAR(50) NOT NULL,ADDRESSLINE1 VARCHAR(50) NOT NULL,ADDRESSLINE2 VARCHAR(50),CITY VARCHAR(50) NOT NULL,STATE VARCHAR(50),POSTALCODE VARCHAR(15),COUNTRY VARCHAR(50) NOT NULL,SALESREPEMPLOYEENUMBER INTEGER,CREDITLIMIT NUMERIC(17))
; CREATE TABLE sampledata.EMPLOYEES(EMPLOYEENUMBER INTEGER NOT NULL PRIMARY KEY,LASTNAME VARCHAR(50) NOT NULL,FIRSTNAME VARCHAR(50) NOT NULL,EXTENSION VARCHAR(10) NOT NULL,EMAIL VARCHAR(100) NOT NULL,OFFICECODE VARCHAR(20) NOT NULL,REPORTSTO INTEGER,JOBTITLE VARCHAR(50) NOT NULL)
; CREATE TABLE sampledata.OFFICES(OFFICECODE VARCHAR(50) NOT NULL PRIMARY KEY,CITY VARCHAR(50) NOT NULL,PHONE VARCHAR(50) NOT NULL,ADDRESSLINE1 VARCHAR(50) NOT NULL,ADDRESSLINE2 VARCHAR(50),STATE VARCHAR(50),COUNTRY VARCHAR(50) NOT NULL,POSTALCODE VARCHAR(10) NOT NULL,TERRITORY VARCHAR(10) NOT NULL,ACTIVE BOOLEAN NOT NULL)
; CREATE TABLE sampledata.ORDERDETAILS(ORDERNUMBER INTEGER NOT NULL,PRODUCTCODE VARCHAR(50) NOT NULL,QUANTITYORDERED INTEGER NOT NULL,PRICEEACH NUMERIC(17) NOT NULL,ORDERLINENUMBER SMALLINT NOT NULL,PRIMARY KEY(ORDERNUMBER,PRODUCTCODE))

; CREATE TABLE sampledata.PAYMENTS(CUSTOMERNUMBER INTEGER NOT NULL,CHECKNUMBER VARCHAR(50) NOT NULL,PAYMENTDATE TIMESTAMP NOT NULL,AMOUNT NUMERIC(17) NOT NULL,PRIMARY KEY(CUSTOMERNUMBER,CHECKNUMBER))

; CREATE TABLE sampledata.DIM_TIME(TIME_ID VARCHAR(10),MONTH_ID INTEGER,QTR_ID INTEGER,YEAR_ID INTEGER,MONTH_NAME CHARACTER(3),MONTH_DESC VARCHAR(9),QTR_NAME VARCHAR(4),QTR_DESC VARCHAR(9))
; CREATE TABLE sampledata.CUSTOMER_W_TER(CUSTOMERNUMBER INTEGER,CUSTOMERNAME VARCHAR(50),CONTACTLASTNAME VARCHAR(50),CONTACTFIRSTNAME VARCHAR(50),PHONE VARCHAR(50),ADDRESSLINE1 VARCHAR(50),ADDRESSLINE2 VARCHAR(50),CITY VARCHAR(50),STATE VARCHAR(50),POSTALCODE VARCHAR(15),COUNTRY VARCHAR(50),EMPLOYEENUMBER INTEGER,CREDITLIMIT DECIMAL(18,4),TERRITORY VARCHAR(10),TERRITORY_COLOR VARCHAR(7));

DROP TABLE sampledata.ORDERS ;
 
 CREATE TABLE sampledata.ORDERS(ORDERNUMBER INTEGER NOT NULL PRIMARY KEY,
 ORDERDATE TIMESTAMP NOT NULL,REQUIREDDATE TIMESTAMP NOT NULL,SHIPPEDDATE TIMESTAMP,
 STATUS VARCHAR(15) NOT NULL,COMMENTS TEXT,CUSTOMERNUMBER INTEGER NOT NULL);
  
  DROP  TABLE sampledata.PRODUCTS;
  
  CREATE TABLE sampledata.PRODUCTS(PRODUCTCODE VARCHAR(50) NOT NULL PRIMARY KEY,
PRODUCTNAME VARCHAR(70) NOT NULL,PRODUCTLINE VARCHAR(50) NOT NULL,
PRODUCTSCALE VARCHAR(10) NOT NULL,PRODUCTVENDOR VARCHAR(50) NOT NULL,
PRODUCTDESCRIPTION TEXT NOT NULL,
QUANTITYINSTOCK SMALLINT NOT NULL,BUYPRICE NUMERIC(17) NOT NULL,
MSRP NUMERIC(17) NOT NULL)
;

 CREATE TABLE SAMPLEDATA.TRIAL_BALANCE(`TYPE` CHARACTER(1),`ACCOUNT_NUM` INTEGER,`CATEGORY` VARCHAR(57),`CATEGORY2` VARCHAR(57),`DETAIL` VARCHAR(57),`AMOUNT` INTEGER)
;

DROP TABLE sampledata.ORDERFACT ;
 CREATE TABLE sampledata.ORDERFACT(ORDERNUMBER INTEGER,PRODUCTCODE VARCHAR(50),
QUANTITYORDERED INTEGER,PRICEEACH DECIMAL(18,4),ORDERLINENUMBER INTEGER,TOTALPRICE DOUBLE,
ORDERDATE TIMESTAMP,REQUIREDDATE TIMESTAMP,SHIPPEDDATE TIMESTAMP,STATUS VARCHAR(15),
COMMENTS TEXT,CUSTOMERNUMBER INTEGER,TIME_ID VARCHAR(10),QTR_ID BIGINT,MONTH_ID BIGINT,
YEAR_ID BIGINT,FOREIGN KEY(PRODUCTCODE) 
REFERENCES sampledata.PRODUCTS(PRODUCTCODE))
;

　　

相關文章