국비 지원 개발자 과정_Day28

SELECT 컬럼명1, 컬럼명2, 함수(함수()), ROUND(TO_CHAR()) → 제공함수

    FROM 집합1, 집합2(테이블, SELECT문-인라인뷰)

WHERE 컬럼명 비교연산자 값

    AND

    OR(로우의 수가 늘어난다 → 속도 떨어짐)

GROUP BY 컬럼명(주의: 여기 적힌 컬럼명만 SELECT절에 올 수 있다)

(Having절-GROUP BY절 사용에 대한 조건 검색 시 사용, where 사용 X!!)

ORDER BY 컬럼명1 asc | desc, 컬럼명2 asc | desc

 

사용자 정의함수

PL/SQL 표준

CREATE OR REPLACE FUNCTION func_XXX()

CREATE OR REPLACE PROCEDURE proc_XXX()

CREATE OR PREPLACE TRIGGER tri_XXX()

--* : 와일드카드, 집합에 존재하는 모든 컬럼을 의미함
--데이터 검증이나 확인용으로만 사용할 것
--자바 코드에서 사용하면 직관적이지 않아서 권장하지 않음

SELECT
empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp;

SELECT * FROM emp;

--아래와같이 ""없이 표현해도 되지만 붙이는 것이 원칙
SELECT empno 사원번호
FROM emp;

SELECT empno "사원번호"
FROM emp;

--단 생략하는 경우 공백을 줄 수 없음
--SELECT empno 사원 번호
--FROM emp;

 

/* Formatted on 2023/01/04 오후 12:37:00 (QP5 v5.215.12089.38647) */
SELECT * FROM emp;

SELECT DISTINCT (deptno) FROM emp;

--group by에 적힌 컬럼명이 select에 와야한다

  SELECT deptno
    FROM emp
GROUP BY deptno;

--다른 컬럼을 추가하고 싶다면 그룹함수(max, min, sum, count) 사용

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

--부서별 급여평균

  SELECT deptno, ROUND (AVG (sal), -1) AS "평균급여"
    FROM emp
GROUP BY deptno;

--급여 평균이 2000 이상인 부서

  SELECT deptno, ROUND (AVG (sal), -1) AS "평균급여"
    FROM emp
GROUP BY deptno
  HAVING AVG (sal) >= 2000;

SELECT * FROM lecture;

--time과 point가 같으면 일반, 다르면 특별

SELECT lec_id,
       lec_time,
       lec_point,
       DECODE (lec_time, lec_point, '일반과목', '특별과목')
          AS "과목분류"
  FROM lecture;

--강의시간과 학점이 같으면 1을 반환한다
--널은 모른다, 결정되지 않았다

  SELECT lec_id,
         lec_time,
         lec_point,
         DECODE (lec_time, lec_point, 1) AS "과목분류"
    FROM lecture
ORDER BY lec_id ASC;

SELECT COUNT (DECODE (lec_time, lec_point, 1)) AS "과목분류" FROM lecture;

SELECT lec_id,
       lec_time,
       lec_point,
       1
  FROM lecture
 WHERE lec_time = lec_point;

SELECT COUNT (1)
  FROM lecture
 WHERE lec_time = lec_point;

--시간이 크면 실험과목, 학점이 크면 기타과목 둘이 같으면 일반과목

SELECT lec_id,
       lec_time,
       lec_point,
       CASE
          WHEN lec_time > lec_point THEN '실험과목'
          WHEN lec_time < lec_point THEN '기타과목'
          ELSE '일반과목'
       END
          AS 과목분류
  FROM lecture;

SELECT SIGN (100 - 10),
       SIGN (10 - 100),
       0 - 0,
       SIGN (100 - 100)
  FROM DUAL;

  SELECT lec_id,
         lec_time,
         lec_point,
         DECODE (lec_time - lec_point,
                 0, '일반과목',
                 1, '실험과목',
                 -1, '기타과목')
            AS 과목분류
    FROM lecture
ORDER BY lec_id ASC;

SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR (SYSDATE, 'DD') FROM DUAL;

SELECT TO_CHAR (SYSDATE, 'day') FROM DUAL;

