sql实验记录

实验数据

关系数据库SPJ，包含以下四个关系

关系S

SNO

SNAME

STATUS

CITY

S1

精  益

20

天津

S2

盛  锡

10

北京

S3

东方红

30

北京

S4

丰泰盛

20

天津

S5

为  民

30

上海

关系P

PNO

PNAME

COLOR

WEIGHT

P1

螺  母

红

12

P2

螺  栓

绿

17

P3

螺丝刀

蓝

14

P4

螺丝刀

红

14

P5

凸  轮

蓝

40

P6

齿  轮

红

30

关系J

JNO

JNAME

CITY

J1

三    建

北京

J2

一    汽

长春

J3

弹 簧 厂

天津

J4

造 船 厂

天津

J5

机 车 厂

唐山

J6

无线电厂

常州

J7

半导体厂

南京

关系SPJ

SNO

PNO

JNO

QTY

S1

P1

J1

200

S1

P1

J3

100

S1

P1

J4

700

S1

P2

J2

100

S2

P3

J1

400

S2

P3

J2

200

S2

P3

J4

500

S2

P3

J5

400

S2

P5

J1

400

S2

P5

J2

100

S3

P1

J1

200

S3

P3

J1

200

S4

P5

J1

100

S4

P6

J3

300

S4

P6

J4

200

S5

P2

J4

100

S5

P3

J1

200

S5

P6

J2

200

S5

P6

J4

500

对应的各个关系模式如下

S（SNO，SNAME，STATUS，SCITY）

P（PNO，PNAME，COLOR，WEIGHT）

J（JNO，JNAME，JCITY）

SPJ（SNO，PNO，JNO，QTY）

其中：S代表供应商，它的各属性依次表示：供应商号、供应商名、供应商状态、供应商所在城市。

P代表零件，其属性依次表示：零件号、零件名、零件颜色、零件重量。

J代表工程，其属性依次表示：工程号、工程名、工程所在城市。

SPJ代表供货关系，其属性依次表示：供应商号、零件号、工程号、供应数量。

实验平台

MS SQL Server

实验目标

能够熟练使用MS SQL Server的Query Analyzer工具，联机运行SQL并观察分析SQL的执行结果。掌握SQL的数据定义、数据查询和数据修改功能，能独立用SQL表述问题，解决较复杂的应用需求。

实验要求

上机完成实验内容，主要是编写、运行和分析SQL，请预先自行判断SQL的运行结果（例如：查询结果包含哪些数据；更新后关系数据如何变化），然后与实际的运行结果比较，以此发现和修正SQL编写中的错误。

实验内容

一、数据定义

①  用SQL创建SPJ数据库

②  用SQL创建SPJ数据库中的各个关系，要求为属性选择合适的数据类型

③  根据各个关系的意义和彼此间的联系，自行设计并用SQL定义合适的完整性约束，覆盖以下类型：主码约束、外部码约束、非空约束。

④  用SQL定义一个视图，反映：“三建”工程的供应商号，供应商名，零件号，零件重量，供应数量。

（注：②、③的SQL允许合并，即在同一个SQL语句中完成关系的定义和相关完整性的定义）

二、数据操纵

①  用SQL完成：插入各个关系的第一个元组

② 自行选择方法输入各个关系的剩余元组 （不要求编写SQL）

③ 用SQL完成：把S5供应的所有P6零件更改为P4零件

④ 用SQL完成：对供应数量总和超过1500的供应商，供应商状态更改为0。

⑤ 用SQL完成：删除供应商S2，并且删除S2的供货信息。

⑥ 用SQL完成：删除绿色零件的供货信息。

三、数据查询

用SQL完成以下查询

① 至少向北京或天津的工程提供了零件的供应商名，供应的零件名、工程名和供应数量。

② 供应红色零件给工程J1的供应商名

③ 没有使用天津产的零件的工程号

④ 没有向位于相同城市的工程供应零件的供应商名

⑤ 每种颜色零件的总重量

⑥ 红色零件的平均重量

⑦ 供应商最集中的城市（意即，哪个城市的供应商最多？）

⑧ 对上海和天津，统计该城市的供应商一共供应了多少数量的零件。结果保存到一个新关系Temp(City, QTYSum)中 (至多使用一个SQL语句用于定义，和一个SQL语句用于查询)

CREATE TABLE S(SNO VARCHAR(10) NOT NULL UNIQUE,SNAME VARCHAR(40) NOT NULL,STATUS FLOAT,CITY VARCHAR(40),PRIMARY KEY(SNO))CREATE TABLE P(PNO VARCHAR(10) NOT NULL UNIQUE,PNAME VARCHAR(40) NOT NULL,COLOR VARCHAR(40),WEIGHT FLOAT,PRIMARY KEY(PNO))CREATE TABLE J(JNO VARCHAR(10) NOT NULL UNIQUE,JNAME VARCHAR(40) NOT NULL,CITY VARCHAR(40),PRIMARY KEY(JNO))CREATE TABLE SPJ(SNO VARCHAR(10) NOT NULL,PNO VARCHAR(10) NOT NULL,JNO VARCHAR(10) NOT NULL,QTY FLOAT,FOREIGN KEY(SNO) REFERENCES S ON DELETE CASCADE,FOREIGN KEY(PNO) REFERENCES P ON DELETE CASCADE,FOREIGN KEY(JNO) REFERENCES J ON DELETE CASCADE,)CREATE VIEW BUILDONE(SNO, SNAME, PNO, WEIGHT, QTY)AS SELECT S.SNO, S.SNAME, P.PNO, P.WEIGHT, SPJ.QTYFROM S, P, SPJWHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNOUPDATE SPJ SET PNO = 'P4' WHERE PNO = 'P6'SELECT * FROM SPJUPDATE S SET STATUS = '0' WHERE SNO = (SELECT DISTINCT SNOFROM SPJGROUP BY SNOHAVING SUM(QTY) > 1500)DELETE FROM SPJWHERE SNO = 'S2'DELETEFROM SWHERE SNO = 'S2'DELETEFROM PWHERE COLOR = '绿'SELECT * FROM PSELECT SNAME, PNAME, JNAME, QTY FROM S, P, J, SPJWHERE S.SNO = SPJ.SNO AND P.PNO = SPJ.PNO AND J.JNO = SPJ.JNO AND (J.CITY = '北京' OR J.CITY = '天津') SELECT SNAMEFROM SWHERE EXISTS (SELECT *FROM SPJWHERE JNO = 'J1' AND EXISTS (SELECT *FROM PWHERE COLOR = '红'))SELECT JNOFROM SPJWHERE NOT EXISTS (SELECT *FROM SWHERE CITY = '天津')SELECT SUM(WEIGHT)FROM PGROUP BY COLORSELECT DISTINCT SNOFROM SPJWHERE JNO IN(SELECT JNOFROM JWHERE CITY IN(SELECT CITYFROM JGROUP BY CITYHAVING COUNT(*) = 1))SELECT TOP 1 CITYFROM SGROUP BY CITYORDER BY COUNT(*) DESCSELECT SUM(QTY)FROM SPJWHERE SNO IN(SELECT SNOFROM SWHERE CITY = '上海' OR CITY = '天津')INSERT INTO Temp(CITY, QTYSUM)SELECT CITY = S.CITY, QTYSUM = SUM(QTY)FROM S, SPJWHERE S.SNO = SPJ.SNO AND S.CITY IN ('上海','天津')GROUP BY S.CITY