建立銷售管理資料庫

學習SQL語言用的範例資料庫（SQLite用）

CREATE TABLE employee(                    --員工名單
  e_num CHAR(3) PRIMARY KEY,              --員工號
  e_name VARCHAR(40) NOT NULL,            --員工姓名
  year INTEGER CHECK(year >= 1970),       --員工入司年
  gender CHAR(1) CHECK(0 or 1),           --員工性別 (0: 女，1: 男)
  office CHAR(2));                        --員工所屬部門號 (office) 

INSERT INTO employee VALUES(101,'Ichihara Etsuo',1972,1,20);
INSERT INTO employee VALUES(102,'Nishida Toshiko',1978,0,10);
INSERT INTO employee VALUES(103,'Noshikino Akiko',1980,0,30);
INSERT INTO employee VALUES(104,'Ymaguchi Tomohito',1985,1,10);
INSERT INTO employee VALUES(105,'Oda Yuko',1988,0,10);
INSERT INTO employee VALUES(106,'Matsushima Nanao',1995,1,20);
INSERT INTO employee VALUES(107,'Hirosue Ryotaro',1999,1,30);

CREATE TABLE customer(                      --客戶名單
  c_num CHAR(4) PRIMARY KEY,                --客戶號
  c_name VARCHAR(40) NOT NULL,              --客戶姓名
  address VARCHAR(20) DEFAULT 'Osaka city', --客戶住址
  office CHAR(2));                          --客戶所屬部門

INSERT INTO customer VALUES(1001,'Imada Kouchi','Osaka city',10);
INSERT INTO customer VALUES(1002,'Yamada Panako','Osaka city',10);
INSERT INTO customer VALUES(1003,'Nishikawa Kiyopi','Uji city',20);
INSERT INTO customer VALUES(1004,'Matsumoto Hiroshi','Osaka city',10);
INSERT INTO customer VALUES(1005,'Nakayama Mipo','Nishinomiya city',30);
INSERT INTO customer VALUES(1006,'Katsuda Sanshi','Kyoto city',20);
INSERT INTO customer VALUES(1007,'Fujii Takahi','Takatsuki city',10);
INSERT INTO customer VALUES(1008,'Ikeno Metaka','Osaka city',10);
INSERT INTO customer VALUES(1009,'Akashiya Samba','Kyoto city',20);
INSERT INTO customer VALUES(1010,'Tsujimoto Shikeo','Osaka city',10);
INSERT INTO customer VALUES(1011,'Uchiba Kachunori','Kobe city',30);
INSERT INTO customer VALUES(1012,'Hamada Masatohi','Takatsuki city',10);
INSERT INTO customer VALUES(1013,'Charly Pama','Kobe city',30);
INSERT INTO customer VALUES(1014,'Ishida Hasushi','Takatsuki city',10);
INSERT INTO customer VALUES(1015,'Higashino Kouchi','Uji city',20);

CREATE TABLE office(                   --部門管理表
  o_num CHAR(2) PRIMARY KEY,           --部門號
  office VARCHAR(20) UNIQUE NOT NULL); --部門名

INSERT INTO office VALUES(10,'Osaka office');
INSERT INTO office VALUES(20,'Kyoto office');
INSERT INTO office VALUES(30,'Kobe office');

CREATE TABLE product(                  --產品管理表
  p_num CHAR(3) PRIMARY KEY,           --產品號
  p_name VARCHAR(40) UNIQUE NOT NULL,  --產品名
  type VARCHAR(20),                    --產品分類
  price INTEGER);                      --產品價格 (單位: 萬日元)

INSERT INTO product VALUES(101,'Accort','sedan',230);
INSERT INTO product VALUES(102,'Accort Wagon','RV',280);
INSERT INTO product VALUES(103,'Insphire','sedan',300);
INSERT INTO product VALUES(104,'Hodyssey','RV',280);
INSERT INTO product VALUES(105,'Shtep Wagon','RV',200);

CREATE TABLE accept_order(                            --訂單管理表
  o_num CHAR(4) PRIMARY KEY,                          --訂單號
  c_num CHAR(4) NOT NULL,                             --訂購汽車的客戶的客戶號
  p_num CHAR(3) NOT NULL,                             --訂購產品號
  dc_rate INTEGER,                                    --產品折扣率
  option_price INTEGER,                               --任選功能價格（萬日元）
  employee CHAR(3),                                   --銷售擔當的員工號
  accept_date CHAR(10) DEFAULT  CURRENT_TIMESTAMP);   --訂購日

INSERT INTO accept_order VALUES(1001,1007,101,5,5,104,'2001-01-15');
INSERT INTO accept_order VALUES(1002,1010,104,5,38,105,'2001-01-18');
INSERT INTO accept_order VALUES(1003,1006,102,15,15,101,'2001-01-20');
INSERT INTO accept_order VALUES(1004,1013,101,10,2,103,'2001-01-31');
INSERT INTO accept_order VALUES(1005,1014,105,10,15,104,'2001-02-10');
INSERT INTO accept_order VALUES(1006,1002,102,10,35,105,'2001-02-19');
INSERT INTO accept_order VALUES(1007,1001,102,10,30,104,'2001-03-01');
INSERT INTO accept_order VALUES(1008,1015,101,5,25,106,'2001-03-08');
INSERT INTO accept_order VALUES(1009,1011,102,5,40,103,'2001-03-09');
INSERT INTO accept_order VALUES(1010,1006,103,20,150,101,'2001-03-09');
INSERT INTO accept_order VALUES(1011,1004,103,20,200,102,'2001-03-10');
INSERT INTO accept_order VALUES(1012,1012,104,20,200,105,'2001-03-15');
INSERT INTO accept_order VALUES(1013,1008,102,10,150,105,'2001-03-19');
INSERT INTO accept_order VALUES(1014,1009,101,30,50,101,'2001-03-20');
INSERT INTO accept_order VALUES(1015,1009,103,10,100,101,'2001-03-21');
INSERT INTO accept_order VALUES(1016,1003,103,25,15,106,'2001-03-21');
INSERT INTO accept_order VALUES(1017,1006,105,15,60,107,'2001-03-21');
INSERT INTO accept_order VALUES(1018,1005,104,15,60,103,'2001-03-25');

（原文網站：TECHSCORE）