07월 22일 월 | SQL활용 03 - SQL연습문제

SQL 연습문제

 

SQL 쿼리문 작성 연습

 

문제 1) emp 사원테이블을 생성하시오.

create table emp(  empno number(4) --사번  ,ename varchar2(10) --이름  ,job varchar2(9) --직급  ,mgr number(4) --매니저정보  ,hiredate date --입사일  ,sal number(7,2) --급여  ,comm number(7,2) --커미션(보너스)  ,deptno number(2) --부서코드  );

 

 

 

문제 2) emp 사원테이블에 행을 추가하시오.

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7369, '개나리', '사원', 7902, '2000-12-17', 200, 20);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7499, '진달래', '주임', 7698, '2001-12-15', 360, 20);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7521, '라일락', '주임', 7698, '2001-02-17', 355, 30);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7654, '손흥민', '과장', 7839, '2002-01-11', 400, 30);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7698, '박지성', '주임', 7698, '2000-07-12', 325, 20);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7782, '김연아', '사원', 7698, '2001-12-17', 225, 10);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7778, '무궁화', '사원', 7839, '2005-11-14', 200, 10);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7839, '홍길동', '부장', 7566, '2006-06-17', 450, 20);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7844, '송강호', '과장', 7566, '2018-09-17', 400, 30);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7876, '정우성', '대표', 7839, '2004-09-09', 500, 30);  insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)  values(7900, '김혜수', '사원', 7902, '2001-12-03', 200, 20);

 

 

 

문제 3) 테이블의 필드 목록을 확인하시오. 

desc emp;

 

 

 

 

문제 4) 사원이름순(오름차순) 정렬하시오.

select ename from emp order by ename asc;

 

 

 

문제 5) 사원이름순(내림차순) 정렬하시오.

select ename from emp order by ename desc;

 

 

 

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

select ename, sal, deptno from emp order by sal desc;

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

select ename, sal, deptno from emp order by deptno asc, sal desc;

 

 

 

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

select ename, hiredate, deptno from emp order by deptno asc, hiredate asc;

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

select distinct(job) from emp;

 

 

 

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

select ename as 이름, job as 직급, sal as 급여 from emp order by job asc, sal desc;

 

 

 

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

select ename, sal from emp where sal>100 and sal<400 order by sal desc;

 

 

 

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

select ename, sal from emp where sal<100 or sal>400 order by sal desc;

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

select ename, job from emp where job = '과장' or job = '부장' order by ename;

 

 

 

 

문제 14) 부서코드(deptno)가 30인 직원을 조회하시오.

 

 

 

문제 15) 중복된 부서코드를 한개씩만 조회하시오.

select distinct(deptno) from emp;

 

 

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

select ename, deptno from emp where deptno = 10 or deptno = 20 or deptno = 30; select ename, deptno from emp where deptno in (10,20,30);

 

 

 

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

select ename, sal from emp where sal >=300 and sal <=500; select ename, sal from emp where sal between 300 and 500;

 

 

문제 18) 이름이 무궁화 조회하시오.

select ename from emp where ename = '무궁화';

 

 

문제 19) 김씨성을 조회하시오.

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

 

 

 

문제 20) 이름에 '화' 포함된 줄을 조회하시오.

select * from emp where ename like '%화%';

 

 

 

 

문제 21) 연봉을 아래와 같이 구한후 이름, 급여, 커미션, 연봉을 조회하시오.

연봉구하는 식 : 급여(sal)*12개월+보너스(comm)

select ename,sal,comm, (sal*12+comm) as ysal from emp;

 

 

 

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

update emp set comm = 0; select ename,sal,comm, (sal*12+comm) as ysal from emp;

 

+ 부서코드가 20인 레코드에 대해서, 보너스를 급여의 1%로 책정해서 계산하기.

update emp set comm=sal*0.01 where deptno = 20;

 

 

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

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

 

* 문자결합연산자

 

 

 

 

문제 24) 다음의 SQL문을 분석하시오.
select empno, sal from emp
where not(sal>200 and sal<300)
order by sal;

직원번호, (급여가 200~300이 아닌 직원)급여를 200보다 크고 300보다 작은 사람이 아닌 직원을

급여 오름차순으로 정렬

 

 

 

 

문제 25) 다음의 SQL문을 분석하시오.
select empno, sal from emp
where not sal>200 and sal<300
order by sal;

