- 查询“Chisel”所在车间的车间编号、车间主任姓名、主任联系方式
select no,name,phone from yangbin_workshop where no in(select -> shop_no from yangbin_worker where name="Chisel");
- 查询所有员工姓名、员工联系电话、车间地址、车间主任姓名、主任联系方式
select a.name,a.phone,b.address,b.name,b.phone from -> yangbin_worker a right join yangbin_workshop b -> on a.shop_no = b.no;
- 查询“Chisel”所在车间的所有员工姓名、员工联系电话、车间地址
select a.name,a.phone,b.address from -> yangbin_worker a inner join yangbin_workshop b -> on a.shop_no = b.no where a.shop_no = "WS1";
- 查询生产出质量达标产品的产品编号、员工姓名、员工联系电话
select a.no,b.name,b.phone from -> yangbin_product a inner join yangbin_worker b -> on a.work_no = b.no where is_qualified = "1";
- 查询已生产产品车间的车间号、平均工龄
select shop_no ,avg(seniority) from yangbin_worker where -> shop_no="WS2";
- 查询所有产品的产品编号、是否合格(合格返回true不合格返回false)、员工号、员工姓名、员工联系方式、车间编号、车间地址
select a.no,a.is_qualified,b.no,b.name,b.phone,c.no,c.address -> from yangbin_product a inner join yangbin_worker b -> on a.work_no = b.no -> inner join yangbin_workshop c -> on b.shop_no = c.no;
- 查询员工表中是否存在“common”工种的员工,如果存在则查询员工表“common”工种员工的所有字段信息
select * from yangbin_worker where work_type="common";
- 查询存在离职员工的车间编号、车间主任、主任联系方式
select no,name,phone from yangbin_workshop where no in(select
-> shop_no from yangbin_worker where is_onwork="0");