국비 지원 개발자 과정_Day27

/* Formatted on 2023/01/03 오전 9:36:41 (QP5 v5.215.12089.38647) */
SELECT deptno, dname, loc FROM dept;

SELECT empno, ename, deptno FROM emp;

--SELECT 컬럼명1, 컬렁명2, COUNT (컬럼명3)
--  FROM 집합1, 집합2, ... 카타시안의 곱(일어날 수 있는 모든 경우의 수 조회)

--부적합한 식별자입니다
--열 정의가 애매합니다
--이 두가지는 SQLException의 대상 -> try_catch필요(SQLException se){}

--양쪽 테이블에 모두 존재할 경우, 인덱스인 컬럼명을 쓰는 것이 더 빠르다
--카타시안의 곱-일어날 수 있는 모든 경우의 수가 다 나온다
--emp-14
--dept-4
SELECT count(empno) FROM emp;
SELECT count(deptno) FROM dept;

--14*4=56가지 경우의 수가 모두 나온다
SELECT empno, ename, dept.deptno, dname
FROM emp, dept;

--조건절 추가(Natural JOIN)
SELECT empno, ename, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.ename='SMITH';

--조건절을 사용하면 경우의 수가 줄어든다(그러니 검색 속도가 빨라진다)
--AND 교집합(튜플의 수는 줄어든다)
--OR 합집합(튜플의 수는 증가한다)

SELECT empno FROM emp; --PK는 인덱스로 관리하기에 자동 오름차순

--옵티마이저에게 보내는 힌트문 추가해서 내림차로 설정
SELECT /*+index_desc(emp pk_emp)*/ empno FROM emp;

SELECT ename FROM emp
ORDER BY ename asc;

 

/* Formatted on 2023/01/03 오후 12:11:55 (QP5 v5.215.12089.38647) */
SELECT * FROM temp;

--1. 월 급여는 연봉을 18로 나누어 홀수 달에는 연봉의 1/18이 지급되고,
-- 짝수달에는 연봉의 2/18가 지급된다고 가정했을 때 홀수 달과 짝수 달에 받을 금액을 나타내시오.

SELECT emp_name AS "사원명",
       TO_CHAR ( (ROUND (salary / 18, -1)), '999,999,999') || '원'
          AS "홀수달 급여",
       TO_CHAR ( (ROUND (salary * 2 / 18, -1)), '999,999,999') || '원'
          AS "짝수달 급여"
  FROM temp;

SELECT ROUND (123456.789, 0)                                --소수점 첫번째 자리에서 반올림
                            , ROUND (123456.789, 1)         --소수점 두번째 자리에서 반올림
                                                   , ROUND (123456.789, -1) --0의 자리에서 반올림
  FROM DUAL;

--SELECT와 FROM 사이에서 연산이 가능하다(사칙연산이 가능하다)
--컬럼명 자리에 함수를 중첩해서 사용 가능하다
--실제 테이블의 구조가 아닌 다른 구조로 출력이 가능하다
--예를 들어 고객이 원하는 테이블 구조대로 가능하다
--컬럼 하나 로우 하나짜리 가상 테이블 dual

SELECT 1 + 1,
       500 - 300,
       2 * 5,
       5 / 2
  FROM DUAL;

--컬럼명이 늘어나면 가로가 길어짐
--만약 로우를 늘어나게 하고싶다면 합집합 사용
--데이터를 복제하는 용도로 사용
--합집합 - UNION ALL, UNION
--교집합 - INTERSECT
--차집합 - MINUS

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

--2. 위에서 구한 월 급여에 교통비가 10만원씩 지급된다면(짝수달은 20만원)
-- 위의 문장이 어떻게 바뀔지 작성해 보시오.

SELECT emp_name AS "사원명",
       TO_CHAR ( (ROUND (salary / 18, -1) + 100000), '999,999,999') || '원'
          AS "홀수달 급여",
          TO_CHAR ( (ROUND (salary * 2 / 18, -1) + 200000), '999,999,999')
       || '원'
          AS "짝수달 급여"
  FROM temp;

