[SQL] 활용 연습 문제

[SQL 연습문제]

1. 테이블 만들기

 

2. 행 추가

 

3. 문제 

 1) 테이블의 필드 목록을 확인하시오 (사번과 이름)

 select emptno, ename from emp;

 

 

 

 

 

 

 

 

 

 

 2) 사원이름순 (오름차순) 정렬하시오

select empno, ename   --2) 칼럼 선택

from emp                       --1) 테이블

order by ename asc;     --3) 정렬 (asc : 오름차순, 굳이 안 써도 됨)

 

 

 

 

 

 

 

 3) 사원이름순(내림차순) 정렬하시오

select empno, ename   

from emp                       

order by ename desc; 

 

 

 

 

 

 

 

4) 급여 내림차순으로 사원이름(ename), 급여(sal), 부서코드(deptno)를 조회하시오

select ename, sal, deptno

from emp

order by sal desc;

 

 

 

 

 

 

 

 5) 부서코드 오름차순, 급여 내림차순으로 사원이름(ename), 급여(sal), 부서코드(deptno)를 조회하시오  

select name, sal, deptno

from emp

order by deptno asc, sal desc;

 

 ▶  , 를 사용해서 한 번에 두 가지 정렬 가능 

(deptno asc의 asc라는 오름차순 정렬은 생략가능)

 

 

 

 

 

6) 이름(ename)/입사일자(hiredate)/부서코드(deptno)를 부서코드 오름차순, 입사일자(hiredate) 오름차순으로 조회하시오

select ename, hiredate, deptno

from emp

order by deptno asc, hiredate asc;

 

 

 

 

 

 

7) 직급(job) 칼럼의 중복데이터를 하나씩만 조회하시오

select distinct (job) from emp;

 

 

 

 

 

8) emp테이블의 job 오름차순, sal 내림차순으로 정렬해서 ename 이름, job 직급, sal 급여 칼럼명으로 별칭을 바꿔서 조회하시오

select ename as 이름, job as 직급, sal as 급여

from emp

order by job, sal desc;

 

 ▶ order by job, sal desc;

  : 두 가지 정렬을 한 번에 적용가능, 실행 순서상 칼럼명이 '급여'로 바뀌었기 때문에 정렬에 넣을 때 '급여'라고 'sal' 대신 넣는 것이 가능함

 (= order by job, 급여 desc;)

 

9) 급여가 100보다 많고 400보다 작은 직원 조회하기(급여 내림차순)

select ename, sal

from emp

where sal>100 and sal<400

order by sal desc; 

 

 

 

10) 급여가 100이하 또는 400 이상의 직원 검색하기(급여 내림차순)

1) select ename, sal from emp

     where sal<=100 or sal>=400 order by sal desc;

2) select ename, sal from emp

     where not (sal>100 and sal<400) order by sal desc;

 

11) 직급(job)이 과장 또는 부장인 직원들을 이름순으로 조회하시오

select ename, job from emp 

where job='과장' or job='부장';

 

 

12) 부서코드(deptno)가 30인 직원을 조회하시오

select ename, deptno

from emp

where deptno=30;

 

 

13) 중복된 부서코드를 한개씩만 조회하시오

select distinct (deptno) from emp;

 

 

 

14) 부서코드가 10 또는 20 또는 30인 직원을 조회하시오 (or, in연산자 각각 활용해서 조회)

1) select ename, deptno from emp

where deptno=10 or deptno=20 or deptno=30;

 

2) select ename, deptno from emp 

where deptno in (10, 20, 30) ; 

 

▶ in : 여러 값을 or 관계로 묶어 나열하는 조건을 where 절에 사용할 때 쓸 수 있는 키워드.

       : 조건의 범위를 지정하는 데 사용, 값은 콤마( , )로 구분하여 괄호 내에 묶으며 이 값 중에서 하나 이상과 일치하면 조건에 맞는 것으로 평가 된다

 

15) 급여가 300~500인 직원을 급여순으로 조회하시오 (and, between연산자 각각 활용해서 조회)

1) select ename, sal from emp

where sal between 300 and 500 order by sal;

 

2) select ename, sal from emp

where sal>=300 and sal<=500 order by sal;

 

 

16) 이름이 무궁화 조회하시오

