SQL语句知识总结(五)
01-Dec-02
Shipment Table
SID
PID
PrjNo
Qty
ShDate
S1
P1
J1
200
03-Apr-02
S1
P1
J4
700
03-Apr-01
S2
P3
J1
400
03-Feb-01
S2
P3
J2
200
03-Jan-02
S2
P3
J3
200
03-Sep-00
S3
P3
J4
500
03-Oct-01
S3
P4
J5
600
13-Apr-02
S4
P5
J6
400
23-Apr-99
S4
P5
J7
800
03-Oct-02
S5
P1
J2
100
13-Apr-01
S5
P2
J1
200
23-Apr-02
S5
P2
J2
500
23-Apr-02
S6
P5
J3
300
03-Oct-00
S6
P5
J7
300
13-Apr-00
题目：
1. 查询在伦敦的所有项目的详细信息
2. 查询项目J1的供应商的编号，查询结果按供应商编号排列
3. 查询货物供应量在300到750（包括300和750）之间的所有货物供应
4. 找出零件的所有颜色和零件的储藏地并且消除取值重复的行
5. 查询零件名称中包含字母T的所有零件的信息
6. 查询所有红色和蓝色的零件并且使用关键字IN
7. 查询供应商为S1的货物供应量的总数
8. 查询所有货物供应中的零件名称和颜色
9. 查询供应商从伦敦供应的所有零件的名称
10. 查询项目的项目号，在这个项目中至少有一个供应商且不在同一个城市。
11. 查询供应商不在巴黎，但是供应给巴黎的项目的绿色螺钉的总数
12. 查询零件的储藏位置与项目所在地相同时，货物供应的总数和货物供应的平均值
13. 查询每个城市中有多少个供应商
14. 查询属于一种颜色的零件有多少种
15. 查询所有零件的零件编号，这些零件被供应给至少一个项目，且供应的零件数量的平均值要大于320
答案：
1、Select *
From Project
Where City=‘London’
2、Select SID
From Shipment
Where PrjNo=‘J1’
Order By SID
3、Select *
From Shipment
Where Qty Between 300 And 750
4、Select Distinct Colour,City
From Parts
5、Select *
From Parts
Where Pname LIKE ‘%T%’
6、Select *
From Parts
Where Colour IN (‘Red’,’Blue’)
7、Select Sum(Qty)
From Shipment
Where SID=‘S1’
8、Select Shipment.PID,Colour
From Shipment,Parts
Where Shipment.PID=Parts.PID
9、Select PName
From Parts,Shipment,Suppliers
Where Parts.PID=Shipment.PID
And Shipment.SID=Suppliers.SID
And Suppliers.City=‘London’
10、Select PrjNo
From Project
Where Exists
(Select *
From Shipment,Suppliers
Where Project.PrjNo=Shipment.PrjNo
And Shipment.SID=Suppliers.SID
And Project.City!=Suppliers.City
11、Select Sum(Qty)
From Shipment,Parts,Projects
Where Shipment.PID=Parts.PID And Shipment.PRjNo=Project.PrjNo
And Project.City=‘Paris’ And colour=‘Green’
And SID Not IN
(Select SID From Suppliers Where City=‘Paris’)
12、Select Sum(Qty),Avg(Qty)
From Shipment,Parts,Project
Where Shipment.PID=Parts.PID
And Shipments.PrjNo=Project.PrjNo
And Parts.City=Project.City22
13、Select COUNT(SID)
From Suppliers
Group By City
14、Select Count(PID)
From Parts
Group By Colour
15、Select PID
From Shipment
Group By PID
Having Avg(Qty)>320