[수업 D-15] SQL SUBQUERY의 종류, VIEW 파일

2020.07.30(금) 3주차 5일

 

SQL은 시험을 총 두 번 볼 예정이다.  

 

일단 다음주 목 기초 한 번 

 

---

1. SUBQUERY  : QUERY안에 QUERY 
 

(1) 박치기 직원과 직급이 같은 직원들 출력

1) DB서버에 두번갔다옴. DB서버는 바쁜데 이러면 안됨 select jikwon_jik  FROM jikwon WHERE jikwon_name = '박치기';  SELECT * FROM jikwon WHERE jikwon_jik = '사원';    2) SUBQUERY : 안쪽의 QUERY의 결과를 바깥쪽에 넘겨주는,  SELECT * FROM jikwon WHERE jikwon_jik=(select jikwon_jik  FROM jikwon WHERE jikwon_name = '박치기');

  (2) 직급이 대리 중 가장 먼저 입사한 직원은 ?

SELECT * FROM jikwon WHERE jikwon_ibsail=(SELECT MIN(jikwon_ibsail) FROM jikwon WHERE jikwon_jik='대리');  안의 쿼리는 2013-02-05 을 넘겨준거다. 잘못된 서브쿼리!!!  SELECT * FROM jikwon WHERE jikwon_jik='대리' AND jikwon_ibsail =  (SELECT MIN(jikwon_ibsail) FROM jikwon WHERE jikwon_jik='대리'); 이렇게 풀어야 한다.

 (3) 인천에 근무하는 직원 출력 (=대신 in사용가능)

SELECT * FROM jikwon WHERE buser_num IN  (SELECT buser_no FROM buser where buser_loc = '인천');

 (4)  '최부자'고객님 담당 직원이 관리하는 다른 고객들을 출력 

※ * 은 칼럼명을 쓰는걸 추천한다. 지금은 배우니까 쓰는데 안 좋은 작업방식

SELECT * FROM gogek WHERE gogek_damsano =  (SELECT gogek_damsano FROM gogek WHERE gogek_name = '최부자');

 (5) 똑같은 내용을 다양한 방식으로 출력할 수 있다. 

-- 총무부 직원들이 관리하는 고객 출력 -- 1) SUBQUERY SELECT gogek_no, gogek_name, gogek_tel FROM gogek WHERE gogek_damsano IN (SELECT jikwon_no FROM jikwon  WHERE buser_num = (SELECT buser_no FROM buser WHERE buser_name = '총무부')); -- 2) JOIN SELECT gogek_no, gogek_name, gogek_tel FROM gogek INNER JOIN jikwon ON jikwon_no = gogek_damsano INNER JOIN buser ON buser_num = buser_no WHERE buser_name = '총무부';

 

2. 문제 풀이  
문제 4,  문제6(하긴했는데 너무 길다),  --(다중 조인같은 느낌)-- 문제9, 문제10 

JOIN을 써야한다는 느낌은 있었는데 아직 어떻게 써야할 지 감이 안 온다. 

3. ANY, ALL

  ANY ALL 연산자 : null 인 자료는 제외하고 작업함
     <ANY : subquery 반환값 중 최소값보다 작은 ~
     >ANY : subquery 반환값 중 최소값보다 큰 ~
     <ALL : subquery 반환값 중 최대값보다 작은 ~
     >ALL : subquery 반환값 중 최대값보다 큰 ~

-- 대리들 연봉의 최대값보다 작은 연봉을 받는 직원은? SELECT jikwon_no, jikwon_name, jikwon_pay FROM jikwon  WHERE jikwon_pay <ANY (select jikwon_pay FROM jikwon WHERE jikwon_jik = '대리'); --30번 부서의 최고 급여자보다 급여를 많이 받는 직원은? SELECT jikwon_no, jikwon_name, jikwon_pay FROM jikwon  WHERE jikwon_pay >ALL (select jikwon_pay FROM jikwon WHERE buser_num = 30);

4.  FROM 앞에 SUBQUERY (이때부터 이해가 안가고 ,,,, 머리가 아프고,,,집에 가고 싶고,,, )

-- 전체 평균 연봉과 최대 연봉 사이의 연봉을 받는 직원은?  SELECT jikwon_no, jikwon_name, jikwon_pay FROM jikwon a,  (SELECT AVG(jikwon_pay) avgs, MAX(jikwon_pay) maxs FROM jikwon) b WHERE a.jikwon_pay  BETWEEN b.avgs AND b.maxs;

5. group by의 having 절에 SUBQUERY

-- 부서별 평균 연봉 중 30번 부서의 연봉보다 큰 자료 출력 SELECT buser_num, AVG(jikwon_pay) FROM jikwon WHERE buser_num IS NOT null GROUP BY buser_num  HAVING AVG(jikwon_pay) > (SELECT AVG(jikwon_pay) FROM jikwon WHERE buser_num =30);

6. exists 연산자 

-- 직원이 없는 부서 출력  SELECT buser_name, buser_loc FROM buser bu  WHERE NOT EXISTS (SELECT 'imsi' FROM jikwon WHERE buser_num = bu.buser_no);

7. (이건 시험용. 실무에선 안쓰임) 상관서브 쿼리 : 안쪽 쿼리 에서  바깥쪽 쿼리에서 참조하고, 다시 바깥쪽 쿼리가  그  안쪽의 결과를  참조 

