국비지원 JAVA(자바) 프로그래밍 (데이터베이스, 오라클,SQL,SQLD, 정규화, 정규형)

qlplus / as sysdba cmd 창에서

 

 

1학년  성적표 ( 1반 ~ 10반) 

반별로 수학평균 구하고 싶음

수학점수가 80미만인건 제외하고 평균을 구함

평균이 80이상인것만 보고싶을 떄

 

select avg(수학)

from 성적표

where 수학점수>= 80 ( 데이터에 조건)

group by 반

  having avg(수학) >= 80(그룹에 조건)

order by 

 

---

EMP 테이블에서 직책별 인원수를 출력

SELECT JOB, COUNT(*) 
FROM EMP
GROUP BY JOB;

입사일을 이용해서 입사년도별 급여의 합계를 출력

SELECT TO_CHAR(HIREDATE,'YYYY'),SUM(SAL)
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY');

추가수당이있으면 O 없으면X로 처리해서

추가수당별 인원수를 출력

인원수를 기준으로 오름차순 정렬 

---

정규화/ 정규형 

 

정규화: 데이터베이스 불필요한 중복을 제거 하다보면이상현상이 생기게 되는데 이러한 경우를 방지하기 위한 것.

+추가설명: 공간낭비, 데이터 중복

갱신 이상, 삽입 이상, 삭제 이상 등을 방지하고 정규화를 함

 

정규형 :정규형은 위의 것이 완성된 상태를 정규형이라고 함! 

---

---

제1정규화

하나의 컬럼은 하나의 값만 가져야 한다( 원자값을 가진다)

구매현황 테이블			바지만 구매한 사람을 조회
고객번호	이름	상품	SELECT
1001	유재석	셔츠	WHERE 상품 ='바지'
1002	노홍철	바지	바지-> 청바지
1003	정준하	바지,반팔티	상품 = '청바지'
			상품 LIKE  '$\%바지%'

지금 위 테이블은 제1정규형을 만족하지 않는 테이블 1003 의 바지,반팔티 때문에

 

↓ 제1정규화 시키면 아래와 같이 제1정규형 테이블이됨. 

고객번호	이름	상품
1001	유재석	셔츠
1002	노홍철	바지
1003	정준하	바지
1003	정준하	반팔티

---

---

PRIMARY KEY: 기본키

각 레코드를 구별하는 유일한 필드( 중복 X . 빈칸 X)

COMPOSITE PRIMARY KEY 

하나로 구별 못해서 여러개로(2개이상) 으로 기본키 역할을함 

밑에서는 고객번호와 상품이 COMPOSITE PRIMARY KEY가 됨

 

고객번호	이름	상품	가격	회원등급
1001	유재석	셔츠	13000	일반
1002	노홍철	바지	25000	우수
1003	정준하	바지	25000	VIP
1003	정준하	반팔티	8000	VIP
1004	유재석	패딩	25000	일반

위테이블은 제1정규화가 된 제1정규형 테이블이긴 하나

데이터 수정할 때 여러번 작업을 해야해서 굉장히 비효율적임

 

 

제2정규화 작업하면 ↓

테이블에 관련없는(연관X)  컬럼을 다른테이블로 옮기는 것 

 

한 개 이상의 데이터(컬럼) 들로 구성된 후보키가 없으면  제2정규형 

PARTIAL DEPENDENCY를 제거한 테이블-> 제2정규형 

 

제2정규형 테이블들 ↓

 

<구매현황> 테이블 COMPOSITE PRIMAY 키 고객번호, 상품 

고객번호(FK)	상품(FK)
1001	셔츠
1002	바지
1003	바지
1004	반팔티

 

 

<고객> 테이블을 만듬 

고객번호(PK)	이름	회원등급
1001	유재석	일반
1002	노홍철	우수
1003	정준하	VIP

<상품>테이블 

상품(PK)	가격
셔츠	13000
바지	25000
반팔티	8000

---

 

---

---

