xmut数据库实验4复杂的SQL查询.docx
《xmut数据库实验4复杂的SQL查询.docx》由会员分享，可在线阅读，更多相关《xmut数据库实验4复杂的SQL查询.docx（40页珍藏版）》请在冰豆网上搜索。
xmut数据库实验4复杂的SQL查询
《数据库原理与应用》实验报告
实验序号：
4 实验项目：
复杂的SQL查询
学 号
姓 名
专业、班级
实验地点
指导教师
实验时间
本实验中的表名和属性名全部用中文
一、实验内容与步骤
1．建立基本表
（1）创建仓库表，由下列属性组成：
仓库号（CHAR型），城市（CHAR型），面积（INT型），其中仓库号为主码，并且有面积大于0的限定条件。
（2）创建职工表，由下列属性组成：
仓库号（CHAR型），职工号（CHAR型），工资（INT型），其中职工号为主码，仓库号为外码，工资的限定条件为1000<=工资<=5000，并且设定缺省值为1200
（3）创建供应商表，由下列属性组成：
供应商号（CHAR型），供应商名（CHAR型），地址（CHAR型），其中供应商号为主码。
（4）创建订购单表，由下列属性组成：
职工号（CHAR型），供应商号（CHAR型），订购单号（CHAR型）、订购日期（CHAR型），其中订购单号为主码，职工号和供应商号为外码，订购日期的缺省值为当前系统日期。
2．修改基本表
（1）往订购单表中增加一个新的属性“完成日期”，DATE型，并且允许为空值
（2）将职工表中的工资属性类型改成SMALLINT
（3）删除刚才在订购单表中加入的“完成日期”属性
3．建立索引
（1）在订购单表上建立关于供应商号的普通索引
（2）在订购单表的多个字段建立索引，先按照供应商号升序索引，再按照职工号升序索引
4．建立视图
（1）创建视图，提供职工号、职工工资和职工工作所在城市的信息
（2）创建视图，给出有北京仓库订购单的供应商的名称
5．插入操作
（1）向仓库表插入下列数据：
WH1,北京,370
WH2,上海,500
WH3,广州,200
WH4,武汉,400
（2）向职工表插入下列数据：
WH2,E1,1220
WH4,E2,1270
WH1,E3,1210
WH2,E4,1250
WH3,E5,1200
WH3,E6,1230
WH1,E7,1250
（3）向供应商表插入下列数据：
S1,利民电子元件厂,上海
S2,联华电子公司,武汉
S3,振华电子厂,西安
S4,华通电子公司,北京
S5,607厂,郑州
S7,爱华电子厂,北京
（4）向订购单表插入下列数据：
E3,S7,OR67,20020623
E1,S4,OR73,20020728
E7,S4,OR76,20020525
E6,NULL,OR77,NULL
E3,S5,OR79,20020613
E1,NULL,OR80,NULL
E3,NULL,OR90,NULL
E3,S3,OR91,20020713
6．修改数据
（1）将武汉仓库的职工工资提高10%
（2）将S6经手的订购单的订购日期改成20050101
7．根据视图完成查询
（1）查询在北京工作职工的职工号和工资
（2）查询有北京仓库订购单的北京供应商的名称
8．单表查询
（1）查询工资多于1210元的职工所在仓库的仓库号
（2）查询尚未确定供应商的订购单
（3）先按仓库号，后按工资排序输出全部职工信息
（4）查询至少有两个职工的仓库的职工平均工资
9．连接查询
（1）查询工作在面积大于400的仓库的职工号及这些职工工作的城市
（2）查询与上海的仓库有联系的供应商个数
（3）查询由工资多于1230元的职工向北京的供应商发出的订购单号
10．嵌套查询
（1）查询所有职工工资都高于1210元的仓库的信息
（2）查询向北京的供应商发出订购单的仓库所在城市
（3）查询有职工工资大于或等于”WH1”仓库中所有职工工资的仓库号
（4）查询仓库中还没有职工的仓库信息
11．删除数据
（1）删除所在城市是上海的仓库的所有职工元组
12．删除视图
（1）在操作结束后删除第4步建立的视图
13．删除索引
（1）删除订购单表上建立的索引
14．删除基本表
（1）在所有操作结束后删除仓库表
（2）在所有操作结束后删除职工表
（3）在所有操作结束后删除供应商表
（4）在所有操作结束后删除订购单表
二、分析与讨论
CREATETABLE"仓库表"（
"仓库号"CHAR（7）PRIMARYKEY,
"城市"CHAR（20）,
"面积"INT
）
CREATETABLE"仓库号"（
"仓库号"CHAR（7）,
"职工号"CHAR（7）PRIMARYKEY,
"工资"INT,
FOREIGNKEY（"仓库号"）REFERENCES"仓库表"
）
CREATETABLE"供应商表"（
"供应商号"CHAR（7）PRIMARYKEY,
"供应商名"CHAR（20）,
"地址"CHAR（20）
）
CREATETABLE"订购单表"（
"职工号"CHAR（7）,
"供应商号"CHAR（7）,
"订购单号"CHAR（7）PRIMARYKEY,
"订购日期"CHAR（20）,
FOREIGNKEY（"职工号"）REFERENCES"职工表"（"职工号"）,
FOREIGNKEY（"供应商号"）REFERENCES"供应商表"（"供应商号"）
）
ALTERTABLE"订购单表"ADD"完成日期"DATE
ALTERTABLE"职工表"ALTERCOLUMN"工资"TYPESMALLINT
ALTERTABLE"订购单表"DROPCOLUMN"完成日期"
CREATEINDEX"供应商号索引"ON"订购单表"（"供应商号"）
CREATEINDEX"供应商号和职工号升序"ON"订购单表"（"供应商号","职工号"）
CREATEVIEW"北京仓库供应商"
ASSELECT"供应商名"
FROM"供应商表","仓库表","订购单表","职工表"
WHERE"供应商表"."供应商号"="订购单表"."供应商号"AND
"订购单表"."职工号"="职工表"."职工号"AND
"职工表"."仓库号"="仓库表"."仓库号"AND
"仓库表"."仓库号"='北京仓库'
INSERTINTO"供应商表"VALUES（'S1','利民电子元件厂','上海'）;
INSERTINTO"供应商表"VALUES（'S2','联华电子公司','武汉'）;
INSERTINTO"供应商表"VALUES（'S3','振华电子厂','西安'）;
INSERTINTO"供应商表"VALUES（'S4','华通电子公司','北京'）;
INSERTINTO"供应商表"VALUES（'S5','607厂','郑州'）;
INSERTINTO"供应商表"VALUES（'S7','爱华电子厂','北京'）;
INSERTINTO"订购单表"VALUES（'E3','S7','OR67','20020623'）;
INSERTINTO"订购单表"VALUES（'E1','S4','OR73','20020728'）;
INSERTINTO"订购单表"VALUES（'E7','S4','OR76','20020525'）;
INSERTINTO"订购单表"VALUES（'E3','S5','OR79','20020613'）
INSERTINTO"订购单表"VALUES（'E3','S3','OR91','20020713'）;
UPDATE"职工表"SET"工资"=1.1*"工资"WHERE"工资"IN
（SELECT"工资"
FROM"仓库表","职工表"
WHERE"仓库表"."仓库号"="职工表"."仓库号"
）
UPDATE"订购单表"SET"订购日期"='20051001'WHERE"供应商号"='S6'
SELECT"职工号","工资"
FROM"职工信息"
WHERE"城市"='北京'
SELECT"供应商名"
FROM"北京仓库供应商"
SELECT"仓库号"
FROM"职工表"
WHERE"工资">1210
GROUPBY"仓库号"
SELECT"订购单号"
FROM"订购单表"
WHERE"供应商号"ISNULL
SELECT*
FROM"职工表"
ORDERBY"仓库号","工资"
SELECTAVG（"工资"）
FROM"职工表"
HAVINGCOUNT（"职工号"）>2
SELECT"仓库号",AVG（"工资"）
FROM"职工表"
GROUPBY"仓库号"
HAVINGCOUNT（*）>=2
SELECT"职工号","城市"
FROM"仓库表","职工表"
WHERE"仓库表"."仓库号"="职工表"."仓库号"AND"面积">400
SELECTCOUNT（"订购单号"）
FROM"订购单表","职工表","仓库表"
WHERE"订购单表"."职工号"="职工表"."职工号"AND
"职工表"."仓库号"="仓库表"."仓库号"AND
"城市"='上海'
SELECT"订购单号"
FROM"职工表","订购单表","供应商表"
WHERE"职工表"."职工号"="订购单表"."职工号"AND
"订购单表"."供应商号"="供应商表"."供应商号"AND
"地址"='北京'AND
"工资">1230
SELECT"仓库号","城市","面积"
FROM"仓库表"
WHERE"仓库号"IN
（
SELECT"仓库号"
FROM"职工表"
WHERE"工资">1210
）
SELECT"城市"
FROM"仓库表"
WHERE"仓库号"IN
（
SELECT"仓库号"
FROM"供应商表","订购单表","职工表"
WHERE"地址"='北京'AND
"供应商表"."供应商号"="订购单表"."供应商号"AND
"订购单表"."职工号"="职工表"."职工号"
）
SELECT"仓库号"
FROM"职工表"
WHERE"工资">（
SELECTMAX（"工资"）
FROM"职工表"
WHERE"仓库号"='WH1'
）
SELECT*
FROM"仓库表"
WHERENOTEXISTS
（SELECT"仓库号"
FROM"职工表"
）
SELECT*
FROM"仓库表"
WHERE"仓库号"NOTIN
（SELECT"仓库号"
FROM"职工表"
）
DROPVIEW"北京仓库供应商"CASCADE
DROPVIEW"职工信息"CASCADE
DROPINDEX"供应商号索引"CASCADE
DROPINDEX"供应商号和职工号升序"CASCADE
DELETE
FROM"订购单表"
WHERE'上海'=
（SELECT"城市"
FROM"仓库表","职工表"
WHERE"仓库表"."仓库号"="职工表"."仓库号"AND
"职工表"."职工号"="订购单表"."职工号"
）
DELETE
FROM"职工表"
WHERE"仓库号"=
（SELECT"仓库号"
FROM"仓库表"
WHERE"城市"='上海'
）
DROPVIEW"北京仓库供应商"CASCADE
DROPVIEW"职工信息"CASCADE
DROPINDEX"供应商号索引"CASCADE
DROPINDEX"供应商号和职工号升序"CASCADE
DROPTABLE"仓库表"CASCADE
DROPTABLE"供应商表"CASCADE
DROPTABLE"职工表"CASCADE
DROPTABLE"订购单表"CASCADE
三、教师评语
签名：
日期：
成绩
升级会员 