Kettle: create_sampledata_mysql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | 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)) ; |
优质生活从拆开始
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步