2022.04.27. 수요일 이공계 전문기술 연수사업 23일차

오늘 배운 것

rownum
select 결과에 행번호를 매겨주는 키워드

ex)'대한민국'에 거주하는 고객의 이름과 주소 출력, 출력 결과에 행 번호를 붙임
select rownum 순번,custid,name,address
from customer where address like '대한민국%';

ex)대한민국에 거주하는 고객의 이름과 주소, 행번호를 앞에서 2명만 출력
select rownum 순번,custid,name,address
from customer where address like '대한민국%' and rownum<=2;

ex)개발팀에 근무하는 남자직원들의 사원번호,이름,관리자명,연봉,입사일,아이디,주민번호를 출력
출력결과에 순번을 매겨 출력하고 연봉은 반올림하여 10의자리까지 10칸을 잡아 오른쪽 정렬하여
빈칸을 0으로 채우고 아이디는 10칸을 잡아 왼쪽 정렬하여 출력하고 빈 칸은 '*'로 채움
주민번호는 앞에서 8글자만 14칸을 잡아 왼쪽 정렬하여 출력하고 빈 칸은 '*'로 채움

select rownum,e.eno,e.ename,m.ename,lpad(round((e.salary+e.comm)*12,1),10,0) 연봉,
e.hiredate,rpad(substr(e.email,1,instr(e.email,'@')-1),10,'*') 아이디,
rpad(substr(e.jumin,1,8),14,'*') 주민번호 
from emp e, emp m,dept d 
where e.mgr=m.eno and e.dno=d.dno 
and substr(e.jumin,8,1)='1' and dname like '개발%';

ex)모든 고객의 고객번호와 이름을 행번호를 붙여서 출력
select rownum,custid, name from customer;

ex)고객번호 순으로 내림차순하여 고객번호와 이름을 행번호를 붙여 출력
select rownum, custid, name from customer order by custid desc;

order by 와 행번호를 같이 쓸 때는 order by 실행 결과를 먼저 동작하도록 하기 위하여
서브쿼리를 사용해야 함
select custid, name from customer order by custid desc;
select rownum,custid,name from (select custid, name from customer order by custid desc);

ex)가장 비싼 도서 세권의 정보를 출력
select rownum, bookid, bookname ,price
from (select bookid, bookname, price from book order by price desc) 
where rownum < 4;

rownum 쓸 때만 from 절에 상관서브쿼리 !
상관서브쿼리말고 rownum은 *이랑 같이 올 수 없음(컬럼이름들을 나열해야 함)

select * from (select * from book order by price desc)
where rownum <=3;
행 번호를 출력할 필요가 없다면 *로 표현할 수 있음 (그럼 상관서브쿼리를 왜 써..?)

create table mybook(
bookid number primary key,
price number
);

price='';랑 price is null은 같나?
null에 대한 개념 정리하기 !!!
SQL> create table mybook(
  2  bookid number primary key,
  3  price number
  4  );

테이블이 생성되었습니다.

SQL> insert into mybook values(1,10000);

1 개의 행이 만들어졌습니다.

SQL> insert into mybook values(2,20000);

1 개의 행이 만들어졌습니다.

SQL> insert into mybook values(3,null);

1 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> select * from mybook where price is null;

BOOKID    PRICE
------ --------
     3

SQL> select * from mybook where price = '';

선택된 레코드가 없습니다.
//price는 숫자자료형이고 ''은 문자를 나타내는 표현이기 때문에 다름

SQL> select sum(price),avg(price),count(*) from mybook where bookid>=4;

SUM(PRICE) AVG(PRICE)   COUNT(*)
---------- ---------- ----------
                               0

SQL> select count(price),count(*) from mybook;

COUNT(PRICE)   COUNT(*)
------------ ----------
           2          3

SQL> select sum(price),avg(price) from mybook;

