N-coding

사원번호, 이름, 월급을 조회하시오 (특정 컬럼 조회)

select empno, ename, sal /* empno, ename, sal을 검색하라 */
from emp;                /* emp로부터 */

 

사원이름, 입사일, 직업, 부서번호를 조회하시오

select ename, hiredate, job, deptno
from emp;

# 연산자, as

사원이름, 월급, 커미션, 월급 + 커미션을 출력하시오

select ename, sal, comm, sal + comm
from emp;

 

사원 이름, 월급*12 (연봉) 를 출력하시오

select ename, sal * 12 as 연봉
from emp;

 

사원이름, 월급, 직업을 출력하는데 컬럼명을 각각 한글로 이름, 월급, 직업으로 붙인다

select ename as 이름, sal as 월급, job as 직업
from emp;

 

사원이름, 입사일, 부서번호를 출력하는데 컬럼명을 각각 한글로 이름, 입사일, 부서번호로 붙인다

select ename as 이름, hiredate as 입사일, deptno as 부서번호
from emp;

 

# where절

사원번호가 7788번인 사원의 사원번호, 이름, 월급을 출력하시오

select empno, ename, sal
from emp
where empno = 7788;

 

월급인 3000인 사원의 이름, 월급을 출력하시오

select ename, sal
from emp
where sal = 3000;

 

부서번호가 20인 사원들의 이름, 월급, 사원번호, 부서번호를 출력하시오

select ename, sal, hiredate, deptno
from emp
where deptno = 20;

 

월급이 3000 이상인 사원들의 이름과 월급을 출력하시오

select ename, sal
from emp
where sal >= 3000;

 

이름이 SCOTT인 사원의 이름과 월급을 출력하시오

select ename, sal
from emp
where ename = 'SCOTT';

 

직업이 SALESMAN인 사원들의 이름과 직업을 출력하시오

select ename, job
from emp
where job = 'SALESMAN';

 

직업이 SALESMAN이 아닌 사원들의 이름과 월급과 직업을 출력하시오

select ename, sal, job
from emp
where job <> 'SALESMAN';

 

# 기타 비교 연산자

월급이 1000에서 3000 사이인 사원들의 이름과 월급을 출력하시오

select ename, sal
from emp
where sal between 1000 and 3000; or where sal >= 1000 and sal <= 3000;

 

월급이 1000에서 3000 사이가 아닌 사원들의 이름과 월급을 출력하시오

select ename, sal
from emp
where sal not between 1000 and 3000;

 

이름의 첫 번째 철자가 S로 시작하는 사원들의 이름을 출력하시오

select ename
from emp
where ename like 's%';  

 

이름의 끝 글자가 T로 끝나는 사원들의 이름을 출력하시오

select ename
from emp
where ename like '%T';

 

이름의 두 번째 철자가 M인 사원들의 이름을 출력하시오

select ename
from emp
where ename like '_M%';

 

이름의 세 번째 철자가 A인 사원들의 이름을 출력하시오

select ename
from emp
where ename like '__A%';

 

사원번호가 7788, 7902, 7369번인 사원들의 사원번호와 이름을 출력하시오

select empno, ename
from emp
where empno in ( 7788, 7902, 7369 ) ; or where empno = 7788 or empno = 7902 or empno = 7369;

 

직업이 SALESMAN, ANALYST가 아닌 사원들의 이름과 직업을 출력하시오

select ename, job
from emp
where job not in ('SALESMAN', 'ANALYST'); or where job != 'SALESMAN' and job != 'ANALYST';

 

커미션이 null인 사람들의 이름과 커미션을 출력하시오

select ename, comm
from emp
where comm is null;

 

커미션이 null이 아닌 사람들의 이름과 커미션을 출력하시오

select ename, comm
from emp
where comm is not null;

 

# 문자형 함수

이름을 대문자, 이름을 소문자로 출력하고 이름의 첫 번째 철자는 대문자, 나머지는 소문자로 출력하시오

select upper(ename), lower(ename), initcap(ename)
from emp;

 

이름이 scott인 사원의 이름과 월급을 출력하는데 where절의 scott을 소문자로 비교해서 출력하시오

select ename, sal
from emp
where lower(ename) = 'scott';

 

이름, 이름의 첫 번째 철자만 출력하시오!

select ename, substr(ename,1,1)
from emp;

 

이름의 첫 번째 철자를 출력하는데 소문자로 출력하시오

select substr(lower(ename),1,1) or select lower(substr(ename,1,1))
from emp;

 

