数据库系统概论（第5版）第三章作业（1）课本P71表SPJ

根据SPJ数据库，给出下列各题的命令语句，将执行的语句及结果复制到word文档中，（查询结果可复制，选中查询结果后，点击右键，在快捷菜单中选择相应选项）：

针对SPJ数据库，查询S表中供应商所在城市为天津的SNO，SNAME， CITY 。

1.查询工程项目所在城市有哪些？

select City from J

2.查询SPJ表中供应数量大于200的记录的SNO，PNO，JNO，QTY，分别以供应商代码，零件代码，工程项目代码，供应数量显示

select Sno '供应商代码',Pno '零件代码',Jno '工程项目代码'from SPJ where QTY > 200

3.查询工程项目所在城市为北京、天津、南京的工程项目信息。

select * from J where City in ('北京','天津')

4.查询所有名称第一个字为“螺”的零件的信息

select * from P where Pname like '螺%'

5.查询第二个字为“轮”的零件的信息

select * from P where Pname like '_轮%'

6.查询红色且重量大于20的零件的信息

select * from P where Color = '红' and weight > 20

7.查询红色或重量大于20的零件的信息

select * from P where Color = '红' or weight > 20

8.查询工程项目所在地不为北京和天津的工程项目信息

select * from J where City not in ('北京','天津')

9.从SPJ表中查询工程项目代码为J1的记录信息，并按照供应数量递增排序。

select * from SPJ where Jno = 'J1' order by QTY desc

10.统计SPJ表中使用P3零件的总数量，最小数量、最多数量、平均数量

select sum(QTY) sum,min(QTY) min,max(QTY) max,avg(QTY) avg from SPJ where Pno ='P3'

11.统计SPJ表中使用各种零件的总数量，最小数量、最多数量、平均数量

select sum(QTY) sum,min(QTY) min,max(QTY) max,avg(QTY) avg from SPJ group by Pno

12.统计SPJ表中各工程项目分别使用了几种零件

select count(*) from SPJ group by Pno

13.统计SPJ表中各工程项目分别使用各种零件的总数量

select sum(QTY) from SPJ group by Jno

14.查询SPJ表中使用零件种类超过两种的工程项目。

select Jno from SPJ group by Jno having count(Pno)>2

15.查询SPJ表中各供应商分别给几个项目供应零件

select count(Jno) from SPJ group by Sno

16.查询SPJ表中各供应商分别供应每种零件给几个项目，及供应每种零件的总数量

select Sno,Pno,count(Jno) 零件供给的项目数,sum(QTY) 总数 from SPJ group by Pno,Sno

17.查询SPJ表中所有供应商代码，供应商姓名，零件名，工程项目名，供应数量，且供应数量>200的项目信息

select SPJ.Sno,Sname,Pname,Jname,QTY from SPJ,S,P,J

where SPJ.Sno=S.Sno and SPJ.Pno = P.Pno and SPJ.Jno = J.Jno

and QTY > 200

（中间好像就是少了俩题，不过没关系，写的方法都一样）

20.查询所有工程项目代码，工程项目名，所在城市，及使用的各零件代码，和数量

select J.Jno,Jname,City,Pno,sum(QTY) 零件数量 from J,SPJ  where J.Jno = SPJ.Jno group by J.Jno,Jname,City,Pno

以下使用嵌套查询实现

21.查询S1供应商供应的工程项目代码，工程项目名，所在城市

select Jno,Jname,City from J

where Jno in

(select Jno from SPJ

where Sno = 'S1')

22.查询使用蓝色零件的项目的工程项目代码，工程项目名，所在城市

select Jno,Jname,City from J

where Jno in

(select Jno from SPJ

where Pno in

(select Pno from P

where Color = '蓝')

)

23.查询SPJ表中使用数量超过200的零件代码，零件名，颜色，

select Pno,Pname,Color from P

where Pno in

(select Pno from SPJ

where QTY>200)

24.查询和s1在同一个城市的供应商代码，供应商名称

select Sno,Sname from S

where City = 

(select City from S

where Sno = 'S1')

25.查询所有使用了p1零件项目代码，名称,城市（用exists查询实现）

select Jno,Jname,City from J

where exists

(select * from SPJ

where Jno = J.Jno and Pno ='P1')

26.查询没有使用p1零件的项目代码，名称,城市(用not  exists查询)

select Jno,Jname,City from J

where not exists

(select * from SPJ

where Jno = J.Jno and Pno ='P1')