数据库第九次作业

1. 查询“Chisel”所在车间的车间编号、车间主任姓名、主任联系方式
2. select no,name,phone from tushi_workshop where no in(select
   shop_no from tushi_worker where name=“Chisel”);
3. 
4. 查询所有员工姓名、员工联系电话、车间地址、车间主任姓名、主任联系方式
5. select t.name,t.phone,x.address,x.name,x.phone from tushi_worker
   t right join tushi_workshop x on t.shop_no=x.no;
6. 
7. 查询“Chisel”所在车间的所有员工姓名、员工联系电话、车间地址
8. select t.name,t.phone,x.address from tushi_worker t inner join
   tushi_workshop x on t.shop_no=x.no where t.shop_no=“WS1”;
9. 
10. 查询生产出质量达标产品的产品编号、员工姓名、员工联系电话
11. select t.no,x.name,x.phone from tushi_product t inner join
    tushi_worker x on t.work_no=x.no where is_qualified=“1”;
12. 
13. 查询已生产产品车间的车间号、平均工龄
14. select shop_no ,avg(seniority) from tushi_worker where
    shop_no=“WS2”;
15. 
16. 查询所有产品的产品编号、是否合格（合格返回true不合格返回false）、员工号、员工姓名、员工联系方式、车间编号、车间地址
17. select t.no,t.is_qualified,a.no,a.name,a.phone,b.no,b.address from
    tushi_product t inner join tushi_worker a on t.work_no=a.no
    inner join tushi_workshop b on a.shop_no=b.no;
    
18. 查询员工表中是否存在“common”工种的员工，如果存在则查询员工表“common”工种员工的所有字段信息
19. select * from tushi_worker where work_type=“common”;
20. 
21. 查询存在离职员工的车间编号、车间主任、主任联系方式

select no,name,phone from tushi_workshop where no in(select
shop_no from tushi_worker where is_onwork=“0”);