급여가 200보다 크지 않은 것들 중에서 300보다 작은 것??

괄호가 없기 때문에 not연산자가 sal>200에만 적용된다.

 

 

※ 연산자 우선순위

() 괄호: 연산자 우선순위보다 우선함

1순위: 비교연산자, SQL연산자, 산술연산자

2순위: not

3순위: and

4순위: or

5순위: || (결합연산자)

 

 

 

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

select ename, hiredate, deptno from emp where hiredate < '2005-01-01' order by hiredate;

 

 

 

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

select ename, job, deptno from emp where deptno in (20,30) order by ename; select ename, job, deptno from emp where deptno = 20 or deptno =30 order by ename;

 

 

 

 

 

SQL 함수

오라클 함수

 

ASCII 문자변환

select chr(65) from dual; select chr(97) from dual;

 

|| 결합연산자

A || B -> A와 B를 연결시킴

 

 

concat(칼럼명, '문자열')

컬럼에 해당하는 문자열을 붙임

select concat('로미오와','줄리엣') from dual;

select concat(ename, '의 직급은'), job from emp;

 

 

 

sysdate

시스템의 현재 날짜/시간을 리턴하는 함수

select sysdate from dual;

select sysdate+100 from dual;

select sysdate+200 from dual;

 

--rownum: 행(레코드) 번호

--rowid: 행(레코드)의 주소값

--의사칼럼(모조칼럼)

select empno, ename from emp;

select rownum, empno, ename from emp;

select rowid, empno, ename from emp;

select rowid, rownum, empno, ename from emp;

 

--두 날짜 사이의 개월 수 계산 함수

select('','') from dual;

select months_between('2019-09-01','2019-07-05') from dual;

 

 

--to_date('날짜문자열')

문자열을 날짜형으로 변환

select to_date('2019-05-20') from dual;

select to_date('2019-05-20')-to_date() from dual;

 

 

--to_char(날짜. '출력형식')

날짜자료형의 데이터를 문자변환 함수

select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss day') from dual;

 

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

24시간 기준

 

 

--숫자변환 함수

-> to_number('숫자형태의문자열')

-> 내부적으로 to_number()가 호출됨

select 100+10, 100-10, 100*10 100/10 from dual;

 

--숫자함수

 

trunc(숫자1, 자리수)

-> 버림, 숫자1을 소수점 자리수에서 절사

 

round(숫자1, 자리수)

-> 숫자1을 소수점 자리수에서 반올림

 

ceil(숫자1)

-> 올림

 

--급여평균, 급여합계, 최고급여, 최저급여, 갯수

select avg(sal), sum(sal), max(sal), min(sal), min(sal), count(sal) --null은 카운트 안함

from emp;

 

select round(avg(sal),2) --소수 둘째자리에서 반올림

        ,ceil(avg(sal)) --올림

        ,trunc(avg(sal),1) --소수 첫째자리에서 절사

from emp;

 

 

문제 28) 각 직원들에 대해 직원의 이름과 근속연수를 구하시오. 단, 근속연수는 연단위를 버림하여 나타내시오.

select ename, trunc(months_between(sysdate,hiredate)/12) as 근속연수 from emp; --내가 한 것 select ename, trunc((sysdate-hiredate)/365) as 근속연수 from emp; --선생님 풀이

 

decode

decode(A, B, A==B일 때의 값, A<>B일 때의 값)

A<>B일 때의 값을 생략하면 null로 처리됨.

decode함수의 매개변수의 갯수는 다중조건에 의해 늘어날 수 있음

 

 

예) 주민번호를 이용해서 성별출력

SQL문을 인덱스가 1부터 시작

select substr('8912303',7,1) from dual;

 

 

문제 29) 각 직원의 이름, 직급, 급여를 나타내는데 단, 급여는 5자리로 나타내며 부족한 자리수는 '*'로 표시한다.

월급이 300이상인 직원만 나타내시오.

select ename, job, lpad(sal, 5, '*') from emp where sal>=300;

 

 

문제 30) 전체 직원에 대하여 직원의 이름과 직급, 총 근무주(week)수를 구하시오 (단, 근무주수가 많은 직원부터 나타내고, 근무주수가 같으면 이름에 대하여 오름차순 정렬하시오.)

select ename, job, trunc((sysdate-hiredate)/7) as 근무주수 from emp order by 근무주수 desc, ename;