--상수로하면 변하지 않음

SELECT '04' || '21' FROM DUAL;

--월요일엔 해당일자에 01을 붙여서 4자리 암호를 만들고,
--화요일엔 11, 수요일엔 21, 목요일엔, 31, 금요일엔 41, 토요일엔 51,
--일요일엔 61을 붙여서 4자리 암호를 만든다고 할 때
--암호를 SELECT하는 SQL을 만들어 보시오.

SELECT DECODE (TO_CHAR (SYSDATE, 'day'),
               '월요일', TO_CHAR (SYSDATE, 'DD') || '01',
               '화요일', TO_CHAR (SYSDATE, 'DD') || '11',
               '수요일', TO_CHAR (SYSDATE, 'DD') || '21',
               '목요일', TO_CHAR (SYSDATE, 'DD') || '31',
               '금요일', TO_CHAR (SYSDATE, 'DD') || '41',
               '토요일', TO_CHAR (SYSDATE, 'DD') || '51',
               '일요일', TO_CHAR (SYSDATE, 'DD') || '61')
          AS 비밀번호
  FROM DUAL;

SELECT    TO_CHAR (SYSDATE, 'DD')
       || DECODE (TO_CHAR (SYSDATE, 'day'),
                  '월요일', '01',
                  '화요일', '11',
                  '수요일', '21',
                  '목요일', '31',
                  '금요일', '41',
                  '토요일', '51',
                  '일요일', '61')
          AS 비밀번호
  FROM DUAL;

SELECT * FROM t_letitbe;

--1)영어가사만 나오게 하기

SELECT seq_vc, DECODE (MOD (seq_vc, 2), 1, words_vc, NULL) FROM t_letitbe;

SELECT DECODE (MOD (seq_vc, 2), 1, words_vc, NULL)
  FROM t_letitbe
 WHERE MOD (seq_vc, 2) = 1;

--num은 별칭이지 t_letitbe에 존재하는 컬럼이 아니다
--따라서 where절에 사용 불가
--하지만 인라인뷰를 사용하면 가능하다
--인라인뷰를 사용해서 집합을 가공(컬럼, 로우값)할 수 있다
--SELECT
--MOD(seq_vc, 2) as "num", seq_vc
--,DECODE(MOD(seq_vc, 2),1,words_vc,null)
--FROM t_letitbe
--WHERE num=1;

--인라인뷰는 from 뒤에 오는 select문을 말한다
--인라인부에서 사용된 컬럼명은 where절에서 사용이 가능하다
--그 컬럼명이 t_letitbe 테이블에 존재하는 컬럼이 아니더라도 사용 가능!

SELECT *
  FROM (SELECT MOD (seq_vc, 2) num,
               seq_vc,
               DECODE (MOD (seq_vc, 2), 1, words_vc, NULL)
          FROM t_letitbe) a
 WHERE a.num = 1;

--2)한글가사만 나오게 하기

SELECT DECODE (MOD (seq_vc, 2), 0, words_vc, NULL)
  FROM t_letitbe
 WHERE MOD (seq_vc, 2) = 0;

SELECT *
  FROM (SELECT MOD (seq_vc, 2) num,
               seq_vc,
               DECODE (MOD (seq_vc, 2), 0, words_vc, NULL)
          FROM t_letitbe) a
 WHERE a.num = 0;

--3)영문가사와 한글 가사 모두 나오게 하기
--3번 문제의 경우 SELECT * FROM t_letitbe는 답이 아닙니다.
--조건:합집합을 이용, 정렬, 영문가사와 한글가사 교대로 출력

  SELECT seq_vc, MAX (w)                --w에 사용되는 그룹 함수는 null과 비교되니까 값은 오직 하나뿐
    FROM (SELECT seq_vc, DECODE (MOD (seq_vc, 2), 1, words_vc) w FROM t_letitbe
          UNION ALL
          SELECT seq_vc, DECODE (MOD (seq_vc, 2), 0, words_vc) FROM t_letitbe)
GROUP BY seq_vc
ORDER BY TO_NUMBER (seq_vc) ASC;