-- 급여 순위 3위 이내의 자료 출력 (내림차순) SELECT a.jikwon_name, a.jikwon_pay FROM jikwon a  WHERE 3 > (SELECT COUNT(*) FROM jikwon b WHERE b.jikwon_pay > a.jikwon_pay) AND buser_num IS NOT null ORDER BY jikwon_pay DESC;

8. --create table,  INSERT : SUBQUERY 

CREATE TABLE sa1 AS SELECT * FROM jikwon; DESC sa1;  SELECT * FROM sa1;  DROP TABLE sa1; CREATE TABLE sa2 AS SELECT * FROM jikwon WHERE 1=0; DESC sa2; INSERT INTO sa2 SELECT * FROM jikwon WHERE jikwon_jik = '과장'; INSERT INTO sa2(jikwon_no, jikwon_name, buser_num) SELECT jikwon_no, jikwon_name, buser_num FROM  jikwon WHERE jikwon_jik = '부장'; SELECT * FROM sa2;

9. -- UPDATE , DELETE : SUBQUERY 

SELECT * FROM sa1;  UPDATE sa1 SET jikwon_jik = (SELECT jikwon_jik FROM jikwon WHERE jikwon_name = '이순신') WHERE jikwon_no =2;  SELECT FROM sa1 WHERE jikwon_no IN (SELECT DISTINCT gogek_damsano FROM gogek); SELECT * FROM sa1;

10.  VIEW 파일 : 물리적인 테이블을 근거로 SELECT 문을 파일로 저장하여 가상의 테이블로 사용  중요!!!!!!!!!!!!!!!
-- 형식  CREATE [or REPLACE ] VIEW 뷰파일명 AS SELECT문 
-- DROP VIEW 뷰파일명  (조건만 같고 있다. 물리적인 파일이 아니다. ) 
-- 보통 직원들에게 전체 데이터를 보여주기보다 이런 VIEW  파일을 보내준다. 
-- 메모리를 절약할 수 있고, 데이터의 독립성을 보장하고, 보안을 강화할 수 있다. 

 

CREATE OR REPLACE VIEW v_1 AS   SELECT jikwon_no, jikwon_name, jikwon_pay FROM jikwon WHERE jikwon_ibsail < '2010-12-31';   DESC v_1;  SELECT * FROM v_1;  SELECT SUM(jikwon_pay) FROM v_1;

CREATE OR REPLACE VIEW v_2 AS   SELECT * FROM jikwon WHERE jikwon_name LIKE '김%';  SELECT * FROM  v_2;  ALTER TABLE jikwon RENAME kbs;   SELECT * FROM  v_1; -- err jikwon이 없어져서  SELECT * FROM  v_2; -- err   ALTER TABLE kbs RENAME  jikwon;   SELECT * FROM  v_1;  SELECT * FROM  v_2;

CREATE OR REPLACE VIEW  v_3 AS SELECT * FROM jikwon ORDER BY jikwon_pay DESC;  SELECT * FROM  v_3;

CREATE OR REPLACE VIEW  v_4 AS SELECT jikwon_no, jikwon_name, jikwon_pay * 10000  AS ypay FROM jikwon;  SELECT * FROM  v_4;  UPDATE v_4 SET ypay = 500 WHERE jikwon_no = 1 ; -- VIEW 파일은 물리적으로 바뀔수 없어서 err   UPDATE v_4 SET jikwon_name = '홍두깨' WHERE jikwon_no = 1 ; -- 물리적인 jikwon_name이 바뀜    SELECT * FROM v_4;

-- 뷰로 뷰를 만들 수 있다.   CREATE OR REPLACE VIEW v_5 AS SELECT * FROM v_4 WHERE jikwon_no v_5;  SELECT * FROM v_5;

-- delete   CREATE OR REPLACE VIEW v_6 AS SELECT jikwon_no, jikwon_name, jikwon_pay , buser_num, jikwon_jik FROM jikwon  WHERE jikwon_jik = '사원';  SELECT * FROM v_6;   INSERT INTO V_6(jikwon_no, jikwon_name, jikwon_pay , buser_num, jikwon_jik)  VALUES(50, '사오정', 1234, 10, '사원');  SELECT * FROM v_6;  INSERT INTO v_6(jikwon_no, jikwon_name, jikwon_pay , buser_num, jikwon_jik)  VALUES(51, '사거리', 1234, 20, '과장');  SELECT * FROM v_6;   -- 51번 사거리는 v_6에 보이지 않는다. 과장이니까. 하지만 jikwon 테이블에선 보인다.   -- VIEW를  INSERT 할 때, NOT NULL인 곳엔 iSERT 하지 못한다.   SELECT * FROM jikwon;  UPDATE v_6 SET jikwon_name = '신선한' WHERE jikwon_no = 6;  DELETE FROM v_6 WHERE jikwon_no = 15;  SELECT * FROM jikwon; -- 15번 직원이 사라짐  DELETE FROM jikwon WHERE jikwon_no>= 50;

 

---

※ 오늘 배우고 느낀 것 

 

1. 오늘따라 등허리 위쪽이 아프다... 이것땜에 수업에 집중을 못하겠음...가서 파스나 마사지를 좀 해야겠다.

 

2. SQL의 핵심, JOIN 을 아직도 잘 이해를 못하였다. 그래서 오늘 문제를 해결하는데 있어 많이 헤맸는데, 주말동안 잘 공부해서 

 

3. VIEW 파일은 SQL 실무에 있어 굉장히 중요하다. 그 특성을 잘 이해하고 활용해야 한다. 

 * '메모리를 절약'할 수 있고, '데이터의 독립성을 보장'하고, '보안을 강화'할 수 있다. 

(시험문제 각)