数据库实验：使用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代表供货关系，其属性依次表示：供应商号、零件号、工程号、供应数量。

 

实验平台

 

MSSQL Server 2008

 

实验目标

 

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

 

实验要求

 

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

 

实验内容

 

一、数据定义

 

①       用SQL创建SPJ数据库

create database spj

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

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

create table s (        sno char(8) notnull unique,        sname char(16)not null,        status int,        city char(16),        primary key(sno)) create table p (        pno char(8) notnull unique,        pname char(16)not null,        color char(8)not null,        weight int,        primary key(pno)) create table j (        jno char(8) notnull unique,        jname char(16)not null,        city char(16),        primary key(jno)) create table spj (        sno char(8) notnull unique,        pno char(8) notnull unique,        jno char(8) notnull unique,        qty int,        primary key(sno, pno, jno),        foreign key(sno)references s(sno) on delete cascade,        foreign key(pno)references p(pno) on delete cascade,        foreign key(jno)references j(jno) on delete cascade)

 

④ 用SQL定义一个视图，反映：“三建”工程的供应商号，供应商名，零件号，零件重量，供应数量。（注：②、③的SQL允许合并，即在同一个SQL语句中完成关系的定义和相关完整性的定义）

create view sanjian(sno, sname, pno, weight, qty)        as select s.sno,s.sname, p.pno, weight, qty              from s, p,j, spj              wherejname = '三建'                     ands.sno = spj.sno                     andp.pno = spj.pno                     andj.jno = spj.jnowith check option

二、数据操纵

 

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

insert into svalues('s1', '精益' , '20' , '天津')insert into pvalues('p1', '螺母' , '红', '12')insert into jvalues('j1', '三建', '北京')insert into spjvalues('s1', 'p1', 'j1', '200')

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

我们用Excel表格导入数据，操作过程如下：

 

剩下的就是下一步，完成之类的了。

 

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

updatespjsetpno = 'p4'wherepno = 'p6' and sno = 's5'

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

update sset status = 0where sno in (       select sno from spj       group by sno having SUM(qty) > 1500)

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

delete from s where sno = 's2'delete from spj where sno = 's2'

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

delete from spjwhere pno in (    select pno from p    where color = '绿'           )

 

三、数据查询

 

用SQL完成以下查询

 

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

selectsname, pname, jname, qty from s, p, j, spjwherespj.sno = s.sno    and spj.pno = p.pno    and spj.jno = j.jno    and (j.city = '北京' or j.city = '天津')

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

selectsname from s, p, spjwherespj.sno = s.sno    and spj.pno = p.pno    and spj.jno = 'J1'    and color = '红'

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

selectjno from jwherejno not in (    select jno from spj, s    where spj.sno = s.sno           and s.city = '天津')

 

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

selectsname from swheresno not in (    select s.sno from s, j, spj    where s.sno = spj.sno    and j.jno = spj.jno    and s.city = j.city)

⑤       每种颜色零件的总重量

selectcolor, SUM(weight) from pgroupby color

⑥       红色零件的平均重量

selectcolor, AVG(weight) from pwherecolor = '红'groupby color

 

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

select city, COUNT(city) from sgroup by cityhaving COUNT(city) >= all (select COUNT(city) froms group by city)

 

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

createtable temp (    city char(16) not null,    qtysum float) insertinto temp(city, qtysum)selects.city, SUM(qty) from s, spjwheres.sno = spj.sno    and (s.city = '上海' or s.city = '天津')groupby s.city