[days03] SQL 테이블

 

1. 특정 테이블의 구조 확인

ex)인사 테이블 구조 확인

desc insa;

 

출력값

2. SQL문 실행 순서

1 with

6 select

2 from

3 where

4 group by

5 having

7 order by

 

3. 특정 테이블의 모든 컬럼 조회

ex) 인사테이블의 모든 컬럼 조회

select *
from insa;

출력값

* 테이블은 뭐고 컬럼은 뭐야? 

 테이블은 가장 기본이 되는 객체 저장소 (각 행을 [레코드]라고 하며 열을 [칼럼]이라고 한다)

 

4. emp 테이블의 사원번호, 사원명, 급여(sal+comm) 출력 - comm이 null인 것은 0으로 처리해서 급여 계산

select empno 사원번호, 
        ename 사원명,  
        sal, comm, 
        sal+NVL(comm,0) 급여 
        , nvl2(comm, sal+comm, sal) --널이면 뒤에꺼, 아니면 앞에꺼 출력 
from emp;

 

* Null값 처리 : NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위한 함수이다.

- NVL(comm,0)  인 경우, comm의 값이 null이라면 0으로 바꾼다는 의미

- NVL2(comm, sal+comm, sal)  이라면 ,  comm 값이 null이라면  sal(뒤의 값)을, 아니라면 sal(앞의 값)을 출력

 

 

where 사용시 주의

SQL문에 사용하는 기본 형식은 대,소문자를 구분하지 않고 사용할 수 있으나

테이블 안에 들어있는 문자 또는 문자열 데이터는 대,소문자를 구분한다.

 

 5. 인사테이블에서 수도권 출신의 사원 정보를 출력 (수도권 : 서울 경기 인천)

- 'A' or 'B' or 'C'

- in ('A', 'B', 'C')

select * 
from insa 
where city = '서울' or city='경기' or city='인천';  --1번
where  city in('서울', '경기', '인천');            --2번

출력값

수도권이 [아닌] 사원이라면 ?

where not( city='서울' or city='경기' or city='인천');
where  city not in('서울', '경기', '인천');

 

1) [not] in(목록)
2) [not] between A and B

 

6. emp테이블에서 사원번호, 사원명, 상사사번, 급여를 조회한 후, 상사 사번이 없는 경우 CEO로 인식, (CEO 찾기)

select empno, ename, mgr, sal+nvl(comm, 0) pay 
        ,nvl(mgr, 0) 
from emp 
where mgr is null;

입력값

출력값

7. emp테이블에서 pay 1000이상~2000이하를 받는 사원

select empno, ename, mgr, sal+nvl(comm, 0) pay, deptno 
from emp 
where pay>=1000 and pay<=2000;

이렇게 하면 오류 : ORA-00904: "PAY": invalid identifier (효력없는 식별자)

왜일까 ? 앞서 말했듯이 where이 실행된 후 select로 실행 순서가 이동되기 때문에 'pay'를 인식하지 못한다.

 

해결방법 

(1) - alias를 where절에서 사용하지 않기

select empno, ename, mgr, sal+nvl(comm, 0) pay, deptno 
from emp 
where sal+nvl(comm, 0)>=1000 and sal+nvl(comm, 0)<=2000 
order by pay asc;

order by에서는 alias를 사용해도 된다 ! select 이후에 order by가 실행되기 떄문

 

(2) - with 서브쿼리

WITH  temp  AS  
 ( 
 SELECT empno, ename, job, mgr, hiredate, sal+nvl(comm,0) pay, deptno 
 FROM emp 

 ) 
select * 
from temp 
where pay>=1000 and pay<=2000;

 

(3) from절 뒤의 서브쿼리 (이를 인라인 뷰라고 한다.)

select t.* 
from ( 
        SELECT empno, ename, job, mgr, hiredate, sal+nvl(comm,0)pay, deptno 
        from emp
    ) t --서브쿼리 결과물을 가진 테이블의 alias 

where t.pay>=1000 and t.pay<=2000;

 

 

8. 실제 emp 사원테이블에서 실제 사원이 존재하는 부서번호만 출력

사진과 같이 부서 테이블에서 부서번호를 조회한 결과 10, 20, 30, 40이 존재함을 확인했다.

그러나 사원 테이블의 부서번호를 조회한 결과 부서번호40의 사원은 존재하지 않았다.

