[Oracle] 오라클 INNER JOIN 연습문제

예제 테이블 SQL 다운로드

https://drive.google.com/file/d/1J-FsC7T67emrsQ52GJWFizrnV8gsuWJa/view?usp=sharing

SELECT * FROM emp;

SELECT * FROM dept;

--INNER JOIN 문제

--직업이 SALESMAN인 사원들 중에서 최대 월급을 출력하시오

SELECT MAX(sal) FROM emp WHERE job = 'SALESMAN';

--부서번호, 부서번호별 최대 월급을 출력하시오.

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;

--위의 결과에서 20번은 제외하고 출력

SELECT deptno, MAX(sal) FROM emp WHERE deptno<>20 GROUP BY deptno;

--직업, 직업별 최소 월급을 출력하는데, 직업이 SALESMAN인 사원들을 제외하고 출력하시오.

SELECT job, MIN(sal) FROM emp WHERE job <> 'SALESMAN' GROUP BY job;

--위의 결과에서 최소월급이 높은것부터 출력하시오.

SELECT job, MIN(sal) FROM emp WHERE job <> 'SALESMAN' GROUP BY job ORDER BY MIN(sal) DESC;

--직업과 직업별 토탈 월급을 출력하는데, 직업별 토탈 월급이 4000 이상인 것만 출력하시오.

SELECT job, SUM(sal) FROM emp GROUP BY job HAVING SUM(sal) >= 4000;

--직업, 직업별 토탈 월급을 출력하는데, 직업이 SALESMAN은 제외하고 출력하고, 직업별 토탈 월급이 4000이상인 것만 출력하고 또 직업별 토탈 월급이 높은 것 부터 출력하시오.

SELECT job, SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal) >= 4000 ORDER BY SUM(sal) DESC;

--입사한 년도(4자리), 입사한 년도별 토탈 월급을 출력하시오.

SELECT 19||SUBSTR(hiredate,1,2), SUM(sal) FROM emp GROUP BY SUBSTR(hiredate,1,2);
SELECT TO_CHAR(hiredate,'RRRR'),SUM(sal) FROM emp GROUP BY TO_CHAR(hiredate,'RRRR');

--위의 문제에서 토탈 월급을 출력할떄 콤마가 찍히게 천단위를 부여하시오.

SELECT 19||SUBSTR(hiredate,1,2), TO_CHAR( SUM(sal) , '999,999,999,999,999') FROM emp GROUP BY SUBSTR(hiredate,1,2);

--사원 테이블의 사원수가 몇명인지 조회하시오.

SELECT COUNT(*) FROM emp;

--커미션을 카운트 하시오. --> MAX, MIN, COUNT => NULL값을 무시(제외)

SELECT COUNT(comm) FROM emp;
SELECT COUNT(NVL(comm,0)) FROM emp;

--사원 테이블의 커미션의 평균값을 출력하시오. --> MAX, MIN, COUNT => NULL값을 무시(제외)

SELECT ROUND( AVG(comm),2) FROM emp; 
SELECT ROUND( AVG(NVL(comm,0)),2 ) FROM emp; --위와 나누는 값이 달라서 값이 달라진다.

--직업, 직업별 인원수를 출력하는데, 직업이 SALESMAN은 제외하고 출력하고 직업별 인원수가 3명 이상인 것만 출력하고, 직업별 인원수가 높은것 부터 출력하시오.

SELECT job, COUNT(job) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING COUNT(job) >= 3 ORDER BY COUNT(job) DESC;
SELECT job, COUNT(*) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING COUNT(*) >= 3 ORDER BY COUNT(*) DESC;

--직업, 직업별 인원수를 출력하는데 직업별 인원수가 3명 이상인것만 출력하시오.

SELECT job, COUNT(job) FROM emp GROUP BY job HAVING COUNT(job) >= 3;
SELECT job, COUNT(*) FROM emp GROUP BY job HAVING COUNT(*) >= 3;

--부서번호, 부서번호별 평균 월급을 출력하는데, 20번 부서번호는 제외하고 출력하고 부서번호별 평균 월급이 2000이상인 것만 출력하며 부서번호별 평균 월급이 높은것 부터 출력하시오

SELECT deptno, AVG(sal) FROM emp WHERE deptno<>20 GROUP BY deptno HAVING AVG(sal) >= 2000 ORDER BY 2 DESC;

SELECT * FROM emp;

SELECT * FROM dept;

--사원이름, 부서위치를 출력하시오.

SELECT ename, loc FROM emp, dept WHERE emp.deptno = dept.deptno;

--직업이 SALESMAN인 사원들의 이름과 월급과 직업과 부서위치를 출력하세요.

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

--사원이름, 월급, 부서위치, 부서번호를 출력하시오. (SELECT문에서 조회할 데이터의 테이블명을 명시해주면 속도가 빨라진다.)

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

--DALLAS에서 근무하는 사원들의 이름과 부서위치와 월급을 출력하시오.

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

--월급이 3000이상인 사원들의 이름과 월급과 부서위치와 입사일을 출력하시오.

SELECT ename, loc, hiredate FROM emp, dept WHERE emp.deptno = dept.deptno AND (sal)>=3000;

--1981년도에 입사한 사원들의 이름과 입사일과 부서위치를 출력하시오.

SELECT ename, hiredate, loc FROM emp, dept WHERE emp.deptno = dept.deptno AND TO_CHAR(hiredate,'RRRR')=1981; --ORACLE JOIN
SELECT ename, hiredate, loc FROM emp, dept WHERE emp.deptno = dept.deptno AND TO_CHAR(hiredate,'YYYY')=1981; --ORACLE JOIN
SELECT ename, hiredate, loc FROM emp, dept WHERE emp.deptno = dept.deptno AND hiredate LIKE '81%'; --ORACLE JOIN
SELECT ename, hiredate, loc FROM emp JOIN dept ON emp.deptno = dept.deptno AND TO_CHAR(hiredate,'RRRR')=1981; --ANSI JOIN

--부서위치, 부서위치별 토탈월급을 출력하시오

SELECT loc, TO_CHAR(SUM(sal),'9,999,999') FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY loc; --ORACLE JOIN
SELECT loc, TO_CHAR(SUM(sal),'9,999,999') FROM emp JOIN dept on emp.deptno = dept.deptno GROUP BY loc; --ANSI JOIN

--emp 테이블과 salgrade 테이블을 조인해서 이름과 월급과 grade(급여등급)을 출력하시오.

SELECT empno, ename, job, hiredate, sal, grade FROM emp, salgrade s WHERE sal BETWEEN s.losal AND s.hisal;

--급여 등급이 2등급인 사원들의 이름과 월급과 급여등급을 출력하시오.

SELECT empno, ename, job, hiredate, sal, grade FROM emp, salgrade s WHERE sal BETWEEN s.losal AND s.hisal AND grade=2;

SELECT e1.empno "사번" ,e1.ename "이름", e2.MGR "사수번호", e2.ename "사수이름"
FROM emp e1, emp e2
WHERE e1.mgr=e2.empno(+);