select ename from emp

where ename='무궁화';

 

17) 김씨성을 조회하시오

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

▶  where 칼럼명 like 연산자 : 문자열에서 비슷한 유형, 문자열에서 만능문자 [% (문자 개수 상관 없이 조건만 맞으면 됨), _ (문자 개수가 맞게 이용 되야 함)]를 이용해 비슷한 유형을 조회

 

18) 이름에 '화' 포함된 줄을 조회하시오

select ename from emp

where ename like '%화%';

                       ▶ EX) %화% : 중간에 ' 화 '만 들어가면 됨   // _화_ : 무조건 세 글자 중 가운데 글자가 '화' 인 경우

 

 19) 연봉을 아래와 같이 구한후 이름, 급여, 커미션, 연봉을 조회하시오. (연봉구하는 식 : 급여(sal)*12개월+보너스(comm))

 <'연봉' 칼럼을 만들지 않고 바로 적용> → comm와 연봉 모두 null 값이 나옴

select ename as 이름, sal as 급여, comm as 커미션, sal*12+comm as 연봉

from emp;

 

★ select null+1 from dual;     - null ★

  : null 값에 계산이 들어가게 되면 null이 발생함

  : 때문에 comm과 연봉 모두 null 값이 나옴 

 

 

 

20) 커미션이 null이면 0으로 바꾼후 연봉을 다시 계산해서 이름, 급여, 커미션, 연봉을 조회하시오

select nvl(comm, 0) from emp;

 

▶ nvl(comm,0)

 : comm가 null이 나올 경우, 0으로 치환

 

select ename as 이름, sal as 급여, nvl(comm,0) as 커미션, sal*12+nvl(comm,0) as 연봉

from emp;

 

 21) 각 사람의 급여를 검색해서 '누구누구의 급여는 얼마입니다'로 조회하시오 (|| 결합연산자)

select ename||'의 급여는 '  || sal ||'입니다' from emp;

 

▶ 문자결합연산자

 : || 를 사용

 : 넣고 싶은 칼럼명||'  '

  → 작은 따옴표 안에 연결할 말을 넣음 (자바에서 +" " 쓰듯이 사용하면 됨)

 

 

 

 

22) 다음의 SQL문을 분석하시오

select empno, sal from emp   where not(sal>200 and sal<300)  order by sal;

-( ) 괄호 연산자는 최우선 연산자, NOT 연산자는 말그대로 바로 뒤에 오는 조건을 부정하는 역할 -  급여가 200초과이면서 300미만인 사원을 제외한 나머지 사원의 사번, 급여를 급여를 기준으로 정렬하시오     = 급여가 200이하 또는 300이상인 사원의 사번과 급여를, 급여를 기준으로 정렬하시오

 

 23) 다음의 SQL문을 분석하시오

select empno, sal from emp   where not sal>200 and sal<300   order by sql;

- 괄호가 없기 때문에  not은 sal>200에만 적용이 되고 sal<300에는 영향X - 급여가 200이하이고 (= 200보다 작고) 300미만인 사원의 사번, 급여를 급여를 기준으로 정렬하시오

 

<연산자 우선순위>    () 괄호 : 연산자 우선순위보다 우선함    1순위   : 비교연산자, SQL연산자, 산술연산자    2순위   : not    3순위   : and    4순위   : or    5순위   : 결합연산자

 

24) emp테이블에서 입사일(hiredate)이 2005년 1월 1일 이전인 사원에 대해 사원의 이름(ename), 입사일, 부서번호(deptno)를 입사일순으로 조회하시오

방법1) 

select ename, hiredate, deptno from emp

where hiredate < to_date('05/01/01', 'YY/MM/DD') order by hiredate;

 

방법2)

select ename, hiredate, deptno from emp

where hirdate <'2005-01-01' order by hiredate;

 

25) emp테이블에서 부서번호가 20번이나 30번인 부서에 속한 사원들에 대하여 이름, 직업코드(job), 부서번호를 이름순으로 조회하시오 (or, in연산자 각각 활용해서 모두 조회)

방법1)

select ename, job, deptno from emp

where deptno=20 or deptno=30 order by ename;

 

방법2)

select ename, job, deptno from emp

where deptno in (20, 30) order by ename;