728x90
반응형
/*
select 컬러명 || * from 테이블명 => 필수. dual 더미테이블을 이용해서 문자 한개 조회가능
[where 조건문] => 레코드 선택의 기준
[group by 컬럼] => 컬럼을 기준으로 그룹화
[having 조건문] => 그룹함수의 조건문.
[order by 컬럼명||별명||컬럼순서 DESC, ASC ]
*/
SELECT SYSDATE
FROM DUAL
;
/*
함수 정리
1. 문자함수
initcap : 첫문자는 대문자. 나머지는 소문자로.
upper : 모든 문자를 대문자로 변환
lower : 모든 문자를 소문자로 변환
length : 문자열의 길이
lengthb : 문자열을 이루는 바이트 수
substr : 부분문자열.
substr(컬럼|문자열,시작인덱스(1부터시작),[문자의 갯수])
instr : 주어진 문자의 위치 리턴
instr('문자열'||컬럼명,'문자열',[시작인덱스],[번째])
lpad, rpad : 왼쪽/오른쪽 문자 추가
lpad(문자열||컬럼명, 전체길이, 채울문자)
ltrim, rtrim : 왼쪽/오른쪽 문자(공백) 제거.
ltrim(문자열||컬럼명,['제거할 문자'])
trim : trim(문자열||컬럼명) : 양쪽의 공백 제거
2. 숫자 관련 함수
round : 반올림 함수
round(숫자||컬럼,숫자) : 0인경우 정수로 표시
1 인경우 소숫점이하 한자리만 표시
-1 인경우 10자리까지만 표시
trunc : 버림함수
ceil : 근사정수. 숫자보다큰 근사정수
ceil(숫자||컬럼) :
floor : 근사정수. 숫자보다 작은 근사정수
mod : 나머지 함수. mod(숫자1,숫자2) 숫자1 에서 숫자2로 나눈 나머지
power : 제곱 power(3,3) : 3 * 3 * 3
abs : 절대값.
sign : 부호 리턴. 양수:1, 음수:-1, 영:0
3. 날짜 관련 함수
sysdate : 현재일시
months_between : 두날짜 사이의 개월수
round : 날짜관련 반올림.
trunc : 날짜관련 버림
last_day : last_day(날짜). 날짜의 월의 마지막 날자 리턴
4. 형변환함수
to_char : 날짜=>문자열, 숫자=>문자열 패턴: 날짜인경우=>yyyy,mm,dd,hh .......
숫자인경우=> 9, 0, ,(천단위콤마), .(소숫점표시)
to_number : 숫자형문자 => 숫자.
to_date : 문자열=>날짜 패턴: 날짜인경우=>yyyy,mm,dd,hh .......
5. 일반함수
nvl : nvl(컬럼,기본값) : 컬럼의 값이 null인 경우는 기본값으로 치환.
6. 그룹함수
count : 레코드의 건수 리턴, null 값은 제회
count(컬럼명) : 해당 컬럼의 값이 null아닌 갯수
count(*) : 전체 레코드의 갯수
*/
-- sum() : 합계
-- 교수들의 전체 급여 합계 출력하기
SELECT SUM(PAY)
FROM PROFESSOR
;
-- 사원들의 전체 급여 합계 출력하기
SELECT SUM(SAL)
FROM EMP
;
-- 교수들의 부서별 급여 합계 출력하기
SELECT DEPTNO, SUM(PAY)
FROM PROFESSOR
GROUP BY DEPTNO
;
-- AVG() : 평균
-- AVG(컬럼) : NULL값은 제외됨.
/*
100,NULL,50,50 => 200 / 4 = 50 (평균값)
100,50,50 => 200 / 3 = 66.6666 (평균값)
*/
-- 교수들의 부서별 인원수, 급여 합계, 평균 출력하기
SELECT DEPTNO, COUNT(*), SUM(PAY), AVG(PAY)
FROM PROFESSOR
GROUP BY DEPTNO
;
-- 교수들의 부서별 인원수, 보너스 합계, 평균 출력하기
SELECT DEPTNO, COUNT(*), SUM(BONUS), AVG(BONUS), AVG(NVL(BONUS,0))
FROM PROFESSOR
GROUP BY DEPTNO
;
-- 문제 1. 학생의 학년별 키와 몸무게의 평균 출력하기.
-- 학년별로 정렬하기.
SELECT GRADE, AVG(HEIGHT), AVG(WEIGHT)
FROM STUDENT
GROUP BY GRADE
ORDER BY GRADE
;
-- MAX(), MIN() : 최대값, 최소값
-- 학생 중 키가 가장 큰 학생의 키, 키가 가장 작은 학생의 키, 평균키 출력하기
SELECT MAX(HEIGHT), MIN(HEIGHT), AVG(HEIGHT)
FROM STUDENT
;
-- HAVING : 그룹함수의 조건문
-- 학년별 키가 가장 큰 학생의 키, 키가 가장 작은 학생의 키, 평균키 출력하기
-- 단, 평균키가 170 이상인 학년만 출력하기
SELECT MAX(HEIGHT), MIN(HEIGHT), AVG(HEIGHT)
FROM STUDENT
GROUP BY GRADE
HAVING AVG(HEIGHT) >= 170
;
-- 문제 2 : 교수의 학과별 평균 급여가 400이상인 부서의 부서코드와 평균급여, 급여합계를 출력하기
-- 평균급여는 소숫점이하 2자리로, 평균급여와 급여합계는 천단위로 출력하기
SELECT DEPTNO, TO_CHAR(AVG(PAY),'9,999.99'), TO_CHAR(SUM(PAY),'999,999')
FROM PROFESSOR
GROUP BY DEPTNO
HAVING AVG(PAY) >= 400
;
-- 문제 3 : 주민번호를 기준으로 남학생과 여학생의 최대키, 최소키, 평균키를 출력하기
-- 주민번호의 7번째 자리가 1:남학생, 2:여학생
SELECT SUBSTR(JUMIN,7,1), MAX(HEIGHT), MIN(HEIGHT), ROUND(AVG(HEIGHT),2)
FROM STUDENT
GROUP BY SUBSTR(JUMIN,7,1)
;
-- 표준편차 : STDDEV()
-- 분산 : VARIANCE()
-- 교수중 가장 많은 급여와 가장 적은 급여, 평균급여, 급여의 표준편차, 급여의 분산 값 출력하기
SELECT MAX(PAY), MIN(PAY), AVG(PAY), STDDEV(PAY), VARIANCE(PAY)
FROM PROFESSOR
;
-- 학생의 생일을 기준으로 월별 태어난 인원수를 출력하기
SELECT TO_CHAR(BIRTHDAY,'MM') || '월' 월, COUNT(*)
FROM STUDENT
GROUP BY TO_CHAR(BIRTHDAY,'MM')
ORDER BY 월
;
-----------------------------------------------------------------------------
-- JOIN : 두개의 테이블을 연결하는 기능
-- 카티션곱, CROSS JOIN :
-- 두개 테이블의 레코드의 곱만 레코드 건수가 발생 됨.
SELECT * FROM EMP, DEPT --14*4=56
;
-- 두개 테이블에 같은 이름의 컬럼은 테이블 이름(별명)을 붙여 주어야 한다.
-- 같은 이름이 아닌 컬럼(유일한 컬럼)은 테이블 이름을 생략해도 됨.
SELECT ENAME, EMP.DEPTNO, DEPT.DEPTNO
FROM EMP, DEPT
;
SELECT ENAME, EMP.DEPTNO E, DEPT.DEPTNO D
FROM EMP, DEPT
;
-- 오라클 방식
SELECT ENAME, E.DEPTNO, D.DEPTNO
FROM EMP E, DEPT D
;
-- ANSI 방식
SELECT ENAME, E.DEPTNO, D.DEPTNO
FROM EMP E CROSS JOIN DEPT D
;
-- 등가조인 (EQUI JOIN) : 두개의 테이블을 연결해 주는 컬럼의 값이 같은 경우로 레코드를 선택함.
SELECT *
FROM STUDENT
;
SELECT *
FROM EXAM_01
;
-- 오라클 방식
SELECT S.STUDNO, S.NAME, E.STUDNO ,E.TOTAL
FROM STUDENT S, EXAM_01 E
WHERE S.STUDNO = E.STUDNO -- JOIN 컬럼
;
-- ANSI 방식
SELECT S.STUDNO, S.NAME, E.STUDNO ,E.TOTAL
FROM STUDENT S JOIN EXAM_01 E
ON S.STUDNO = E.STUDNO -- JOIN 컬럼
;
-- 문제 4 : 학생테이블과 학과(DEPARTMENT) 테이블을 사용하여 학생이름, 전공학과1코드, 전공학과 이름을 출력하기
-- 오라클 방식
SELECT S.NAME, S.DEPTNO1, D.DNAME
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
;
-- ANSI 방식
SELECT S.NAME, S.DEPTNO1, D.DNAME
FROM STUDENT S JOIN DEPARTMENT D
ON S.DEPTNO1 = D.DEPTNO
;
-- 문제 5 : 학생테이블과 교수테이블을 사용하여 학생이름, 학년, 지도교수번호, 지도교수이름을 출력하기
-- 오라클 방식
SELECT S.NAME, S.GRADE, S.PROFNO, P.NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO
;
-- ANSI 방식
SELECT S.NAME, S.GRADE, S.PROFNO, P.NAME
FROM STUDENT S JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
;
-- 학생의 이름, 학과이름, 지도교수이름 출력하기
-- STUDENT, DEPARTMENT, PROFESSOR 테이블의 조인이 필요함
-- 오라클 방식
SELECT S.NAME, D.DNAME, P.NAME
FROM STUDENT S, DEPARTMENT D, PROFESSOR P
WHERE S.DEPTNO1 = D.DEPTNO
AND S.PROFNO = P.PROFNO
;
-- ANSI 방식
SELECT S.NAME, D.DNAME, P.NAME
FROM STUDENT S JOIN DEPARTMENT D
ON S.DEPTNO1 = D.DEPTNO
JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
;
-- 홍길동 학생의 학번, 이름, 학과번호, 학과이름, 학과위치(DEPARTMENT.BUILD) 출력하기
-- 오라클 방식
SELECT S.STUDNO, S.NAME, S.DEPTNO1,D.DNAME, D.BUILD
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = D.DEPTNO
AND S.NAME = '홍길동'
;
-- ANSI 방식
SELECT S.STUDNO, S.NAME, S.DEPTNO1,D.DNAME, D.BUILD
FROM STUDENT S JOIN DEPARTMENT D
ON S.DEPTNO1 = D.DEPTNO
WHERE S.NAME = '홍길동'
;
-- 문제 6 : 학과코드1이 101번인 학생들의 학생이름, 학과명, 지도교수이름을 출력하기
-- 학생이름순으로 정렬하기
-- 오라클 방식
SELECT S.NAME, D.DNAME, P.NAME
FROM STUDENT S, DEPARTMENT D, PROFESSOR P
WHERE S.DEPTNO1 = D.DEPTNO --STUDENT, DEPARTMENT 테이블의 조인 컬럼
AND S.PROFNO = P.PROFNO --STUDENT, PROFESSOR 테이블의 조인 컬럼
AND S.DEPTNO1 = 101
ORDER BY S.NAME
;
-- ANSI 방식
SELECT S.NAME, D.DNAME, P.NAME
FROM STUDENT S JOIN DEPARTMENT D
ON S.DEPTNO1 = D.DEPTNO JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
WHERE S.DEPTNO1 = 101
ORDER BY S.NAME
;
-- 비등가 조인 (NON EQUI JOIN)
-- 조인컬럼의 범위값을 지정해서 연결하는 방식
SELECT *
FROM HAKJUM
;
SELECT *
FROM EXAM_01
;
-- 오라클 방식
SELECT E.STUDNO, E.TOTAL, H.GRADE
FROM EXAM_01 E, HAKJUM H
WHERE E.TOTAL BETWEEN H.MIN_POINT AND H.MAX_POINT
;
-- ANSI 방식
SELECT E.STUDNO, E.TOTAL, H.GRADE
FROM EXAM_01 E JOIN HAKJUM H
ON E.TOTAL BETWEEN H.MIN_POINT AND H.MAX_POINT
;
SELECT *
FROM GOGAK
;
SELECT *
FROM GIFT
;
-- 문제 7 : 고객테이블과 상품테이블에서 고객의 포인트로 받을 수 있는 상품을 출력하기
-- 고객이름, 포인트, 상품명
-- 오라클 방식
SELECT G1.GNAME, G1.POINT, G2.GNAME
FROM GOGAK G1, GIFT G2
WHERE G1.POINT BETWEEN G2.G_START AND G2.G_END
;
-- ANSI 방식
SELECT G1.GNAME, G1.POINT, G2.GNAME
FROM GOGAK G1 JOIN GIFT G2
ON G1.POINT BETWEEN G2.G_START AND G2.G_END
;
-- 고객테이블과 상품테이블을 조인하여 고객의 포인트별 받을 수 있는 상품의 이름과 필요수량을 출력하기
-- 단, 수량이 많은 순으로 정렬
SELECT G2.GNAME, COUNT(*)
FROM GOGAK G1, GIFT G2
WHERE G1.POINT BETWEEN G2.G_START AND G2.G_END
GROUP BY G2.GNAME
ORDER BY 2 DESC
;
SELECT G1.GNAME 고객명, G2.GNAME 상품명
FROM GOGAK G1, GIFT G2
WHERE G1.POINT BETWEEN G2.G_START AND G2.G_END
;
SELECT *
FROM EMP2
;
SELECT *
FROM P_GRADE
;
-- EMP2 테이블과 P_GRADE 테이블을 이용하여 사원이름, 현재직급(POSITION), 급여기준 예상 직급 출력하기
SELECT E.NAME, E.POSITION 현재직급, P.POSITION 예상직급
FROM EMP2 E, P_GRADE P
WHERE E.PAY BETWEEN P.S_PAY AND P.E_PAY
;
-------------------------------------------------------------------------------
-- OUTER JOIN : 조인 조건을 만족하지 않아도 조회가 됨
-- INNER JOIN : 조인 조건을 만족하지 않으면 조회되지 않음
/*
1. LEFT OUTER JOIN : 왼쪽 테이블의 모든 정보를 조회
2. RIGHT OUTER JOIN : 오른쪽 테이블의 모든 정보를 조회
3. FULL OUTER JOIN : 양쪽 테이블의 모든 정보를 조회
오라클 방식으로는 안됨
*/
-- 학생의 이름과 지도교수 이름을 출력하기
-- 지도교수가 있는 학생들만 조회 됨
SELECT S.NAME, P.NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO
;
-- 학생의 이름과 지도교수 이름을 출력하기. 학생의 정보는 모두 조회되도록 한다.
-- 지도교수가 없는 학생들도 조회 됨
-- 오라클 방식 : LEFT OUTER JOIN
SELECT S.NAME, P.NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+)
;
-- ANSI 방식 : LEFT OUTER JOIN
SELECT S.NAME, P.NAME
FROM STUDENT S LEFT OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
;
-- 학생의 이름과 지도교수 이름을 출력하기. 교수 정보는 모두 조회되도록 한다.
-- 지도학생이 없는 교수들도 조회 됨
-- 오라클 방식 : RIGHT OUTER JOIN
SELECT S.NAME, P.NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO(+) = P.PROFNO
;
-- ANSI 방식 : RIGHT OUTER JOIN
SELECT S.NAME, P.NAME
FROM STUDENT S RIGHT OUTER JOIN PROFESSOR P
ON S.PROFNO(+) = P.PROFNO
;
-- 학생의 이름과 지도교수 이름을 출력하기. 교수, 학생의 정보는 모두 조회되도록 한다.
-- 지도교수가 없는 학생, 지도학생이 없는 교수도 조회 됨
-- 오라클 방식 : FULL OUTER JOIN
SELECT S.NAME, P.NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO = P.PROFNO(+)
UNION
SELECT S.NAME, P.NAME
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO(+)= P.PROFNO
;
-- ANSI 방식 : FULL OUTER JOIN
SELECT S.NAME, P.NAME
FROM STUDENT S FULL OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO
;
---- SELF JOIN ----
-- 같은 테이블 내의 두개의 컬럼으로 조인을 하는 기능
-- 테이블에 별명(ALIAS)설정이 필수.
-- 별명.컬럼명으로 조회해야함
SELECT *
FROM DEPT2
;
-- DEPT2 테이블에서 부서코드, 부서명, 상위부서명을 출력하기
SELECT D1.DCODE 부서코드, D1.DNAME 부서, D2.DNAME 상위부서명
FROM DEPT2 D1, DEPT2 D2
WHERE D1.PDEPT = D2.DCODE
;
SELECT D1.DCODE 부서코드, D1.DNAME 부서, D2.DNAME 상위부서명
FROM DEPT2 D1, DEPT2 D2
WHERE D1.PDEPT = D2.DCODE
;728x90
반응형