数据库第九次作业-连接查询&子查询

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

 select no,name,phone from zhounianhuan_workshop where no in(select
    -> shop_no from zhounianhuan_worker where name="Chisel");

 

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

 select z.name,z.phone,x.address,x.name,x.phone from zhounianhuan_worker
    -> z right join zhounianhuan_workshop x on z.shop_no=x.no;

 

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

 select z.name,z.phone,x.address from zhounianhuan_worker z inner join
    -> zhounianhuan_workshop x on z.shop_no=x.no where z.shop_no="WS1";

 

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

 select z.no,x.name,x.phone from zhounianhuan_product z inner join
    -> zhounianhuan_worker x on z.work_no=x.no where is_qualified="1";

 

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

 select shop_no ,avg(seniority) from zhounianhuan_worker where
    -> shop_no="WS2";

 

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

 select z.no,z.is_qualified,a.no,a.name,a.phone,b.no,b.address from
    -> zhounianhuan_product z inner join zhounianhuan_worker a on z.work_no=a.no
    -> inner join zhounianhuan_workshop b on a.shop_no=b.no;

 

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

 select * from zhounianhuan_worker where work_type="common";

 

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

 select no,name,phone from zhounianhuan_workshop where no in(select
    -> shop_no from zhounianhuan_worker where is_onwork="0");