학생번호	이름	전화번호	수강과목	담당강사
1001	유재석	010-1234-5678	JAVA	김자바
1001	유재석	010-1234-5678	SQL	이스큐
1002	노홍철	010-1111-2222	C	박씨
1003	정준하	010-3333-4444	JAVA	김자바
1003	정준하	010-3333-4444	C	박씨
1004	하하	010-5555-6666	SQL	이스큐
1005	길성준	010-8888-9999	HTML	최에치
1005	길성준	010-8888-9999	JAVA	김자바

제2정규화하기 ↓

 

학생테이블

학생번호(PK)	이름	전화번호
1001	유재석	010-1234-5678
1002	노홍철	010-1111-2222
1003	정준하	010-3333-4444
1004	하하	010-5555-6666
1005	길성준	010-8888-9999

수강과목테이블

수강과목(PK)	담당강사
JAVA	김자바
SQL	이스큐
C	박씨
HTML	최에치

 

수강정보테이블

학생번호(FK)	수강과목(FK)
1001	JAVA
1001	SQL
1002	C
1003	JAVA
1003	C
1004	SQL
1005	HTML
1005	JAVA

---

---

---

<도서대여내역>  2차정규화 전 

회원명	도서명	대여일자	회원등급	대여금액	반납여부
A	파친코	01월01일	일반	500	O
B	피노키오	01월02일	일반	300	X
C	코스모스	01월03일	우수	1000	O
D	난쏘공	01월03일	우수	800	X
B	해리포터	01월04일	일반	600	X

 

 

<회원테이블>

회원명	회원등급
A	일반
B	일반
C	우수
D	우수

 

<도서 테이블>

도서명	대여금액
파친코	500
피노키오	300
코스모스	1000
난쏘공	800
해리포터	600

<도서대여내역 테이블>

회원명	도서명	대여일자	반납여부
A	파친코	01월01일	O
B	피노키오	01월02일	X
C	코스모스	01월03일	O
D	난쏘공	01월03일	X
B	해리포터	01월04일	X

---

---

---

---

 

제3정규형

테이블이 제2정규형에 해당

기본키가 아닌 모든컬럼이 기본키에 이행적 함수 종속(전이의존성)이 되지않으면 

제3정규형테이블이 됨   

 

 

 

---

---

학생 테이블

학생번호(PK)	이름	전화번호
1001	유재석	010-1234-5678
1002	노홍철	010-1111-2222
1003	정준하	010-3333-4444
1004	하하	010-5555-6666
1005	길성준	010-8888-9999

과목 테이블

수강과목(PK)	강사코드(FK)
JAVA	G-001
SQL	G-002
C	G-003
C++	G-003
HTML	G-004
CSS	G-005

 

수강정보테이블

학생번호(FK)	수강과목(FK)
1001	JAVA
1001	SQL
1002	C
1003	JAVA
1003	C
1004	SQL
1005	HTML
1005	JAVA

 

강사테이블

강사코드(PK)	담당강사	학력
G-001	김자바	고졸
G-002	이스큐	대졸
G-003	박씨	고졸
G-004	최에치	대졸
G-005	김자바	대졸

강사코드를 추가함으로써 (김자바 대졸) 같은것을구별할수있게 됨

---

---

구매내역 테이블

아이디	이름	상품명	상품카테고리	구매수량	구매일자	가격	무료배송여부
qwer	유재석	청바지	하의	2	01월01일	8000	O
asd	노홍철	반바지	하의	1	01월02일	5000	O
zsc	정준하	반팔티	상의	3	01월02알	6000	X

상품카테고리는 상품명에 의해 결정됨

무료배송여부는 상품카테고리에의해 결정됨

가격은 상품1개의 가격임  정규화시키기 ↓

 

구매내역

아이디	상품명	구매수량	구매일자
qwer	청바지	2	01월01일
asd	반바지	1	01월02일
zsc	반팔티	3	01월02알

회원

아이디	이름
qwer	유재석
asd	노홍철
zsc	정준하

 

상품

상품명	상품카테고리	가격
청바지	하의	8000
반바지	하의	5000
반팔티	상의	6000

 

상품카테고리

상품카테고리	무료배송여부
하의	O
상의	x

---

---

집합 연산자 

여러개의 select를 하나로 연결시켜주는 기능

 

UNION  합집합 중복은 알아서 걸러줌 (컬럼명은 일치해야함)

 

