spj实验一、二及习题三.doc
- 文档编号:14662183
- 上传时间:2023-06-25
- 格式:DOC
- 页数:9
- 大小:38.50KB
spj实验一、二及习题三.doc
《spj实验一、二及习题三.doc》由会员分享,可在线阅读,更多相关《spj实验一、二及习题三.doc(9页珍藏版)》请在冰点文库上搜索。
spj实验一、二及习题三.txt如果我穷得还剩下一碗饭我也会让你先吃饱全天下最好的东西都应该归我所有,包括你!
!
先说喜欢我能死啊?
别闹,听话。
有本事你就照顾好自己,不然就老老实实地让我来照顾你!
实验一
(1)在SQLServer2000环境下创建数据库SPJ;
(2)将习题2-5的编码字段改为int型,其它字段类型不变,创建基本表S、P、J和SPJ;
(5)在SPJ表的字段SNO、PNO、JNO上建立外键约束(参照完整性),可以在创建表时一并创建,也可以创建完表后单独创建。
(6)将课本上四个表的数据用SQL语句输入到表中,其中编号为课本上的数据去掉前面的字母后剩下的整数;
创建表s
createtables
(snosmallintprimarykey,
snamechar(20),
statussmallint,
citychar(10)
)
go
insertintos
values(1,'精益',20,'天津')
go
insertintos
values(2,'盛锡',10,'北京')
go
insertintos
values(3,'东方红',20,'北京')
go
updates
setstatus=30
wheresno=3
go
insertintos
values(4,'丰泰盛',20,'天津')
go
insertintos
values(5,'为民',30,'上海')
go
------------------------------------
创建表p
createtablep
(pnosmallintprimarykey,
pnamechar(20),
colorchar(10),
weightsmallint
)
go
insertintop
values(1,'螺母','红',12)
go
insertintop
values(2,'螺栓','绿',17)
go
insertintop
values(3,'螺丝刀','蓝红',14)
go
insertintop
values(4,'螺丝刀','红',14)
go
insertintop
values(5,'凸轮','蓝',40)
go
insertintop
values(6,'齿轮','红',30)
go
-------------------------------------
创建表j
createtablej
(jnosmallintprimarykey,
jnamechar(20),
citychar(10),
)
go
insertintoj
values(1,'三建','北京')
go
insertintoj
values(2,'一汽','长春')
go
insertintoj
values(3,'弹簧厂','天津')
go
insertintoj
values(4,'造船厂','天津')
go
insertintoj
values(5,'机车厂','唐山')
go
insertintoj
values(6,'无线电厂','常州')
go
insertintoj
values(7,'半导体厂','南京')
go
--------------------------------------
创建表spj
CREATETABLEspj
(
snosmallint,
pnosmallint,
jnosmallint,
qtysmallint,
primarykey(sno,pno,jno),
foreignkey(sno)referencess(sno),
foreignkey(pno)referencesp(pno),
foreignkey(jno)referencesj(jno),
)
go
insertintospj
values(1,1,1,200)
go
insertintospj
values(1,1,3,100)
go
insertintospj
values(1,1,4,700)
go
insertintospj
values(1,2,2,100)
go
insertintospj
values(2,3,1,400)
go
insertintospj
values(2,3,2,200)
go
insertintospj
values(2,3,4,500)
go
insertintospj
values(2,3,5,400)
go
insertintospj
values(2,5,1,400)
go
insertintospj
values(2,5,2,100)
go
insertintospj
values(3,1,1,200)
go
insertintospj
values(3,3,1,200)
go
insertintospj
values(4,5,1,100)
go
insertintospj
values(4,6,3,300)
go
insertintospj
values(4,6,4,200)
go
insertintospj
values(5,2,4,100)
go
insertintospj
values(5,3,1,200)
go
insertintospj
values(5,6,2,200)
go
insertintospj
values(5,6,4,500)
go
--------------------------------------
(3)在S、P、J三个表上建立对编码的索引,索引名分别为S_Index、P_Index和J_Index;
createindexs_indexons(sno)
go
createindexp_indexonp(pno)
go
createindexj_indexonj(jno)
(4)在SPJ表的字段SNO、PNO、JNO上建立名为SPJ_Index的索引;
createindexspj_index
onspj(sno,pno,jno)
(7)创建视图SPJ_View(SNO,SName,Status,SCity,PNO,PName,Color,Weight,JNO,JName,JCity,Qty);
笛卡尔集
createviewspj_view(SNO,SName,Status,SCity,PNO,PName,Color,Weight,JNO,JName,JCity,Qty)
as
selects.SNO,SName,Status,s.city,p.PNO,PName,Color,Weight,j.JNO,JName,j.city,Qty
froms,p,j,spj
go
createviewspj_view2(SNO,SName,Status,SCity,PNO,PName,Color,Weight,JNO,JName,JCity,Qty)
as
selects.SNO,SName,Status,s.city,p.PNO,PName,Color,Weight,j.JNO,JName,j.city,Qty
froms,p,j,spj
wheres.sno=spj.snoandp.pno=spj.pnoandj.jno=spj.jno
go
---------------------------------------------------------------------------------
第二次实验
(2) 从基本表查询天津供应商的编号、姓名、状态和城市;
selectsno,sname,status,cityfroms
wherecity='天津'
(3) 从基本表查询重量大于14的零件的名称、颜色和重量;
selectpname,color,weightfromp
whereweight>14
(4) 从基本表查询供应工程1零件1的供应商号码;
selectsnofromspj
wherejno=1andpno=1
(5) 从基本表查询供应数量大于200小于等于400的供应商姓名;
selectsnamefroms,spj
wheres.sno=spj.snoandqty>200andqty<=400
go
selectdistinctsnamefroms,spj
wheres.sno=spj.snoandqty>200andqty<=400
(6) 从基本表查询没有使用天津供应商生产的红色零件的工程号;
使用天津供应商生产的红色零件的工程号
selectdistinctspj.jnofroms,spj,p
wherecolor='红'ands.city='天津'
andspj.sno=s.snoandspj.pno=p.pno
go
没有使用天津供应商生产的红色零件的工程号
2-5
SELECTdistinctJNOFROMSPJWHEREJNO!
=all
(SELECTjnoFROMSPJ,s,p
wheres.sno=SPJ.snoANDp.pno=SPJ.pno
ANDp.color='红'ANDs.city='天津')
go
SELECTdistinctJNOFROMSPJWHEREJNOnotin
(SELECTjnoFROMSPJ,s,P
wheres.sno=SPJ.snoANDp.pno=SPJ.pno
ANDp.color='红'ANDs.city='天津')
go
2-5-6-7
SELECTdistinctJNOFROMJWHEREJNO!
=all
(SELECTjnoFROMSPJ,s,p
wheres.sno=SPJ.snoANDp.pno=SPJ.pno
ANDp.color='红'ANDs.city='天津')
go
SELECTdistinctJNOFROMJWHEREJNOnotin
(SELECTjnoFROMSPJ,s,P
wheres.sno=SPJ.snoANDp.pno=SPJ.pno
ANDp.color='红'ANDs.city='天津')
go
SELECTdistinctjnoFROMjwherenotexists
(select*fromspj,s,p
wherespj.jno=j.jnoandspj.sno=s.snoandspj.pno=p.pno
andp.color='红'ANDs.city='天津')
go
selectjnofromj
wherenotexists
(select*fromspj,s,p
wherespj.jno=j.jnoandspj.sno=s.snoandspj.pno=p.pno
andp.color='红'ANDs.city='天津')
go
selectjnofromj
wherenotexists
(select*fromspj
wherespj.jno=j.jnoandsnoin
(selectsnofromswherecity='天津')andpnoin
(selectpnofrompwherecolor='红'))
(7) 从基本表查询所供应零件数量大于等于300的供应商编号。
SELECTdistinctsnoFROMSPJ
whereqty>=300
(8) 从基本表查询至少用了供应商1所供应的全部零件的工程号;p111例47
selectdistinctjnofromSPJspj1wherenotexists
(select*fromSPJspj2wherespj2.jno='1'andnotexists
(select*fromSPJspj3wherespj3.jno=spj1.jnoandspj3.pno=spj2.pno))
(9) 从视图查询上海供应商供应的全部零件的零件号及名称;
selectdistinctpno,pnamefromspj_view2
wherescity='上海'
go
selectpno,pnamefromPwherepnoin(selectpnofromSPJ
wheresnoin(selectsnofromSwherecity='上海'))
(10) 计算每个各供应商供应零件的总数量。
selectsno,sum(qty)总数量fromSPJgroupbysno
--------------------------------------------------------------------------------------
p127习题三第5题
(1)找出所有供应商的姓名和城市
selectsname,cityfroms
(2)找出
selectpname,color,weightfromp
(3)
selectjnofromspjwheresno=1
(4)
selectpname,qtyfromp,spjwherejno=2andp.pno=spj.pno
(5)
selectdistinctpnofromspj
wheresnoin
(selectsnofroms
wherecity='上海')
go
selectdistinctpnofroms,spjwheres.sno=spj.snoandcity='上海'
(6)
selectdistinctjnamefromj,spj,s
wherej.jno=spj.jnoandspj.sno=s.snoands.city='上海'
go
selectjnamefromj
wherejnoin
(selectjnofromspj,s
wheres.sno=spj.snoandcity='上海')
(7)
selectjnofromj
wherenotexists
(select*fromspj
wherespj.jno=j.jnoandsnoin
(selectsnofromswherecity='天津'))
go
selectjnofromjwherenotexists
(select*fromspj,s
wherespj.jno=j.jnoandspj.sno=s.snoandcity='天津')
(8)
updatepsetcolor='蓝'wherecolor='红'
(9)
updatespj
setsno=3
wheresno=5andpno=6andjno=4
(10)
deletefromspjwheresno=2
go
deletefromswheresno=2
(11)
insertintospjvalues(2,6,4,200)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- spj 实验 习题
![提示](https://static.bingdoc.com/images/bang_tan.gif)