따라서 실제 사원이 존재하는 부서번호만을 출력하고 싶은 경우는 다음과 같이 입력하자.

select distinct deptno 
from emp;

9. 사원의 입사일자가 1981년만 조회

 

select empno, ename, hiredate --DATE RR/MM/DD 
from emp 
where hiredate >='1981/01/01' and hiredate <='1981.12.31';  --1
where hiredate between '1981/01/01' and '1981.12.31';       --2



select ename, substr(hiredate, 0, 2) RR --문자열             --3
from emp 
where substr(hiredate, 0, 2) = '81';



select ename, hiredate                                       --4
from emp 
where hiredate like '81%'; 

 

 

* 날짜 형식 : RR/MM/DD   <-이거 어떻게 알았어 ? (도구>환경설정>데이터베이스>NLS에서 확인) 

  날짜형도 문자열처럼 앞 뒤에 홑따옴표를 붙히자 '1981/01/01'

  날짜형은 중간에 / 나 .를 붙혀도 되고 아무것도 안줘도 된다.    '1981/01/01'     '1981.01.01'      '19810101'

   

 

* 오라클에도 자바처럼 substring()이 있나? --substr()함수가 있다

    substr(ssn, 0, 6) -- 앞 숫자는 0과 1이 동일하고, 맨 첫자부터 6자리까지 끊겠다는 의미다.

    substr(ssn, 8) -- 뒤 숫자 값을 주지 않으면 끝까지 읽어오겠다는 의미다.

 

select name, ssn 
        ,substr(ssn, 0, 6) || '-*******' --문자열 연결 연산자 
        ,substr(ssn, 1, 6)  || '-*******'
        ,'******-'||substr(ssn,8,7) 
         ,'******-'||substr(ssn,8)
from insa;

출력값

10. 인사테이블에서 사원명이 '김'씨 또는 '이'씨 사원들만 조회

select * 
from insa 
where name like '김%' or name like '이%';  --1
where substr(name, 0, 1) in('김','이');    --2
where regexp_like(name, '^[김이]');        --3

출력값

 

11. 인사테이블에서 신씨 성은 제외하고 "신"의 이름을 가진 사람 출력

select * 
from insa 
--where name like '_%신%'; --잘못된 것. 신신애인 경우 포함됨 
where name like '%신%' and name not like '신%';  
where substr(name, 2) like '%신%';  
where regexp_like(name, '^[^신].*신.*'); --중요!!! 정규표현식

 

* ^[^신] : 신씨를 포함하지 말라는 뜻 

  .*의 의미 : 문자가 여러개 와도 좋고 안와도 좋고

 

 

12. 1001 홍길동 -> 신길동 수정

update insa  
set name = '신길동' 
where num = 1001;

 

13. insa 테이블에서 주민번호(ssn)를 기준으로 남자사원만 출력

남자는 주민번호 뒷자리가 1뿐만이 아니라 3,5,... 홀수이다.

select name, ssn 
from insa 
where mod(SUBSTR(ssn,8,1), 2)='1';               --1
where substr(ssn, 8, 1) in (1,3,5,7,9);          --2
where regexp_like(SUBSTR(ssn,8,1), '[13579]');   --3

 

14. insa 테이블에서 80년대 태어난 사원 출력

select name, ssn 
from insa 
where  substr(ssn, 0,1) = '8';     --1 정확히는 틀린코딩, 1880년대 사람일지도  
where ssn like '8%';               --2
where regexp_like(ssn, '^8');      --3 숫자 하나니까 대괄호[] 팔요없음

 

* comm이 널이 아니다 ? 

  - where comm is not null;
  - where not (comm is null);

  -  comm = null 사용하면 아무 값도 안뜨는 이유 : NULL은 산술연산자나 비교연산자로 비교하면 결과값이 NULL이 됨

 

15. emp 테이블에서 ename이  s 또는 j 또는 m 문자를 대소문자 구분하지 않고 포함하는 사원 출력.

select ename, lower(ename) 
from emp 
where lower(ename) like '%s%' or lower(ename) like '%j%'or lower(ename) like '%m%';  --1
where regexp_like(ename, '[sjm]', 'i'); --i의 의미 : 대소문자 구분x                   --2
where regexp_like(ename, '[sjmSJM]');                                                --3

출력값