test 2 多表查询(JOINS; SUB_QUERIES)

EXERCISES 2 JOINS

1. Find the name and salary of employees in Luton.

SELECT ename, sal
FROM emp, dept
WHERE loc = 'LUTON'
AND emp.deptno = dept.deptno;

2. Join the DEPT table to the EMP table and show in department number order.

SELECT  empno, ename, job, mgr, hiredate, sal, comm, dept.deptno, dept.dname, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY deptno;

3. List the names of all salesmen who work in SALES

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE job = 'SALESMAN'
AND dname = 'SALES';

4. List all departments that do not have any employees.

SELECT dept.deptno
FROM dept
WHERE deptno NOT IN (
SELECT dept.deptno
FROM dept, emp
WHERE dept.deptno = emp.deptno
)

5 For each employee whose salary exceeds his manager's salary, list the employee's name and salary and the manager's name and salary.

SELECT worker.ename, worker.sal, manager.ename, manager.sal
FROM emp worker, dept manager
WHERE worker.mgr = worker.empno
AND worker.sal > manager.sal;

这里“未选定行”代表不存在工资超过经理工资的员工。

6 List the employees who have BLAKE as their manager.

SELECT worker.ename, worker.mgr
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno
AND manager.ename = 'BLAKE';
SELECT empno, ename
FROM emp
WHERE ename = 'BLAKE';

EXERCISES 6 SUB QUERIES

1 List the name and job of employees who have the same job as Jones.

SELECT ename, job
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'JONES')
AND ename != 'JONES';

其中JONES的工作：

SELECT ename, job
FROM emp e, dept d
WHERE ename = 'JONES'
AND e.deptno = d.deptno;

2 Find all the employees in Department 10 that have a job that is the same as anyone in department 30.

SELECT *
FROM emp e, emp m
WHERE e.deptno != m.deptno
and e.deptno = 10 and m.deptno = 30
AND e.job = m.job;

3 List the name, job, and department of employees who have the same job as Jones or a salary greater than or equal to Ford.

SELECT ename, job, deptno
FROM emp
WHERE (job = (SELECT job FROM emp WHERE ename = 'JONES')
AND ename != 'JONES')
OR sal >= (SELECT sal FROM emp WHERE ename = 'FORD' AND ename != 'FORD');

4 Find all employees in department 10 that have a job that is the same as anyone in the Sales department

SELECT * 
FROM emp
WHERE deptno = 10
AND job in (SELECT job FROM emp WHERE deptno = 
    (SELECT deptno FROM dept WHERE dname = 'SALES'));

5 Find the employees located in Liverpool who have the same job as Allen. Return the results in alphabetical order by employee name.

SELECT ename, loc, job
FROM emp e, dept d
WHERE d.loc = 'LIVERPOOL' and e.deptno = d.deptno
AND job = (SELECT job FROM emp WHERE ename = 'ALLEN')
AND ename != 'ALLEN'
ORDER BY ename;

其中，ALLEN的JOB：

SELECT ename, job
FROM emp
WHERE ename = 'ALLEN';

6 Find all the employees that earn more than the average salary of employees in their department.

select e.empno,e.ename ,e.sal ,d.salVal, e.deptno
from emp e,
(
select deptno ,avg(sal) salVal
from emp
group by deptno
) d
where e.deptno = d.deptno
and e.sal > d.salVal;

在这一题，SALVAL分别为各部门的平均工资。我列出了高于平均工资的员工以及他们所在的部门号和部门平均工资。

7 Find all the employees that earn more than JONES, using temporary labels to abbreviate table names.

SELECT e.ename, e.sal
FROM emp e
WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES');
SELECT ename, sal
FROM emp
WHERE ename = 'JONES';//JONES的工资，核对本题结果