连接查询 作业

/1、显示所有员工的姓名ename，部门号deptno和部门名称dname。/
select emp2.empno ,emp2.empname,emp2.deptno,dept.dname
from emp2 ,dept
where emp2.deptno = dept.deptno ;

select e.empno ,e.empname,e.deptno,d.dname
from emp2 e
left join dept d
on e.deptno = d.deptno ;

/2、查询20号部门员工的job和20号部门的loc/
–第一种
select e.empname,e.deptno,e.job,d.loc
from emp2 e ,
dept d
where
e.deptno = 20
and
e.deptno = d.deptno
–第二种
select e.empname,e.deptno,e.job,d.loc
from emp2 e
left join dept d on e.deptno = d.deptno
where
e.deptno = 20

/3、选择所有有奖金comm的员工的ename , dname , loc/
select e.empname,e.deptno,d.dname,d.loc,e.comm
from emp2 e
left join dept d
on e.deptno = d.deptno
where e.comm is not null
order by e.empname,d.deptno
;

select e.empname,e.deptno,d.