201012 Oracle

테이블이 분할됨 - 정규화가 일어났다

여러테이블에 분할ㅇ되어있는거를 취합해서 꺼내자 > join

join이 많이 발생되어지면 성능에 문제가 있다.

 

select 의 가장 고정적인 form은 select from where 임 where는 물론생략가능

select절과 from절이 필수불가결한 절 두개임

 

select절에 나와있는건 결과임 결과중에 세로의 컬럼임 필요한 열을 선택하는 것

where 다음엔 조건이 나옴. 이 조건에 의해서 취사선택되는건 행임  필요한행을 선택하는것

where절이 생략되어지면 그 테이블의 모든 행이 나온다는 말

 

PK는 NULL 허용하지 않는다. / 중복이 되어선 안된다(그 테이블에서 유일한 값이어야함)

~LGU는 대분류. 분류코드. 

 

정규화가 능사는 아님. 정규화를 안하고 데이터가 중복되었을때 데이터를 더 빨리찾을수있다면 다시 반정규화하게됨

반정규화는 정규화안한거는 할수없음. 정규화끝난것만 반정규화 가능

 

자주쓰게될 테이블 보기

BUYER 테이블

LPROD_Id : 순번

LPROD_GU = BUYER_LGU

 

 

BUYPROD : 매입한 데이터 저장하는 테이블

-BUY_DATE : 매입일자

-BUY_PROD : 상품코드 >>PROD테이블에있는걸 끌어다씀

-BUY_QTY : ~QTY는 어디에서건 수량을 말함

금액은 매입, 판매, 할인가격 이렇게 크게 세가지가 나옴

-BUY_COST : 매입가

 

CART테이블

CART_MEMBER : MEMEER테이블 MEM_ID에서 가져옴

CART_NO: CART에 부여된 번호 (오늘사고 내일사면 번호가 바뀜. 한번에 여러물건 사면 카트번호는 같다)

CART_NO와 CART_PROD를결합해서 기본키

CART_QTY : 구매수량

 

>>수량은 있는데 단가가없으므로 물건가격 계산못함

 

LPROD 구분코드

LPROD ID:  순번

 

여기는 회원제니까 회원번호 MEM_ID (CART의 CART_MEMBER와 같아)

 

MEMBER테이블

MEM_MILEAGE : 마일리지

 

PROD

PROD LGU : 다른테이블꺼

PROD BUYER : 이물건을 거래한 거래처코드

PROD TOTAL STOCK : 재고량

 

---

 

SELECT에 테이블이 없으면 어케함?

SELECT 2753*2753 FROM 테이블이 와야함

이럴때 컴터가 제공해주는 가상의 테이블인 DUAL이 있음

DUAL은 시스템이 제공해주는 가상의 테이블

SELECT 2753*2753 FROM DUAL; <실행됨

 

SELECT SYSDATE FROM DUAL;

 

 

---

 

연산자 이어서

 

관계연산자 : 6가지 (> , < , = , <= , >=, != (또는 <>)) : 대소관계(크기)를 비교해주는 연산자

**크다의 반대는 작거나 같다!

크다 작다 같다 세개니까 하나의 반대는 나머지 두개인거임

-조건문에 주로 사용됨

 

ex 사원테이블(EMPLOYEES)에서 급여가 10000이상인 사원을 조회하시오 (ALIAS는 사원번호, 사원명, 부서코드, 급여), 부서코드순으로 출력하시오

  >>모든 사원을 조회하는게 아님. '급여가 5000이상' -> 조건

 

SELECT EMPLOYEE_ID AS 사원번호, 
        EMP_NAME AS 사원명, 
        DEPARTMENT_ID AS 부서코드, 
        SALARY AS 급여 
FROM EMPLOYEES 
WHERE SALARY>=10000
ORDER BY DEPARTMENT_ID;

ASC는 생략가능 AS도 생략가능

 

**SELECT문은 FROM절이 일빠, WHERE절 이하가 이빠 SELECT가 맨 마지막이므로

WHERE에 SELCECT에서 정해준 별칭을 쓰면 안된다.

 

만약 급여가 많은 사람부터?

ORDER BY SALARY DESC;

 

이렇게 하면 됨

이때 SALARY 쓰는 대신에

SELECT EMPLOYEE_ID AS 사원번호, 
        EMP_NAME AS 사원명, 
        DEPARTMENT_ID AS 부서코드, 
        SALARY AS 급여 

SALARY의 순번인 4를 써도 됨

 

급여가 많은 사람부터 출력하되 부서코드 순으로 출력하십시오

ORDER BY SALARY DESC, DEPARTMENT_ID;

>>급여많은순 (1차정렬) 그안에서 부서코드 오름차순으로(2차정렬)하라는 말임

급여가 같으면 부서코드가 오름차순정렬되어있음

EX )

사원테이블 (EMPLOYEES)에서 사원들의 보너스를 계산하여 원 지급액을 조회하시오

보너스 = 급여 * 영업실적코드 (COMMISION_PCT)

지급액 = 급여 + 보너스

단, ALIAS는 사원번호, 사원명, 급여, 영업실적, (까지는 테이블에 있음 )보너스, 지급액 (이 두개는 계산해줘야 하는)

 

SELECT EMPLOYEE_ID 사원번호,
        EMP_NAME 사원명,
        SALARY 급여,
        COMMISSION_PCT 영업실적,
        SALARY*COMMISSION_PCT 보너스,
        SALARY+(SALARY*COMMISSION_PCT) 지급액
        FROM EMPLOYEES;

