헷깔리는 함수문제~

 

--문제3) EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가
--많은 다른 모든 부서를 출력하라

->>부서출력 : 먼저 부서를 그룹으로 묶어주고 HAVING절에 서브쿼리 추가

그룹명과 그룹함수만 출력 가능, WHERE절 추가하여 IS NOT NULL 추가가능

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
                  FROM employees
                  WHERE department_id = 100);

 

 

--문제4) 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호
--를 출력하여라. 단 업무별로 정렬하여라.

->> '업무별로 최소급여' 기 때문에  서브쿼리에서 가져와야 하는 것도 '업무와 최소급여' 

최소급여만 가져올 경우 최소급여와 같은 사원들을 뽑게됨

SELECT employee_id, first_name, job_id, department_id, salary
FROM employees
WHERE (job_id,salary) IN (SELECT job_id, MIN(salary)
                  FROM employees
                  GROUP BY job_id)
ORDER BY job_id;

 

 

 

--문제6) EMPLOYEES 테이블에서 가장 많은 사원을 갖는 MANAGER의
--사원번호를 출력하라.

->> 매니저 아이디로 그룹화 하여 최대 카운팅을 구한 후 카운팅으로 넘겨받고 매니저 아이디로 출력....ㅜ

SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT(*) IN (SELECT MAX(COUNT(*))
                    FROM employees
                    GROUP BY manager_id
                    );

 

 

 

--문제9) 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업
--무, 부서명을 출력하라.
-- 조건1 : 직업별로 내림차순 정렬

SELECT e.employee_id, e.first_name, e.job_id, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
    AND (job_id, salary) IN (SELECT job_id, MIN(salary)
                                    FROM employees
                                    GROUP BY job_id)
ORDER BY job_id DESC;

 

 

--문제10) EMPLOYEES 테이블에서 업무별로 최소 급여를 받는 사원의 정보를 사
--원번호,이름,업무,입사일자,급여,부서번호를 출력하라

SELECT
    EMPLOYEE_ID,FIRST_NAME,JOB_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE e.employee_id = D.DEPARTMENT_ID
AND(JOB_ID,SALARY) IN(SELECT JOB_ID,MIN(SALARY) FROM EMPLOYEES GROUP BY JOB_ID)
ORDER BY E.JOB_ID DESC;

 

 

-- 65) 직속상사가 KING인 모든 사원의 이름과 급여를 출력하시오.

SELECT ename, sal, mgr
FROM emp
WHERE MGR IN (SELECT empno FROM emp WHERE ename = 'KING');

 

-- 66) 20번 부서에서 가장 급여를 많이 받는 사원과 동일한 급여를 받는 
-- 사원의 이름과 부서명,급여, 급여등급을 출력하시오.(emp, dept, salgrade)

SELECT d.dname, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno
    AND SAL BETWEEN s.losal AND s.hisal
    AND sal IN (SELECT MAX(sal) FROM emp WHERE deptno = 20);

 

 

--67) 총급여sal+comm가 평균 급여보다 많은 급여를 받는 사람의 부서번호, 이름, 총급여, 
--    커미션을 출력하시오.(커미션은 유(O),무(X)로 표시하고 컬럼명은 "comm유무" 출력)

    NVL(컬럼, 컬럼이 NULL의 경우 설정할 값)
    
    NVL2(컬럼, 컬럼이 NULL이 아닐 경우 설정할 값, 컬럼이 NULL일경우 설정 할 값)

SELECT DEPTNO, ENAME, SAL+NVL(COMM,0), NVL2(COMM,'유','무') AS "comm유무"
FROM EMP
WHERE SAL+NVL(COMM,0) > (SELECT AVG(SAL)
                    FROM EMP);

 

-- 68) CHICAGO 지역에서 근무하는 사원의 평균 급여보다 높은 급여를 받는 사원의 이름과 급여,
--    지역명을 출력하시오.

SELECT e.ename, e.sal, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
    AND sal > (SELECT AVG(sal) 
                FROM emp e, dept d
                WHERE e.deptno = d.deptno
                AND loc = 'CHICAGO'); 

SELECT e.ename, e.sal, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND sal> (SELECT AVG(sal)                           --2. 평균급여   --3. 비교
            FROM emp
            WHERE deptno = (SELECT deptno           --1. 지역번호
                            FROM dept
                             WHERE loc = 'CHICAGO')
            );

 

-- 69) 업무가 SALESMAN인 직원이 2명 이상인 부서의 이름, 근무하는 사원의 이름, 업무를 출력
--    하시오.(컬럼명은 부서명, 사원명, 업무로 출력)

SELECT DNAME "부서명",ENAME "사원명",JOB "업무"
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND
        DNAME IN (SELECT DNAME
                    FROM EMP E, DEPT D
                    WHERE E.DEPTNO = D.DEPTNO
                    AND JOB = 'SALESMAN'
                    GROUP BY DNAME
                    HAVING COUNT(*) > 2);

SELECT DNAME "부서명",ENAME "사원명",JOB "업무"
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND
        E.DEPTNO IN (SELECT DEPTNO
                FROM EMP 
                WHERE JOB = 'SALESMAN'
                GROUP BY DEPTNO
                HAVING COUNT(job) >= 2);

 

 

 

-- 70) 커미션이 없는 사원들 중 월급이 가장 높은 사원의 이름과 급여등급을 출력하시오.

SELECT ENAME,GRADE
FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
        AND SAL = ( SELECT MAX(SAL)
                    FROM EMP)
AND COMM IS NULL;

SELECT e.ename, s.grade
FROM emp e, salgrade s
WHERE sal = ( SELECT MAX(sal)
            FROM emp
            WHERE comm IS NULL or comm = 0)
    AND sal BETWEEN s.losal AND s.hisal;

 

 

-- 71) SMITH의 관리자의 이름과 부서명, 근무지역을 출력하시오. 

SELECT e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
    AND e.empno = (SELECT mgr
                FROM emp 
                WHERE ename = 'SMITH');