SUM(PRICE) AVG(PRICE)
---------- ----------
     30000      15000
     
     
     =>null은 count(*)에는 집계되지만 avg,sum에는 집계되지 않는다!
SQL> select avg(nvl(price,0)) from mybook;

AVG(NVL(PRICE,0))
-----------------
            10000
     
     문자자료형애서 ''과 null은 똑같다..?
     먼소리지...
     
어쨌든 null과 수학 연산은 되지 않음!
     
SQL> select bookid, price+100 from mybook;

BOOKID  PRICE+100
------ ----------
     1      10100
     2      20100
     3

SQL> select bookid, nvl(price,0)+100 from mybook;

BOOKID NVL(PRICE,0)+100
------ ----------------
     1            10100
     2            20100
     3              100
     
-----------------------------------------------------------------------------------------
rownum에 대한 실습

SQL> select * from book;

BOOKID BOOKNAME             PUBLISHER          PRICE
------ -------------------- --------------- --------
     1 축구의 역사          굿스포츠           7,000
     2 축구의 이해          굿스포츠          17,000
     3 골프 바이블          굿스포츠          27,000
     4 피겨 교본            굿스포츠          37,000
     5 역도 단계별 기술     삼성당            47,000
     6 야구의 추억          kbo               57,000
     7 등산의 매력          내가엄홍길         1,000
     8 달리기 잘하는 법     런런런            70,070
     9 snpe 완전정복        한국snpe협회      70,800
    10 케틀밸 스윙 접근하기 홈트나라          15,000

10 행이 선택되었습니다.

SQL> select * from book where rownum<=5;

BOOKID BOOKNAME             PUBLISHER          PRICE
------ -------------------- --------------- --------
     1 축구의 역사          굿스포츠           7,000
     2 축구의 이해          굿스포츠          17,000
     3 골프 바이블          굿스포츠          27,000
     4 피겨 교본            굿스포츠          37,000
     5 역도 단계별 기술     삼성당            47,000

SQL> select * from book where rownum<=5 order by price;

BOOKID BOOKNAME             PUBLISHER          PRICE
------ -------------------- --------------- --------
     1 축구의 역사          굿스포츠           7,000
     2 축구의 이해          굿스포츠          17,000
     3 골프 바이블          굿스포츠          27,000
     4 피겨 교본            굿스포츠          37,000
     5 역도 단계별 기술     삼성당            47,000
     //행번호를 붙혀 다섯개를 뽑은 다음 가격순으로 정렬
     //가격순으로 정렬하여 다섯개 뽑는 거랑은 다름!!

SQL> select * from (select * from book order by price) where rownum<=5;
//select * from (여기에 정렬하는 sql) where rownum<=5;

BOOKID BOOKNAME             PUBLISHER          PRICE
------ -------------------- --------------- --------
     7 등산의 매력          내가엄홍길         1,000
     1 축구의 역사          굿스포츠           7,000
    10 케틀밸 스윙 접근하기 홈트나라          15,000
     2 축구의 이해          굿스포츠          17,000
     3 골프 바이블          굿스포츠          27,000

SQL> select * from (select * from book where rownum<=5) order by price desc;
//행번호 붙여서 5번까지 뽑은 다음 가격순으로 정렬

BOOKID BOOKNAME             PUBLISHER          PRICE
------ -------------------- --------------- --------
     5 역도 단계별 기술     삼성당            47,000
     4 피겨 교본            굿스포츠          37,000
     3 골프 바이블          굿스포츠          27,000
     2 축구의 이해          굿스포츠          17,000
     1 축구의 역사          굿스포츠           7,000

SQL> select * from (select * from book where rownum<=5 order by price desc);
//굳이 서브쿼리를 안 해도 됨

BOOKID BOOKNAME             PUBLISHER          PRICE
------ -------------------- --------------- --------
     5 역도 단계별 기술     삼성당            47,000
     4 피겨 교본            굿스포츠          37,000
     3 골프 바이블          굿스포츠          27,000
     2 축구의 이해          굿스포츠          17,000
     1 축구의 역사          굿스포츠           7,000