보너스가 없으면 급여가 지급액이 되어야하는데  NULL이 된다?

영업실적이 있으면 제대로 계산이 되고, 영업실적이 NULL이면 급여곱하니까 (어떤숫자에 NULL을 곱하거나 더해도 결과는 NULL)NULL이 돼벌임

 

이러한 경우를 없애려고 NVL >널처리함수임

NVL(SALARY*COMMISSION_PCT,0) 보너스,
        SALARY+ NVL((SALARY*COMMISSION_PCT),0) 지급액

 

 

예 ) 회원테이블 (MEMBER)에서 나이가 50대 회원 중 마일리지가 4000이상인 회원정보를 조회하시오 

  (나이는 주민번호를 이용)

 >> 조건이 두개 (나이,마일리지)

  단, ALIAS는 회원번호, 회원명, 나이, 마일리지

 

문자열을 뽑아서 숫자로 바꿔줘야함

EXTRACT (YEAR/MONTH/DAY FROM SYSDATE) <날짜 추출하는 함수. 숫자다

 

SUBSTR(MEM_REGNO1,1,2) << 문자열에서 1번글자부터 2글자 떼어넴 << 떼어내도 STRING이다.

TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))  숫자로 바꿔줌

여기에 +1900더해줌

TO_NUMBER(SUBSTR(MEM_REGNO1,1,2)) + 1900  >>숫+숫이니까 결과도 숫자

 

SELECT MEM_ID 회원번호, 
       MEM_NAME 회원명, 
       EXTRACT(YEAR FROM SYSDATE)- 
       (TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))+1900) 나이, 
       MEM_MILEAGE 마일리지 
       FROM MEMBER 
       WHERE EXTRACT(YEAR FROM SYSDATE)- 
       (TO_NUMBER(SUBSTR(MEM_REGNO1,1,2))+1900) 
       BETWEEN 50 AND 59 
       AND MEM_MILEAGE>=2000;       
      

 

 

 

 

EX

회원테이블에서 회원들의 거주지의 종류를 조회하시오

거주지는 광역시도이름이다.

ALIAS는 거주지명

 

MEM_ADD1에서 거주지를 뽑아내야함 여기서 앞에 두글자 떼어내면 거주지 (서울 대전 충남 ...)

 

SELECT SUBSTR(MEM_ADD1,1,2) 거주지명
FROM MEMBER;

이런식으로 나옴 근데 나는 중복된값을 원하지 않음

중복을 거르는 >> DISTINCT

SELECT DISTINCT SUBSTR(MEM_ADD1,1,2) 거주지명 FROM MEMBER;

 

회원테이블에서 회원들의 거주지의 종류와 회원수를 조회하시오

거주지는 광역시도이름이다.

ALIAS는 거주지명, 회원수

 

이떄는 DISTINCT가 불필요

SELECT SUBSTR(MEM_ADD1,1,2) 거주지명,
    COUNT(*) AS 회원수
FROM MEMBER
GROUP BY SUBSTR(MEM_ADD1,1,2)
ORDER BY 2 DESC;

주소의 앞에 두글자가 같은사람들끼리 GROUP BY해서 회원수가 많은순부터 ORDER해서 나타냄 (DISTINCT필용벗음)

 

 

---

 

 

관계계산자

상품테이블(PROD)에서 판매가격이 10만원 이상인 상품을 조회하시오

ALIAS는 상품코드, 상품명, 판매가, 거래처코드, 마일리지

 

일단 지문에서 조건이 뭔지를 판단해야함 : 판매가격 >=100000

조건은 WHERE절에 

 

조건은 반드시 값과 변수가 와야함

값은 10만 이상 변수는 판매가격을 가지고있는 컬럼명

 

SELECT  PROD_ID 상품코드, 
        PROD_NAME 상품명, 
        PROD_PRICE 판매가, 
        PROD_BUYER 거래처코드, 
        PROD_MILEAGE 마일리지 
FROM PROD
WHERE PROD_PRICE>=100000;

 

 

 

마일리지가 NULL이니까 바꿔주자

 

상품테이블에서 상품별 마일리지를 설정하시오

있는값을 변경 >>> INSERT가 아니라 UPDATE

상품별 마일리지를 판매가격의 0.1%로 한다.

(마일리지는 소숫점이 없어야하고, 주는거니까 반올림해도된다 모든상품에 대해 마일리지적용할거임(WHERE절X))

 

일단

상품별 마일리지 조회

SELECT PROD_NAME, PROD_MILEAGE FROM PROD;

 

이제 상품별 마일리지 변경

UPDATE PROD 
SET PROD_MILEAGE=ROUND(PROD_PRICE*0.001);

확인해보자

SELECT PROD_NAME, PROD_MILEAGE, PROD_PRICE FROM PROD;

 

EX

상품테이블에서 판매가격이 10~20만원 사이인 P101분류에 속한 제품읇 분류하시오

ALIAS는 상품코드. 상품명, 분류코드. 판매가격

 

P101이기때문에 ' '써줘야하고, 대솜누자도 확실히 구분해야함

SELECT PROD_ID 상품코드,
        PROD_NAME 상품명, 
        PROD_LGU 분류코드, 
        PROD_PRICE 판매가격
FROM PROD
WHERE PROD_PRICE >= 500000 AND PROD_PRICE <= 1000000 
AND LOWER(PROD_LGU)='p102';