西北工业大学数据库实验报告2
- 格式:doc
- 大小:163.50 KB
- 文档页数:10
《数据库原理》实验报告
题目:实验二
基本表的数据操作
学号姓名班级日期2014302692 孟玉军10011402 16.10.13
一. 实验内容、步骤以及结果
1.在图形用户界面中对表中的数据进行更新。(6分,每小题2分)
(1)按照实验一图2.1-图2.4中所示数据,输入SPJ数据库所含四张表中的数据。
S表:
P表:
J表:
SPJ表:
(2)修改S表的任意一条数据
把S表中S1的status数据进行了修改
(3)删除S表的任意一条数据。
删除了S3的数据
2.针对SPJ数据库,用SQL语句完成下面的数据更新。(12分,每小题3分)(1)第五版教材第三章第5题的8-11小题。
红色零件颜色修改为蓝色:
⑧UPDATE P
SET COLOR=’蓝’
WHERE SNO=’红’
⑨由S5供给J4的零件P6改为由S3提供:
Update SPJ
Set sno=’S3’
Where sno=’S5’and pno=’P6’and jno=’J4’
⑩从供应商关系中删除S2的记录,从供应关系表中删除相应的记录:Delete
From S
Where sno=’S2’
Delete
From SPJ
Where sno=’S2’
11.请将(S2,J6,P4,200)插入供应情况关系:
Insert into SPJ
Values (‘S2’,’ P4’,’J6’,200)
3.针对SPJ数据库,用SQL语句完成下面的数据查询。(52分,每小题4分) (1)第五版教材第三章第4题。(5道小题)
①:
select distinct sno
from SPJ
where jno=’J1’
②:
select distinct sno
from SPJ
where jno='J1'and pno='P1'
③:
select sno
from SPJ,P
where jno='J1'and SPJ.pno=P.pno and color='红'
④:
select distinct jno
from SPJ
where jno not in
(select jno
from SPJ,P,S
where SPJ.sno=S.sno and city='天津'
and SPJ.pno=P.pno and color ='红'
)
⑤:select jno
from SPJ
where pno in
(select pno from SPJ where sno='S1' )
group by jno
having count(pno)=(select count(pno) from SPJ where sno ='J1')
(2)第五版教材第三章第5题的1-7小题。(7道小题)
①:
select sno,sname from S
②:
select pname ,color ,weight from P
③:
select distinct jno
from SPJ
where sno='S1'
④:
select pname,qty
from SPJ,P
where spj.pno=p.pno and jno='J2'
⑤:
select distinct pno
from S,SPJ
where spj.sno=s.sno and city='上海'
⑥:
select distinct jname
from spj,j,s
where spj.sno=s.sno and s.city ='上海'
and spj.jno=j.jno
⑦:
select distinct jno
from spj,s
where jno not in (select jno from spj,s
where spj.sno=s.sno and city='天津')
(3)查询这样的工程:供给该工程的零件P1的平均供应量大于供给工程J1的任何一种
零件的最大供应量:
select spj.jno,jname
from j,spj
where spj.jno=j.jno and pno='P1' and qty > (select MAX(qty)
from spj
where spj.jno='J1')
4.针对Student数据库用SQL语句完成下面的数据查询。(12分,每小题4分)
(1)查询不选修“C语言”课程的学生学号。
select distinct sno
from sc
where sno not in (select sno
from sc,c
where cname='C语言' and o=o)
(2)查询每个学生已经获得的学分的总分(成绩及格表示获得该门课的学分),并按照
所获学分由高到低的顺序输出学号,姓名,所获学分。
select SC.sno,sname ,sum(ccredit) as ccredit_sum
from C,S,SC
where o=o and grade>60 and sc.sno=s.sno
group by SC.sno,sname
order by ccredit_sum desc