数据库学习——建表

建立一个数据库，数据库名称自定。

设有一个设有一个SPJ数据库,包括S,P,J,SPJ四个表
(1) 供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；
(2) 零件表P由零件代码（PNO）、零件名称（PNAME）、颜色（COLOR）、重量（WEIGHT）所组成；
(3) 工程项目表J由工程项目代码（JNO）、工程项目名称（JNAME）、工程项目所在城市（CITY）组成；
(4) 供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量(QTY)所组成；表示某供应商供应某种零件给某工程项目的数量为QTY.

create table s
(
  sno char(2),
  sname char(10),
  status smallint,
  city char(10),
);

insert into s values('S1','精益','20','天津');
insert into s values('S2','盛锡','10','北京');
insert into s values('S3','东方红','30','北京');
insert into s values('S4','丰泰盛','20','天津');
insert into s values('S5','为民','30','上海');

create table j
(
   jno char(2),
   jname char(10),
   city char(10),
);

insert into j values('J1','三建','北京');
insert into j values('J2','一汽','长春');
insert into j values('J3','弹簧厂','天津');
insert into j values('J4','造船厂','天津');
insert into j values('J5','机车厂','唐山');
insert into j values('J6','无线电厂','常州');
insert into j values('J7','半导体厂','南京');

create table p
(
   pno char(2),
   pname char(10),
   color char(10),
   weight smallint,
);

insert into p values('P1','螺母','红','12');
insert into p values('P2','螺栓','绿','17');
insert into p values('P3','螺丝刀','蓝','14');
insert into p values('P4','螺丝刀','红','14');
insert into p values('P5','凸轮','蓝','40');
insert into p values('P6','齿轮','红','30');

create table spj
(
   sno char(2),
   pno char(2),
   jno char(2),
   qty smallint, 
);

insert into spj values('S1','P1','J1','200');
insert into spj values('S1','P1','J3','100');
insert into spj values('S1','P1','J4','700');
insert into spj values('S1','P2','J2','100');
insert into spj values('S2','P3','J1','400');
insert into spj values('S2','P3','J2','200');
insert into spj values('S2','P3','J4','500');
insert into spj values('S2','P3','J5','400');
insert into spj values('S2','P5','J1','400');
insert into spj values('S2','P5','J2','100');
insert into spj values('S3','P1','J1','200');
insert into spj values('S3','P3','J1','200');
insert into spj values('S4','P5','J1','100');
insert into spj values('S4','P6','J3','300');
insert into spj values('S4','P6','J4','200');
insert into spj values('S5','P2','J4','100');
insert into spj values('S5','P3','J1','200');
insert into spj values('S5','P6','J2','200');
insert into spj values('S5','P6','J4','500');

上一封  下一封

« 返回