Database D+4

database D+4

## 서브쿼리

=> SQL 내부에 있는 또다른 쿼리

=> 소괄호 ( 서브쿼리 ) 감싼다

=> 서브쿼리안에 또 서브쿼리가 올 수 있다

=> 메인쿼리안에 여러개의 서버쿼리가 올 수 있다

=> 수행순서 : 서브쿼리(내부쿼리) 실행 -> 메인쿼리(외부쿼리) 실행

=> 서브쿼리의 리턴값이 NULL 이면 결과값도 NULL 이다

=> 사용위치 :

- SELECT 절 -> 함수로 구현하여 제공하는 추세

- WHERE 절 -> 조건식의 우항(오른쪽)

- FROM 절 -> IN-LINE 뷰

 

-- Chen 보다 많은 급여를 받는 사원의 이름과 급여

 

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY > ( SELECT SALARY

FROM EMP

WHERE LAST_NAME ='Chen' ) ;

 

-- 부서가 101번 사원과 같고, 급여이 141번 사원보다 많은 사원의 이름과 급여

 

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY > ( SELECT SALARY

 

FROM EMP

WHERE EMPLOYEE_ID =141 )

and DEPARTMENT_ID = ( SELECT DEPARTMENT_ID

FROM EMP

WHERE EMPLOYEE_ID = 101 ) ;

 

-- 우리회사에서 가장 월급을 많이 받는 사원의 이름과 급여

 

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY = ( SELECT max(SALARY ) FROM EMP ) ;

 

-- 부서별 최저급여를 검색, 단, 60번 부서의 최저급여보다 큰값만 검색

 

select department_id, min(salary) as 최저급여

from emp

group by department_id

having min(salary) > ( select min(salary)

from emp

where department_id = 60 ) ;

 

 

==============================================

 

-- 부서별 최저급여를 검색, 단, 60번 부서의 급여보다 큰값만 검색

 

select department_id, min(salary) as 최저급여

from emp

group by department_id

having min(salary) > ( select salary

from emp

where department_id = 60 ) ;

 

==> 오류발생 : single-row subquery returns more than one row

 

## 단일행 비교연산자

-- >, <,>=, <=, !=, <>

 

## 다중행 비교연산자

-- in, any, all

-- in , 단일행비교연산자 any, 단일행비교연산자 all

-- >= any , > any , <= all, < all , in

 

## any

-- 서브쿼리의 리턴값중 아무것 하나하고 만족

 

## all

-- 서브쿼리의 리턴값 모두하고 만족

 

-- 부서별 최고급여와 같은 급여를 받는 사원의 이름과 급여

 

select last_name, salary

from emp

where salary in ( select max(salary)

from emp

group by department_id )

 

-- IT_PROG 직군의 어떤보다도 급여를 많이 받는 사원

 

select last_name, salary

from emp

where salary > all ( select salary

from emp

where job_id = 'IT_PROG' )

 

===============================================

 

## TOP-N 알고리즘

 

-- FROM 절에 사용되는 서브쿼리

-- INLINE VIEW

-- SQL 실행시 잠깐 생성되었다가 사라지는 임시테이블

-- ROWNUM : 의사컬럼, 없지만 늘 사용가능한 컬럼 , 줄번호

 

 

 

SELECT LAST_NAME, SALARY

FROM EMP

WHERE SALARY >= 10000 ;

 

-- 급여를 많이 받는 10명의 명단

 

SELECT LAST_NAME, SALARY

FROM ( SELECT LAST_NAME, SALARY

FROM EMP

ORDER BY SALARY DESC )

WHERE ROWNUM <= 10 ;

 

-- 급여를 적게 받는 10명의 명단

 

SELECT LAST_NAME, SALARY

FROM ( SELECT LAST_NAME, SALARY

FROM EMP

ORDER BY SALARY )

WHERE ROWNUM <= 10 ;

 

===============================================

상품(상품번호, 상품명, 상품가격..... )

 

구매(구매번호, 구매자, 상품번호, 구매갯수, 구매금액, 구매날짜 )

 

1. 2020년 하반기 1000개 이상 팔린 상품번호과 총판매갯수

 

SELECT 상품번호, SUM(구매갯수) AS 총판매갯수

FROM 구매

WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'

GROUP BY 상품번호

HAVING SUM(구매갯수) >= 1000

ORDER BY 2 DESC ;

 

-- 베스트 상품 10개 검색

 

SELECT 상품번호, 총판매갯수

FROM ( SELECT 상품번호, SUM(구매갯수) AS 총판매갯수

FROM 구매

WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'

GROUP BY 상품번호

ORDER BY 2 DESC )

WHERE ROWNUM <= 10 ;

 

-- 베스트 상품 10 검색

-- 상품명, 총판매갯수

 

SELECT 상품명, 총판매갯수

FROM 상품 JOIN ( SELECT 상품번호, 총판매갯수

FROM ( SELECT 상품번호, SUM(구매갯수) AS 총판매갯수

FROM 구매

WHERE 구매날짜 BETWEEN '2020-07-01' AND '2020-12-31'

GROUP BY 상품번호

ORDER BY 2 DESC )

WHERE ROWNUM <= 10 )

USING ( 상품번호 ) ;

 

2. 2020년 베스트상품 10개 검색 ( 상품번호, 총판매갯수 )

 

SELECT ROWNUM AS 순위, 상품명, 총판매갯수