--3. TEMP 테이블에서 취미가 NULL 이 아닌 사람의 성명을 읽어오시오.

SELECT emp_name AS "사원명", hobby AS "취미"
  FROM temp
 WHERE hobby IS NOT NULL;

SELECT emp_name AS "사원명", hobby AS "취미"
  FROM temp
 WHERE hobby IS NULL;

--4. TEMP 테이블에서 취미가 NULL인 사람은 모두 HOBBY를 “없음”이라고 값을 치환하여 가져오고
-- 나머지는 그대로 값을 읽어오시오.

SELECT emp_name AS "사원명", NVL (hobby, '없음') AS "취미", hobby
  FROM temp;

--5. TEMP의 자료 중 HOBBY의 값이 NULL인 사원을 ‘등산’으로 치환했을 때
-- HOBBY가 ‘등산인 사람의 성명을 가져오는 문장을 작성하시오.

SELECT emp_name AS "사원명", NVL (hobby, '등산') AS "취미"
  FROM temp
 WHERE hobby = '등산' OR hobby IS NULL;

SELECT emp_name AS "사원명", NVL (hobby, '등산') AS "취미"
  FROM temp
 WHERE hobby = '등산';

SELECT emp_name AS "사원명", NVL (hobby, '등산') AS "취미"
  FROM temp
 WHERE hobby IS NULL;

SELECT emp_name AS "사원명", NVL (hobby, '등산') AS "취미"
  FROM temp
 WHERE NVL (hobby, '등산') = '등산';

--6. TEMP의 자료 중 EMP_ID와 EMP_NAME을 각각 ‘사번’,’성명’으로 표시되어 DISPLAY되도록
-- COLUMN ALLIAS를 부여하여 SELECT 하시오.

SELECT emp_id AS "사번", emp_name AS "성명" FROM temp;

--7. TEMP의 자료를 직급 명(LEV)에 ASCENDING하면서 결과내에서 다시 사번 순으로 DESCENDING하게 하는
-- ORDER BY하는 문장을 만들어 보시오.

  SELECT emp_id AS "사번", emp_name AS "사원명", lev AS "직급"
    FROM temp
ORDER BY lev ASC, emp_id DESC;

  SELECT emp_id AS "사번", emp_name AS "사원명", lev AS "직급"
    FROM temp
ORDER BY step ASC, emp_id DESC;

  SELECT emp_id AS "사번", emp_name AS "사원명", lev AS "직급"
    FROM temp
ORDER BY step DESC, emp_id ASC;

 

/* Formatted on 2023/01/03 오후 2:41:05 (QP5 v5.215.12089.38647) */
SELECT * FROM t_letitbe;

--나머지 1 혹은 0 반환
SELECT MOD (5, 2), MOD (6, 2), MOD (120, 2) FROM DUAL;

SELECT seq_vc FROM t_letitbe;

----varchar2 타입이기에 1 다음 10 나옴
  SELECT seq_vc
    FROM t_letitbe
ORDER BY seq_vc ASC;

--숫자로 변환한 후 오름차순 정렬
  SELECT seq_vc
    FROM t_letitbe
ORDER BY TO_NUMBER (seq_vc) ASC;

--오라클에서 형전환 함수가 있다
--to_char() 날짜, 숫자 -> 문자
--to_numver() 문자 -> 숫자

--함수(파라미터1, 파라미터2)
--함수는 리턴값이 있다.
--함수는 파라미터를 맞춰야 한다. 개수와 타입 모두를 맞춰애 한다

--문자여도 자동으로 숫자로 변환하고 연산해줌
SELECT MOD (seq_vc, 2) FROM t_letitbe;

--1이 1개만 나옴
SELECT MOD (seq_vc, 2)
  FROM t_letitbe
 WHERE seq_vc = 1;