이름에 A를 포함하고 있는 사원들의 이름과 월급을 출력하시오

select ename, sal
from emp
where ename like '%A%';
/*or*/
select ename, instr(ename, 'A')
from emp;

 

instr을 이용하여 이름에 A를 포함하고 있는 사원들의 이름을 출력하시오

select ename
from emp
where instr(ename,'A') > 0;

 

이름과 월급을 출력하는데 월급을 출력할 때에 0 대신에 *로 출력하시오

select ename, replace(sal,0,'*') /* 암호화를 하고 싶을 때 */
from emp;

 

이름과 월급을 출력하는데 월급의 0부터 3까지를 *로 출력하시오

select ename, regexp_replace(sal,'[0-3]+','*')
from emp;

 

* 아래의 명령어를 수행해서 emp 테이블에 data를 입력하시오

insert into emp(empno, ename, sal)
values(1215, 'jane     ', 3000);
commit;  // 지금 입력한 작업을 db에 영구히 저장

 

이름이 jane 인 사원의 이름과 월급을 출력하시오

select ename, sal
from emp
where ename = 'jane    '; or where trim(ename)='jane';\

 

* lpad - 왼쪽에 채워 넣다

select ename, lpad(sal,10,'*')
from emp;

 

* rpad - 오른쪽에 채워 넣다

select ename, rpad(sal,10,'*')
from emp;

 

* round - 반올림

select 783.576,
round(783.576,1), // .첫번째 자리를 기준으로 뒤에서 반올림
round(783,576,2),
round(783,576,0)  // 0이 제일 많이 쓰임
from dual;        // 결과를 보기 위한 가상 테이블

 

* trunc - 내림

select 783.576,
trunc(783.576,1),      //   .첫번째 자리에서 자름
trunc(783,576,2),
trunc(783,576,0)
from dual;

select trunc(783.576)  // 소수점 뒤 없애버림
from dual;

 

* mod - 나눈 나머지 값

select mod(10,3)
from dual;

 

# 날짜 함수 ★

날짜 - 날짜 = 숫자

날짜 - 숫자 = 날짜

 

- 오늘 날짜를 보는 키워드

select sysdate
from dual;

 

이름, 입사한 날짜부터 오늘까지 총 며칠 근무했는지 출력하시오

select ename, sysdate - hiredate
from emp;

 

위의 결과에서 소숫점 이하는 나오지 않게 자르시오

select ename, trunc(sysdate-hiredate)
from emp;

 

이름, 입사한 날짜부터 오늘까지 총 몇 달 근무했는지 출력하시오

select ename, months_between(sysdate, hiredate)
from emp;

 

오늘부터 100달 뒤의 날짜를 출력하시오

select add_months(sysdate, 100)
from dual;

 

오늘부터 앞으로 돌아올 금요일의 날짜를 출력하시오

select next_day(sysdate,'금요일')
from dual;

 

오늘부터 100달 뒤에 돌아올 월요일의 날짜를 출력하시오

select next_day(add_months(sysdate,100),'월요일')
from dual;

 

이번달 의 마지막 날짜를 출력하시오

select last_day(sysdate)
from dual;

 

오늘부터 이번달 말일까지 총 며칠 남았는지 출력하시오

select last_day(sysdate)-sysdate
from dual;

 

# 변환 함수

이름과 월급을 출력하는데 월급을 출력할 때에 천 단위를 표시하시오

select ename, to_char(sal,'999,999')     // 0부터 9까지 뭐가 오든 상관이 없다
from emp;

 

이름, 월급 * 20300을 출력하는데 천 단위와 백만 단위도 표시되게 하시오

select ename, to_char(sal*20300,'999,999,999')
from emp;

 

오늘 날짜 요일을 출력하시오 ( 날짜 -> 문자 )

select sysdate, to_char(sysdate,'DAY')
from dual;

 

오늘부터 100달 뒤에 돌아오는 날짜의 요일을 출력하시오

select to_char(add_months(sysdate,100),'day')
from dual;

 

이름, 입사일, 입사한 요일을 출력하시오

select ename, hiredate, to_char(hiredate,'day')
from emp;

 

이름, 입사한 년도를 출력하시오

select ename, to_char(hiredate, 'YYYY')
from ep;

 

현재 시간

select to_char(sysdate, 'yyyy/mm/dd:hh24:mi:ss')
from dual;

/             -> 초 바뀜 현재 시간으로

 

# 일반 함수

이름, 월급, 커미션, 월급 + 커미션을 출력하시오

select ename, sal, nvl(comm,0), sal + svl(comm,0)
from emp;

 

