JOIN 헷깔리는 문제~

JOIN에서 3 테이블 연결시키기 

SELECT e.employee_id, j.job_title, d.department_name
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
 AND j.job_id = e.job_id;

 

 

 

--문제8) EMPLOYEES 테이블에서 left join하여 관리자(매니저)를 출력하고
--매니저 아이디가 없는 사람은 배제하고 하향식으로 하며, 급여는 역순으로
--출력하라.

SELECT a.employee_id, a.first_name, 
    a.manager_id, b.employee_id,
    b.first_name
FROM employees a, employees b   -- a : 사원   b : 상사(사원)
WHERE a.manager_id = b.employee_id(+)
START WITH a.manager_id IS NOT NULL
CONNECT BY a.manager_id = PRIOR a.employee_id
ORDER BY a.salary DESC;

 

 

 

 

--문제9) EMPLOYEES 테이블에서 right join하여 관리자(매니저)가 108번 상향식
-- 급여는 역순으로 출력하라.

SELECT a.employee_id, a.first_name as "사원명", a.salary,
    a.manager_id, b.employee_id, b.first_name as "상사명"
FROM employees a, employees b
WHERE a.manager_id(+) = b.employee_id
    AND a.manager_id = '108'
CONNECT BY PRIOR a.manager_id = a.employee_id
ORDER BY a.salary DESC;

 

 

 

 

-- 54) 근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시순으로 정렬하시오.
-- (근무 인원이 0명인 곳도 표시)

SELECT d.loc, count(e.empno)
FROM emp e , dept d
    WHERE e.deptno(+) = d.deptno
    GROUP BY d.loc
    HAVING count(e.empno) < 5
    ORDER BY d.loc ASC;

 

 

 

 

-- 55) 사원의 이름 및 사원 번호를 관리자의 이름과 관리자 번호와 함께 표시하고 
-- 각각의 열 레이블은 employee, emp#, manager, mgr#로 지정하시오.

SELECT a.ename employee, a.mgr emp, b.empno, b.ename manager, b.empno mgr
FROM emp a, emp b
WHERE a.mgr = b.empno;

 

 

 

-- 58) 지정한 부서번호, 사원이름 및 지정한 사원과 동일한 부서에서 근무하는 
-- 모든 사원을 표시하도록 질의를 작성하고 
-- 부서번호는 department, 사원이름은 employee, 동일한 부서에서 근무하는 사원은 colleague로 표시하시오.
-- (부서번호, 사원이름,동료 순으로 오름차순 정렬)

SELECT e.ename, e.deptno, d.deptno, d.ename colleague
FROM emp e, emp d
WHERE e.deptno = d.deptno
    AND e.empno <> d.empno --자신과 자신비교 제외
ORDER BY e.deptno, e.ename, b.ename ASC;

 

 

 

-- 59)10번 부서에서 근무하는 사원들의 
-- 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하시오.

SELECT e.deptno, d.dname, e.ename, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno
    AND e.deptno = 10
    --AND (e.sal >= s.losal AND e.sal <= s.hisal);
    AND e.sal BETWEEN s.losal AND s.hisal;