--where절에 함수 부분을 그대로 써야 17개 전부 나옴
SELECT MOD (seq_vc, 2)
  FROM t_letitbe
 WHERE MOD (seq_vc, 2) = 1;

--짝수일 경우
SELECT MOD (seq_vc, 2)
  FROM t_letitbe
 WHERE MOD (seq_vc, 2) = 0;

--별칭 붙여줌
SELECT MOD (seq_vc, 2) AS "no"
  FROM t_letitbe
 WHERE MOD (seq_vc, 2) = 0;

--where문 없으면 as 생략 가능
SELECT MOD (seq_vc, 2) "no" FROM t_letitbe;

--조건절에 사용하는 컬럼은 반드시 집합에 있는 컬럼명만 가능하다
--아래와 같이 사용 불가!
--SELECT MOD(seq_vc,2) as "no"
--FROM t_letitbe
--WHERE no = 0;

--인라인뷰
SELECT no
  FROM (SELECT MOD (seq_vc, 2) no FROM t_letitbe)
 WHERE no = 1;

--decode문 -> ifelse와 같은 기능
--만약 clerk이라면 sal 출력하고 나머지는 null값으로 출력
SELECT ename, DECODE (job, 'CLERK', sal, NULL) FROM emp;

SELECT ename, DECODE (job, 'CLERK', sal), DECODE (job, 'SALESMAN', sal)
  FROM emp;

SELECT ename,
       DECODE (job, 'CLERK', sal, NULL),
       DECODE (job, 'SALESMAN', sal, NULL),
       DECODE (job,  'CLERK', NULL,  'SALESMAN', NULL,  sal)
  FROM emp;

SELECT deptno,
       DECODE (deptno,
               10, 'ACCOUNTING',
               20, 'RESEARCH',
               30, 'SALES',
               'OPERATIONS')
          name
  FROM dept;

--10부서는 급여합계를, 20부서는 최대값을, 30부서는 최소값을 출력
  SELECT deptno,
         DECODE (deptno,  10, SUM (sal),  20, MAX (sal),  30, MIN (sal)) sal
    FROM emp
GROUP BY deptno;

SELECT SUM (sal) AS "전체급여합",
       COUNT (empno) AS "사원수",
       ROUND (AVG (sal), -1) AS "평균급여"
  FROM emp;

SELECT '10', SUM (sal)
  FROM emp
 WHERE deptno = 10
UNION ALL
SELECT '20', SUM (sal)
  FROM emp
 WHERE deptno = 20
UNION ALL
SELECT '30', SUM (sal)
  FROM emp
 WHERE deptno = 30;

SELECT DISTINCT (deptno) FROM emp;

  SELECT deptno
    FROM emp
GROUP BY deptno;

  SELECT deptno, SUM (sal)
    FROM emp
GROUP BY deptno;

--distinct를 쓰면 전체 부서 사람이 나옴
SELECT DISTINCT (deptno), ename FROM emp;

--아래와같이 GROUP BY를 쓸 수는 없음(부서에 사람이 여러명이니까)
--SELECT deptno, ename
--FROM emp
--GROUP BY deptno;

--1명으로 한정하면 사용 가능
  SELECT deptno, MAX (ename)
    FROM emp
GROUP BY deptno;

  SELECT deptno, MIN (ename)
    FROM emp
GROUP BY deptno;

  SELECT deptno, COUNT (ename)
    FROM emp
GROUP BY deptno;

-- 부서별로 급여 합계를 출력 
 SELECT NVL((DECODE(deptno, 10, sal)),0) deptno10, 
               NVL((DECODE(deptno, 20, sal)),0) deptno20,
               NVL((DECODE(deptno, 30, sal)),0) deptno30,
               NVL((DECODE(deptno, 40, sal)),0) deptno40
  FROM emp;

 SELECT NVL(SUM(DECODE(deptno, 10, sal)),0) deptno10, 
               NVL(SUM(DECODE(deptno, 20, sal)),0) deptno20,
               NVL(SUM(DECODE(deptno, 30, sal)),0) deptno30,
               NVL(SUM(DECODE(deptno, 40, sal)),0) deptno40
  FROM emp;
  
