学习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