DB 구조와 관리 1 중간고사 시험 범위 정리 (oracle SQL 문제) : 140418

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

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;