SELECT NVL(SUM(DECODE(deptno, 10, sal)),0) deptno10, 
               NVL(SUM(DECODE(deptno, 20, sal)),0) deptno20,
               NVL(SUM(DECODE(deptno, 30, sal)),0) deptno30,
               NVL(SUM(DECODE(deptno, 40, sal)),0) deptno40
  FROM emp
 GROUP BY deptno;
 
-- 40번 부서에 근무하는 사람은 없음
 SELECT deptno FROM dept
 MINUS
 SELECT deptno FROM emp;
 
 SELECT deptno, DECODE(deptno, 10, 'ACCOUNTING'
 ,20,'RESEARCH'
 ,30,'SALES'
 ,'OPERATIONS')
 FROM dept;
 
 --급여별로 인상율을 다르게 계산 
SELECT ename , sal,
       CASE
          WHEN sal < 1000  THEN sal+(sal*0.8)
          WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
          WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
          ELSE sal+(sal*0.1)
       END sal
  FROM emp; 
  
--우리회사 직원중에 급여가 2000이상이고 3000이하인 직원들의 이름, 급여액 출력
SELECT ename, sal
FROM emp
WHERE sal >= 2000
AND sal <= 3000;

SELECT ename, sal
FROM emp
WHERE sal BETWEEN 2000 AND 3000;

 

/* Formatted on 2023-01-03 오후 3:46:53 (QP5 v5.215.12089.38647) */
SELECT * FROM MEMBER;

--아이디가 존재하지 않으면 -1, 둘 다 맞으면 1, 비밀번호가 다르면 0
SELECT CASE
          WHEN mem_id != :id THEN -1
          WHEN mem_id = :id AND mem_pw = :pw THEN 1
          WHEN mem_id = :id AND mem_pw != :pw THEN 0
       END
          AS result
  FROM MEMBER;

--결과값 순서 가장 위로 올리기
  SELECT result
    FROM (SELECT CASE WHEN mem_id = :id THEN 1 ELSE -1 END AS result
            FROM MEMBER)
ORDER BY result DESC;

--가장 위의 값만 반환
SELECT result
  FROM (  SELECT CASE WHEN mem_id = :id THEN 1 ELSE -1 END AS result
            FROM MEMBER
        ORDER BY result DESC)
 WHERE ROWNUM = 1;
 
--아이디가 존재하지 않으면 -1, 둘 다 맞으면 1, 비밀번호가 다르면 0
SELECT result
  FROM (  SELECT CASE
                    WHEN mem_id != :id THEN -1
                    WHEN mem_id = :id AND mem_pw = :pw THEN 1
                    WHEN mem_id = :id AND mem_pw != :pw THEN 0
                 END
                    AS result
            FROM MEMBER
        ORDER BY result DESC)
 WHERE ROWNUM = 1;

 

정규화(Normalization)

이상현상이 있는 릴레이션을 분해하여 이상현상을 없애는 과정

릴레이션을 분해하여 여러 개의 릴레이션을 생성하게 되는데, 이를 단계별로 구분하여 정규형이 높아질수록 이상현상은 줄어들게 됨

 

제1 정규형(1NF)

각 컬럼이 하나의 속성만 가져야 함

하나의 컬럼은 같은 종류나 타입의 값을 가져야 함

각 컬럼이 유 일한 이름을 가져야 함

컬럼의 순서가 상관없어야 함

 

제2 정규형(2NF)

제1 정규형을 만족해야 함

모든 컬럼이 부분적 종속이 없어야 함(완전 함수 종속을 만족해야 함)

부분적 종속이란 기본키 중 특정 컬럼에만 종속되는 것

