1일차
--select * FROM TEAM_T;
SELECT * FROM PLAYER_T;
--SELECT team_name, tel, translate(tel,'0123456789','영일이삼사오육칠팔구') FROM TEAM_T;
--SELECT player_name, e_player_name FROM PLAYER_T WHERE e_player_name LIKE '%e%e%e%' AND e_player_name NOT LIKE '%e%e%e%e%';
--SELECT PLAYER_NAME, E_PLAYER_NAME FROM PLAYER_T WHERE INSTR(UPPER(e_player_name),'E',1,3)>0 AND INSTR(UPPER(e_player_name),'E',1,4)=0;
/* 130521(월)
--SELECT ename, LOWER(ENAME) FROM EMP;
--SELECT ename, INITCAP(ENAME) FROM EMP;
--SELECT ename, LENGTH(ENAME) FROM EMP;
--SELECT * FROM PLAYER_T;
--SELECT ename, SUBSTR(EMPNO,2) FROM EMP;
--SELECT ename, Rtrim(ename,'T') FROM EMP;
--SELECT ENAME, TRIM('T' FROM ENAME) FROM EMP;
--SELECT ENAME, LPAD(ENAME,10,'0') FROM EMP;
--SELECT ENAME, RPAD(ENAME,10,'*') FROM EMP;
--SELECT ENAME, REPLACE(ENAME, 'MI','*?') FROM EMP;
--SELECT ENAME, TRANSLATE(ENAME,'MI','*?') FROM EMP;
--SELECT ENAME, INSTR(ENAME, 'T', 1,2) 결과1 FROM EMP;
--SELECT PLAYER_NAME FROM PLAYER_T WHERE LENGTH(PLAYER_NAME)!=3;
--SELECT SUBSTR(PLAYER_NAME,1,1) || '-' || SUBSTR(PLAYER_NAME,2) FROM PLAYER_T;
--SELECT ENAME, SAL FROM EMP ORDER BY ENAME ASC;
--SELECT ENAME, SAL FROM EMP ORDER BY ENAME DESC;
--SELECT ENAME 이름, SAL*12 NSAL, (SAL*12)+500 결과 FROM EMP WHERE (SAL*12) >20000 ORDER BY 1;
--SELECT ENAME, COMM FROM EMP ORDER BY COMM DESC;
--SELECT ENAME, SAL, COMM, COMM+700 FROM EMP;
--SELECT ENAME, SAL, COMM FROM EMP WHERE COMM IS NULL;
--SELECT * FROM EMP;
SELECT PLAYER_NAME || '--' || HEIGHT FROM PLAYER_T WHERE HEIGHT>=190;
SELECT E_PLAYER_NAME, HEIGHT FROM PLAYER_T WHERE PLAYER_NAME = '김병지' OR PLAYER_NAME = '고종수' OR PLAYER_NAME='가비';
SELECT PLAYER_NAME, POSITION FROM PLAYER_T WHERE POSITION LIKE '__' AND POSITION LIKE 'T%';
SELECT PLAYER_NAME, TEAM_ID, HEIGHT, POSITION FROM PLAYER_T WHERE TEAM_ID='K02' AND HEIGHT>189;
SELECT PLAYER_NAME, TEAM_ID, HEIGHT, POSITION FROM PLAYER_T WHERE TEAM_ID='K02' AND (HEIGHT>189 OR POSITION='GK');
SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE '%A%A%';
SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE '%A%' AND ENAME NOT LIKE '%A%A%';
--SELECT * FROM PLAYER_T;
--SELECT DISTINCT TEAM_ID FROM PLAYER_T;
--SELECT ENAME, SAL FROM EMP WHERE 3-3=0;
--SELECT * FROM EMP;
--SELECT ENAME, SAL FROM EMP WHERE JOB='CLERK';
--SELECT * FROM PLAYER_T;
--SELECT PLAYER_NAME, HEIGHT FROM PLAYER_T WHERE TEAM_ID='K04';
--SELECT PLAYER_NAME || '.' || HEIGHT || ',' || TEAM_ID FROM PLAYER_T WHERE HEIGHT>=188 AND TEAM_ID='K04';
--SELECT PLAYER_NAME, HEIGHT FROM PLAYER_T WHERE HEIGHT=180 OR HEIGHT=190 OR HEIGHT=170;
--SELECT PLAYER_NAME, HEIGHT FROM PLAYER_T WHERE HEIGHT=ANY(170,180,190);
--SELECT PLAYER_NAME, HEIGHT FROM PLAYER_T WHERE HEIGHT IN (170,180,190);
--SELECT PLAYER_NAME, HEIGHT FROM PLAYER_T WHERE HEIGHT>=190 AND HEIGHT<=195;
--SELECT PLAYER_NAME, HEIGHT FROM PLAYER_T WHERE HEIGHT BETWEEN 190 AND 195;
--SELECT PLAYER_NAME FROM PLAYER_T WHERE PLAYER_NAME LIKE '가___';
--SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE '%A%';
--SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE '_A%';
--SELECT PLAYER_NAME FROM PLAYER_T WHERE PLAYER_NAME LIKE '%_%%' ESCAPE '_';
--SELECT ENAME FROM EMP;
--SELECT * FROM EMP;
--SELECT ename FROM EMP;
--SELECT EMPNO,ENAME,SAL, SAL+500 FROM EMP;
--SELECT 'ENAME' FROM EMP;
--SELECT EMPNO, SAL/2, 'SAL/2' FROM EMP;
--SELECT ENAME 이름, SAL 급여, SAL*12+700 연봉 FROM EMP;
--SELECT ENAME 이름, SAL 급여, SAL*12+700 "연봉" FROM EMP;
--SELECT ENAME 이름, SAL 급여, SAL*12+700 "연봉Result" FROM EMP;
--SELECT ENAME 이름, SAL 급여, SAL*12+700 "NEW Result" FROM EMP;
--SELECT ENAME 이름, SAL 급여, SAL*12+700 "77SAL?" FROM EMP;
--SELECT ename, 'abc', sal, 500 FROM EMP;
--SELECT ENAME, JOB FROM EMP;
--SELECT ENAME || JOB AS "ADD" FROM EMP;
--SELECT * FROM PLAYER_T;
--SELECT PLAYER_NAME|| '(' ||PLAYER_ID || ')' AS 결과 FROM PLAYER_T;
--SELECT DISTINCT DEPTNO FROM EMP;
--SELECT DISTINCT DEPTNO, JOB FROM EMP;
*/
2일차
/* 13. 05. 21(화) */
/* DB 속도 측면의 문제
Select ename, sal, deptno From emp Where ename like 'A%' ename like '%A';
- DB는 인덱스를 사용하여 데이터 처리를 하기 때문에, 속도 측면에서 자주 쓰던 인덱스로 지정된 변수에 계산식을 추가한다든지의 작업은 속도 저하를 가져옴
- A% 와 같은 경우는 인덱스로 처리될 수 있는 반면에 %A 와 같은 경우는 인덱스 사용이 불가능하기 때문에 Insrt 함수를 사용하는 것이 현명함
*/
/* << 숫자함수 >> */
/*
Select 12*76 FROM dual;
단순 계산식이 한 번만 출력됨
Select 12*76 From EMP;
해보면 데이터가 계속 반복되고 있음
Select * from dual;
해보면 컬럼 하나만 들어가 있음
*/
/*
1. 반올림함수 Round( 값 , 자리수)
2. 버림함수 Trunc( 값, 자리수)
100을 33으로 나눈 값 / 몫 / 나머지 구하기
SELECT 100/33, TRUNC(100/33,0), 100/33-TRUNC(100/33,0) FROM dual;
3. 근데, 그냥 나머지 구하는 함수 쓰면 됨 Mod( 피제수 , 제수 )
4. 올림, 내림하는 함수 Ceil( 값 ) Floor( 값 )
5. 부호 알려주는 함수 Sign( 값 )
*/
/* << 날짜 데이타 >> */
/*
컬럼정보
- 거래일자 : CHAR(8) : YYYYMMDD, 시분초 저장 안함
- 주문일자 : date : 시분초 포함
- 실험일자 : timestamp : 시분초 포함 + 초 뒤에 9자리까지 표시 가능(정밀한 비교)
Alter Session Set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
오라클 DB에서 출력데는 날짜 데이타 형식 바꾸는 방법(함수가 없어 명령어 이렇게 써야 함)
SQLGate는 보기-옵션-쪽에서 바꿀 수 있음
*/
/*
1. 날짜 + 정수값
Select sysdate, sysdate+5 from dual;
- 오늘 날짜에 5일을 더한 결과가 나옴 (Day + 5)
Select sysdate, sysdate+1/24/2 From dual;
- 현재 시간에 30분 더하기
주 단위로 더하기 : 날짜 * 주
2. n개월 더하기
SELECT SYSDATE, ADD_MONTHS(SYSDATE,6) FROM dual;
- 오늘 날짜 기준으로 6개월 후는 언제인지 조회(윤년 등 다 고려 된 함수)
연 단위로 더하기 : 개월 * 년
3. 날짜 - 날짜 계산(두 날짜 사이의 경과 일수)
SELECT ename, SYSDATE - hiredate FROM EMP;
4. 두 날짜 사이 경과 개월 수
Months_Between( 날짜1, 날짜2 )
SELECT ename, months_between(SYSDATE, hiredate) FROM EMP;
5. 해당 월의 마지막 일자 조회 시
Select Last_Day(sysdate) from emp;
포지션이 GK인 선수의 명단과, 그 선수의 70번째 생일 구하기
SELECT player_name, birth, ADD_MONTHS(birth,12*70) AS "70번째생일" FROM PLAYER_T WHERE Position = 'GK';
선수의 나이 / 살아온 날을 년+개월로 표기하기
SELECT player_name, birth, TRUNC(MONTHS_BETWEEN(sysdate,birth)/12,0)||'세', TRUNC(MONTHS_BETWEEN(sysdate,birth)/12,0)||'년' || LPAD(TRUNC(MOD( MONTHS_BETWEEN(SYSDATE,birth),12),0),2,' ') ||'개월' FROM PLAYER_T;
*/
/*
SELECT player_name, height FROM PLAYER_T WHERE player_id = 1997051;
SELECT player_name, height FROM PLAYER_T WHERE player_id = '1997051';
결과는 같음
- player_id 는 CHAR(7) 형식인데, 1번은 숫자로 2번은 문자열로 검색
- 속도 면에서 당연히 2번 문자열로 찾을 때 훨씬 빠름.
- 숫자형식으로 찾으면 변환 과정도 필요(암시적 변환)하고, 인덱스로 등록된 것도 사용하지 못함.
암시적변환 : DB내에서 자동으로 타입을 바꿔 계산을 한 것
SELECT '007'*3 FROM dual;
문자열 상태에서 곱하기는 못하니까 자동으로 수로 바꿔서 계산하게 됨
*/
/*
날짜(DATE) → 문자(CHAR) → 숫자(number)
← ←
날짜→문자 : TO_CHAR
문자→날짜 : TO_DATE
문자→숫자 : TO_NUMBER
숫자→문자 : TO_CHAR
1. 날짜→문자
- 날짜 표시형태 셋팅 변경없이 날짜 정보 중, 일부 정보만 사용하고 싶을 때
- TO_CHAR(날짜,'사용할정보')
SELECT SYSDATE, TO_CHAR(SYSDATE,'DAY');
'D' : 해당 요일의 번째
'DD' : 날짜
'DDD' : 1년 중 몇 번째 날인지
'SSSSS' 등등 책 89쪽~
2. 숫자→문자 : TO_CHAR
SELECT 12345, TO_CHAR(12345,'000,000') FROM DUAL;
빈 공간은 0으로 채워서 딱 맞게 6자리 채움
SELECT 12345, TO_CHAR(12345,'999,999') FROM DUAL;
12345를 자리수 맞게 12,345 로 표기함
만약, 자리수 범위를 넘어서면 ######으로 표기 안됨
3. 문자→날짜 : TO_DATE
해당 문자가 어떤 날짜 형식에 속하는지 분류해줌(년도/월/일/시간/분/초 등등)
SELECT TO_DATE('1105','YYMM') FROM DUAL;
SELECT TO_DATE('1105','MMDD') FROM DUAL;
SELECT TO_DATE('1105','MISS') FROM DUAL;
사용자가 입력하지 않은 정보는 DEFAULT 기본값으로 들어감
년/월 : SYSDATE 값
일 : 01
시 : 00(오전12시)
분 : 00
초 : 00
SELECT TO_DATE('77','YY') FROM DUAL;
SELECT TO_DATE('77','RR') FROM DUAL;
50보다 큰 데이터는 1900년대로, 50보다 작은 데이터는 2000년대로 인식하는 기능이 'RR'
*/
/*
1. PLAYER_T 선수 중에서, 5월달에 태어난 선수이름, 출생년도 4자리만 조회
- SELECT PLAYER_NAME, 19||SUBSTR(TO_CHAR(BIRTH),8,2) FROM PLAYER_T WHERE SUBSTR(TO_CHAR(BIRTH),4,3)='MAY';
- SELECT PLAYER_NAME, TO_CHAR(BIRTH,'YYYY') FROM PLAYER_T WHERE RTRIM(TO_CHAR(BIRTH,'MONTH'))='may';
2. PLAYER_T 선수 중에서, 일요일에 태어난 선수 이름 조회
- SELECT PLAYER_NAME FROM PLAYER_T WHERE TO_CHAR(BIRTH,'day')='sunday ';
- SELECT PLAYER_NAME FROM PLAYER_T WHERE TO_CHAR(BIRTH,'day')='sunday ' OR TO_CHAR(BIRTH,'DY') = '일';
3. 1975.10.09 출생 선수 이름 조회
- SELECT PLAYER_NAME FROM PLAYER_T WHERE TO_CHAR(BIRTH,'YYYY-MM-DD')='1975-10-09';
- SELECT PLAYER_NAME FROM PLAYER_T WHERE TO_CHAR(BIRTH,'YYYYMMDD') = '19751009';
- SELECT PLAYER_NAME FROM PLAYER_T WHERE BIRTH = TO_DATE('1975-10-09','YYYY-MM-DD');
- SELECT PLAYER_NAME FROM PLAYER_T WHERE BIRTH='1975-10-09'; // 근데 이 방법은 안좋으니까 쓰지 말 것(될 때도 있고 안 될 때도 있고)
1번과 3번의 차이점은 뭘까요?
3번은 속도는 빠름, birth로 바로접근하니까. 근데 정확히 자정 시점까지 똑같은 애들만 조회 됨
반면에 1번은 하루라는 기간 동안에 해당되는 애들까지 대려옴
즉, 조회 건수가 다름
1번과 3번의 데이터는 확연하게 다르다는 것이다.
때문에, 가이드라인은 다음 아래처럼 조회 하는 것을 지시함
- SELECT PLAYER_NAME FROM PLAYER_T WHERE BIRTH >= TO_DATE('1975-10-09','YYYY-MM-DD') AND BIRTH < TO_DATE('1975-10-09','YYYY-MM-DD')+1;
이 방법은 속도도 상대적으로 빠를 뿐더러, 조회에 필요한 오차 발생을 없엘 수 있음
4. 이운재, 고종수, 가비 선수의 영문이름, 60번째 생일이 무슨 요일인지 조회
- SELECT E_PLAYER_NAME, TO_CHAR(ADD_MONTHS(TO_CHAR(BIRTH),12*60) , 'DAY') FROM PLAYER_T WHERE PLAYER_NAME='이운재' OR PLAYER_NAME='고종수' OR PLAYER_NAME='가비';
5. 2099년 12월 25일은 무슨요일인가?
SELECT TO_CHAR(TO_DATE(20991225,'YYYY-MM-DD'),'DY') FROM DUAL;
*/
/*
NULL 값을 다른 값으로
1. NVL ( 비교대상, NULL인 경우의 값 )
SELECT ename, comm, NVL(comm,-7) FROM EMP;
SELECT ename, sal, comm, NVL(sal+comm,0) 결과1, NVL(sal,0)+ NVL(Comm,0) 결과2 FROM EMP;
주의
- SELECT ename, comm, NVL(comm,'미정') FROM EMP;
- 그래서 이놈을 이렇게 바꿔서 사용한다
SELECT ename, comm, NVL(TO_CHAR(comm),'미정') FROM EMP;
NVL은 타입 확인해서, NVL 내의 두 변수 데이터형식이 같아아 처리해 줌
2. NVL2 ( 비교대상, NULL아닐 때 값, NULL일 때 값)
SELECT ename, comm, NVL2(comm, comm+700,100) FROM EMP;
*/
/*
Case When 조건식1 Then 처리1 When 조건식2 Then 처리2 Else 기타처리 END
- 처리에는 문자, 함수 등 모든 내용 들어갈 수 있음
이거랑 비슷한게 Decode함수
- Decode( 비교대상, 값1, 처리1, 값2, 처리2,.... , 기타처리 ) 한계가 있음.
- Decode 안에는 연산자가 들어갈 수 없음
SELECT ename, deptno, CASE WHEN deptno=20 THEN 'A팀' WHEN deptno=30 THEN 'B팀' ELSE 'C팀' END AS "팀정보" FROM EMP;
SELECT ename, deptno, DECODE(deptno, 20, 'A팀', 30, 'B팀', 'C팀') FROM EMP;
1. ename에 A에 있는 사원은 A팀, 없는 사원은 B팀으로
SELECT ename, CASE WHEN ename LIKE '%A%' THEN 'A팀' ELSE 'B팀' END FROM EMP;
SELECT ename, DECODE(INSTR(ename,'A',1,1) , 0,'B팀','A팀') FROM EMP;
- instr() 함수는 A가 있으면 값이 1 이상으로 되니까(A가 있는 위치 반환) 0이 나오면 없다는 뜻으로 B팀 나머지는 A팀
2. 사원 이름, 급여, 급여변경금액 조회 - 급여가 2700 이상이면 10%증가 미만은 20%증가하도록 출력
SELECT ename, sal, CASE WHEN sal>=2700 THEN sal+sal*0.1 ELSE sal+sal*0.2 END FROM EMP;
SELECT ename, sal, DECODE(sal/2700, 1,sal*1.1,2,sal*1.1,sal*1.2) FROM EMP;
- Decode 안에는 함수를 못쓰니까, 나누기를 해서 1보다 작고 크고로 분류하면 됨
SELECT ename, sal, DECODE(trunc(sal/2700), 0, sal*1.2, sal*1.1) FROM EMP;
SELECT ename, sal, DECODE(SIGN(sal-3000),-1,'A팀','B팀') AS 팀정보 FROM EMP;
- 몫이 아닌 SIGN을 통해서 분류하기도 함
*/
/* << 그룹함수 >> */
/*
1.
SELECT LOWER(ename) FROM EMP;
SELECT MIN(Ename) FROM EMP;
두 함수는 같은 내용이지만, 밑에 MIN(그룹함수)은 중복되는 것을 제외시킴
→ 그룹함수의 장점
2.
그룹함수는 계산시 null 값 제외함
SELECT COUNT(Ename), COUNT(mgr), COUNT(comm) FROM EMP;
SELECT SUM(NVL(comm,0)) AS 결과1 , SUM(comm) AS 결과2 FROM EMP;
- NVL(comm,0) 을 하면, 모든 데이터들을 한번씩 필터링 하게 됨
즉, 300 / NULL / 300 / 0 이라는 데이터가 있으면, 300->300, null->0 이렇게 다 바꾸는 작업이 들어감
- SUM(comm) 을 하면, 이건 그냥 바로 더하면서 가는 것.
- 결론적으로 SUM( VALUE ) 이것이 훨씬 빠름.
SELECT AVG(NVL(comm,0)) , AVG(comm) FROM EMP;
- 근데 평균을 구할 때는, NULL 값은 없는 걸로 생각을 해야 함
- NVL을 쓰면, NULL 포함해서 더해서 나눔
- AVG를 바로 쓰면, NULL 없이 더해서 나눔(없는 거 제외)
- 회사로 치면, 뒤에 방법은 지금까지 계약 완료한 애들의 평균 구하는 것이고, 앞에 것은 계약 안한 것은 0이라고 생각하고 하는 것(의미가 달라짐)
SELECT SUM(sal) FROM EMP;
SELECT SUM(sal) FROM EMP WHERE deptno=10;
데이터 처리시, FROM -> WHERE -> SELECT 순서로 작업처리가 이루어짐
위에 것은, 모든 애들의 SAL 급여 더한 것임
밑에 것은, 부서가 10번인 애들의 합계만 구한 것
3.
group 함수 계산순서는 WHERE 절 처리한 다음.
작은 묶음으로 나눌때, Group by 절 사용함
group by 절이 나온 컬럼만 select 절 표시 가능
WHERE -> group by 절 처리함
SELECT SUM(sal) FROM EMP WHERE deptno=20;
SELECT SUM(Sal) FROM EMP GROUP BY deptno;
- 위에 것은, 테이블에 있는 놈들 중 WHERE 만족하는 애들을 뽑아내어 값들을 하나의 덩어리에 집어넣고 뽑아서 출력하는 것
- 아래 것은, 덩어리를 각각 따로 만듬. 즉, deptno에 대한 덩어리를 각각 만들어서 결과를 다 담은 뒤, 해당되는 곳 출력하는 것
SELECT SUM(sal), deptno FROM EMP WHERE deptno=20;
- 그래서 위에 것에 deptno를 출력하려고 하면 안뜸
SELECT SUM(Sal), deptno FROM EMP GROUP BY deptno;
- 반면 밑에 것은, 각각 봉투를 다 따로 만들기 때문에 뜬다
*/
/*
1. player_t 선수중 키가 187 이상인 선수이름 조회
SELECT player_name FROM PLAYER_T WHERE height>=187;
2. 키 187 이상인 선수는 몇 명?
SELECT COUNT(player_name) FROM PLAYER_T WHERE height>=187;
3. 키 187 이상인 선수가 각 팀에 몇명씩 있는지 조회
SELECT COUNT(player_name) FROM PLAYER_T WHERE height>=187 GROUP BY team_id;
4. 키 187 이상인 선수를 5명 이상 보유한 팀 조회(team_id)
SELECT team_id FROM PLAYER_T WHERE height>=187 GROUP BY team_id HAVING COUNT(player_name)>=5;
→ 기준이 Team_id에서 나오기 때문에, WHERE 절에서 물어보면 안되 → Having 절에서 물어봐줘야 함
→ 중요한건 having 안에 들어오는 변수가 player_name 임을 중시
*/
3일차
/*13. 05. 22(수) */
/* 복습문제
1. player_t 테이블에서 각 팀 선수들의 평균키, 평균 몸무게 조회하기/ 팀 id 평균키 평균몸무게
SELECT team_id, AVG(height), AVG(weight) FROM PLAYER_T GROUP BY team_id;
2. 소속인원이 50명보다 많은 팀 id 조회
SELECT team_id AS 팀원50명이상, COUNT(Team_id)||'명' FROM PLAYER_T GROUP BY team_id HAVING COUNT(Team_id)>=50;;
3. 각 포지션 선수들의 평균키가 180이상인 포지션 평균키 조회
SELECT position, AVG(height) FROM PLAYER_T WHERE height>=180 GROUP BY position;
4. 이름 글자수ㅡ 해당인원수 조회
SELECT LENGTH(player_name) AS 이름길이, COUNT(player_id) AS 해당인원 FROM PLAYER_T GROUP BY LENGTH(player_name);
5. 생일이 등록된 선수들을 대상으로 월별 생일자수 조회 / 월 생일자수
SELECT TO_CHAR(birth,'mm')||'월 생일자', COUNT(birth)||'명' FROM PLAYER_T GROUP BY TO_CHAR(birth,'mm');
6. k02팀 선수들을 대상으로 각 연령대별 선수들이 몇명씩 있는지 조회 , 연령대/인원
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,birth)/12,-1) AS 연령대 , COUNT(*) AS 인원수 FROM PLAYER_T WHERE team_id='K02' GROUP BY TRUNC(MONTHS_BETWEEN(SYSDATE,birth)/12,-1) ;
** 년, 월, 일 DB등록된 날 기준으로 추출함수 EXTRACT
SELECT TO_CHAR(SYSDATE,'year'), extract(year FROM sysdate) FROM dual;
SELECT TO_CHAR(SYSDATE,'year'), extract(month FROM sysdate) FROM dual;
SELECT TO_CHAR(SYSDATE,'year'), extract(day FROM sysdate) FROM dual;
*/
/*
1. Rollup 함수
- group by를 통해 그룹지은 결과의 총 합을 얻고 싶을 때, group by 다음에 rollup 함수를 사용함
SELECT deptno, SUM(sal), COUNT(*) FROM EMP GROUP BY ROLLUP(deptno);
- Group By deptno + Group By null = ROLLUP()
SELECT deptno, job, SUM(sal) FROM EMP GROUP BY ROLLUP(deptno, job);
- Group by deptno, job + Group by deptno + Group by null = RULLUP()
ROLLUP 은 제거하는 순서가 오른쪽부터.
SELECT deptno, job, SUM(sal) FROM EMP GROUP BY ROLLUP(deptno, job);
SELECT deptno, job, SUM(sal) FROM EMP GROUP BY ROLLUP(job, deptno);
부서별로 보고 싶으면 부서를, 업무별로 보고 싶으면 업무별로 ROLLUP 순서 지정하면 됨
2. CUBE 함수
CUBE함수는 ROLLUP 함수에 하나가 더 들으감
Group by deptno, job + Group by deptno + Group by null + Group by job = CUBE()
→ GROUP BY ROLLUP( 지역, 연령대, 성별 )(2^2 = 4)
1. 지역, 연령대, 성별
2. 지역, 연령대
3. 지역
4. NULL(전체내용)
→ GROUP BY CUBE( 지역, 연령대, 성별 ) (2^3 = 8)
1. NULL(전체)
2. 성별
3. 연령대
4. 지역
5. 성별,연령대
6. 성별,지역
7. 연령대,지역
8. 성별,연령대,지역
3. GROUPINGSETS ( 성별, (지역,연령대) )
SELECT DEPTNO, JOB, SUM(sAL) FROM EMP GROUP BY GROUPING SETS(DEPTNO, JOB);
SELECT DEPTNO, JOB, SUM(sAL) FROM EMP GROUP BY GROUPING SETS(DEPTNO, JOB,NULL);
ROLLUP 을 쓰면, 제일 마지막 누적에서의 NULL 과 원 존재 데이터의 NULL 이 구분이 안됨
→ NVL 사용해서 대체하려고 하면 전체 NULL이 다 바껴서 못 씀
→ 그래서 나온 함수가 Grouping
SELECT DECODE(GROUPING(POSITION),1,'전체',NVL(POSITION,'미정')) 포지션, COUNT(*) 인원 FROM PLAYER_T GROUP BY ROLLUP(POSITION);
SELECT CASE WHEN GROUPING(POSITION)=1 THEN '전체' ELSE POSITION END 포지션, COUNT(*) 인원 FROM PLAYER_T GROUP BY ROLLUP(POSITION)
- 같은 내용
- NVL( ) 사용했을 경우, POSITION 내의 NULL 내용을 미정으로 바꿨음.
- ROULLUP 사용을 통해 가장 총 누계의 데이터를 전체와 같은 구분 할 수 있는 말로 바꾸기 위해서는 GROUPING 함수를 사용해야 함
- GROUPING 함수는, 원 데이터들의 내용은 0으로, ROLLUP을 통한 데이터는 1로 취급을 하기 때문에 CASE WHEN을 사용할 수 있음
*/
/* << JOIN >> */
/*
SELECT ename, dname FROM EMP E, DEPT D WHERE E.deptno=D.deptno ORDER BY ename;
- FROM에서 가져온 애들에 별칭을 달면, 그 뒤부턴 별칭으로 계속 사용해야 됨
1. EMP사원 중 급여 2000보다 많이 받는 사람의 이름,급여,소속부서명(dname) 조회
SELECT E.ENAME, E.SAL, D.DNAME FROM EMP E, DEPT D WHERE E.SAL>2000 AND E.DEPTNO=D.DEPTNO;
2. 부서명 sales 인 부서의 소속된 사원 이름, 부서번호, 급여조회
SELECT E.ENAME, D.DEPTNO, E.SAL , D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DNAME='SALES';
3. 부서번호가 15보다 큰 부서에 소속된 사원의 이름, 소속부서명, 근무도시 조회
SELECT E.ENAME, D.DNAME, D.LOC_CODE FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO>15;
*/
/*
1. OUTER JOIN
SELECT T.TEAM_name, S.STADIUM_NAME FROM TEAM_T T, STADIUM_T S WHERE T.STADIUM_ID =S.STADIUM_ID;
SELECT T.TEAM_name, S.STADIUM_NAME FROM TEAM_T T, STADIUM_T S WHERE T.STADIUM_ID(+) =S.STADIUM_ID;
- WHERE 조건에 만족하지 않는 애들도 모드 출력하게 하고 싶을때, (+) 를 집어넣는다.
- STADIUL(+) 를 했으므로, 경기장의 모든 내용이 출력됨
2. SELF JOIN
SELECT A.ENAME 사원이름, B.ENAME 사수이름 FROM EMP A, EMP B WHERE A.MGR=B.EMPNO;
- 같은 EMP 테이블 내의 자기참조로, 이름을 서로 분리해서 줌으로써 해깔리는 것 방지
emp dept salgrade locations 중 필요한 테이블사용.
1.사원명에 'S'가 들어 있는 사원의 이름,급여,급여GRADE조회
SELECT e.ename, e.sal, s.grade
FROM EMP e, SALGRADE s
WHERE e.sal BETWEEN s.LOSAL AND s.HISAL
AND INSTR(e.ename,'s',1,1)>0;
2. salgrade테이블grade가 4등급에 해당되는 사원의 이름,급여, 부서명(dname)조회
SELECT e.ename, e.sal, d.dname
FROM EMP e, DEPT d, SALGRADE s
WHERE E.DEPTNO = D.DEPTNO AND grade=4
AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
3. 본인의 관리자보다 먼저 입사한 사원들의 사원이름,입사일,관리자이름,관리자입사일자 조회.
(self 조인 사용해서 확인)
SELECT 사원.ename, 사원.hiredate, 관리자.ename, 관리자.hiredate
FROM EMP 사원, EMP 관리자
WHERE 사원.mgr = 관리자.empno
AND 사원.hiredate < 관리자.hiredate;
4. 급여를 1700 보다 많이 받는 사원의 이름, 급여, 부서명, 근무지(city), 급여등급(grade) 조회하기.
SELECT e.ename, e.sal, d.dname, l.city, s.grade
FROM emp e, dept d, locations l, salgrade s
WHERE e.deptno = d.deptno AND
d.loc_code = l.loc_code AND
e.sal between s.losal and s.hisal AND
e.sal > 1700;
5.
부서별 급여합계,소속인원수 조회
1) 부서번호 급여합계 인원
------- --------- -----
2) 부서명 급여합계 인원
----- --------- -----
-->소속사원이 없는 부서라도 모두 부서명 나오도록
..
SELECT dname, SUM(sal), COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPTNO(+)=DEPT.DEPTNO
GROUP BY dname;
- 이상태로 두면, 인원수가 0인 인사, 오퍼레이션에도 카운팅(*) 때문에 1로 된다.
- 그래서 Count(ename) 을 써야함
SELECT dname, SUM(sal), COUNT(ename)
FROM EMP, DEPT
WHERE EMP.DEPTNO(+)=DEPT.DEPTNO
GROUP BY dname;
*/
/* << SET 연산자 >> */
/*
SELECT deptno FROM DEPT INTERSECT SELECT deptno FROM EMP;
SELECT deptno FROM DEPT MINUS SELECT deptno FROM EMP;
SELECT deptno FROM DEPT UNION SELECT deptno FROM EMP;
SELECT deptno FROM DEPT UNION ALL SELECT deptno FROM EMP;
A 덩어리 - SET연산자 - B덩어리
→ A덩어리 → B덩어리 → SET연산자 → Order By
→ A덩어리 바로 뒤에 Order by 못 씀. 가장 마지막에 정렬함
→ A덩어리와 B 덩어리의 select 뽑아내는 컬럼 개수 / 데이터 타입이 일치해야 함(컬럼은 달라도 되지만 개수는 같아야 하고 타입 또한 일치해야 함)
*/
/*
** Rollup 없을 땐, 이렇게 해서 썻었음
SELECT deptno, SUM(sal) 합계1
FROM EMP
GROUP BY deptno
UNION ALL
SELECT NULL, SUM(sal) 합계2
FROM EMP
order by 합계2 desc;
Rollup 없을 땐, 이렇게 메모리 상에서 테이블 두 번 읽었어야 했음
*/
/*
1. emp에서 급여 4000 보다 많이 받는 사원 이름, 급여 조회
2. emp에서 ford 보다 급여 많이 받는 사원이름, 급여조회
SELECT * FROM EMP;
SELECT ename, sal FROM EMP WHERE sal>4000;
SELECT sal FROM EMP WHERE ename='FORD';
SELECT ename, sal FROM EMP WHERE sal > (SELECT sal FROM EMP WHERE ename='FORD');
*/
/*
1. player_t 선수 중에서 서동명선수와 포지션이 같은 선수이름, 팀id 조회하기
SELECT player_name, team_id FROM PLAYER_T WHERE position = (SELECT position FROM PLAYER_T WHERE player_name='서동명');
2. player_t 선수 중에서 GK 포지션 선수들의 평균키보다 키가 큰 선수들의 이름,키 조회
SELECT player_name, height FROM PLAYER_T WHERE height > ( SELECT AVG(height) FROM PLAYER_T WHERE position='GK');
3. player_t 선수 중 키가 제일 작은 선수가 누구인지, 이름,키,팀아이디조회
SELECT player_name, height, team_id FROM PLAYER_T WHERE height = (SELECT MIN(height) FROM PLAYER_T );
4. 박동우 선수와 포지션 같은 선수이름, 포지션 조회
SELECT player_name, position FROM PLAYER_T WHERE position in (SELECT position FROM PLAYER_T WHERE player_name='박동우');
5. 소속선수를 K08 팀 소속선수들보다 많이 보유한 팀아이디 조회
SELECT team_id, COUNT(*) FROM PLAYER_T GROUP BY team_id HAVING COUNT(team_id)>47
SELECT team_id, COUNT(*) FROM PLAYER_T group BY team_id HAVING COUNT(team_id) > (SELECT COUNT(*) FROM PLAYER_T WHERE team_id='k08');
*/
4일차
/* 130523(목)
SELECT : 단일행함수
FROM : 테이블명
WHERE : 조건식(그룹 나누기 전에 DATA 선별)
GROUP BY : WHERE절을 만족하는 데이터를 가지고 분류 작업을 함. 분류해서 처리하고 싶은 기준.
HAVING : 그룹 결과를 대상으로 확인
ORDER BY : 최종 결과를 우선순위를 통해 정렬
그룹함수 : MIN, MAX, AVG, SUM, COUNT.. 등은 SELECT, HAVING, ORDER BY에 슬 수 있음 (WHERE에 사용불가)
SELECT 지역, COUNT(*)
FROM 고객
WHERE 생일 > '1965010'
GROUP BY 지역
HAVING COUNT(*)>100
ORDER BY 2 DESC
→ HAVING COUNT(*)>100 은, 해당 지역에 100명이 넘는 지역만 끌고오는 것.
SELECT 지역, COUNT(*)
FROM 고객
WHERE 생일 > '1965010'
GROUP BY 지역
HAVING 지역 LIKE 'A%'
ORDER BY 2 DESC
→ 고객 생일기준으로 캐낸 자료에서 a로 시작하는 지역 봉투로 분류
SELECT 지역, COUNT(*)
FROM 고객
WHERE 생일 > '1965010'
GROUP BY 지역
HAVING 고객이름 LIKE '김%'
ORDER BY 2 DESC
→ 생일기준으로 뽑아서 봉투에 지역으로 넣어버림 → 이름 확인은 이제 불가능. 실행안됨
HAVING 절은, GROUP BY 절에 있는 컬럼 조건이나 그룹함수 조건만 사용 가능
*/
/* << ORACLE 조인문장 >>
1. FROM 절 : 사용하는 테이블명을 ,(콤마)로 구분하여 사용
2. WHERE 절 : 각 테이블 연결조건 (업무상 의미있는 DATA 들을 결합할 조건), AND 기타조건 등.
*/
/*
1. 30번 부서에 소속된 모든 사원보다 급여 많이 받는 사원이름,급여 조회
- SELECT ename, sal FROM EMP WHERE (SELECT MAX(sal) FROM EMP WHERE deptno=30) < sal;
- SELECT ename, sal FROM EMP WHERE ALL (SELECT sal FROM EMP WHERE deptno=30) < sal;
all 키워드를 써도 됨
2. 30번 부서 모든 사원보다 급여 적게 받는 사원이름, 급여조회
SELECT ename, sal FROM EMP WHERE (SELECT MIN(sal) FROM EMP WHERE deptno=30) > sal;
SELECT ename, sal FROM EMP WHERE all (SELECT sal FROM EMP WHERE deptno=30) > sal;
*/
/*
emp사원 중 10번부서에서~ 20번부서에서~ ~조회
SELECT ename, deptno, job
FROM EMP
WHERE (deptno=10 AND job='CLERK')
OR (deptno=20 AND job='MANAGEr')
OR (deptno=30 AND job='ABC');
SELECT ename, deptno, job
FROM EMP
WHERE deptno || job IN ( '10CLERK', '20MANAGER','30ABC');
결과는 똑같지만, 길이측면에서 밑에 것이 더 나아보임
- 근데, 이렇게 붙여버리면 인덱스를 사용할 수 없음
- 때문에 어떤 연산이나 검색시 속도측면에서 비효율적
- 길어도 위에 방법을 써야함
- 그럼 짧은 코딩과 속도를 모두 만족시키는 sql문장은?
SELECT ename, deptno, job
FROM EMP
WHERE (deptno,job) IN ( (10,'CLERK'),(20,'MANAGER'),(30,'ABC') );
- 이렇게 씁시당
*/
/* << 다중 열 서브쿼리 >>
Q. 각 팀에 키가 제일 작은 선수 조회 팀별로, 팀 아이디 이름 키 조회
SELECT player_name, team_id, height
FROM PLAYER_T
WHERE height IN
(SELECT MIN(height) FROM PLAYER_T GROUP BY team_id)
ORDER BY team_id;
이렇게 조회하면, 팀별로 가장 키 작은 170, 175 등이 조회되는데 이 값이 다른팀에 같은 키가 되면 뽑히는 문제 발생
SELECT player_name, team_id, height
FROM PLAYER_T
WHERE (height,team_id) IN
(SELECT MIN(height), team_id FROM PLAYER_T GROUP BY team_id)
ORDER BY team_id;
이렇게 만들면, (170, 'K01') 이런식으로 추출되니까 걸러짐
*/
/* << ROWID >>
SELECT ROWID, ename FROM EMP;
rowid → 실제 디스크상에 저장된 주소(위치) 정보 ( 포인터의 개념 )
방대한 분량의 디스크에서 실제 디스크 주소를 안다면, 특정 작업 수행시에 빠른 속도를 가져올 수 있음
인덱스가 활용하는 것이 rowid~
*/
/* << ROWNUM >>
SELECT ROWNUM, ename FROM EMP;
행 번호를 반환함(행 접근 순서대로 번호 부여)
SELECT ROWNUM, ename FROM EMP where rownum=1;
SELECT ROWNUM, ename FROM EMP where rownum>=1 AND ROWNUM<=5;
이렇게 건수 핸들링 처리를 할 수 있음(1~10번까지 보여줘 등등)
근데 이건 안됨
SELECT ROWNUM, ename FROM EMP where rownum>5;
은행 번호표처럼 처음부터 번호표 발급하는 것 처럼 같아서, 1번부터 뽑아내는 것만 가능함(skip은 불가능)
*/
/* << 인라인 뷰, From 서브쿼리절 >> -from절에 select 넣어서 처리하라는 것
동작은 테이블이 하나 만들어 지는 것 처럼 보이지만, 메모리상에서 만들어서 써먹고 버리는 것이니까 VIEW랑 유사함
Q. emp 테이블 사원 중 급여가 많은 순으로5명만 이름, 급여가 나오도록
이걸 하려면, 정렬해 놓은 다음 뽑아야 함.
SELECT ename, sal FROM EMP WHERE ROWNUM<6 ORDER BY sal desc; 이렇게 하면 안됨
결국, order by를 from 절로 갓다 놓아야 하기 때문에 from 절에 select를 먼저 씀
- SELECT * FROM (SELECT ename, sal FROM EMP ORDER BY sal desc) WHERE ROWNUM<6;
- SELECT ROWNUM, ENAME, SAL FROM (SELECT ename, sal FROM EMP ORDER BY sal desc) WHERE ROWNUM<6;
테이블 명을 가져오는 게 아니라, 테이블을 뽑아내서 특정 애들을 가져 오는 것.
PLAYER_T 이용, 각 팀의 평균키 얼마인지 조회하는데, 팀명 / 평균키를 보고 싶음
SELECT TEAM_ID, AVG(HEIGHT) FROM (SELECT TEAM_ID , HEIGHT FROM PLAYER_T) GROUP BY TEAM_ID;
속도 튜닝,
SELECT t.team_name, p.*
FROM (SELECT team_id, AVG(Height) FROM PLAYER_T GROUP BY team_id) p, TEAM_T t
WHERE p.team_id = t.team_id;
이렇게.
위에 것은, 조인시에 전부 다 조인하게 되지만 밑에 거는 다 추려놓고 조인하게됨
SELECT t.team_name, p.avg(height)
FROM (SELECT team_id, AVG(Height) FROM PLAYER_T GROUP BY team_id) p, TEAM_T t
WHERE p.team_id = t.team_id;
이건 실행이 안됨
p.avg(height) 가 함수처럼 생각해버려서 또 계산을 하려고함. 사용하려면 별칭사용해야함
Q. EMP에서 20번 부서 평균급여보다 많이 받는 사원이름, 급여, 부서번호 조회
SELECT ENAME, SAL, DEPTNO
FROM EMP, (SELECT AVG(SAL) T FROM EMP ) AA
WHERE SAL > AA.T
Q. EMP에서 본인 소속 부서의 평균급여보다 많이 받는 사원 이름, 그병, 부서번호 조회
SELECT ENAME, SAL, DEPTNO
FROM EMP a
WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO=A.DEPTNO);
where 절 안에, 변수처럼 넘겨줄 때는, from에서 별칭을지어주고 저렇게 합니당
근데, 각 건수마다 매번 평균 계산이 필요하기때문에 emp 테이블의 건수가 백만건이면 백만번의 계산이 필요함
값을 받아와서 건마다 다르게 실행할 수 있음(상호연관서브쿼리)
*/
/*
emp 사원의 이름, 본인소속부서명조회, 단 이름 큰 사원부터 나오도록 정렬
SELECT e.ename, D.DNAME FROM EMP e, DEPT d WHERE E.DEPTNO=D.DEPTNO ORDER BY E.EMPNO DESC;
select 다음에 바로, select가 나올 수 있음
다만, 서브쿼리 결과가 컬럼1개, row 1개인 경우에만 가능함
SELECT e.ename, (SELECT dname FROM DEPT WHERE deptno=e.deptno) 부서명 FROM EMP e ORDER BY e.ename DESC;
*/
/*
Q. 사원이름, 급여, 급여를 4000보다 많이 받는 인원수
SELECT ename, sal, (SELECT COUNT(*) FROM EMP WHERE sal>4000)
FROM EMP ;
Q. 사원이름, 급여, 자신의 연봉 순위
SELECT ename, sal, (SELECT COUNT(*) FROM EMP WHERE sal>e.sal)
FROM EMP e;
SELECT ename, sal, (SELECT COUNT(*) FROM EMP WHERE sal>e.sal)+1
FROM EMP e
ORDER BY 3;
*/
/*
1. emp 이용해서 사원이름, 본인의 관리자이름 나오도록 조회. 단 모든 사원이 다 나오도록 작성. 이름순으로정렬
방법1) emp 테이블 self 조인해서
SELECT 사원.empno, 사원.ename, 사원.mgr, 관리자.ename
FROM EMP 사원, EMP 관리자
WHERE 사원.mgr = 관리자.empno(+)
ORDER BY 사원.ename;
방법2) select 절에 본인관리자이름 가져오는 서브쿼리 사용
SELECT 사원.ename, (SELECT ename FROM EMP WHERE 사원.mgr=empno)
FROM EMP 사원
ORDER BY 사원.ename;
2. 모든 부서에 대해서 부서명, 소속인원이 몇 명 있는지 조회해서 나오도록 부서명 기준 정렬 수행
방법1) emp, dept 조인해서
SELECT d.dname, COUNT(*)
FROM EMP e, DEPT d
WHERE E.DEPTNO = D.DEPTNO
Group BY dname;
방법2) SELECT DNAME, ( 해당부서인원조회 ) FROM DEPT ;
SELECT dname, (SELECT COUNT(*) FROM EMP e WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO)
FROM DEPT d
ORDER BY deptno;
*/
/*
1. DEPT 테이블 정보 중 소속사원이 존재하는 부서의 부서번호, 부서명만 조회하기
SELECT deptno, dname
FROM DEPT
WHERE deptno IN (SELECT deptno FROM EMP )
SELECT d.deptno, d.dname
FROM DEPT d
WHERE deptno IN (SELECT deptno FROM EMP where deptno=D.DEPTNO );
SELECT d.deptno, d.dname
FROM DEPT d
WHERE exists (SELECT deptno FROM EMP where deptno=D.DEPTNO );
exists를 쓰면, 뒤에나오는 select에서 만족하는거 하나만 있으면 바로 반환
반대로 in 을 사용하면 select에서 만족한느 값을 모두 찾은 뒤 반환
때에 따라서 속도차이가 날 수 있으니 상황에 맞는 함수 사용
즉, 확인절차를 따질 때 exists를 사용함
2. 소속사원이 없는 부서번호, 부서명만 조회하기
SELECT d.deptno, d.dname
FROM DEPT d
WHERE not exists (SELECT deptno FROM EMP where deptno=D.DEPTNO );
*/
/* ANSI JOIN ( ORACLE )
FROM 절에 , (콤마) 로 테이블명 나열
WHERE 절에 조인조건 + 기타조건지정
ANSI JOIN문장
FROM 절에 조인관련부분 지정
, 대신 ??? JOIN 으로 테이블 나열
조인 조건도 FROM 절에서 지정
WHERE 절 기타조건만 지정
다음 SQL문을 ANSI JOIN으로 변경하기
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
방법1) NATURAL JOIN 사용
- 두 테이블에서 같은 이름의 컬럼을 찾아서 조인시킴
- 조인조건은 = (EQUAL) 일 때만 가능
- 두 테이블에서 컬럼명 같은 모든 컬름을 = 조건으로 조인조건 만들어서 처리함
SELECT E.ENAME, D.DNAME
FROM EMP E NATURAL JOIN DEPT D;
- 사원 : 번호/이름/부서번호 부서 : 부서번호/부서명/번호
여기에서 NATURAL JOIN을 하면, 두 테이블에서 공통인 애들을 자동으로 조인조건시킨 결과랄 나옴
즉, 번호가 공통이고 부서번호가 공통이니까 WHERE절에 번호=번호, 부서번호=부서번호 를 한 결과를 내게 된다
근데 내가 원하는 건 하나를 공통으로 묶는 것을 원하는 것이었다면 USING 조건을 사용하게 된다
방법2) JOIN -USING 사용
SELECT E.ENAME, D.DNAME
FROM EMP E JOIN DEPT D USING(DEPTNO);
- USING ( 합칠조건 ) 을 사용하여, WHERE절에 DEPTNO = DEPTNO 를 자동으로 실행함
방법3) JOIN-ON 사용
SELECT E.ENAME, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO=D.DEPTNO);
- ON 뒤에 실제 합칠 조건을 직접 쓰는 것으로 가장 많이 쓰임(예외발생이없으므로)
cartesian product
SELECT e.ename, d.dname
FROM EMP e CROSS JOIN DEPT d;
*/
/*
1. 각 부서의 근무지가 어디인지 조회해보기. 부서명(dname), 근무도시(city) 조회
단, . 모든 부서명이 다 나오도록...
SELECT d.dname, l.city
FROM DEPT d, LOCATIONS l
WHERE D.LOC_CODE = L.LOC_CODE(+);
이것을 ansi join 써서 사용하면
SELECT d.dname, l.city
FROM DEPT d LEFT JOIN LOCATIONS l ON(D.LOC_CODE=l.loc_code);
- ON 안의 것을 기준으로 왼쪽거를 다 보여주고 싶으면 JOIN 앞에 LEFT 반대로 오른쪽이면 RIGHT 를 써서 (+) 기호를 대신함
*/
/*
EMP, DEPT, SALGRADE, LOCATIONS 중 필요한 테이블 이용
1. 1981년도에 입사한 사원들 중 이름이 5글자인 사원들의 이름, 급여, 부서번호, 부서명, 근무지(city) 조회
방법 1)
SELECT DISTINCT E.ename, E.SAL, D.DEPTNO, L.CITY
FROM EMP e, SALGRADE s, DEPT d, LOCATIONS l
WHERE E.DEPTNO=D.DEPTNO AND D.LOC_CODE=L.LOC_CODE AND E.ENAME LIKE '_____' AND TO_CHAR(E.HIREDATE,'yyyy')='1981';
방법 2) ansi join 사용
SELECT DISTINCT E.ename, E.SAL, D.DEPTNO, L.CITY
FROM EMP e JOIN DEPT d ON (e.deptno=d.deptno) JOIN LOCATIONS l ON (d.loc_code=l.loc_code)
WHERE E.ENAME LIKE '_____' AND TO_CHAR(E.HIREDATE,'yyyy')='1981';
2, 근무지(city)가 SEOUL인 사원들의 이름, 급여, 급여등급(grade) 조회
방법 1)
SELECT e.ename, e.sal, s.grade
FROM EMP e, SALGRADE s, LOCATIONS l, DEPT d
WHERE e.sal BETWEEN S.LOSAL AND S.HISAL
AND L.CITY = 'SEOUL'
AND E.DEPTNO = D.DEPTNO
AND D.LOC_CODE = L.LOC_CODE;
방법 2) ANSI JOIN
SELECT e.ename, e.sal, s.grade
FROM EMP E JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)
JOIN LOCATIONS L ON (D.LOC_CODE = L.LOC_CODE)
WHERE L.CITY = 'SEOUL';
*/
/* << 분석함수 >>
1. select 문 수행 결과 row를 대상으로 적용함
- WHERE // GROUP BY // HAVING 절에 사용할 수 없음 ( 결과를 가지고 적용하기 때문에 )
구문
: 함수이름(인자) OVER ( PARTITION BY ~ ORDER BY ~ ROWS ~ )
- 인자 : 함수에 따라 0~3개
- 순서는 앞에서~
- ORDER BY : 특정함수에선 필수인 경우가 있음
- ROWS : 단독사용불가. 사용시 ORDER BY 절 지정
SELECT ENAME, DEPTNO, SAL, RANK() OVER ( ORDER BY SAL DESC ) 등수
FROM EMP;
SELECT ENAME, DEPTNO, SAL,
RANK() OVER ( ORDER BY SAL DESC ) 등수 ,
RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC)
FROM EMP;
- 위에 RANK가 전체석차
- 아래 RANK는 해당 부서에서의 석차 ( PARTITION BY DEPTNO ) 를 나타냄
SELECT ENAME, DEPTNO, SAL,
RANK() OVER (ORDER BY SAL DESC) 등수,
DENSE_RANK() OVER(ORDER BY SAL DESC) 등수2
FROM EMP;
- DENSE_RANK는 일반적인 순서 말고, 그냥 동석차에 관계없이 석차 1,2,3,4 순서대로 부여
SELECT ENAME, DEPTNO, SAL, MAX(SAL)
FROM EMP;
- 요건 그룹함수라서 같이 쓸 수가 없어요 -> 저 문장만 SELECT 같이 써도 되긴 함
SELECT ENAME, DEPTNO, SAL, (SELECT MAX(SAL) FROM EMP ) FROM EMP
SELECT ENAME, DEPTNO, SAL, MAX(SAL)
OVER ()
FROM EMP;
- 위와 차이는 테이블을 한 번만 봄
- 계산은 묶어서 보되 결과는 한줄씩 표시가능
*/
'공부하자 > IT' 카테고리의 다른 글
| SSL 인증서 종류 및 특징 (5) | 2015.02.08 |
|---|---|
| [트렌드] 핀테크란 무엇일까요? (0) | 2015.01.31 |
| [네트워크] TCP/IP 4계층 (0) | 2012.12.28 |
| [네트워크] OSI 7 계층 (7) | 2012.12.28 |
| [네트워크] MIME, SMTP, POP (1) | 2012.12.28 |
| [네트워크] ARP, RARP (0) | 2012.12.28 |
| [네트워크] ICMP, TCP, UDP, Ping (0) | 2012.12.28 |
| [네트워크] IPv6 (0) | 2012.12.28 |
| [최신기술] 웹(Web) 2.0 (0) | 2012.12.28 |
| [웹언어] ASP/JSP/PHP 특징 및 장단점 (3) | 2012.12.28 |