-----------------------------------------------------------------------------------
서브쿼리
select 절 : 스칼라 서브쿼리 (컬럼자리에,단일 값을 반환하기 때문에 스칼라서브쿼리라고 함)
from 절 : 인라인 뷰, 테이블 서브쿼리 (테이블자리에, from 절에서 결과를 보이지 않는 테이블인 뷰 형태로 반환하기 때문에)
where 절 : 중첩 서브쿼리 (결과를 한정시키기 위해 사용됨,상관 서브쿼리가 올 수도 있고 비상관 서브쿼리가 올 수도 있음) 

서브쿼리란?
하나의 sql문 안에 다른 sql문이 중첩된 질의를 말함
다른 테이블에서 가져온 데이터를 현재 테이블에 있는 정보를 찾거나 가공할 때 사용함
조인보다 검색 결과가 빠름
메인쿼리,서브쿼리로 구성됨

-------------------------------------------------------------------------------------------
스칼라서브쿼리
select 절에 컬럼이름 오는 곳에 사용되는 서브쿼리임
서브쿼리의 결과 값을 단일 행 단일 열의 스칼라 값으로 반환함
메인쿼리와의 관계는 상관/비상관 둘 다 상관 없음

ex)고객 별 고객 아이디,이름,총주문금액 출력
select custid, (select name from customer c where o.custid=c.custid) name, sum(saleprice) 
from orders o group by custid;
//주문에 대한 내역은 orders에 있고 고객 이름은 customer에 있음. 여기서 join대신 스칼라서브쿼리를 사용!
//여기서 서브쿼리는 메인쿼리와 상관서브쿼리임. 이럴거면 그냥 조인을 하는게 낫지 않나...?

ex)고객 이름 별로 이름과 판매액 출력
select (select name from customer c where c.custid=o.custid) name,
sum(saleprice) from orders o group by custid;
//서브쿼리의 조건식이 메인쿼리와 조건식이 있어서 join이라고 생각할 수 있는데
이 경우 join이라고 생각하지 않고 상관서브쿼리라고 생각함!

ex)스칼라 서브쿼리를 이용하여 도서명별 판매건수를 출력
select (select bookname from book b where b.bookid=o.bookid) 도서명,
count(orderid) from orders o group by bookid;

ex)스칼라 서브쿼리를 이용하여 부서명별로 근무자의 수,평균급여, 최고급여를 출력
//내풀이
select dno, (select count(*) from emp e where e.dno=d.dno) count, 
(select avg(salary) from emp e where e.dno=d.dno) salary,
(select max(salary) from emp e where e.dno=d.dno) max_salary
from dept d group by dno;
//강사님풀이
select (select dname from dept d where d.dno=e.dno),
avg(salary),max(salary) 
from emp e group by dno;

실습을 위하여 orders 테이블에 bookname 칼럼 추가
alter table orders add bookname varchar2(30);

ex)orders 테이블의 각 주문에 맞는 도서이름 수정
update orders set bookname = (select bookname from book b where b.bookid=orders.bookid);
이렇게도 update문에 스칼라 서브쿼리 사용 가능함!
update 테이블명 set 컬럼명 (스칼라서브쿼리);
//값이 오는 자리에 서브쿼리가 옴!

-------------------------------------------------------------------------------------------
인라인 뷰
from절에 오는 서브쿼리
실제로 존재하지 않는 가상의 테이블을 뷰라고 하는데 필요한 곳에서 바로 뷰를 만들어서 인라인 뷰라고 이름을 붙임
반환 데이터는 다중 행,다중 열이어도 상관 없음 또, 상관 서브쿼리로 사용될 수는 없음 왜? =>테이블이 오는 자리여서 그 곳은 상관서브쿼리로는 사용 불가능

