查询“Chisel”所在车间的车间编号、车间主任姓名、主任联系方式
输入:select no,name,phone from zlf_workshop where no in(select shop_no from zlf_worker where name="chisel");![](https://i-blog.csdnimg.cn/blog_migrate/a4fd350126be597f4488333ce6875d65.png)
查询所有员工姓名、员工联系电话、车间地址、车间主任姓名、主任联系方式
输入:select a.name,a.phone,b.address,b.name,b.phone from zlf_worker a right join zlf_workshop b on a.shop_no = b.no;![](https://i-blog.csdnimg.cn/blog_migrate/96cd35d443cf3520a8abfa0cbbe2cf31.png)
查询“Chisel”所在车间的所有员工姓名、员工联系电话、车间地址
输入: select a.name,a.phone,b.address from zlf_worker a inner join zlf_workshop b on a.shop_no = b.no where a.shop_no = "ws1";![](https://i-blog.csdnimg.cn/blog_migrate/992d2194827a9007260b786dd2cf7d95.png)
查询生产出质量达标产品的产品编号、员工姓名、员工联系电话
输入: select b.no,a.name,a.phone from zlf_worker a inner join zlf_product b on a.no = b.work_no where b.IS_QUALIFIED = "1";![](https://i-blog.csdnimg.cn/blog_migrate/e3f5de4cd6b7b4dc56a032e33755fac8.png)
查询已生产产品车间的车间号、平均工龄
输入: select a.shop_no,avg(SENIORITY) from zlf_worker a where shop_no = "ws2";![](https://i-blog.csdnimg.cn/blog_migrate/9cca9647be88ad0d0adc565efa6264f6.png)
查询所有产品的产品编号、是否合格(合格返回true不合格返回false)、员工号、员工姓名、员工联系方式、车间编号、车间地址
输入:select a.no,a.IS_QUALIFIED,a.work_no,b.name,b.phone,b.shop_no,c.address from zlf_product a inner join zlf_worker b on a.work_no = b.no inner join zlf_workshop c on b.shop_no = c.no ;![](https://i-blog.csdnimg.cn/blog_migrate/2b7065f44c2a1257cceedf5b3f94b911.png)
查询员工表中是否存在“common”工种的员工,如果存在则查询员工表“common”工种员工的所有字段信息
输入:select * from zlf_worker where work_type = "common";![](https://i-blog.csdnimg.cn/blog_migrate/07cba5c505517d975fc40d4f7c8457bc.png)
查询存在离职员工的车间编号、车间主任、主任联系方式
输入:select no,name,phone from zlf_workshop where no in (select shop_no from zlf_worker where IS_ONWORK = "0");![](https://i-blog.csdnimg.cn/blog_migrate/2fdf67a7a9d0f87ee92923ea50102be8.png)