数据库原理1009(改).docVIP

select * from dbo.j--工程 select * from dbo.p--零件表 select * from dbo.s--供应商 select * from dbo.spj--供应表 select s.sname from s,spj,j where s.sno=spj.sno and j.jno=spj.jno and j.city=s.city and spj.pno=‘p1’ 2．建立视图view_test，该视图说明北京供应商的情况（在试卷上导出语句），据此视图查询这类供应商中，姓名为N3的供应商的代号及状况乘以100 （1）create view view_test as select * from s where city=‘北京’ select sno,status*100 from view_test where sname=‘n3’ 查询工程名称的首字母为J的工程的代号及工地 （2）select jno,city from j where jname like ‘j%’ 3建立视图view_test，其提供关于供应商S1 所供应零件的情况 并据此视图查询S1 供应商供应零件的代号及数量，并按数量降序排列 create view view_test as select p.*,spj.qty from p,spj where p.pno=spj.pno and spj.sno=‘s1’ go select pno,qty from view_test order by qty desc 查询零件重量在12与17的零件名称 select pname from p where weight between 12 and 17 4．建立视图view_test，其提供关于住在北京，状况大于等于20的供应商的情况，并据此视图查询状况为30的供应商姓名 （1）create view view_test as select * from s where city=‘北京’ and status=20 select sname from view_test where status=30 查询j1工程所使用的s1供应商提供的零件代号及数量 工程编号 供应商编号 数量 select pno,qty from spj where jno=j1 and sno=s1 5．建立视图view_test，其提供关于重量在14与19之间的零件的情况，并据此视图查询重量为17的零件的名称及颜色 （1）create view view_test as select * from p where weight between 14 and 19 select pname,color from view_test where weight=17 查询供应商记录，并按状况升序排序 （2）select * from s order by status 6.查询供应商S1所供应的零件按零件分组后的总量在200以上的记录 （1）select pno from spj where sno=‘s1’ group by pno having sum(qty)200 select * from dbo.spj where spj.sno=s1 group by pno,sno,jno,qty, sno having SUM(qty)200 查询零件名称为PN4的零件情况 （2）select * from p where pname=‘pn4’ 7．查询住在同一城市的供应商记录。 （1）select * from s s1,s s2 where s1.city=s2.city and s1.snos2.sno 查询零件表P，城市名中个第二个字是“京”的零件、代号及名称 （2）select pno,pname from p where city like ‘_京%’ 8．查询住在上海并且向工地在南京的项目提供零件的供应商情况 s j spj （1）select s.* from s,j,spj where s.sno=spj.sno and j.jno=spj.jno and s.city=‘上海’ and j.city=‘南京’ select * from dbo.s where s.city=上海 and sno in( select sno from dbo.spj where spj.jno in( select jno from dbo.j where j.city=南京) )select pname,city from p where weight=12 or weight=14 9.