在数据库中使用SELECT INTO 语句从SP、J、P表中创建一个新表J_P_SPJ

新表J_P_QTY中的属性列包括：工程号 (JNO)、工程名(JNAME)、零件号(PNO)、零件名(PNAME)及总数量(QTY)，其中总数量的值是各工程使用各种零件的数量总和（不考虑零件是由哪个供应商提供的）。

SPJ(SNO,PNO,JNO,QTY)、J(JNO,JNAME,CITY),P(PNO,PNAME,COLOR,WEIGHT)然后创建一个作用在SPJ表上的Insert型触发器，该触发器能完成两项任务：

附 spj表建表

CREATE DATABASE SPJDB;

GO

USE SPJDB

GO

CREATE TABLE S(

SNO CHAR(3) PRIMARY KEY,

SNAME VARCHAR(10) NOT NULL,

STATUS CHAR(2) NOT NULL,

CITY VARCHAR(6) );

GO

CREATE TABLE P(

PNO CHAR(3) PRIMARY KEY,

PNAME VARCHAR(10) NOT NULL,

COLOR VARCHAR(6) NOT NULL,

WEIGHT NUMERIC(5,3) );

GO

CREATE TABLE J(

JNO CHAR(3) PRIMARY KEY,

JNAME VARCHAR(10) NOT NULL,

CITY VARCHAR(6) );

GO

CREATE TABLE SPJ(

SNO CHAR(3) REFERENCES S,

PNO CHAR(3) REFERENCES P,

JNO CHAR(3) REFERENCES J,

QTY NUMERIC(6),

PRIMARY KEY (SNO,PNO,JNO));

GO

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 INTO S VALUES('S6','吉利',10,'武汉');

GO

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 INTO P VALUES('P7','把手','红',30);

GO

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 INTO J VALUES('J8','二建','南京');

GO

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','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('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);