이름, 커미션을 출력하는데 커미션이 null인 사원들은 no comm이라는 글씨로 출력하시오

select ename, nvl(comm, 'no comm') from emp; //error

comm은 숫자형 데이터인데 문자를 출력하려고 하니 안 되는 것

select ename, nvl(to_char(comm), 'no comm')
from emp;

 

이름, 월급, 부서번호, 보너스를 출력하는데 부서번호가 10번이면 보너스를 600으로 출력하고 20번이면 400으로 출력하고 나머지 부서번호는 0으로 출력하시오

select ename, sal, deptno, decode(deptno, 10, 600, 20, 400, 0) as bonus
from emp;

 

이름, 직업, 월급, 보너스를 출력하는데 직업이 SALESMAN이면 보너스를 900으로 출력하고 직업이 ANALYST면 보너스를 500으로 출력하고 직업이 CLERK이면 보너스를 200으로 출력하시오

select ename, job, sal, decode(job,'SALESMAN', 900, 'ANALYST', 500, 'CLERK', 200) as bonus
from emp;

 

이름, 월급, 입사일, 입사한 년도(4자리)를 출력하시오

select ename, sal, hiredate,  to_char(hiredate, 'RRRR')
from emp;

 

이름, 입사한 년도, 보너스를 출력하는데 입사한 년도가 1981년이면 보너스를 500으로, 1982년이면 800으로, 나머지 년도는 0으로 출력하시오

select ename, to_char(hiredate, 'RRRR'), decode(to_char(hiredate, 'RRRR'), '1981', 500, '1982', 800, 0) as bonus
from emp;

 

이름, 월급, 보너스를 출력하는데 월급이 3000 이상이면 보너스를 900으로, 월급이 1000 이상이고 3000보다 작으면 500으로 출력하고 나머지는 0으로 출력하시오

select ename, sal, case when sal >=3000 then 900 when sal >= 1000 then 500 else 0 end as "bonus"
from emp;

 

# 복수행 함수 ★

사원테이블의 최대월급을 출력하시오

select max(sal)
from emp;

 

직업이 SALESMAN인 사람들 중 최대 월급을 출력하시오

select max(sal)
from emp
where job='SALESMAN';

 

직업과 직업이 SALESMAN인 사람들 중 최대 월급을 출력하시오

select job, max(sal)
from emp
where job='SALESMAN'
group by job;

 

부서번호, 부서번호별 최대 월급을 출력하시오

select deptno, max(sal)
from emp
group by deptno;

 

이름, 월급을 출력하는데 월급이 낮은 사원부터 높은 사원 순으로 출력하시오

select ename, sal
from emp
order by sal asc;

 

이름, 입사일을 출력하는데 최근 입사한 사원부터 출력하시오

select ename, hiredate
from emp
order by hiredate desc;

 

부서번호, 부서번호별 최소 월급을 출력하는데 최소 월급이 높은 순으로 출력하시오

select deptno, min(sal)
from emp
group by deptno
order by min(sal) asc;

 

입사한 년도(4자리), 입사한 년도별 평균 월급을 출력하시오

select to_char(hiredate, 'YYYY'), avg(sal)
from emp
group by to_char(hiredate, 'YYYY');

 

직업, 직업별 인원 수를 출력하시오

select job, count(*)
from emp
group by job;

 

사원 테이블 전체 인원 수를 출력하시오

select count(*) from emp;
select count(empno) from emp;

count(*)은 사원 테이블의 열을 센 것 / empno는 empno만

 

커미션을 count 해 보시오

select count(comm) from emp;

☆ gruop함수는 null값을 무시한다

 

사원 테이블의 월급 평균 값을 구하시오

select avg(sal)
from emp;

 

사원 테이블의 커미션 평균 값을 구하시오

select avg(comm) from emp;

// 이 결과는 comm을 다 더해서 4로 나눈 것이다 그룹 함수는 null값을 무시하기 때문

 

위의 문제를 다시 수행하는데 전체 사원 수로 나눠지게 하시오

select avg(nvl(comm,0))
from emp;

 

직업, 직업별 토탈 월급을 출력하는데 직업별 토탈월급이 4000 이상인 것만 출력하시오

select job, sum(sal)
from emp
group by job
having sum(sal) >= 4000;

 

위의 결과를 다시 출력하는데 토탈 월급이 높은 것부터 출력하시오

select job, sum(sal)
from emp
group by job
having sum(sal) >= 4000
order by sum(sal) desc;