--아래 방법은 group by에대한 문법적인 오류만 해결하고
--group by한 효과가 전혀없는 경우임

  SELECT deptno, ename
    FROM emp
GROUP BY deptno, ename;

--널값은 모르기에 가장 하단에 위치

  SELECT ename, comm
    FROM emp
ORDER BY comm ASC;

--temp의 자료를 salary로 분류하여 30,000,000이하는 'D',
--30,000,000 초과 50,000,000이하는 'C'
--50,000,000 초과 70,000,000이하는 'B'
--70,000,000 초과는 'A'라고 등급을 분류하여 등급별 인원수 출력

SELECT * FROM temp;

  SELECT emp_name,
         salary,
         CASE
            WHEN salary <= 30000000 THEN 'D'
            WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C'
            WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B'
            WHEN salary > 70000000 THEN 'A'
         END
            AS sal_grade
    FROM temp
ORDER BY salary DESC;

--group by 사용해서 인원수 카운트

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

--group by 사용

  SELECT CASE
            WHEN salary <= 30000000 THEN 'D'
            WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C'
            WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B'
            WHEN salary > 70000000 THEN 'A'
         END,
         COUNT (salary)
    FROM temp
GROUP BY CASE
            WHEN salary <= 30000000 THEN 'D'
            WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C'
            WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B'
            WHEN salary > 70000000 THEN 'A'
         END;

--group by 사용하지 않음

