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

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

select no,name,phone from shengdian_workshop where
no in (select shop_no from shengdian_worker where name = "chisel");

 

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

select a.name,a.phone,b.address,b.name,b.phone from shengdian_worker a right join shengdian_workshop b
on a.shop_no = b.no;

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

select a.name,a.phone,b.address from shengdian_worker a inner join  shengdian_workshop b
on a.shop_no = b.no where a.shop_no = "ws1";

 

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

select a.no,b.name,b.phone from shengdian_product a inner join shengdian_worker b
on a.work_no = b.no where is_qualified = "1";

 

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

select shop_no,avg(seniority) from shengdian_worker where shop_no = "ws2";

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

 

select a.no,a.is_qualified,b.no,b.name,b.phone,c.no,c.address from
shengdian_product a inner join shengdian_worker b
on a.work_no = b.no
inner join shengdian_workshop c
on b.shop_no = c.no;

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

select * from shengdian_worker where work_type = "common";

 

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

select no,name,phone from shengdian_workshop where no
in (select shop_no from shengdian_worker where is_onwork = "0");