Day17_데이터 딕셔너리, JOIN, SUBQUERY, Java-OracleDB 연동(try-catch)

2023.09.05

 

Day17 세 줄 요약

- 데이터 딕셔너리를 통해 테이블 간 PARENT-CHILD 관계를 도식화 해 나타낼 수 있다.

- 두 개 이상의 TABLE을 JOIN을 통해 하나로 연결할 수 있다.

- SELECT문의 결과를 하나의 조회 조건과 같이 사용하는 것을 '서브쿼리'라 한다.

 

---

 

데이터 딕셔너리

 

DATA DICTIONARY

각 TABLE간의 관계(PARENT-CHILD) 혹은 제약 정를 도식화 해서 나타낼 수 있음

USER 선택

TABLE 선택

EMP, DEPT TABLE 병합 결과 (화살표를 받는 쪽이 PARENT)

 

---

 

테이블 JOIN

 

JOIN

두 개 이상의 TABLE을 연결하여 하나의 TABLE처럼 사용하는 것

보통 PK-FK로 JOIN

     - PK 제약을 걸면 그 값은 중복되지 않고 null이 아닌 값으로 참조할 data로 매우 적합

     - ...FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

 

INNER JOIN

두 테이블의 컬럼 값이 같을 때(공통된 컬럼, 컬럼의 이름은 무관)

-- EMP와 DEPT의 'DEPTNO'를 서로 매칭 시킨 후 조회
SELECT * FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

중복을 피하기 위해 자동으로 'DEPTNO_1'로 새롭게 NAMING

/*
	INNER JOIN EXAMPLES
*/
-- Q1. 사원번호, 사원이름, 부서번호, 부서이름, 부서위치를 조회
-- 두 TABLE에 모두 포함된 'DEPTNO' 컬럼은 이름 앞에 E. 혹은 D.을 붙여야 함
SELECT EMPNO AS 사원번호, ENAME AS 사원이름, E.DEPTNO AS 부서번호, DNAME AS 부서이름, LOC AS 부서위치
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- Q2. MILLER 사원은 어떤 부서에서 근무하고 있는가?
SELECT ENAME AS 사원이름, DNAME AS 부서이름
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND ENAME = 'MILLER';
-- Q3. TURNER 사원은 어디 지역에서 근무하고 있는가?
SELECT ENAME AS 사원이름, LOC AS 근무지역
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND ENAME = 'TURNER';
-- Q4. MARTIN 사원의 이름, 급여, 급여등급을 표시하시오
SELECT E.ENAME AS 사원이름, E.SAL AS 급여, S.GRADE AS 급여등급 
FROM EMP E, SALGRADE S
WHERE E.ENAME = 'MARTIN' AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

Q1

Q2

Q3

Q4

 

---

 

SUBQUERY

 

서브쿼리

SQL문을 실행하는데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 SELECT문을 사용하는 것

/*
	서브쿼리 특징
    1) 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이며, 괄호()로 묶어서 사용한다.
    2) 특정한 몇 몇 경우를 제외하고 ORDER BY절을 사용할 수 없다.
    3) 서브쿼리의 SELECT 절에 명시된 컬럼은 메인쿼리의 비교대상과 같은 TYPE, 개수여야 한다.
*/

-- Q1. EMP TABLE에서 사원이름이 JONES인 사원의 급여를 출력 (이름, 급여)
SELECT ENAME AS 사원이름, SAL AS 급여 FROM EMP WHERE ENAME = 'JONES';
-- Q2. 2975보다 많은 급여를 받는 사원정보 조회
SELECT * FROM EMP WHERE SAL > 2975;
-- Q3. JONES보다(=2975보다) 많은 급여를 받는 사원정보 조회 (SELECT안에 SELECT)
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');

/*
	(SELECT SAL FROM EMP WHERE ENAME = 'JONES') = 2975
*/

Q2와 Q3의 결과가 서로 같음

-- Q4. 받는 급여가 평균 급여 이상인 사원의 정보를 조회
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
-- Q5. 각 부서별 최고급여와 동일한 급여를 받는 사원의 정보를 조회 (IN 사용)
SELECT * FROM EMP WHERE SAL IN ((SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO));

Q4

Q5

 

---

 

Java - OracleDB 연동

 

Libraries Path 설정

C:\app\user\product\21c\dbhomeXE\jdbc\lib

 

 

DBConnection class 생성

package JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {
	// import가 필요한 객체를 갖는 메소드 이름 앞에 'static'을 붙이면 import 없이 메소드 명만 가지고 해당 객체 사용가능
	public static Connection DBConnect() {
		
		// DB에 접속정보를 저장하기 위한 Connection type의 객체 'con' 선언
		Connection con = null;
		
		// DB에 접속할 계정 정보
		String user = "ICIA";		// DB USER ID
		String password = "1111";	// DB USER PW
		
		// DB에 접속할 주소
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		
//		try-catch (switch-case와 비슷한 개념)
//		try문 실행 중 발생할 수 있는 오류들을 catch문에 미리 정리
//		catch (A) {		-> A라는 오류 발생시 해당 catch문 실행
//		...				
//		 }
		try {
			// ojdbc8.jar 파일을 현재 프로젝트에 적용
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 오라클 데이터베이스에 접속하는 계정정보 등록
			con = DriverManager.getConnection(url, user, password);		// 변수로 받지 않고 바로 입력해도 무관
			System.out.println("DB 접속 성공!");
		} catch (ClassNotFoundException ce) {
			// ojdbc8.jar 파일이 존재하지 않을 경우(오라클 드라이버를 찾지 못 할 경우) 오류가 발생한 경로를 찾아줌
			ce.printStackTrace();
			System.out.println("DB 접속 실패...드라이버 로딩 실패...");
		} catch (SQLException se) {
        	// 오라클 계정정보(user, pw, url) 오류
			se.printStackTrace();
			System.out.println("DB 접속 실패...DB 접속 정보 오류...");
		}		
		return con;
	}
}