SELECT COUNT (CASE WHEN salary <= 30000000 THEN 'D' END) AS "D",
       COUNT (CASE WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C' END)
          AS "C",
       COUNT (CASE WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B' END)
          AS "B",
       COUNT (CASE WHEN salary > 70000000 THEN 'A' END) AS "A"
  FROM temp;
  
  
  SELECT result, count(result)
FROM (SELECT 
       CASE
          WHEN salary <= 30000000  THEN 'D'
          WHEN salary BETWEEN 30000001 AND 50000000 THEN 'C'
          WHEN salary BETWEEN 50000001 AND 70000000 THEN 'B'
          ELSE 'A'
       END as result
FROM temp)
GROUP BY result
ORDER BY result ASC;

 

관계형태

1:1 →사원-가족테이블의 관계

1:n

n:n →회원-도서, 상품/학생-교과목

업무에 대한 정의가 덜 됨

카테시안의 곱이 발생하므로 조인하면 안 됨

n:n의 관계를 1:n으로 만들어주는 교차엔티티(행위엔티티)를 찾는 것이 중요함

 

제1정규화

중복되는 값을 관리하지 않는다

 

제2정규화

복합키 모두에 종속적인 컬럼명만 쓴다

교차엔티티 또는 행위엔티티

 

제3정규화

테이블에 관계형태로 FK가 존재하는 경우 PK가 아닌 FK에 종속적인 컬럼이 존재하면 안 된다

 

/* Formatted on 2023/01/04 오후 3:15:10 (QP5 v5.215.12089.38647) */
SELECT * FROM employees;

--case_when 구문 연습
--job_id가 ad_ap면 10%,
--it_prog면 15%,
--st_man이면 20%
--나머지는 5% 인상하고
--이름 job_id 현재급여 인상적용된급여를 출력

  SELECT first_name || ' ' || last_name AS "이름",
         job_id AS "직업",
         salary AS "기존급여",
         CASE
            WHEN job_id = 'AD_VP' THEN salary * 1.1
            WHEN job_id = 'IT_PROG' THEN salary * 1.15
            WHEN job_id = 'ST_MAN' THEN salary * 1.2
            ELSE salary * 1.05
         END
            AS "인상급여"
    FROM employees
ORDER BY first_name ASC;

 

/* Formatted on 2023/01/04 오후 5:25:02 (QP5 v5.215.12089.38647) */
SELECT * FROM zipcode_t;

--대분류-zdo
--중분류-sigu
--소분류-dong
--중복제거, 오름차

SELECT '전체' ZDO FROM DUAL
UNION ALL
SELECT DISTINCT (ZDO) ZDO FROM zipcode_t
ORDER BY ZDO ASC;

SELECT '전체' ZDO FROM DUAL
UNION ALL
SELECT zdo
  FROM (  SELECT DISTINCT (ZDO) ZDO
            FROM zipcode_t
        ORDER BY ZDO ASC);

  SELECT DISTINCT (sigu) sigu
    FROM zipcode_t
   WHERE ZDO = :zdo
ORDER BY sigu ASC;

SELECT '전체' sigu FROM DUAL
UNION ALL
SELECT sigu
  FROM (  SELECT DISTINCT (sigu) sigu
            FROM zipcode_t
           WHERE zdo = :zdo
        ORDER BY sigu ASC);

  SELECT DISTINCT (dong) dong
    FROM zipcode_t
   WHERE sigu = :sigu
ORDER BY dong ASC;

SELECT '전체' DONG FROM DUAL
UNION ALL
SELECT DONG
  FROM (  SELECT DISTINCT (DONG) DONG
            FROM zipcode_t
           WHERE sigu = :sigu
        ORDER BY DONG ASC);

 

package dev_java.basic3;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JPanel;
import dev_java.util.DBConnectionMgr;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;

public class ZipCodeSearchView extends JFrame implements ItemListener {
  // 선언부
  // 사용자가 선택한 zdo
  String zdo = null;
  // 사용자가 선택한 sigu
  String sigu = null;
  // 사용자가 선택한 dong
  String dong = null;

  // DB에서 가져온 zdos[]
  String[] zdos = null;
  // 사DB에서 가져온 sigus[]
  String[] sigus = null;
  // DB에서 가져온 dongs[]
  String[] dongs = null;
  // 중분휴(sigu), 소분류(dong)
  String totals[] = { "전체" };
  JPanel jp_north = new JPanel();
  JComboBox jcb_zdo = null;
  JComboBox jcb_sigu = null;
  JComboBox jcb_dong = null;
  DBConnectionMgr dbMgr = new DBConnectionMgr();
  Connection con = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;

  // 생성자
  public ZipCodeSearchView() {
    zdos = getZDOList();
    jcb_zdo = new JComboBox<>(zdos);
    jcb_sigu = new JComboBox<>(totals);
    jcb_dong = new JComboBox<>(totals);
    jcb_zdo.addItemListener(this);
    jcb_sigu.addItemListener(this);
    jcb_dong.addItemListener(this);
  }

  // 대분류 정보 초기화에 필요한 DB조회하기 구현
  public String[] getZDOList() {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT '전체' ZDO FROM DUAL ");
    sql.append("UNION ALL ");
    sql.append("SELECT zdo ");
    sql.append("FROM (  SELECT DISTINCT (ZDO) ZDO ");
    sql.append("FROM zipcode_t ");
    sql.append("ORDER BY ZDO ASC)");
    try {
      // con의 주소번지가 확인되면 오라클서버와 연결통로가 확보된 것
      con = dbMgr.getConnection();
      pstmt = con.prepareStatement(sql.toString());
      // 오라클에서 생성된 테이블의 커서 디폴트위치는 항상 isTop이다
      rs = pstmt.executeQuery();
      Vector<String> v = new Vector<>();
      while (rs.next()) {
        String zdo = rs.getString("zdo");
        v.add(zdo);
      }
      zdos = new String[v.size()];
      v.copyInto(zdos);
    } catch (SQLException se) {
      System.out.println(se.toString());
      System.out.println(sql.toString());
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        // 사용한 자원 반납하기 - 생성된 역순으로 할 것
        // 생략해도 언젠가 반납은 이루어짐, 명시적으로 반납처리 권장
        // 왜냐하면 오라킁 서버에서 커넥션을 강제로 종료시켜버리니까
        dbMgr.freeConnection(con, pstmt, rs);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return zdos;
  } // end of getZDOList

  // 중분류 정보 초기화에 필요한 DB조회하기 구현
  public String[] getSIGUList(String zdo) {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT '전체' sigu FROM DUAL ");
    sql.append("UNION ALL ");
    sql.append("SELECT sigu ");
    sql.append("FROM (  SELECT DISTINCT (sigu) sigu ");
    sql.append("FROM zipcode_t ");
    sql.append(" WHERE zdo = ? ");
    sql.append("ORDER BY sigu ASC)");
    try {
      con = dbMgr.getConnection();
      pstmt = con.prepareStatement(sql.toString());
      pstmt.setString(1, zdo);
      rs = pstmt.executeQuery();
      Vector<String> v = new Vector<>(); // copyInto() 사용하기위해 벡터사용
      while (rs.next()) {
        String sigu = rs.getString("sigu");
        v.add(sigu);
      }
      // sigu콤보박스에 들어갈 배열 생성하기
      sigus = new String[v.size()];
      // 벡터에 들어있는 값 String 배열에 복사하기
      v.copyInto(sigus);
    } catch (SQLException se) {
      System.out.println(se.toString());
      System.out.println(sql.toString());
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        dbMgr.freeConnection(con, pstmt, rs);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return sigus;
  }

  // 소분류 정보 초기화에 필요한 DB조회하기 구현
  public String[] getDONGList(String sigu) {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT '전체' DONG FROM DUAL ");
    sql.append("UNION ALL ");
    sql.append("SELECT DONG ");
    sql.append("FROM (  SELECT DISTINCT (DONG) DONG ");
    sql.append("FROM zipcode_t ");
    sql.append("WHERE sigu = ? ");
    sql.append("ORDER BY DONG ASC) ");
    try {
      con = dbMgr.getConnection();
      pstmt = con.prepareStatement(sql.toString());
      pstmt.setString(1, sigu);
      rs = pstmt.executeQuery();
      Vector<String> v = new Vector<>();
      while (rs.next()) {
        String dong = rs.getString("dong");
        v.add(dong);
      }
      dongs = new String[v.size()];
      v.copyInto(dongs);
    } catch (SQLException se) {
      System.out.println(se.toString());
      System.out.println(sql.toString());
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        dbMgr.freeConnection(con, pstmt, rs);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return dongs;
  }

  // 화면처리부
  public void initDisplay() {
    // 윈도우창 닫기 버튼 - 자원 회수하기
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    jp_north.add(jcb_zdo);
    jp_north.add(jcb_sigu);
    jp_north.add(jcb_dong);
    this.add("North", jp_north);
    this.setSize(630, 400);
    this.setVisible(true);
  }

  // 메인메소드
  public static void main(String[] args) {
    JFrame.setDefaultLookAndFeelDecorated(true);
    ZipCodeSearchView zcsv = new ZipCodeSearchView();
    zcsv.initDisplay();
  }

  @Override
  public void itemStateChanged(ItemEvent ie) {
    // 이벤트가 감지되는 소스 가져오기
    Object obj = ie.getSource();
    // ZDO 콤보박스인지 확인
    if (obj == jcb_zdo) {
      if (ie.getStateChange() == ItemEvent.SELECTED) {
        System.out.println("선택한 ZDO: " + zdos[jcb_zdo.getSelectedIndex()]);
        zdo = zdos[jcb_zdo.getSelectedIndex()];
        sigus = getSIGUList(zdo);
        // 대분류가 경정이 되었을 때 sigus를 초기화 해줘야 함
        // 기존에 디폴트로 전체 상수값을 널어두었으니 이것을 삭제하고
        // 새로운 DB서버에서 읽어온 값으로 아이템을 추가해야한다.
        jcb_sigu.removeAllItems();
        for (int i = 0; i < sigus.length; i++) {
          jcb_sigu.addItem(sigus[i]);
        }
      }

    } else if (obj == jcb_sigu) {
      if (ie.getStateChange() == ItemEvent.SELECTED) {
        System.out.println("선택한 SIGU: " + sigus[jcb_sigu.getSelectedIndex()]);
        sigu = sigus[jcb_sigu.getSelectedIndex()];
        dongs = getDONGList(sigu);
        jcb_dong.removeAllItems();
        for (int i = 0; i < dongs.length; i++) {
          jcb_dong.addItem(dongs[i]);
        }
      }
    } else if (obj == jcb_dong) {
      if (ie.getStateChange() == ItemEvent.SELECTED) {
        System.out.println("선택한 SIGU: " + dongs[jcb_dong.getSelectedIndex()]);
      }
    }
  }
}