设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：

1. 创建表

以S表为例，P表、J表、SPJ表同理的。

create table s

(sno char(5),

sname char(20),

status char(8),

city char(15))

向表内插入记录，以第一条为例，其它同理。

insert into s values('s1','精益','20','天津')

2. 查询

1）select sname,city

from s

2）select pname,color,weight

from p

3）select jno

from spj

where sno='s1'

4）select pname,qty

from p,spj

where p.pno=spj.pno and jno='j2'

5）select pno

from s,spj

where s.sno=spj.sno and city='上海'

6）select jno

from s,spj

where s.sno=spj.sno and city='上海'

7）select jno

from s,spj

where s.sno=spj.sno and city not in('上海')

8）update p

set color='蓝'

where color='红'

9）update spj

set sno='s3'

where sno='s5' and jno='j4' and pno='p6'

10）delete

from s

where sno='s2'

delete

from spj

where sno='s2'

（如果学过触发器的话，也可以用触发器做）

11）insert into spj

values('s2','j6','p4','200')

我记得我以前做过这个题目，我把创建表的脚本给你吧，其它的你自己想着做吧，呵呵不难的

create database SPJ

use SPJ

create table S(SNO varchar(10) primary key,

SNAME varchar(200) NOT NULL,

STATUS int NOT NULL,

CITY varchar(20) NOT NULL)

create table P(PNO varchar(10) primary key,

PNAME varchar(200) NOT NULL,

COLOR varchar(20) NOT NULL,

WEIGHT int check (WEIGHT >0) NOT NULL)

create table J(JNO varchar(10) primary key,

JNAME varchar(200) NOT NULL,

CITY varchar(20) NOT NULL)

create table SPJ(SNO varchar(10) NOT NULL,

PNO varchar(10) NOT NULL,

JNO varchar(10) NOT NULL,

QTY int NOT NULL check(QTY >= 0),

FOREIGN KEY (SNO) REFERENCES S(SNO),

FOREIGN KEY (PNO) REFERENCES P(PNO),

FOREIGN KEY (JNO) REFERENCES J(JNO)

)

INSERT INTO S(SNO,SNAME,STATUS,CITY)

SELECT 'S1','精益',20,'天津'

UNION ALL

SELECT 'S2','盛锡',10,'北京'

UNION ALL

SELECT 'S3','东方红',30,'北京'

UNION ALL

SELECT 'S4','丰泰盛',20,'天津'

UNION ALL

SELECT 'S5','为民',30,'上海'

INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)

SELECT 'P1','螺母','红',12

UNION ALL

SELECT 'P2','螺栓','绿',17

UNION ALL

SELECT 'P3','螺丝刀','蓝',14

UNION ALL

SELECT 'P4','螺丝刀','红',14

UNION ALL

SELECT 'P5','凸轮','蓝',40

UNION ALL

SELECT 'P6','齿轮','红',30

INSERT INTO J(JNO,JNAME,CITY)

SELECT 'J1','三建','北京'

UNION ALL

SELECT 'J2','一汽','长春'

UNION ALL

SELECT 'J3','d簧厂','天津'

UNION ALL

SELECT 'J4','造船厂','天津'

UNION ALL

SELECT 'J5','机车厂','唐山'

UNION ALL

SELECT 'J6','无线电厂','常州'

UNION ALL

SELECT 'J7','半导体厂','南京'

INSERT INTO SPJ(SNO,PNO,JNO,QTY)

SELECT 'S1','P1','J1',200

UNION ALL

SELECT 'S1','P1','J3',100

UNION ALL

SELECT 'S1','P1','J4',700

UNION ALL

SELECT 'S1','P2','J2',100

UNION ALL

SELECT 'S2','P3','J1',400

UNION ALL

SELECT 'S2','P3','J2',200

UNION ALL

SELECT 'S2','P3','J4',500

UNION ALL

SELECT 'S2','P3','J5',400

UNION ALL

SELECT 'S2','P5','J1',400

UNION ALL

SELECT 'S2','P5','J2',100

UNION ALL

SELECT 'S3','P1','J1',200

UNION ALL

SELECT 'S3','P3','J1',200

UNION ALL

SELECT 'S4','P5','J1',100

UNION ALL

SELECT 'S4','P6','J3',300

UNION ALL

SELECT 'S4','P6','J4',200

UNION ALL

SELECT 'S5','P2','J4',100

UNION ALL

SELECT 'S5','P3','J1',200

UNION ALL

SELECT 'S5','P6','J2',200

UNION ALL

SELECT 'S5','P6','J4',500

---