UNION ALL은 중복된거 상관없이 모든 데이터 다합쳐서 보여줌

 

INTERSECT 교집합 (중복제거)  (컬럼명도 일치해야함) 

 

MINUS 차집합 

---

JOIN 

여러개의 테이블을 하나로 합쳐서 출력 (옆으로 붙는다고 생각하면됨)

 

테이블은 다르지만 같은 컬럼이름의 내용을 비교할때

SELECT * FROM EMP,DEPT

WHERE EMP.DEPTNO =DEPT.DEPTNO; 

 

 

INNER JON ( ON이 WHERE같은 역할을 함) 

SELECT EMP.JOB, EMP.DEPTNO, DEPT.DNAME
FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO= DEPT.DEPTNO
WHERE EMP.DEPTNO=20;

위처럼 사용 

 

OUTER JOIN ( RIGHT OUTER JOIN, LEFT OUTER JOIN) 

 

RIGHT OUTER JOIN 이나 LEFT OUTER JOIN에 ON을 사용하면 ON 을 먼저 생각하고(다나오게하고)

나머지거는 추가하는 느낌 

 

FULL OUTER JOIN 다 나오는 것

 

CREATE TABLE 구매현황(
   고객번호 NUMBER(4),
   상품 VARCHAR2(50)
);

CREATE TABLE 고객(
   고객번호 NUMBER(4),
   이름 VARCHAR2(50),
   회원등급 VARCHAR2(50)
);

INSERT INTO 구매현황 VALUES(1001, '셔츠');
INSERT INTO 구매현황 VALUES(1002, '바지');
INSERT INTO 구매현황 VALUES(1003, '반팔티');
INSERT INTO 구매현황 VALUES(9999, '악세사리');

INSERT INTO 고객 VALUES(1001, '유재석', '일반');
INSERT INTO 고객 VALUES(1002, '노홍철', '우수');
INSERT INTO 고객 VALUES(1003, '정준하', 'VIP');
INSERT INTO 고객 VALUES(5555, '하동훈', '일반');

--테이블 생성--

SELECT 고객."고객번호", 고객."회원등급", 구매현황."상품"
FROM 고객 INNER JOIN 구매현황
ON 고객."고객번호" = 구매현황."고객번호";



SELECT 고객."고객번호",
        고객."이름",
        고객."회원등급",
        구매현황."상품"
        FROM 고객 LEFT OUTER JOIN 구매현황
        on 구매현황."고객번호" =  고객."고객번호"
WHERE 구매현황."상품" IS NULL;


SELECT 고객."고객번호",
        고객."이름",
        고객."회원등급",
        구매현황."상품"
        from 고객 RIGHT OUTER JOIN 구매현황
        on 구매현황."고객번호" =  고객."고객번호"
        WHERE 고객."고객번호" IS NULL;


SELECT 고객."고객번호",
        고객."이름",
        고객."회원등급",
        구매현황."상품"
        from 고객 FULL OUTER JOIN 구매현황
        on 구매현황."고객번호" =  고객."고객번호"

---

서브쿼리

SQL문 안에 SELECT를 작성 

 

서브쿼리는 반드시 괄호안에 작성해야함

(SELECT~~FROM~~~WHERE~~)

 

 

 

SELECT DNAME FROM DEPT WHERE ???? 

                                                                 DEPTNO=10

 

1.EMP테이블에서 이름이 KING인 부서번호를검색

2.DEPT 테이블에 부서번호랑 KING인 부서번호가 같은 것을 찾아라

SELECT DNAME
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='KING');

EMP테이블에서 급여가 이름이 CLARK인 급여보다 큰사람들의 모든데이터출력

 

SELECT *
FROM EMP
WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='CLARK');

BLAKE보다 먼저 입사람들의 레코드를 조회 

SELECT *
    FROM EMP
WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME='BLAKE');

급여가 총급여평균보다 큰사람들의 레코드를 조회 

SELECT*
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);

위의 것들은 전부 단일행 서브 쿼리 (서브쿼리의 결과물이 1개만나옴) 

단일행 서브쿼리 > <=  >= <= !=

다중행 서브쿼리 > <= >= >= <= != 로 비교가 안됨