数据库概论——SQL练习一(SPJ零件问题)

系统:

MySQL 8.0.19

题目:

三张表:
S(SNO, SNAME, STATUS, CITY)
P(PNO, PNAME, COLOR, WEIGHT, CITY)
J(JNO, JNAME,CITY)
SPJ(SNO, PNO, JNO, PRICE, QTY)

S表示供应商,各属性依次为供应商号,供应商名,供应商状态值,供应商所在城市;

P表示零件,各属性依次为零件号,零件名,零件颜色,零件重量,零件存放的城市;

J表示工程,各属性依次为工程号,工程名,工程所在城市;

SPJ表示供货关系,各属性依次为供应商号,零件号,工程号,单价, QTY。

基于以上SPJ关系模式用SQL完成下面的查询:

问题1:

求没有供应零件号为P1和P2两种零件的供应商姓名。

数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
1 select SNAME 
2 from S
3 where SNO not in 
4 (
5     select SNO
6     from SPJ
7     where PNO = 'P1' or PNO = 'P2'
8 );
View Code

注意:不是“即没有P1也没有P2零件”,而是“没有P1或没有P2零件”。

问题2:

列出所有供应商的信息,包括供应商姓名、所供应的零件名(没有供应零件的供应商也要列出,最后结果中不要出现重复元组。

数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
1 select distinct S.SNAME, P.PNAME
2 from S left join (SPJ inner join P on SPJ.PNO = P.PNO) on S.SNO = SPJ.SNO
View Code

注意:去重用distinct关键字,外连接用left/right join。

问题3:

求只向与自己位于不同城市的工程供应零件的供应商姓名。

数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
 1 select SNAME
 2 from S
 3 where SNO not in
 4     (
 5     select S.SNO
 6     from S, SPJ, J
 7     where S.SNO = SPJ.SNO 
 8         and SPJ.JNO = J.JNO 
 9         and S.CITY = J.CITY
10     )
11     and SNO in
12     (
13     select S.SNO
14     from S, SPJ
15     where S.SNO = SPJ.SNO
16     );
方法一 数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
 1 select S.SNAME
 2 from S, SPJ
 3 where S.SNO = SPJ.SNO
 4     and S.SNO not in
 5         (
 6         select S.SNO
 7         from S, SPJ, J
 8         where S.SNO = SPJ.SNO 
 9             and SPJ.JNO = J.JNO 
10             and S.CITY = J.CITY
11         );
方法二

注意:(1) 不要输出不向任何工程供应零件的供应商,(2) MySQL不支持集合交intersect和集合差except,只支持集合交union(all)。

问题4:

求只向与自己位于相同城市的工程供应零件的供应商姓名。

数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
 1 select SNAME
 2 from S
 3 where SNO in
 4     (
 5         select S.SNO
 6         from S, SPJ, J
 7         where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY = J.CITY
 8     )
 9     and SNO not in
10     (
11         select S.SNO
12         from S, SPJ, J
13         where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY <> J.CITY
14     );
方法一 数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
1 select S.SNAME
2 from S, SPJ
3 where S.SNO = SPJ.SNO
4     and S.SNO not in
5     (
6         select S.SNO
7         from S, SPJ, J
8         where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY <> J.CITY
9     );
方法二

问题5:

求供应了所有零件的供应商姓名。

数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
 1 select SNAME
 2 from S
 3 where not exists
 4 (  
 5     select *
 6     from P
 7     where not exists
 8     (
 9         select *
10         from SPJ
11         where SPJ.SNO = S.SNO and SPJ.PNO = P.PNO
12     )
13 );
not exists方法 数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
1 select SNAME
2 from S
3 where SNO in
4 (
5     select SNO
6     from SPJ
7     group by SNO
8     having count(JNO) = (select count(*) from J)
9 );
group by + count()方法

要点:(1) not exists ... not exists ... 表示双重否定,可用于实现关系代数的除法,(2) count方法在J表有重复行(即JNO没有主码约束)、或在SPJ表的JNO属性有空值(即没有JNO的外码约束)的情况下不一定能给出正确答案,不过本例比较简单、没有这种风险。

问题6:

求供应了所有红色零件的供应商姓名。

数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
 1 select SNAME
 2 from S
 3 where not exists
 4 (
 5     select *
 6     from (select * from P where P.COLOR = '红色') as P1
 7     where not exists
 8     (
 9         select *
10         from SPJ
11         where S.SNO = SPJ.SNO and P1.PNO = SPJ.PNO
12     )
13 );
P表替换为红色的P表 数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
 1 select SNAME
 2 from S
 3 where not exists
 4 (
 5     select *
 6     from P
 7     where P.COLOR = '红色'
 8     and not exists
 9     (
10         select *
11         from SPJ
12         where S.SNO = SPJ.SNO and P.PNO = SPJ.PNO
13     )
14 );
直接加上‘红色’约束

问题7:

列出每个城市的工程所使用的零件总的数量。

数据库概论——SQL练习一(SPJ零件问题)数据库概论——SQL练习一(SPJ零件问题)
1 select CITY, sum(QTY)
2 from J join SPJ using(JNO)
3 group by CITY;
View Code

问题8:

 

上一篇:非完整调研:主流数据库对IPv6支持度


下一篇:java:线上问题排查常用手段