본문 바로가기

공부하자/IT

[SQL] 실무에 바로 쓰는 SQL 활용

728x90
반응형

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