Oracle/ SELECT절 복습 adv

-- 조인 - ON절, 좌우 외부조인, 교차조인
--1. 직원중에 사수보다 급여가 높은 사람의 이름과 급여, 그리고 이를 관리하는 관리자의 이름과 급여를 검색한다.
SELECT e1.ename 이름, e1.sal 급여, e2.ename 관리자, e2.sal 급여
FROM emp e1, emp e2
WHERE e1.mgr = e2.eno
AND e2.SAL < e1.SAL;

--2. 학생 중 동명이인을 검색한다.
SELECT s1.sname, s1.sno, s2.sname, s2.sno
FROM student s1, student s2
WHERE s1.sname = s2.sname
AND s1.sno != s2.sno;



--    단일 행 함수 - 문자 함수
--1. 'scott의 업무는 analyst입니다'형태로 모든 직원을 검색한다
SELECT ename || '의 업무는 ' || job ||' 입니다.'
FROM emp;

--2. 직원의 연봉을 10자리로 검색한다. 단 공백은 임의의 채움 문자로 채워넣는다
SELECT eno, ename, LPAD((sal*12+NVL(comm,0)), 10, '*') 연봉
FROM emp;

--3. 학생의 이름을 검색한다. 단 성이 '맹'인 학생은 성을 '사마'로 바꾸어 검색한다.
SELECT sno, sname, REPLACE (sname, '맹', '사마')
FROM student
WHERE sname LIKE '맹%';



--    단일 행 함수 - 숫자, 날짜형 함수
--1. 사원들의 급여가 현재 급여와 동일하다면 입사한 달에 급여는 얼마나 지급되었을지 검색한다.
SELECT eno, ename, hdate, sal/20*(LAST_DAY(TRUNC(hdate))-TRUNC(hdate)) 입사달급여
FROM emp;

--2. 사원들의 현재까지 근무 기간이 몇년 몇개월 몇일인지 검색한다.
SELECT  TRUNC(MONTHS_BETWEEN(sysdate, hdate)/12)||'년 '||
         TRUNC(MOD(MONTHS_BETWEEN(sysdate, hdate),12))||'개월 '||
         TRUNC(sysdate-ADD_MONTHS(hdate,TRUNC(MONTHS_BETWEEN(sysdate, hdate)))+1)||
         '일 입니다.'
FROM emp;



--    단일 행 함수 - 변환 함수
--1. 화학과 1학년 학생들의 4.5 환산 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검색한다.
--   'xxx학생의 4.5 환산 평점은 x.xx입니다'
SELECT sname|| ' 학생의 4.5환산 평점은 '|| ROUND((avr/8*9),2)||'점 입니다.'
FROM student
WHERE major='화학'
AND syear=1;



--   그룹 함수
--1. 30번 부서의 업무별 연봉의 평균을 검색한다
--   단 출력 양식은 소수이하 두 자리까지 통일된 형식으로 출력한다.
SELECT dno, job, AVG(sal*12+NVL(comm,0))
FROM emp
WHERE dno=30
GROUP BY job, dno;

--2. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다
SELECT syear, MAX(avr)
FROM student
WHERE major='물리' 
GROUP BY syear;

--3. 학년별로 환산 평점의 평균값을 검색한다.
--   단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력한다.
SELECT syear, ROUND(AVG((avr/8*9)), 2) 환산평점
FROM student
GROUP BY syear;



--   그룹 함수와 Having절
--1. 근무 중인 직원이 4명 이상인 부서를 검색한다(부서번호, 인원)
SELECT dno, COUNT(eno) 인원
FROM emp 
GROUP BY dno
HAVING COUNT(eno) >= 4;

--2. 업무별 평균 연봉이 2만불 이상인 업무를 검색한다
SELECT job, AVG(sal*12+NVL(comm,0))
FROM emp
GROUP BY job
HAVING AVG(sal*12+NVL(comm,0)) >= 20000;

--3. 각 학과의 학년별 인원중 인원이 6명 이상인 학년을 검색한다.
SELECT major, syear, COUNT(sno)
FROM student
GROUP BY major, syear
HAVING COUNT(sno)>=6;