국비 지원 개발자 과정_Day100

테이블구조(로우+컬럼: 로우를 컬럼으로 자르면 셀이 생김)와는 다르게 화면 출력하기

→ 로우에 있는 값을 컬럼레벨에, 컬럼레벨에 있는 값을 로우레벨에 출력하기

SELECT 1, 2, 3 FROM dual;

SELECT 1 FROM dual
UNION ALL
SELECT 2 FROM dual
UNION ALL
SELECT 3 FROM dual;

 

--문법적인 문제만 해결할 뿐 의미없는 MIN함수
SELECT MIN (ename),
       SUM (sal),
       COUNT (sal),
       AVG (sal)
  FROM emp;

--공백포함 하나씩 출력
SELECT deptno, SUM (sal), COUNT (sal), AVG (sal)
    FROM emp
GROUP BY deptno;

  SELECT CEIL (ROWNUM / 4) cno,
         DECODE (MOD (ROWNUM, 4), 1, ename) n1,
         DECODE (MOD (ROWNUM, 4), 2, ename) n2,
         DECODE (MOD (ROWNUM, 4), 3, ename) n3
    FROM (  SELECT ename
              FROM emp
          ORDER BY ename);

--공백 제거 하나씩 출력
  SELECT CEIL(ROWNUM / 4) cno,
         MAX(DECODE (MOD (ROWNUM, 4), 1, ename)) n1,
         MAX(DECODE (MOD (ROWNUM, 4), 2, ename)) n2,
         MAX(DECODE (MOD (ROWNUM, 4), 3, ename)) n3,
         MAX(DECODE (MOD (ROWNUM, 4), 0, ename)) n4
    FROM (  SELECT ename
              FROM emp
          ORDER BY ename)
GROUP BY CEIL(ROWNUM / 4)
ORDER BY CEIL(ROWNUM / 4)

--콤마로 표시
  SELECT CEIL(ROWNUM / 4) cno,
         MAX(DECODE (MOD (ROWNUM, 4), 1, ename)) || ',' ||
         MAX(DECODE (MOD (ROWNUM, 4), 2, ename)) || ',' ||
         MAX(DECODE (MOD (ROWNUM, 4), 3, ename)) || ',' ||
         MAX(DECODE (MOD (ROWNUM, 4), 0, ename)) as ename
    FROM (  SELECT ename
              FROM emp
          ORDER BY ename)
GROUP BY CEIL(ROWNUM / 4)
ORDER BY CEIL(ROWNUM / 4)

 

서브쿼리(직접조건이 아니라 간접조건을 주기)

temp에서 연봉이 가장 많은 직원 사번, 성명, 연봉 출력

SELECT MAX(salary) FROM temp;

SELECT emp_id, emp_name, salary FROM temp
WHERE salary = 100000000;

SELECT emp_id, emp_name, salary FROM temp
WHERE salary = (SELECT MAX(salary) FROM temp);

 

temp에서 salary평균을 구하고 이보다 큰 금액을 받는 사원 사번, 성명, 연봉 출력

SELECT AVG(salary) FROM temp;

SELECT emp_id, emp_name, salary FROM temp
WHERE salary > (SELECT AVG(salary) FROM temp);

 

temp 직원 중 인천에 근무하는 직원의 사번, 성명 출력

SELECT emp_id, emp_name, dept_code FROM temp;

SELECT dept_code
  FROM tdept
 WHERE area = '인천';

--IN예약어 사용방법
SELECT empno, ename, deptno FROM emp
WHERE deptno IN(10, 20)

--IN 적용
SELECT emp_id, emp_name, dept_code FROM temp
WHERE dept_code IN (SELECT dept_code
  FROM tdept
 WHERE area = '인천')

 

연봉 외 커미션을 받는 직원 정보를 서브쿼리로 select 하여 부서 명칭별로 커미션 받는 인원수 출력

SELECT b.dept_name, count(*)
FROM temp a, tdept b
WHERE a.dept_code = b.dept_code
AND a.emp_id IN (SELECT emp_id FROM tcom)
GROUP BY b.dept_name

 

having 이용해 group by 조건주기

SELECT 
    AVG(sal) avg_sal
    FROM emp
GROUP BY deptno
HAVING AVG(SAL) > 2000

 

영화 티켓을 받을 수 있는 사람의 명단, 현재 포인트, 영화 티켓 포인트, 티켓 사용 후 예상 잔여 포인트 출력

SELECT 
    b.name_vc as "이름"
  , b.point_nu as "보유포인트"
  , (SELECT point_nu
    FROM t_giftpoint
    WHERE name_vc = '영화티켓') as "영화티켓 포인트"
  , b.point_nu - a.point_nu as "잔여포인트"
FROM t_giftpoint a, t_giftmem b
WHERE b.point_nu > a.point_nu
AND a.name_vc = '영화티켓'

 

김유신 씨가 보유하고 있는 마일리지 포인트로 얻을 수 있는 상품 중 가장 포인트가 높은 것 출력

SELECT name_vc, point_nu
FROM t_giftpoint
WHERE point_nu = (
    SELECT  MAX(a.point_nu)
FROM t_giftpoint a, t_giftmem b
WHERE a.point_nu < b.point_nu
AND b.name_vc = '김유신'

 

1. equlas

양쪽에 모두 있는 것 조회(둘 중 한 곳이라도 없으면 조회 안됨)

--temp에서 사번, 성명, 부서코드를 읽으며 부서명칭을 함께 출력
SELECT a.emp_id, a.emp_name, b.dept_code
 FROM temp a, tdept b
WHERE a.dept_code = b.dept_code;

 

2. non-equals

조인 조건이 ‘=’이 아닌 다른 연산기호로 주어지는 경우

--temp와 emp_level을 이용해 emp_level의 과장 직급 연봉 상한/하한 범위 내에드는 직원의 사번, 성명, 직급, salary 출력
SELECT a.emp_id 사번
     , a.emp_name 성명
     , a.salary
     , a.lev
FROM temp a, emp_level b
WHERE a.salary BETWEEN from_sal AND to_sal
AND b.lev = '과장'

 

3. outer-join

두 개이상의 테이블 조인할 때 한쪽 테이블의 행에 대해서 다른 쪽 테이블에 일치하는 행이 없더라도 다른 쪽 테이블 행을 null로 하여 행을 리턴해줌

SELECT d.deptno, d.dname, e.ename
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno

SELECT b.emp_id 사번
     , b.emp_name 성명
     , b.salary 연봉
     , a.from_sal 하한
     , a.to_sal 상한
  FROM emp_level a, temp b
  WHERE b.lev = a.lev(+)

 

4. self-join

보통의 경우에 조인은 서로 다른 테이블과 관계형태를 갖지만, self join은 자기 자신과 관계형태를 맺음

SELECT
    a.dept_code 부서코드
  , a.dept_name 부서명
  , b.dept_code 상위부서코드
  , b.dept_name 상위부서명
FROM tdept a, tdept b
WHERE b.dept_code = a.parent_dept;