数据库作业 SQL语言.docx
- 文档编号:11147327
- 上传时间:2023-02-25
- 格式:DOCX
- 页数:15
- 大小:123.24KB
数据库作业 SQL语言.docx
《数据库作业 SQL语言.docx》由会员分享,可在线阅读,更多相关《数据库作业 SQL语言.docx(15页珍藏版)》请在冰豆网上搜索。
数据库作业SQL语言
1.设有一个SPJ数据库,包括S、P、J、SPJ四个关系模式:
S(SNO,SNAME,STATUS,CITY);
P(PNO,PNAME,COLOR,WEIGHT);
J(JNO,JNAME,CITY);
SPJ(SNO,PNO,JNO,QTY);
供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成;
零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成;
工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成;
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,表示某供应商供应某种零件给某个项目的数量为QTY。
请用SQL语句建立这四张表,并写出主键和外键约束。
CREATETABLES
(SNOVARCHAR(9)PRIMARYKEY,/*列级完整性约束条件,SNO是主码*/
SNAMEVARCHAR(20),
STATUSVARCHAR(20),
CITYVARCHAR(20)
);
CREATETABLEP
(PNOVARCHAR(9)PRIMARYKEY,/*列级完整性约束条件,PNO是主码*/
PNAMEVARCHAR(20),
COLORVARCHAR(20),
WEIGHTVARCHAR(9)
);
CREATETABLEJ
(JNOVARCHAR(9)PRIMARYKEY,/*列级完整性约束条件,JNO是主码*/
JNAMEVARCHAR(20),
CITYVARCHAR(20)
);
CREATETABLESPJ
(SNOVARCHAR(9),
PNOVARCHAR(9),
JNOVARCHAR(9),
QTYVARCHAR(9),
PRIMARYKEY(SNO,PNO,JNO),/*主码由三个属性构成*/
FOREIGNKEY(SNO)REFERENCESS(SNO),/*SNO是外键,被参照表是S*/
FOREIGNKEY(PNO)REFERENCESP(PNO),/*PNO是外键,被参照表是P*/
FOREIGNKEY(JNO)REFERENCESJ(JNO)/*JNO是外键,被参照表是J*/
);
2.基于题1建立的四张表,输入如下数据:
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
解:
数据建立:
INSERTINTOS(SNO,SNAME,STATUS,CITY)VALUES('S1','精益','20','天津');
INSERTINTOS(SNO,SNAME,STATUS,CITY)VALUES('S2','盛锡','10','北京');
INSERTINTOS(SNO,SNAME,STATUS,CITY)VALUES('S3','东方红','30','北京');
INSERTINTOS(SNO,SNAME,STATUS,CITY)VALUES('S4','丰泰盛','20','天津');
INSERTINTOS(SNO,SNAME,STATUS,CITY)VALUES('S5','为民','30','上海');
INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES('P1','螺母','红','12');
INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES('P2','螺栓','绿','17');
INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES('P3','螺丝刀','蓝','14');
INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES('P4','螺丝刀','红','14');
INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES('P5','凸轮','蓝','40');
INSERTINTOP(PNO,PNAME,COLOR,WEIGHT)VALUES('P6','齿轮','红','30');
INSERTINTOJ(JNO,JNAME,CITY)VALUES('J1','三建','北京');
INSERTINTOJ(JNO,JNAME,CITY)VALUES('J2','一汽','长春');
INSERTINTOJ(JNO,JNAME,CITY)VALUES('J3','弹簧厂','天津');
INSERTINTOJ(JNO,JNAME,CITY)VALUES('J4','造船厂','天津');
INSERTINTOJ(JNO,JNAME,CITY)VALUES('J5','机车厂','唐山');
INSERTINTOJ(JNO,JNAME,CITY)VALUES('J6','无线电厂','常州');
INSERTINTOJ(JNO,JNAME,CITY)VALUES('J7','半导体厂','南京');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J1','200');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J3','100');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J4','700');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S1','P2','J2','100');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J1','400');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J2','200');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J4','500');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J5','400');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S2','P5','J1','400');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S2','P5','J2','100');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S3','P1','J1','200');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S3','P3','J1','200');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S4','P5','J1','100');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S4','P6','J3','300');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S4','P6','J4','200');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S5','P2','J4','100');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S5','P3','J1','200');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S5','P6','J2','200');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S5','P6','J4','500');
请用SQL语句完成以下查询:
1)找出供应工程J1零件的供应商号码SNO;
SELECTDISTINCTSNO
FROMSPJ
WHEREJNOLIKE'J1';
2)找出供应工程J1零件P1的供应商号码SNO;
SELECTDISTINCTSNO
FROMSPJ
WHEREJNOLIKE'J1'ANDPNOLIKE'P1';
3)找出供应工程J1零件为红色的供应商号码SNO;
SELECTDISTINCTSNO
FROMSPJ,P
WHEREP.PNO=SPJ.PNOANDSPJ.JNO='J1'ANDP.COLOR='红';
4)找出没有使用天津供应商生产的红色零件的工程号JNO;
SELECTJNO
FROMJ
WHERENOTEXISTS(SELECT*FROMSPJ,S,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY='天津'ANDP.COLOR='红');
5)找出至少用了供应商S1所供应的全部零件的工程号JNO。
SELECTDISTINCTJNO
FROMSPJASX
WHERENOTEXISTS(SELECT*FROMSPJASYWHERESNO='S1'ANDNOTEXISTS(SELECT*FROMSPJASZWHEREZ.JNO=X.JNOANDZ.PNO=Y.PNO));
3.基于题1、2所建立的四张表,试用SQL完成以下各项操作:
1)找出所有供应商的姓名和所在城市;
SELECTSNAME,CITY
FROMS
2)找出所有零件的名称、颜色、重量;
SELECTPNAME,COLOR,WEIGHT
FROMP
3)找出使用供应商S1所供应零件的工程号码;
SELECTDISTINCTJNO
FROMSPJ
WHERESNO='S1';
4)找出工程项目J2使用的各种零件的名称及其数量;
SELECTPNAME,QTY
FROMSPJ,P
WHERESPJ.PNO=P.PNOANDSPJ.JNO='J2';
5)找出上海厂商供应的所有零件号码;
SELECTDISTINCTPNO
FROMSPJ,S
WHERESPJ.SNO=S.SNOANDS.CITY='上海';
6)找出使用上海产的零件的工程名称;
SELECTDISTINCTJNAME
FROMSPJ,J,P,S
WHERES.CITY='上海'ANDSPJ.SNO=S.SNOANDSPJ.JNO=J.JNO;
7)找出没有使用天津产的零件的工程号码;
SELECTJNO
FROMJ
WHEREJNONOTIN(SELECTJNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERES.CITY='天津'));
8)把全部红色零件的颜色改为蓝色;
UPDATEP
SETCOLOR='蓝'
WHERECOLOR='红';
9)由S5供给J4的零件P6改为由S3供应,请做必要的修改;
UPDATESPJ
SETSNO='S3'
WHERESNO='S5'ANDJNO='J4'ANDPNO='P6';
10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录;
DELETE
FROMSPJ
WHERESNO='S2';
DELETE
FROMS
WHERESNO='S2';
11)请将(S2,J6,P4,200)插入供应情况关系。
INSERTINTOS(SNO,SNAME,STATUS,CITY)VALUES('S2','盛锡','10','北京');
INSERTINTOSPJ(SNO,PNO,JNO,QTY)VALUES('S2','P4','J6','200');
4.基于题1、2所建立的四张表,请为三建工程项目建立一个供应情况的视图,包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
针对该视图完成下列查询:
1)找出三建工程项目使用的各种零件代码及其数量;
2)找出供应商S1的供应情况。
CREATEVIEWVST
AS
SELECTSNO,PNO,QTY
FROMSPJ
WHEREJNOIN
(SELECTJNO
FROMJ
WHEREJNAME='三建');
1)SELECTPNO,QTY
FROMVST;
2)SELECTPNO,QTY
FROMVST
WHERESNO='S1';
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库作业 SQL语言 数据库 作业 SQL 语言