多表练习题

1.查询雇员姓名，雇员编号，工资和所在部门的名称及位置

select e.ename,e.empno,e.sal,d.dname,d.loc 

from emp e ,dept d 

where e.deptno=d.deptno;

2.查询雇员姓名，工资，入职时间，所在部门编号，部门名称 

select e.ename,e.sal,e.hiredate,d.deptno, d.dname 

from emp e ,dept d 

where e.deptno=d.deptno;

3.查询雇员姓名，雇员工资，领导姓名，领导工资（自连接查询）

select e.ename,e.sal,m.ename,m.sal 

from emp e,emp m 

where e.mgr=m.empno;

4.查询雇员姓名，雇员工资，部门名称，领导姓名，领导工资 

select e.ename,e.sal,d.dname,m.ename,m.sal 

from emp e,emp m,dept d 

where e.mgr=m.empno and e.deptno=d.deptno;

5.查询雇员姓名，雇员工资，部门名称，工资所在等级

select e.ename,e.sal,d.deptno,s.grade 

from emp e,dept d,salgrade s 

where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

6，查询雇员姓名，雇员工资，部门名称，雇员工资等级，领导姓名，领导工资，领导工资等级

SELECT e.ename, e.sal, d.dname, s.grade, m.ename, m.sal, ms.grade

FROM emp e, dept d, salgrade s, emp m, salgrade ms

WHERE e.deptno = d.deptno

AND e.mgr = m.empno

AND e.sal BETWEEN s.losal AND s.hisal

AND m.sal BETWEEN ms.losal AND ms.hisal;

7.查询雇员编号，雇员姓名，工资和部门名称 

select select e.empno as 雇员编号, e.ename as 雇员姓名, e.sal as 工资, d.dname as 部门名称 

from emp e inner join dept d on e.deptno = d.deptno;

8.查询工资大于1500的雇员姓名，雇员工资，部门名称，领导姓名

SELECT e.ename AS 雇员姓名, e.sal AS 雇员工资, d.dname AS 部门名称, m.ename AS 领导姓名

FROM emp e

INNER JOIN dept d ON e.deptno = d.deptno

LEFT JOIN emp m ON e.mgr = m.empno

WHERE e.sal > 1500;