ex)고객 번호가 2이하인 고객의 이름별로 이름과 총판매액을 출력 //이거 다시 생각!!!!!!!!!!!!
select name,sum(saleprice) from (select custid, name from customer where custid <=2) c,orders o where c.custid=o.custid group by name;
from 절에 고객번호가 2이하인 테이블을 추출함(인라인뷰로) 그것을 orders와 조인하여 이름과 총판매액을 출력
아하 이제 이해 갔지만 그래도 또 한번 보기! (원래의 customer가 아닌 인라인뷰가 조인됨)

select 컬럼1,컬럼2 from 테이블1,테이블2 where 조건식
=>테이블1, 테이블2 자리에 서브쿼리를 표현할 시점에는 바깥에 메인쿼리가 누군지 모르기때문에 상관서브쿼리로 표현할 수 없음

---------------------------------------------------------------------------------------------------------------------------------------------
중첩서브쿼리(중첩질의)
where절에 오는 서브쿼리
반환 데이터는 단일 행(비교연산자)일 수도 있고 다중 행(집합,한정,존재연산자)일 수도 있음

다중행 연산자의 정의?
다중행 연산자는 일단 서브쿼리에서 사용됨.
서브쿼리가 where 절에 사용될 때 서브쿼리의 건수가 여러 건일 때 사용하는 연산자이며
in(하나라도 포함되는지),not in(하나라도 여기에 포함되지 않는지),all(비교연산자-모두),some(비교연산자-하나라도),any(some과 동일),exists(서브쿼리에 결과가 있으면 메인쿼리실행),not exists(결과가 없으면 메인쿼리 실행) 등이 있음!

ex)평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오
select orderid, saleprice from orders where saleprice <= (모든 주문에 대한 평균 주문금액을 구하는 sql);
select orderid, saleprice from orders where saleprice <= (select avg(saleprice) from orders);
위의 중첩서브쿼리는 메인쿼리와 관계가 없음 이것을 비상관서브쿼리라고 함!

ex)각 고객의 평균 주문금액 이하의 주문에 대해서 주문번호와 고객번호, 주문금액을 보이시오
select orderid, custid, saleprice from orders where saleprice <= (메인 쿼리의 주문한 고객에 대한 평균 주문금액을 구하는 sql);
select orderid, custid, saleprice from orders o2 where saleprice <= (select avg(saleprice) from orders o1 where o1.custid=o2.custid);

ex)대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오
select sum(saleprice) from orders where custid in(select custid from customer where address like ('대한민국%'));
select sum(saleprice) from orders where custid =(select custid from customer where name='박지성');
이랑 차이는 =과 in 이것이 다중행 연산자(입사면접단골질문)

ex)3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오
select orderid,saleprice from orders where saleprice > all(select saleprice from orders where custid=3);
select orderid,saleprice from orders where saleprice > (select max(saleprice) from orders where custid=3);

SQL> select orderid,saleprice from orders where saleprice > (select saleprice from orders where custid=3);
select orderid,saleprice from orders where saleprice > (select saleprice from orders where custid=3)
                                                        *
1행에 오류:
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.
서브쿼리의 건수가 여러 건이기 때문에 단일행 연산자인 >을 사용할 수 없고
다중행 연산자인 all이나 some을 같이 사용해야 함.
지금은 3번 고객이 주문한 최고금액보다 비싼 주문을 검색해야하기 때문에
모든 서브쿼리의 결과보다 커야함! 그래서 all을 사용함.

ex)exists 연산자를 이용하여 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액 출력
select sum(saleprice) from orders where custid in(select custid from customer c where address like '대한민국%'); //이렇게 까지만 하면 다중행연산자
select sum(saleprice) from orders o where exists(select custid from customer c where address like '대한민국%' and o.custid = c.custid);

ex)고객 아이디와 주소와 총 주문금액 출력 (스칼라 서브쿼리 사용)
select custid, (select address from customer c where c.custid=o.custid) address,sum(saleprice) total from orders o group by o.custid;

