--create database 
use master;
if exists (select * from sysdatabases where name='spj')  drop database spj;
create database spj;

use spj;

--create table s
create table s
(
 sno char(2),
 sname char(10),
 status char(2),
 city char(20)
 primary key(sno)
);

--create table p
create table p
(
 pno char(2),
 pname char(10),
 color char(2),
 weight int,
 primary key(pno)
);

--create table j
create table j
(
 jno char(2),
 jname char(10),
 city char(20),
 primary key(jno)
);

--create table spj
create table spj
(
 sno char(2),
 pno char(2),
 jno char(2),
 qty int,
 primary key(sno,pno,jno),
 foreign key(sno) references s(sno),
 foreign key(pno) references p(pno),
 foreign key(jno) references j(jno)
);


use spj;
--Insert values into tablse s
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', '上海');

--Insert values into tablse p
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);

--Insert values into tablse j
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', '半導體廠', '南京');

--Insert values into tablse spj
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);