# SQL (Structure Query Language, 구조적 질의 언어) : Database에 있는 데이터를 엑세스하고 조작하는 언어

1. Query (질의어) ~> select문 6가지 절

2. DML (Data Mnipulation Language) ~> insert, update, delete, merge

3. DDL (Data Definition Language) ~> create, alter, drop, truncate, rename

4. DCL (Data Control Language) ~> grant, revoke

5. TCL (Transaction Control Language ) ~> commit, rollback, savepoint

 

# emp 테이블의 컬럼명

empno : 사원번호	ename : 사원이름	sal : 월급	job : 직업
comm : 커미션	hiredate : 입사일	deptno : 부서번호	mgr : 관리자의 사원번호

 

# select문 : 데이터 액세스 (검색) ★ 제일 중요한 SQL

select ~> 검색해라

*       ~> 모든 컬럼 전부

from  ~> 어느 테이블로부터 결과를 볼지

;        ~> 앞의 문장을 실행해라

where 절에 검색할 조건을 명시

as      다음에 컬럼 별칭을 사용할 수 있다 - 컬럼명이 별칭으로 출력됨 (안 써주면 컬럼명은 sal * 12로 나옴)

group by ~> 그룹화             // null 값 무시

    where 절에는 group 함수로 조건을 줄 수 없다

    group 함수로 조건을 주려면 having절 사용

order by  ~> 데이터 정렬하는 절 ( asc / desc )

문자 혹은 날자를 쓸 때는 양쪽에 싱글 쿼테이션 마크를 사용해야 한다 // 이름이 대문자면 대문자로만 사용해야 함

 

# 연산자

1. 산술 연산자 : * / + -

2. 비교 연산자 : > < >= <= = != <> ^=            // !=, <>, ^= : 같지 않거나

3. 논리 연산자 : and or not

4. 기타 비교 연산자

  1) between A and B ( A에서 B 사이 )

  2) like ( 주어진 문자열이 지정된 패턴과 일치하는지 여부 확인 )

      % ~> wild card : 문자가 0개 이상인 문자열

      _ ~> 단일 문자

  3) in : 여러 값을 지정하여 비교할 때 ( =는 하나만 비교, 반대의 값을 얻고 싶을 때는 not in )

  4) is ( not ) null : null ~> 데이터가 없는 상태 or 알 수 없는 값 ( unknown ) / 비교 불가

 

# 함수

1. 단일행 함수

- 문자 : upper(대문자), lower(소문자), initcap(첫 철자만 대문자)

   substr(잘라냄), instr(포함하여 출력), replace(대신하여 출력)

   lpad(왼쪽에 채우기), rpad(오른쪽에 채우기), trim(양쪽 공백 무시)

- 숫자 : round(반올림), trunc(자르기-내림), mod(나눈 나머지 값 출력)

- 날짜 : sysdate, months_between(날짜와 날짜 사이의 개월 수), add_months(날짜에 개월 수를 더한 날짜)

  next_day(앞으로 돌아올 요일의 날짜), last_day(해당 날짜가 있는 달의 마지막 날짜)

* 날짜 포맷	년 : RRRR, RR, YYYY, YY	월 : MM, MON	일 : DD
시 : HH, HH24	분 : MI	초 : SS	요일 : DAY, D

- 변환 : to_char(문자로 형변환), to_number(숫자로 형변환), to_date(날짜로 형변환)

   data type - 문자형(ename, job 컬럼들), 숫자형(sal, deptno 컬럼들), 날짜형(hiredate 컬럼)

- 일반 : nvl(null값 대체), decode(if then else), case(decode, 부등호 비교도 가능)

   decode(expr, search1, result1, search2, result2, ..., default_result) // 등호 비교만

   case // 등호 비교, 부등호 비교 가능

 

2. 복수행 함수 ( = ★group 함수 )

- max : 최대값

- min : 최소값

- avg : 평균값

- sum : 합계

- count : 건수를 세는 함수

 

Tip. 깔끔하게 나오게 하려면 라인이랑 페이지를 조절해야 함

show lines            // 테이블 가로 사이즈 확인

set lines 5000        // 테이블의 가로 사이즈 조절

set pages 4000        // 테이블의 세로 사이즈 조절

Tip. 철자를 틀렸을 때 문자 ed를 쓰고 엔터를 치면 방금 작성한 SQL의 메모장이 열림 -> 수정 가능, 저장 후 / 엔터

 

참고: partition by

http://blog.naver.com/dirgh1234/220657322746

 

대학 다닐 때 정리했던 것 혹시나 도움이 될까 싶어 올립니다!