edu day 4
SQL함수
3일차 배운 내용 응용
- CASE 조건함수가 아닌 DECODE 조건함수에 SIGN함수를 써서 월급 별 등급 구현
SELECT SAL 급여,
DECODE( SIGN(SAL-4000), 1, 'A',
DECODE( SIGN(SAL-3000), 1, 'B',
DECODE( SIGN(SAL-2000), 1, 'C',
DECODE( SIGN(SAL-1000), 1, 'D', 'E')
))) GRADE
FROM EMP;- 80년생인 여학생 중 성이 '김'인 학생의 주민번호, 학생이름을 조회하시고, 학생이름으로 오름차순 정렬, 주민번호 -> [주민번호]로 표시
SELECT '[' || RPAD(SUBSTR(student_ssn, 1, 8), 14, '*') || ']' 주민번호, student_name 이름
FROM TB_STUDENT
WHERE SUBSTR(student_ssn, 8, 1) = 2 AND SUBSTR(student_name,1, 1) = '김' AND SUBSTR(student_ssn, 1, 2) = '80' ORDER BY 2;- 영어영문학과 교수의 이름과 주소 출생년도를 출력. 나이가 적은 순으로 정렬.
-- JOIN 미사용. 영어영문학과의 no를 찾고 그 no를 통해 교수의 정보 SELECT.
SELECT department_no FROM TB_DEPARTMENT WHERE department_name = '영어영문학과';
SELECT professor_name 교수이름, SUBSTR(professor_ssn, 1, 2) 출생년도, professor_address FROM TB_PROFESSOR WHERE department_no = '002' ORDER BY 2 DESC;
-- JOIN 사용
SELECT p.professor_name 교수이름, SUBSTR(p.professor_ssn, 1, 2) 출생년도, p.professor_address 주소 FROM TB_DEPARTMENT D, TB_PROFESSOR P WHERE d.department_no = p.department_no AND d.department_name = '영어영문학과' ORDER BY 2 DESC;- 계열이 '예체능'인 학과의 정원을 기준으로 40 이상이면 '대강의실' 30이상이면 '중강의실' 나머지는 '소강의실'로 출력한다. 단, 정원이 많은 순으로 출력한다.
SELECT department_name 학과이름, capacity 현재정원,
CASE
WHEN capacity >= 40 THEN '대강의실'
WHEN capacity >= 30 THEN '중강의실'
ELSE '소강의실'
END 강의실크기
FROM TB_DEPARTMENT WHERE category = '예체능' ORDER BY 1, 2 DESC ;그룹함수
단일함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 반환하는 함수를 의미한다.
원리는 다음 그림과 같다.
그룹함수 종류
SUM : NULL값을 제외한 해달 컬럼 값들의 총합을 구한다.
인자로 DISTINCT를 사용하면 중복된 값을 제외하고 총합 계산이 처리된다.
ALL은 중복된 값을 모두 포함하여 총합계산이 처리되며 일반적으로 생략하고 사용된다.
SUM( DISTINCT | ALL | 컬럼명 )
AVG : NULL값을 제외한 평균을 구한다.
인자로 DISTINCT를 사용하면 중복된 값을 제외하고 평균 계산이 처리된다.
ALL은 중복된 값을 모두 포함하여 평균계산이 처리되며 일반적으로 생략하고 사용된다.
AVG( DISTINCT | ALL | 컬럼명 )
SELECT SUM(SAL), AVG(SAL) FROM EMP;!주의!
그룹함수와 컬럼값 출력은 동시에 하면 에러. 그룹함수는 하나의 값만 출력하기 때문이다.
-> 추후에 방법이 나온다.
MAX&MIN : 해당 컬럼의 최댓값과 최소값을 구하는 함수이다. 이하 설명 동일.
다른 함수와 다르게 숫자 데이터 뿐만 아니라 문자 및 날짜 데이터에도 사용이 가능하다.
MAX( DISTINCT | ALL | 컬럼명 )
MIN( DISTINCT | ALL | 컬럼명 )
SELECT MAX(hiredate), MIN(HIREDATE) FROM EMP;COUNT : 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수이다. 이하 설명 같다.
COUNT(*)은 NULL값이 있어도 포함하여 전체 행의 개수를 반환한다!!
COUNT( DISTINCT | ALL | 컬럼명 | * )
SELECT COUNT(JOB) FROM EMP; -- 중복없이 JOB의 개수를 센다
SELECT COUNT(COMM) FROM EMP; -- NULL값을 제외한 개수를 센다.
SELECT COUNT(*) FROM EMP; -- 테이블 전체 행을 구할 수 있다.GROUP BY
GROUP BY 절 : 특정 컬럼값을 기준으로 그룹으로 묶을 때 사용하는 방법이다. 대표적으로 부서별, 성별, 직급별, 또는 학년별로 묶는 경우이다.
SELECT [ 단순 컬럼 ,] 그룹함수 , 그룹함수2
FROM 테이블명
[WHERE 조건식][GROUP BY 단순 컬럼]
[ORDER BY 표현식];
- GROUP BY절을 작성할 때 알아야 될 작성 지침
■ SELECT절 뒤에 사용할 수 있는 컬럼은 GROUP BY뒤에 기술된 컬럼이거나 그룹함수가 적용된 컬럼만 사용 가능하다.
■ WHERE 절을 사용하여 행을 그룹으로 그룹핑(Grouping)하기 전에 제외 시킬 수 있다.
■ 그룹으로 묶은 후 행을 제외 시키려면 HAVING절을 사용한다.
■ GROUP BY 절 뒤에는 컬럼 별칭(alias) 및 컬럼 순서 위치값을 사용할 수 없다.
■ WHERE 절에는 그룹함수를 사용할 수 없다
- 부서별 월급 평균
SELECT DEPTNO 부서, ROUND(AVG(SAL), 1) "부서별 월급 평균" FROM EMP GROUP BY deptno ORDER BY 1;- 직업별 최고 월급
SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB ORDER BY 2 DESC;- 가장 빠른 입사 날짜
SELECT DEPTNO 부서, MIN(hiredate) "가장 빠른 입사날짜" FROM EMP GROUP BY DEPTNO ORDER BY 2;- 부서별 총 인원
SELECT DEPTNO 부서, COUNT(*) "부서별 인원" FROM EMP GROUP BY deptno ORDER BY 2 DESC;- 사원들의 입사년도별 총 인원 비교
SELECT COUNT(*) 총인원수,
SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1980, 1, 0)) "1980",
SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1981, 1, 0)) "1981",
SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1982, 1, 0)) "1982"
FROM EMP;- 입사년도 별 총 인원 수
SELECT TO_CHAR(HIREDATE, 'YYYY') 입사년도, COUNT(*) 인원 FROM EMP GROUP BY TO_CHAR(hiredate, 'YYYY') ORDER BY 1;- 같은 년/월 별 월급 총합
SELECT TO_CHAR(hiredate, 'YYYY/MM') "년/월", SUM(SAL) 월급총합 FROM EMP GROUP BY TO_CHAR(hiredate, 'YYYY/MM') ORDER BY 1;
SELECT TO_CHAR(hiredate, 'YYYY') 년, TO_CHAR(hiredate, 'MM') 월, SUM(SAL) 월급총합 FROM EMP GROUP BY TO_CHAR(hiredate, 'YYYY'), TO_CHAR(hiredate, 'MM') ORDER BY 1;
HAVING
: WHERE절은 SELECT문에서 조건을 지정하여 조건과 일치하는 데이터만 추출할때 사용되며 HAVING 절은 GROUP BY절에 의해서 생성된 결과 중에서 조건과 일치하는 데이터를 추출할 때 사용한다.
SELECT [ 단순 컬럼 ,] 그룹함수 , 그룹함수2
FROM 테이블명
[WHERE 조건식][GROUP BY 단순 컬럼]
[HAVING 조건식][ORDER BY 표현식];
- 부서 별 월급 총액이 7000이상인 부서 조회
SELECT deptno 부서, SUM(SAL) 월급총액 FROM EMP GROUP BY DEPTNO HAVING SUM(empno) >= 4000 ORDER BY 1;- 부서 별 인원수가 5명 이상인 부서의 부서번호, 인원수
SELECT DEPTNO 부서, COUNT(*) 인원수 FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >= 5 ORDER BY 1;- 부서 별 월급이 1250 이상이고 인원수가 5명 이상인 부서의 부서번호, 인원수
SELECT DEPTNO 부서, COUNT(*) 인원수 FROM EMP WHERE SAL >= 1250 GROUP BY DEPTNO HAVING COUNT(*) >= 5 ORDER BY 1;
- 사원테이블에서 JOB별 인원수와 총 인원수 출력
결과:
SELECT
SUM(DECODE(JOB, 'CLERK', 1, 0)) "CLERK",
SUM(DECODE(JOB, 'SALESMAN', 1, 0)) "SALESMAN",
SUM(DECODE(JOB, 'MANAGER', 1, 0)) "MANAGER",
SUM(DECODE(JOB, 'ANALYST', 1, 0)) "ANALYST",
SUM(DECODE(JOB, 'PRESIDENT', 1, 0)) "PRESIDENT",
COUNT(*)
FROM EMP GROUP BY JOB;JOIN
: 검색하고자 하는 데이터가 여러 테이블에 분산되어 있는 경우에는 여러 테이블은 연결해서 필요한 데이터를 조회해야 된다. 이렇게 테이블을 연결해서 필요한 데이터를 조회하는 방법을 조인(join)이라고 한다.
- 참조 당하는 DEPT테이블을 부모 테이블(master table)이라 부르고 참조하는 EMP테이블을 자식 테이블(slave table)이라고 한다.
외래키는 자식 테이블에 존재한다.
조인 종류
- Oracle에서만 사용되는 오라클 조인
- Oracle 이외의 DBMS에서도 사용 가능한 ANSI 조인
ORACLE JOIN
cartesian Product
: 조인할 때 일치하는 데이터만 반환하지 않고 조인하는 각 테이블의 행 개수를 서로 곱한 결과가 반환된다.
따라서 유효한 데이터로 사용되지 못하며 일반적으로 조인 조건이 생략된 경우에 발생한다.
SELECT last_name,department_name
FROM employees, departments위의 SQL문은 WHERE절에 조건을 명시하지 않았기 때문에 caresian product가 발생되며 실행결과는 EMP행 12개, DEPT행 3개를 곱함 총 36개의 행이 반환된다. 출력된 값은 데이터로서의 가치가 없다.
EQUI JOIN
:Equi 조인은 가장 많이 사용되는 조인 방법으로 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 반드시 일치하는 행을 연결하여 데이터를 반환하는 조인이다.
일치하지 않는 데이터가 존재하는 경우에는 제외되며 대부분 기본키(Primary key)을 가진 테이블(master)과 참조키(Foreign Key)을 가진 테이블(slave)을 조인할 때 사용한다.
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;
- 공통 컬럼 사용 시 모호성 제거
두 개 이상의 테이블을 조인하는 경우 하나는 master테이블의 기본키고 나머지는 slave테이블의 참조키이다. 따라서 공통 컬럼을 사용하는 경우에는 어느 테이블의 컬럼인지 소속이 불분명할 경우가 있으며 이 문제를 해결하기 위해 반드시 앞에 테이블명을 지정해야 된다.
SELECT ENAME, deptno -- deptno가 공통 컬럼인데 어떤 테이블의 컬럼인지 명시하지 않았다.
FROM EMP E, DEPT D WHERE e.deptno = d.deptno; -- 테이블 alias 사용 가능
--수정
SELECT ENAME, D.deptno -- deptno가 공통 컬럼인데 어떤 테이블의 컬럼인지 명시하지 않았다.
FROM EMP E, DEPT D WHERE e.deptno = d.deptno;- 검색 조건 추가
WHERE 뒤에 AND 연산자를 이용해 추가한다
SELECT e.empno 사번 , E.Ename 이름, D.deptno 부서번호, D.Dname 부서이름
FROM EMP E, dept D WHERE e.deptno = d.deptno AND ENAME = 'SMITH';- 81년도에 입사한 사원이름, 입사일, 부서번호, 부서명
SELECT e.ename 사원이름, e.hiredate 입사일, d.deptno 부서번호, d.dname 부서명 FROM EMP E, DEPT D WHERE e.deptno = d.deptno AND SUBSTR(e.hiredate, 1, 2) = '81' ORDER BY 2;- SALES부서에 근무하는 사원번호, 이름, 부서명
SELECT empno, ENAME, DNAME FROM EMP E, DEPT D WHERE e.deptno = d.deptno AND d.dname = 'SALES';- 부서별 부서명과 인원수
SELECT dname, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY DNAME;NON-EQUI JOIN
사원의 월급(salary)이 JOB_GRADES 테이블의 어떤 lowest_sal 컬럼값과 highest_sal 컬
럼값 사이에 포함되는지를 확인하기 위하여 BETWEEN a AND b 연산자를 사용한다. 이때
동등 연산자 이외의 연산자를 사용했기 때문에 Non-Equi 조인이라고 한다
- 기본 조건
SELECT ENAME 이름, SAL 월급, GRADE 월급등급, HISAL, LOSAL, DEPTNO 부서번호 FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;- 기본 조건 + 이름이 SMITH 출력
SELECT ENAME 이름, SAL 월급, GRADE 월급등급 FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND ENAME = 'SMITH';- 기본조건 + GRADE가 2 출력
SELECT ENAME 이름, SAL 월급, GRADE 월급등급, EMPNO 부서번호 FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND GRADE = 2;3개의 테이블 JOIN
: 3개의 테이블을 조인하기 위하여 FROM절에 테이블명을 지정하고 WHERE 절에는 employees 테이블과 departments 테이블에 대해서 Equi 조인을 하고 employees 테이블과 JOB_GRADES 테이블은 Non-Equi 조인으로 처리한다.
SELECT ENAME, SAL, GRADE, DNAME
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
- DALLAS에 근무하는 사원의 이름과 월급, 등급
SELECT ENAME 이름, SAL 월급, GRADE 등급
FROM EMP E, DEPT D, SALGRADE S
WHERE e.deptno = d.deptno AND E.SAL BETWEEN S.LOSAL AND S.HISAL AND d.loc = 'DALLAS';SELF JOIN
: 필요에 의해서 하나의 테이블만 사용하여 자기 자신을 조인하는 것을 SELF JOIN이라고 한다.
SELECT A.ENAME 사원, B.ENAME 관리자
FROM EMP A, EMP B
WHERE A.MGR = B.EMPNO;- 관리자의 관리자까지 출력
SELECT A.ENAME, B.ENAME, C.ENAME FROM EMP A, EMP B, EMP C WHERE A.MGR = B.EMPNO AND B.MGR = C.EMPNO;
