MySQL第九次作业（连接查询和子查询）

1、查询“Chisel”所在车间的车间编号、车间主任姓名、主任联系方式

select no,name,phone from shenqin_workshop where no in (select shop_no from shenqin_worker where name="Chisel");

 

2、查询所有员工姓名、员工联系电话、车间地址、车间主任姓名、主任联系方式

select shenqin_worker.name,shenqin_worker.phone,shenqin_workshop.address,shenqin_workshop.name,shenqin_workshop.phone

    -> from shenqin_workshop inner join shenqin_worker

    -> on shenqin_workshop.no=shenqin_worker.shop_no;

 

3、查询“Chisel”所在车间的所有员工姓名、员工联系电话、车间地址

select shenqin_worker.name,shenqin_worker.phone,shenqin_workshop.address

   -> from shenqin_worker inner join shenqin_workshop

    -> where shenqin_worker.name="Chisel";

 

 

4、查询生产出质量达标产品的产品编号、员工姓名、员工联系电话

select shenqin_worker.name,shenqin_worker.phone,shenqin_product.no

    -> from shenqin_worker inner join shenqin_product    

-> on shenqin_worker.no=shenqin_product.work_no 

 -> where is_qualified="1";

 

5、查询已生产产品车间的车间号、平均工龄

select shenqin_workshop.no,avg(shenqin_worker.seniority) from

    -> shenqin_worker,shenqin_workshop,shenqin_product

-> where shenqin_worker.no=shenqin_product.work_no and shenqin_worker.shop_no=shenqin_workshop.no

   -> group by shenqin_workshop.no;

 

6、查询所有产品的产品编号、是否合格（合格返回true不合格返回false）、员工号、员工姓名、员工联系方式、车间编号、车间地址

select shenqin_product.no,shenqin_product.is_qualified,shenqin_worker.no,shenqin_worker.name,shenqin_worker.phone,shenqin_workshop.no,shenqin_workshop.address from

  -> shenqin_worker,shenqin_workshop,shenqin_product

->where shenqin_worker.no=shenqin_product.work_no and shenqin_worker.shop_no=shenqin_workshop.no;

 

7、查询员工表中是否存在“common”工种的员工，如果存在则查询员工表“common”工种员工的所有字段信息

select*from shenqin_worker where work_type="common";

 

8、查询存在离职员工的车间编号、车间主任、主任联系方式

select shenqin_workshop.no,shenqin_workshop.phone,shenqin_workshop.name from

-> shenqin_worker inner join shenqin_workshop   

-> on shenqin_worker.shop_no=shenqin_workshop.no 

-> where shenqin_worker.is_onwork="0";