test-复习_答案

2、用CREATE语句按要求建立如下四个表，并写出相应的语句。

（1）供应商表S： 列名 SNO SNAME STATUS CITY (

SNO CHAR(6)primary key, SNAME VARCHAR(20)NOT NULL, STATUS varchar(50), CITY VARCHAR(50) )

说明 供应商号 供应商名 供应商状态 所在城市 数据类型 CHAR(6) VARCHAR(20) VARCHAR(50) VARCHAR(50) 约束 PRIMARY KEY NOT NULL Create table S （2）零件表P： 列名 PNO PNAME COLOR WEIGHT

Create table P (

PNO CHAR(6)primary key, PNAME VARCHAR(20)NOT NULL, COLOR char(2),

WEIGHT numeric(9,2)check (WEIGHT>0 AND WEIGHT<=100) )

说明 零件号 零件名 颜色 重量 数据类型 CHAR(6) VARCHAR(20) CHAR(2) NUMERIC(9,2) 约束 PRIMARY KEY NOT NULL CHECK(WEIGHT>0 AND WEIGHT<=100)

（3）工程项目表J： 列名 JNO JNAME 说明 项目号 项目名 数据类型 CHAR(6) VARCHAR(20) 约束 PRIMARY KEY NOT NULL CITY (

城市 VARCHAR(50) Create table J JNO CHAR(6)primary key, JNAME VARCHAR(20)NOT NULL, CITY varchar(50), )

（4）供应情况表SPJ： 列名 SNO PNO JNO QTY (

SNO CHAR(6)not null, PNO CHAR(6)NOT NULL, JNO CHAR(6)NOT NULL, QTY smallInt Default 100 )

说明 供应商号 零件号 项目号 供应数量 数据类型 CHAR(6) CHAR(6) CHAR(6) SMALLINT 约束 NOT NULL NOT NULL NOT NULL DEFAULT 100 Create table SPJ

3、利用SQL语句对表结构进行修改: 为零件表P增加一个规格(GUIGE)列，数据类型

为字符，长度50。 alter table P

add GUIGE char(50)a

4、使用insert 语句像表中输入数据： SNO S1 S2 SNAME 精益 盛锡 STATUS 20 10 CITY 天津 北京 insert dbo.S values('S1','精益','20','天津') insert dbo.S values('S2','盛锡','10','北京')

PNO P1 P2 PNAME 螺母 螺栓 COLOR 红 绿 WEIGHT 12 17 insert into dbo.P(PNO, PNAME, COLOR, WEIGHT) values('P1','螺母','红',12 )

insert into dbo.P values('P2','螺栓','绿',17,default)

JNO J1 J2 JNAME 一汽 半导体厂 CITY 北京 南京 insert into dbo.J values('J1','一汽','北京') insert into dbo.J values('J2','半导体厂','南京') SN0 S1 S1 PNO P1 P2 JNO J1 J1 QTY 200 100 insert into dbo.SPJ values('S1','P1','J1',200) insert into dbo.SPJ(SNO,PNO,JNO,QTY) values('S2','P2','J1',100)

5、按要求修改表中数据：

（1）将所有spj表中数量QTY每行加1。

update spj set QTY=QTY+1

（2）修改“S1”、“P1”的QTY为98。

UPDATE dbo.SPJ set QTY=98 where SNO='S1' and PNO='P1'

（3）删除工程项目表J中城市为“南京”的项目信息。 delete from dbo.J where CITY='南京'

6、按要求写出对应查询数据的SQL语句 （1）查询全体零件的信息。 select * from dbo.P

（2）查询颜色为“红”的零件的零件号，零件名和四舍五入到整数后的零件重量。

select PNO,round(WEIGHT,0) from dbo.P where color='红' （3）找出使用供应商S1锁供应零件的工程号码。

select JNO from dbo.SPJ WHERE SNO ='S1' （4）找出所有零件的名称、颜色、重量。

select PNAME, COLOR, WEIGHT FROM dbo.P

(5) 找出工程项目J2使用的各种零件的名称及其数量。 select PNAME, WEIGHT From dbo.P WHERE PNO in (select PNO from dbo.SPJ where JNO='J2')

select pname,qty from dbo.p inner join dbo.spj on p.sno=spj.sno where pno=’J2’

(6)找出上海厂商供应的所有零件号码。

select PNO FROM dbo.SPJ where SNO in (select SNO from dbo.S where City='上海' )

(7)出使用上海产的零件的工程名称。

select JNAME FROM dbo.J where JNO in (select JNO from dbo.SPJ where sno=(s elect sno from dbo.s where City='上海') )

（8）找出没有使用天津产的零件的工程号码。

select JNO FROM dbo.SPJ where SNO not in (select SNO from dbo.s where City='天津')