SQL语言训练2

时间：2025-03-07 14:44:32

SQL语言训练2

  • 表格属性
  • 创建表格
  • SQL语言查询
    • 1. 查询供应商名称中包含“盛”字的供应商信息
    • 2. 找出向北京供应商购买重量大于30的零件的工程名
    • 3. 查询有3个以上（包含3个）供应零件数是200以上（包含200）的供应商的供应商号及（供应零件200（包含200）以上的）业务数（一条供应记录对应一个业务）
    • 4. 查询供应商“为民”所提供的零件名和数量
    • 5. 查询为 “三建”项目提供零件的供应商名及所在城市
    • 6. 查询每个供应商的供应商号、供应商名、服务的项目名及所在城市
    • 7. 查询所有零件的使用情况，列出零件号、零件名、使用数量
    • 8. 查询所有低于500个的零件使用记录，并按数量降序排列，列出供应商名、项目名、零件名、数量。
    • 9. 查询为项目J1提供零件P3且数量在100个以上的所有供应商的供应商号、供应商名、提供零件数量。
    • 10. 查询“造船厂”项目使用的零件名称和数量
    • 11. 查询同时使用了S1和S4所提供零件的项目号
    • 12. 找出没有使用天津产的红色零件的工程号
    • 13. 查询至少用了S1供应商所供应的全部零件的工程号
    • 14. 查询没有使用零件P2的工程号
    • 15. 查询使用数最多的零件的零件号及其使用数量
    • 16. 查询至少用了S1供应商所供应的全部零件的工程

表格属性

创建表格

主码：primary key()
外码：foreign key() references 表格名称()

supplier 表创建

create table supplier
(sno varchar(10), sname varchar(50), status int, 
city varchar(20), primary key(sno));
  • 1
  • 2
  • 3

component 表创建

create table component (pno varchar(10), pname varchar(50), 
color varchar(20), weight int, primary key(pno));
  • 1
  • 2

project 表创建

create table project (jno varchar(10), jname varchar(50), 
city varchar(20), primary key(jno))
  • 1
  • 2

support 表创建

create table support(sno varchar(10), pno varchar(10), 
 jno varchar(10), qty int, 
primary key(sno, pno, jno), 
foreign key(sno) references supplier(sno), 
foreign key(pno) references component(pno), 
foreign key(jno) references project(jno));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

SQL语言查询

1. 查询供应商名称中包含“盛”字的供应商信息

这里为字符串匹配，且不是固定字符串，不能用＝取代LIKE，其中 % 代表内容任意，长度任意，可以为0，而 _ 代表一个长度，内容任意，这里的 %盛% 即表示含有盛。

select * from supplier where sname like '%盛%';
  • 1

2. 找出向北京供应商购买重量大于30的零件的工程名

select jname from supplier, component, project, support 
where supplier.sno=support.sno and component.pno=support.pno and 
project.jno=support.jno and supplier.city='北京' and weight>30;
  • 1
  • 2
  • 3

3. 查询有3个以上（包含3个）供应零件数是200以上（包含200）的供应商的供应商号及（供应零件200（包含200）以上的）业务数（一条供应记录对应一个业务）

select sno, count(*) from support where qty>=200 group by sno having count(*)>=3;
  • 1

4. 查询供应商“为民”所提供的零件名和数量

不同的零件号可能是同一个零件名称，需要合并统计

select pname, sum(qty) from supplier, component, support 
where supplier.sno=support.sno and component.pno=support.pno and sname='为民' 
group by pname;
  • 1
  • 2
  • 3

5. 查询为 “三建”项目提供零件的供应商名及所在城市

distinct用于去除重复

select distinct sname, supplier.city from supplier, project, support 
where supplier.sno=support.sno and project.jno=support.jno and jname='三建';
  • 1
  • 2

6. 查询每个供应商的供应商号、供应商名、服务的项目名及所在城市

查询的是每个供应商，以supplier为基准

select distinct supplier.sno, sname,jname, project.city 
from (supplier left join support on supplier.sno= support.sno) 
left join project on project.jno= support.jno;
  • 1
  • 2
  • 3

7. 查询所有零件的使用情况，列出零件号、零件名、使用数量

所有零件，以component为基准
这里为了标准化，是将分组的结果当成一个表，对它进行查询

select pno, pname, qty1 
from component left join 
      (select pno as pno1, sum(qty) as qty1 from support group by pno) 
support1 on component.pno=support1.pno1;
  • 1
  • 2
  • 3
  • 4

8. 查询所有低于500个的零件使用记录，并按数量降序排列，列出供应商名、项目名、零件名、数量。

select sname, jname, pname, qty 
from supplier, component, project, support 
where supplier.sno=support.sno and component.pno=support.pno 
and project.jno=support.jno and qty<500 
order by qty desc;
  • 1
  • 2
  • 3
  • 4
  • 5

9. 查询为项目J1提供零件P3且数量在100个以上的所有供应商的供应商号、供应商名、提供零件数量。

select supplier.sno, sname, qty from supplier, support 
where supplier.sno=support.sno and jno='J1' and pno='P3' and qty>100;
  • 1
  • 2

10. 查询“造船厂”项目使用的零件名称和数量

select pname, sum(qty) from component, project, support 
where component.pno=support.pno and project.jno=support.jno and jname='造船厂'
group by pname
  • 1
  • 2
  • 3

11. 查询同时使用了S1和S4所提供零件的项目号

如果是连接表记得取不同名称

select S1.jno from support S1, support S2 
where S1.jno=S2.jno and S1.sno='S1' and S2.sno='S4';
  • 1
  • 2

12. 找出没有使用天津产的红色零件的工程号

select jno from project where jno not in 
(select jno from supplier, component, 
support where supplier.sno=support.sno 
and component.pno=support.pno 
and city='天津' and color='红');
  • 1
  • 2
  • 3
  • 4
  • 5

13. 查询至少用了S1供应商所供应的全部零件的工程号

select distinct jno from support x 
where not exists 
(select * from support y 
where y.sno='S1' and not exists 
 (select * from support z 
 where z.jno=x.jno 
 and z.pno=y.pno));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

14. 查询没有使用零件P2的工程号

select jno from project where jno not in 
(select distinct jno from support where pno='P2')
  • 1
  • 2

15. 查询使用数最多的零件的零件号及其使用数量

这里依然那个问题，找最大，再判别，用limit不是很好

SELECT pno ,A
FROM (SELECT support.pno,sum(qty) A
     FROM component,support
     WHERE component.pno=support.pno
     GROUP BY support.pno,pname)
WHERE A >=ALL(SELECT sum(qty) A
     FROM component,support
     WHERE component.pno=support.pno
     GROUP BY support.pno,pname);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

16. 查询至少用了S1供应商所供应的全部零件的工程

select distinct jname from project where not exists (select * from supplier where not 
exists (select * from support where jno= project.jno and sno= supplier.sno))
  • 1
  • 2

相关文章