ex)고객 아이디별로 이름과 평균 주문금액을 출력 (인라인뷰 사용)
select c.name,s from (select custid,avg(saleprice) s from orders group by custid) o, customer c where c.custid=o.custid;

ex)고객번호가 3이하인 고객들의 총 주문금액 (중첩서브쿼리 사용)
select sum(saleprice) total from orders o where exists (select * from customer c where custid<=3 and c.custid=o.custid);

---------------------------------------------------------------------------------------------------------------------------------------------------------
뷰
실제로 존재하지 않는 가상의 논리적인 테이블, 하나 이상의 테이블을 합하여 만든 가상의 테이블
자주 사용되는 복잡한 sql을 미리 뷰로 정의해놓을 수 있고 보안 유지상 사용자별로 필요한 데이터만 선별하여 보여줄 수 있음
생성,수정,삭제가 가능함

뷰를 생성하는 방법
create view 뷰이름 as select문

ex)book테이블에서 축구라는 문구가 포함된 자료만 보여주는 뷰
select * from book where bookname like '%축구%';
위의 sql을 사용해서 작성한 뷰
create view vw_book as select * from book where bookname like '%축구%';
select * from vw_book;

ex)20번 부서에 근무하는 모든 직원들의 사원번호,사원명,부서번호,전화,주소를 검색하는 뷰를 생성하고 조회
SQL> create view vw_emp as select eno,ename,dno,phone,addr from emp where dno=20;

뷰가 생성되었습니다.

SQL> select * from vw_emp;

  ENO ENAME     DNO PHONE           ADDR
----- ------- ----- --------------- -----
 1011 김소윤     20 010-1234-9999   인천
 1012 이희승     20 010-1234-3223   경기
 1013 조태헌     20 010-9707-7895   서울
 1014 황예은     20 010-9999-7777   경기

ex)vw_emp를 통해 insert 수행
SQL> insert into vw_emp values(1020,'신짱구',20,'010-1234-5678','서울');

1 개의 행이 만들어졌습니다.

SQL> select * from vw_emp;

  ENO ENAME     DNO PHONE           ADDR
----- ------- ----- --------------- -----
 1020 신짱구     20 010-1234-5678   서울
 1011 김소윤     20 010-1234-9999   인천
 1012 이희승     20 010-1234-3223   경기
 1013 조태헌     20 010-9707-7895   서울
 1014 황예은     20 010-9999-7777   경기

SQL> select * from emp;
=>여기에도 추가되어있음!
뷰 생성시에 사용한 칼럼 이외의 칼럼들은 null을 허용하거나 default값이 설정되어 있어야 뷰를 통해 추가할 수 있음

ex)뷰를 통해서 레코드를 수정할 수 있는지 실험
update vw_emp set eno=2000 where ename='신짱구';
=>수정 가능함, emp에도 수정되어있음

ex)뷰를 생성할 때 사용한 조건식에 맞지 않는 값으로 수정이 가능한지 실험
SQL> update vw_emp set dno=30 where ename='신짱구';

1 행이 업데이트되었습니다.
//뷰 생성할 때 조건과 맞지 않는 값으로 수정이 가능함,emp에도 수정되었음 =>그럼 더이상 vw_emp에 나타나지 않음! 오 신기하다

ex)뷰를 통해 레코드를 삭제할 수 있는지 실험
=>삭제할 수 있음!

SQL> insert into emp values(1020,'신짱구',20,300,30,'2022/11/11','010-1234-5678','대전',1012,'사원','djdkas@naver.com','970221-2173310');

1 개의 행이 만들어졌습니다.

SQL> select * from vw_emp;

  ENO ENAME     DNO PHONE           ADDR
----- ------- ----- --------------- -----
 1020 신짱구     20 010-1234-5678   대전
 1011 김소윤     20 010-1234-9999   인천
 1012 이희승     20 010-1234-3223   경기
 1013 조태헌     20 010-9707-7895   서울
 1014 황예은     20 010-9999-7777   경기
