[Oracle] 과제 문제 모음 -1-

---

2015년 Naver Blog 기록

---

1. ename에서 'A'가 몇 번 째에 위치하는 지 출력

SELECT INSTR(ENAME,'A') 
FROM EMP;

 

2. 이름의 세번째 자리가 R인 직원을 검색(ename)

SELECT ENAME 
FROM EMP 
WHERE INSTR(ENAME,'R')=3;

 

3. 이름의 끝자리가 N으로 끝나는 직원을 검색(ename)

SELECT ENAME 
FROM EMP 
WHERE INSTR(ENAME,'N')=LENGTH(ENAME);

 

4. 10번 부서에 근무하는 직원들이 현재까지 근무한 월수를 계산하여 성명,입사일,오늘날짜와 함께 출력

SELECT ENAME,HIREDATE,SYSDATE,
TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) "근무월수" 
FROM EMP 
WHERE DEPTNO=10;

 

5. 10번 부서원의 입사 일자로 부터 5개월이 지난 후의 날짜를 출력하세요.(성명,입사일과 함께 출력)

SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,5) "5개월후" 
FROM EMP 
WHERE DEPTNO=10;

 

6. 직원 중 이름이 5글자인 직원을 검색하되 이름은 소문자로 출력

    ==> 사원번호,성명,입사일출력하기

SELECT EMPNO,LOWER(ENAME),HIREDATE 
FROM EMP 
WHERE LENGTH(ENAME)=5;

 

7. EMP 테이블에서 부서가 20번인 사원의 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 조회

SELECT EMPNO,ENAME,LENGTH(ENAME),SAL,LENGTH(SAL) 
FROM EMP 
WHERE DEPTNO=20;

 

8. EMP 테이블에서 이름 중'L'자의위치를 조회

SELECT ENAME,INSTR(ENAME,'L',1,1) 
FROM EMP;

 

---

groupby / _having 연습하기

---

0. emp테이블에 사원이름, 매니저번호, 매니저번호가 null이면 ‘상위자’ 로 표시하고, 매니저번호가  있으면 '담당' 으로 표시하세요.

 

SELECT ENAME,MGR,NVL2(MGR,'담당','상위자')
FROM EMP;

 

1. 각 부서별로 월급의 평균을 조회한 후 평균값이 6000 보다 큰 값만 출력하고 부서번호가 없을 경우는 'No Department'로 출력되도록 하시오. 출력되는 부서별 평균값은 소수점 일의 자리에서 반올림을 시킵니다. (예 : 4.5 인 경우 5로 변함) alias를 포함하여 실행결과와 동일하게 출력되어야 합니다.

SELECT NVL(TO_CHAR(DEPARTMENT_ID),'NO DEPARTMENT'), ROUND(AVG(SALARY),0) 
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID 
HAVING AVG(SALARY)>6000;

NVL(A,B) : A,B의 TYPE은 같아야한다. 
DEPARTMENT_ID의 TYPE이 NUMBER이기 때문에 CHAR인 'NO DEPARTMENT'와 맞추기 위하여 TO_CHAR 함수를 활용한다. 

 

2.  EMPLOYEES 테이블에서 부서별 평균연봉을 구하는 SQL 문을 작성하시요. 출력결과는 부서아이디와 평균연봉이며 평균 연봉이 10000 이상(같거나 큰) 부서에 대해서만 정보를 출력하시요. <<참고 및 주의사항>> 평균급여 결과의 소수점 뒷자리는 경우에 따라 다를수 있음으로 신경쓰지 않는다.

 

SELECT DEPARTMENT_ID, AVG(SALARY) 
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID 
HAVING AVG(SALARY)>=10000;

 

3. 부서 아이디별로 해당 부서의 부서아이디, 평균급여를 평균급여에 대한 내림차순으로 정렬하여 조회하시오.

 

SELECT DEPARTMENT_ID, AVG(SALARY) 
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN (40,50) 
GROUP BY DEPARTMENT_ID 
ORDER BY AVG(SALARY) DESC;

 

4. EMPLOYEE Table에서 first_name, last_name, 급여, 커미션 금액, 총액(sal + comm)을 구하여 총액이 많은 순서로 출력하라. 단, 커미션이 NULL인 사람은 제외한다

SELECT FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT, SALARY+COMMISSION_PCT 총액 
FROM EMPLOYEES 
WHERE COMMISSION_PCT IS NOT NULL 
ORDER BY 총액 DESC;

 

1. 각 년도별로 지급된 급여의 합계를 다음과 같이 출력되도록 작성하세요.

 

SELECT TO_CHAR(HIRE_DATE,'YYYY') 년도,SUM(SALARY) 지급된급여  
FROM EMPLOYEES 
GROUP BY TO_CHAR(HIRE_DATE,'YYYY') 
ORDER BY 년도 ;

 

2. 5월에 입사한 사람들을 대상으로 각 부서별로 월급의 평균을 조회한 후 평균값이 6000 보다 큰 값만 출력하고 부서번호가 없을 경우는 '부서배정받지 못함'으로 출력되도록 하시오. 출력되는 부서별 평균값은 소수점 일의 자리에서 버립니다.(예 : 4.5 인 경우 4로 변함)alias를 포함하여 실행결과와 동일하게 출력되어야 합니다.

 

SELECT NVL(TO_CHAR(DEPARTMENT_ID),'부서배정받지 못함') 부서번호,TRUNC(AVG(SALARY),0) 평균급여
FROM EMPLOYEES 
WHERE TO_CHAR(HIRE_DATE,'MM')=05 
GROUP BY DEPARTMENT_ID 
HAVING AVG(SALARY)>6000;

WHERE TO_CHAR(HIRE_DATE,'MM')=05 와 WHERE TO_CHAR(HIRE_DATE,'MM')='05'의 차이점
- TO_CHAR 했으니 붙여주는게 정석이지만, ORACLE은 좀 정책이 느슨해서 허가해주기도함

 

3. EMPLOYEES 테이블에서 직업별 평균연봉을 구하는 SQL 문을 작성하시요. 출력결과는 직업코드와 평균연봉이며 평균 연봉이 10000 이상(같거나 큰) 직업에 대해서만 정보를 출력하시요. 

 

SELECT JOB_ID,AVG(SALARY) 
FROM EMPLOYEES 
GROUP BY JOB_ID 
HAVING AVG(SALARY)>=10000;