완전 함수 종속이란 기본키의 부분집합이 결정자가 되어선 안 된다는 것

 

제3 정규형(3NF)

제2 정규형을 만족해야 함

기본키를 제외한 속성들 간의 이행 종속성이 없어야 함

이행 종속성이란 A→B, B→C일 때 A→C가 성립하는 것

 

List<DeptVO> & List<Map>
1. 첫 번째 제네릭 타입은 DeptVO 이다.
private int deptno;
private String dname;
private String loc;

2. 두 번째 제네릭 타입은 Map 이다.
Object -> 연산을 위해 형전환(Casting)필요
CastingException 발생 가능성

ORM솔루션 - MyBatis 연계(반자동) -> Hibernate(DML문 자체가 없음-완전자동)

 

package dev_java.basic2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dev_java.tables.DeptVO;
import dev_java.util.DBConnectionMgr;

public class ListDeptVO {
  DBConnectionMgr dbMgr = new DBConnectionMgr();
  Connection con = null; // 오라클 서버와 연결통로 확보
  PreparedStatement pstmt = null; // 자바에서 오라클 서버로 DML 전달
  ResultSet rs = null; // 오라클 테이블에 제공되는 커서를 조작하는 함수제공

  public List<DeptVO> getEmpList() {
    List<DeptVO> list = null;
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT empno, ename, dname ");
    sql.append("FROM emp, dept ");
    sql.append("WHERE emp.deptno = dept.deptno");
    try {
      con = dbMgr.getConnection();
      pstmt = con.prepareStatement(sql.toString());
      rs = pstmt.executeQuery();
      list = new ArrayList<>(); // list.size() = 0
      DeptVO dVO = null;
      while (rs.next()) {
        dVO = new DeptVO(); // 디폴트 생성자 0 null null
        System.out.println(rs.getInt("empno") + ", " + rs.getString("ename") + ", " + rs.getString("dname"));
        list.add(dVO); // 0부터 차례대로 들어감
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }

  public static void main(String[] args) {
    ListDeptVO listDeptVO = new ListDeptVO();
    List<DeptVO> list = null;
    list = listDeptVO.getEmpList();
    System.out.println("list: " + list);
    for (DeptVO rdVO : list) {
    System.out.println(rdVO.getDeptNo() + ", " + rdVO.getDeptName() + ", " + rdVO.getDeptLoc());
    }
  }
}

 

package dev_java.basic2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import dev_java.util.DBConnectionMgr;

public class ListMap {
  DBConnectionMgr dbMgr = new DBConnectionMgr();
  Connection con = null; // 오라클 서버와 연결통로 확보
  PreparedStatement pstmt = null; // 자바에서 오라클 서버로 DML 전달
  ResultSet rs = null; // 오라클 테이블에 제공되는 커서를 조작하는 함수제공

  public List<Map<String, Object>> getEmpList() {
    List<Map<String, Object>> listMap = null;
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT empno, ename, dname ");
    sql.append("FROM emp, dept ");
    sql.append("WHERE emp.deptno = dept.deptno");
    try {
      con = dbMgr.getConnection();
      pstmt = con.prepareStatement(sql.toString());
      rs = pstmt.executeQuery();
      listMap = new ArrayList<>();
      Map<String, Object> rMap = null;
      while (rs.next()) {
        rMap = new HashMap<>();
        rMap.put("empno", rs.getInt("empno"));
        rMap.put("ename", rs.getString("ename"));
        rMap.put("dname", rs.getString("dname"));
        listMap.add(rMap);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return listMap;
  }

  public static void main(String[] args) {
    ListMap listMap = new ListMap();
    List<Map<String, Object>> list = null;
    list = listMap.getEmpList();
    // System.out.println("list: " + list);
    for (Map<String, Object> rMap : list) {
      System.out.println(rMap.get("empno") + ", " + rMap.get("ename") + ", " + rMap.get("dname"));
    }
  }
}