와 개신기 ㄷㄷㄷㄷㄷㄷㄷ
따로 뷰테이블로 가라고 지정해주지 않아도 값이 충족되면 저절로 뷰로 감

ex)뷰를 통해 뷰 생성 시에 사용한 조건에 맞지 않는 레코드를 추가할 수 있는지 확인
SQL> insert into vw_emp values(1021,'신짱아',30,'010-1234-5678','대전');

1 개의 행이 만들어졌습니다.

SQL> select * from vw_emp;

  ENO ENAME     DNO PHONE           ADDR
----- ------- ----- --------------- -----
 1020 신짱구     20 010-1234-5678   대전
 1011 김소윤     20 010-1234-9999   인천
 1012 이희승     20 010-1234-3223   경기
 1013 조태헌     20 010-9707-7895   서울
 1014 황예은     20 010-9999-7777   경기
 =>emp에는 추가되고 뷰에는 조건이 맞지 않기 때문에 뷰에는 추가되지 않음
 
------------------------------------------------------------------------------------------------------------------------------------
with check option
뷰 생성 시에 사용한 조건식에 맞는 레코드만 추가/수정하도록 하기위한 옵션
create view 뷰이름 as 조건식 with check option;
create view vw_emp_20 as select eno,ename,dno,phone,addr from emp where dno=20 with check option;
SQL> insert into vw_emp_20 values (2000,'유관순',20,'1111','서울');

1 개의 행이 만들어졌습니다.

SQL> insert into vw_emp_20 values (2001,'유광순',10,'1111','서울');
insert into vw_emp_20 values (2001,'유광순',10,'1111','서울')
            *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

SQL> update vw_emp_20 set dno=30 where eno=2000;
update vw_emp_20 set dno=30 where eno=2000
       *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
조건에 맞지 않는 레코드를 추가하거나 수정할 수 없음

-------------------------------------------------------------------------------------------------------------
조회만 가능한 뷰의 생성 with read only
create view 뷰이름 as 조건식 with read only;

SQL> create view vw_emp_20 as select eno,ename,dno,addr from emp where dno=20 with read only;

뷰가 생성되었습니다.

SQL> insert into vw_emp_20 values(2000,'dkdkdk',20,'대전');
insert into vw_emp_20 values(2000,'dkdkdk',20,'대전')
*
1행에 오류:
ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

 

오늘 헷갈린 것

• 배열 복사하는 여러 방법들! 그 중에서도 System.arraycopy() 처음 알게 되었다.. 이렇게 자바는 무궁무진한 메소드들이 참 많다는 것을 또 한 번 느낌
• null 이 집계함수에 끼치는 영향 count에만 집계됨
• sql문이 읽어들이는 순서! from절을 먼저 읽어들이나..?
• 인라인 뷰 쓸 때 from절에 오는 조건식의 열과 메인쿼리 열은 똑같아야하나..?
• 뷰랑 자식테이블의 비교..? 굳이 비교하자면 뷰랑 자식테이블은 비슷한데 뷰는 상속받는 것이 없고 논리적인 invisible 테이블이라는 점일까?

오늘 한 생각

연습문제 풀 때 막히면 어떻게 해야하는지 정말 고민이다..

내가 아직 자바 메소드들을 너무 몰라서 아예 학습하지 않은 메소드들도 많고

답지를 봐야만 하는 문제들이 종종 있어서.. 흠.........ㅠㅠㅠㅠ 이 고민을 어떻게 타파해야할지...

그리고 강사님 티칭이 좀 맘에 안든다..ㅎ 말을 너무 헷갈리게 한달까 ㅠㅠ.. 요즘 팀 연습문제 내주시는 것도 뭔가 성의가 없는 느낌... 하... 그리고 내 질문이 뭔가 귀찮으신 것 같다 ㅠㅋㅋ....