FROM 상품 JOIN ( SELECT 상품번호, 총판매갯수

FROM ( SELECT 상품번호, SUM(구매갯수) AS 총판매갯수

FROM 구매

WHERE TO_CHAR(구매날짜, 'YYYY') = '2020'

GROUP BY 상품번호

ORDER BY 2 DESC )

WHERE ROWNUM <= 10 )

USING ( 상품번호 ) ;

 

---------------------------------------------------------------------------------------------------------------------

## DML

 

 

ST_DEPT ( DEPT_NO, DEPT_NAME)
ST( S_NO, NAME, BIRTH, DEPT_NO )

CREATE TABLE ST_DEPT (
  DEPT_NO    NUMBER(2)    PRIMARY KEY,
   DEPT_NAME VARCHAR2(50)  NOT NULL
);

CREATE TABLE ST (
   S_NO      CHAR(8)            PRIMARY KEY,
   NAME     VARCHAR2(30)   NOT NULL,
   BIRTH     DATE,
   DEPT_NO NUMBER(2) REFERENCES ST_DEPT (DEPT_NO )
);


## 자료입력
## ST_DEPT
## INSERT
-- 컬럼리스트의 수 와 값리스트의 수 일치 -> 문자는 문자 숫자는 숫자
-- 컬럼의 데이터타입과 값의 데이터 타입 일치
--INSERT INTO 테이블명 (컬럼명, .....) VALUES (값1, .....) ;

INSERT INTO ST_DEPT (DEPT_NO, DEPT_NAME ) VALUES ( 10, '국문과') ;        /*  DEPT_NO 숫자타입이니 벨류 값에 숫자 */

INSERT INTO ST_DEPT VALUES ( 20, '영문과' ) ; ( 이처럼 생략도 가능)
INSERT INTO ST_DEPT VALUES ( 30, '수학과' ) ;
INSERT INTO ST_DEPT VALUES ( 40, '멀티과' ) ;

INSERT INTO ST(S_NO, NAME, BIRTH, DEPT_NO)
VALUES ('20151574', '이동진', '1996-03-12', 10 ) ;


INSERT INTO ST
VALUES ('20151478', '김리신', '1996-03-12', 20 ) ;


학번, 이름 , 학과번화 입력 
-- 생일 컬럼 생략하고 입력 
-- 생략할려고 입력할려면 NOTNULL 제약조건이 아니면 가능
INSERT INTO ST (S_NO, NAME, BIRTH, DEPT_NO)
VALUES ('20202222', '아이유', 30) ;


INSERT INTO ST
VALUES ('20145151', '김준리', NULL, 20 ) ; 

## 다른 테이블에서 자료를 읽어서 아주 많이 입력하는 방법

INSERT INTO ST 
  SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE, DEPARTMENT_ID
  FROM EMP
  WHERE DEPARTMENT_ID IN (10, 20, 30, 40) ;


========================
## UPDATE
## row 의 특정 컬럼의 값을 변경


1.지정한 컬럼에 원하는 값을 지정 => 모든 row 값이 변경
2.조건에 맞는 ROW에 대해서 지정한 컬럼에 원하는 값을 지정 => 일반적 사용법
3.WHERE 절 조건 컬럼은 유니크 컬럼으로 하는 것이 좋다.

--수지의 부서번호를 30으로 변경

UPDATE ST
SET DEPT_NO = 30;
=> 모든 ROW 값이 변경 되서 쓰면 X

UPDATE ST
SET DEPT_NO = 30;
WHERE NAME = '수지'
=> 수지의 부서번호를 30으로 변경

UPDATE ST
SET DEPT_NO = 30 , BIRTH = '2000-10-10'
WHERE NAME = '수지';
=> 수지의 부서번호를 30으로 변경


--수지의 학과번호를 송중기와 같게 변경하시오

UPDATE ST
SET DEPT_NO = (SELECT DEPT_NO FROM ST WHERE NAME = '송중기')
WHERE NAME = '수지' ;



##ROW 삭제
## DELETE

1. 모든 ROW 삭제
2. 조건에 맞는 ROW만 삭제 => ***

DELETE ST
DELETE FROM ST;
=> 테이블에 INSERT 한 모든 자료가 삭제 !! 주의

DELETE ST
WHERE BIRTH < '2005-01-01' ;

=========================================================

## DML 작업이 안되는 경우

 INSERT 경우
ST_DEPT 테이블에 INSERT 작업 => OK
ST 테이블에 INSERT 작업 => 항상 되는 것이 아니다.

##UPDATE
ST_DEPT 테이블에 UPDATE 작업 => 항상되는 것은 아니다

UPDATE ST_DEPT
SET DEPT_NO = 35
WHERE DEPT_NAEM = '수학과'  ;

=> violated - child record found 오류발생!!
=> 자식테이블 (ST) DEPT_NO 컬럼에서 30 없앤다. -> NULL
     -> ST_DEPT DEPT_NO 30 -> 35 수정
     -> ST_DEPT DEPT_NO NULL -> 35

NULL로 바꾼다음에 다시 넣어준다

수정할려면
UPDATE ST
SET DEPT_NO = NULL
WHERE DEPT_NO = 30 ;


UPDATE ST
SET DEPT_NO = 35
WHERE DEPT_NO = 30 ;

UPDATE ST
SET DEPT_NO = 35
WHERE DEPT_NO IS NULL 

##DELETE
ST_DEPT 테이블에 DELETE 작업 => 항상되는 것은 아니다

DELETE ST_DEPT
WHERE DEPT_NO=35; -> 레퍼런스에 걸려서 실행 X