테이블 문제
문제 세팅
문1) emp 사원테이블을 생성하시오
create table emp(
empno number(4) primary key --사번(-9999~9999)
,ename varchar2(50) --이름
,job varchar2(10) --직급
,mgr number(4) --매니저정보
,hiredate date --입사일
,sal number(7,2) --급여(소수점 2자리)
,comm number(7,2) --커미션(보너스)
,deptno number(2) --부서코드(-99~99)
);
문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) 전체 행의 갯수를 조회하시오
SELECT COUNT(*) FROM emp;
문4) 테이블의 칼럼(필드) 목록을 확인하시오
SELECT * FROM emp;
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp; --자바 등에서 사용하기 위해서는 열을 다 나열해줘야 한다.
문5) 사원이름순(오름차순) 정렬하시오
SELECT *
FROM emp
ORDER BY ename ASC; --ASC 생략가능
문6) 사원이름순(내림차순) 정렬하시오
SELECT *
FROM emp
ORDER BY ename DESC;
문7) 급여 내림차순으로 사원이름(ename), 급여(sal), 부서코드(deptno)를 조회하시오
SELECT ename, sal, deptno
FROM emp
ORDER BY sal DESC;
문8) 부서코드 오름차순, 급여 내림차순으로
사원이름(ename), 급여(sal), 부서코드(deptno)를 조회하시오
SELECT ename, sal, deptno
FROM emp
ORDER BY deptno, sal DESC;
문9) 이름(ename)/입사일자(hiredate)/부서코드(deptno)를
부서코드 오름차순, 입사일자(hiredate) 오름차순으로 조회하시오
SELECT ename, hiredate, deptno
FROM emp
ORDER BY deptno, hiredate;
문10) 직급(job) 칼럼의 중복데이터를 하나씩만 조회하시오
SELECT DISTINCT job FROM emp;
SELECT job FROM emp GROUP BY job;
문11) emp테이블의 job 오름차순, sal 내림차순으로 정렬해서
ename 이름, job 직급, sal 급여 칼럼명으로 별칭을 바꿔서 조회하시오
SELECT ename 이름, job 직급, sal 급여
FROM emp
ORDER BY job, sal DESC;
문12) 급여가 100이상 400이하보다 작은 직원 조회하기(급여 내림차순)
SELECT ename, sal
FROM emp
WHERE sal>=100 AND sal<=400
ORDER BY sal DESC;
SELECT ename, sal
FROM emp
WHERE sal BETWEEN 100 AND 400
ORDER BY sal DESC;
문13) 급여가 100이하 또는 400 이상의 직원 검색하기(급여 내림차순)
SELECT ename, sal
FROM emp
WHERE sal<=100 OR sal>=400
ORDER BY sal DESC;
문14) 직급(job)이 과장 또는 부장인 직원들을 이름순으로 조회하시오
SELECT job, ename
FROM emp
WHERE job='과장' OR job='부장'
ORDER BY ename;
SELECT job, ename
FROM emp
WHERE job IN ('과장', '부장')
ORDER BY ename;
문15) 부서코드(deptno)가 30인 직원을 조회하시오
SELECT deptno, ename FROM emp WHERE deptno=30;
SELECT deptno, ename FROM emp WHERE deptno IN 30;
문16) 중복된 부서코드를 한개씩만 조회하시오
SELECT DISTINCT deptno FROM emp;
SELECT deptno FROM emp GROUP BY deptno;
문17) 부서코드가 10 또는 20 또는 30인 직원을 조회하시오
(or, in연산자 각각 활용해서 조회)
SELECT deptno, ename
FROM emp
WHERE deptno=10 OR deptno=20 OR deptno=30;
SELECT *
FROM emp
WHERE deptno IN (10, 20, 30);
문18) 급여가 300~500인 직원을 급여순으로 조회하시오
(and, between연산자 각각 활용해서 조회)
SELECT ename, sal
FROM emp
WHERE sal>=300 AND sal<=500
ORDER BY sal;
SELECT ename, sal
FROM emp
WHERE sal BETWEEN 300 AND 500
ORDER BY sal;
문19) 이름이 무궁화 조회하시오
SELECT ename
FROM emp
WHERE ename='무궁화';
문20) 김씨성을 조회하시오
SELECT ename
FROM emp
WHERE ename LIKE '김%';
문21) 이름에 '화' 포함된 줄을 조회하시오
SELECT ename
FROM emp
WHERE ename LIKE '%화%';
문22) 부서코드가 20인 레코드에 대해서
급여의 1%를 커미션으로 책정하시오
UPDATE emp
SET comm=sal*0.01
WHERE deptno=20;
SELECT * FROM emp;
문23) 연봉을 아래와 같이 구한후 이름, 부서, 급여, 커미션, 연봉을 조회하시오
연봉구하는 식 : 급여(sal)*12개월+보너스(comm)
SELECT ename, deptno, sal, comm, sal*12+comm AS 연봉
FROM emp;
문24) 커미션이 null이면 0으로 바꾼후 연봉을 다시 계산해서
이름, 급여, 커미션, 연봉을 조회하시오
--comm이 null값인 행을 조회
SELECT ename, comm FROM emp WHERE comm IS NULL;
--comm칼럼에 null값을 0으로 바꾸기
SELECT ename, NVL(comm, 0)
FROM emp
WHERE comm IS NULL;
--comm칼럼에 null값을 0으로 바꾼 후, 연봉 계산하기
SELECT ename, deptno, sal, sal*12+NVL(comm, 0) AS 연봉
FROM emp;
--연봉을 반올림해서 소수점없이 조회하기
SELECT ename, deptno, sal, ROUND(sal*12+NVL(comm, 0)) AS 연봉
FROM emp;
문25) 각 사람의 급여를 검색해서 '누구누구의 급여는 얼마입니다'로 조회하시오
(|| 결합연산자)
SELECT ename || '의 급여는 ' || sal || '입니다'
FROM emp;
SELECT ename || '의 급여는 ' || sal || '입니다' AS pay
FROM emp;
SELECT CONCAT(ename, '의 급여는 ') CONCAT(sal, '입니다')
FROM emp;
문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=20 OR deptno=30
ORDER BY ename;
SELECT ename, job, deptno
FROM emp
WHERE deptno IN (20, 30)
ORDER BY ename;
문28) 사번, 이름, 부서를 조회하시오
(단, 부서코드가 10이면 관리부
20이면 영업부
30이면 교육부로 바꿔서 출력)
SELECT empno, ename, deptno, CASE WHEN deptno=10 THEN '관리부'
WHEN deptno=20 THEN '영업부'
WHEN deptno=30 THEN '교육부'
END AS deptname
FROM emp;
문29) 다음의 SQL문을 분석하시오
select empno, sal
from emp
where not(sal>200 and sal<300) --not(201~299) 급여가 201~299가 아닌 행 조회
order by sal;
급여가 200 초과 300 미만이 아닌(200이하 또는 300이상) 사람의 사번, 급여를 급여순으로 조회하시오
문30) 다음의 SQL문을 분석하시오
select empno, sal
from emp
where not sal>200 and sal<300 --200이하이면서 300미만. -> 200이하 조회
order by sal;
급여가 200 초과가 아니면서 300 미만인 사람의 사번, 급여를 급여순으로 조회하시오
문31) 부서코드별 급여합계를 구하시오
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
문32) 부서코드별 급여합계를 구해서 그 합계값이 1500이상만 조회하시오
SELECT deptno, SUM(sal) -- 4)
FROM emp --실행순서 1)
GROUP BY deptno -- 2)
HAVING sum(sal)>=1500; -- 3)
문33) 부서코드별 급여평균이 300이상 조회하시오
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>=300;
문34) 급여가 300이상 데이터중에서 부서코드별 급여평균을 구해서 급여평균순으로 조회하시오
SELECT deptno, AVG(sal)
FROM emp
WHERE sal>=300
GROUP BY deptno
ORDER BY AVG(sal);
문35) 급여가 300이상 데이터중에서 부서코드별 급여평균이 400이상을 급여순으로 조회하시오
SELECT deptno, AVG(sal)
FROM emp
WHERE sal>=300
GROUP BY deptno
HAVING AVG(sal)>=400
ORDER BY AVG(sal);
문36) hiredate칼럼을 사용하여 월별로 입사한 인원수를 구하시오
-- 변환함수 : to_char(원래날짜, '원하는모양')
--입사일 조회
SELECT hiredate FROM emp;
--입사일에서 월 가져오기
SELECT to_char(hiredate, 'mm') FROM emp;
SELECT to_char(hiredate, 'mm') AS 월 FROM emp;
SELECT to_char(hiredate, 'yy') AS 입사년도 FROM emp;
SELECT to_char(hiredate, 'dd') AS 일 FROM emp;
--입사월을 정렬해서 조회
SELECT to_char(hiredate, 'mm')
FROM emp
ORDER BY TO_CHAR(hiredate, 'mm');
--입사월별로 그룹화 하기
SELECT to_char(hiredate, 'mm')
FROM emp
GROUP BY TO_CHAR(hiredate, 'mm');
--입사월별로 그룹화하고, 행 갯수 구하기(인원수) & 인원수 순으로 정렬
SELECT to_char(hiredate, 'mm') AS 입사월, COUNT(*) AS 인원수
FROM emp
GROUP BY to_char(hiredate, 'mm')
ORDER BY COUNT(*);
문37) 매니저별 담당인원수를 조회하시오
SELECT mgr, COUNT(*)
FROM emp
GROUP BY mgr;
문38) 사원번호 7654의 급여보다 적은 행을 조회하시오
SELECT sal FROM emp WHERE empno=7654; --400
SELECT empno, ename, sal
FROM emp
WHERE sal<(SELECT sal FROM emp WHERE empno=7654);
문39) 부서별로 급여+커미션를 구했을때 최대값, 최소값, 평균값(반올림 해서)을 부서순으로 조회하시오
SELECT deptno, sal, comm, sal+nvl(comm, 0) FROM emp ORDER BY deptno;
SELECT deptno, MAX(sal+nvl(comm, 0)) 최대값, MIN(sal+nvl(comm, 0)) 최소값, ROUND(AVG(sal+nvl(comm, 0))) 평균값
FROM emp
GROUP BY deptno
ORDER BY deptno;
문40) 각 직원들에 대해서 직원의 이름과 근속년수를 구하시오
단, 근속년수는 연단위를 버림하여 나타내시오
--한 번에 조회하려고 하지 말고 순차적으로 데이터를 보면서 하자
--이름, 입사일을 입사일 순으로 조회
SELECT ename, hiredate
FROM emp
ORDER BY hiredate;
--근속일 : 현재 날짜-입사일
SELECT ename, hiredate, sysdate-hiredate FROM emp;
--(현재 날짜-입사일)/365
SELECT ename, hiredate, (sysdate-hiredate)/365 FROM emp;
--(현재 날짜-입사일)/365 -> 소수점 버림
SELECT ename, hiredate 입사일, TRUNC((sysdate-hiredate)/365) 근속년수 FROM emp;
--근속년도 순으로 내림차순 정렬
SELECT ename, hiredate 입사일, TRUNC((sysdate-hiredate)/365) 근속년수
FROM emp
ORDER BY 근속년수 DESC;
--------------------------------------------------------------------------------------
SELECT ename, sysdate, hiredate, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) 근속년수
FROM emp;
문41) 아래와 같이 출력 하시오
예) 박지성의 근속년수 : 20년
SELECT ename, hiredate FROM emp ORDER BY hiredate;
SELECT ename, sysdate-hiredate FROM emp ORDER BY hiredate;
SELECT ename, (sysdate-hiredate)/365 FROM emp ORDER BY hiredate;
SELECT ename, trunc((sysdate-hiredate)/365) FROM emp ORDER BY hiredate;
SELECT ename || '의 근속년수 ' || trunc((sysdate-hiredate)/365) || '년' FROM emp ORDER BY hiredate;
SELECT ename || '의 근속년수 ' || trunc((sysdate-hiredate)/365) || '년' AS 근속년수 FROM emp ORDER BY hiredate;
-----------------------------------------------------------------------------------------------------------
SELECT ename || '의 근속년수 : ' || TRUNC((sysdate-hiredate)/365) || '년'
FROM emp;
----------------------------------------------------------------------------------------
SELECT ename || '의 근속년수 : ' || TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) || '년'
FROM emp;
문42) 손흥민의 근속년수와 동일한 행을 조회(이름, 근속년수)하시오
--손흥민 조회하기
SELECT * FROM emp WHERE ename='손흥민';
--손흥민 근속년수 구하기
SELECT TRUNC((sysdate-hiredate)/365)
FROM emp
WHERE ename='손흥민'; --20
SELECT ename, TRUNC((sysdate-hiredate)/365) 근속년수
FROM emp
WHERE TRUNC((sysdate-hiredate)/365)=(SELECT TRUNC((sysdate-hiredate)/365)
FROM emp
WHERE ename='손흥민');
문43) 입사한지 만15년 이상된 사람에 한해 현재연봉에서 10% 인상시켰을 때
사번, 이름, 입사일, 현재연봉, 인상후연봉, 인상된금액으로 고액연봉순으로 조회하시오
연봉구하는 식 : 급여(sal)*12개월+보너스(comm)
--입사한지 만15년 이상된 사람 조회하기
SELECT ename, hiredate, sal
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) >= 15;
--현재 연봉 구하기
SELECT ename, hiredate, sal, sal*12+comm
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) >= 15;
--현재 연봉 구하기(단, 커미션이 없으면 0으로 바꿔서 계산)
SELECT ename, hiredate, sal, sal*12+nvl(comm, 0) AS 현재연봉
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) >= 15;
--현재 연봉에서 10% 인상된 금액
SELECT ename, hiredate, sal
, sal*12+nvl(comm, 0) AS 현재연봉
, (sal*12+nvl(comm, 0))*0.1 AS 인상된금액
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) >= 15;
--현재 연봉 + 인상된 금액 = 최종 연봉(인상 후 연봉)
SELECT ename, hiredate, sal
, sal*12+nvl(comm, 0) AS 현재연봉
, (sal*12+nvl(comm, 0))*0.1 AS 인상된금액
, sal*12+nvl(comm, 0) + (sal*12+nvl(comm, 0))*0.1 AS 인상후연봉
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) >= 15;
--최종연봉 소수점없이 반올림
SELECT ename, hiredate, sal
, sal*12+nvl(comm, 0) AS 현재연봉
, (sal*12+nvl(comm, 0))*0.1 AS 인상된금액
, ROUND(sal*12+nvl(comm, 0) + (sal*12+nvl(comm, 0))*0.1) AS 인상후연봉
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) >= 15;
--최종연봉 내림차순 정렬하기
SELECT ename, hiredate, sal
, TRUNC((sysdate-hiredate)/365) AS 근속년수
, sal*12+nvl(comm, 0) AS 현재연봉
, (sal*12+nvl(comm, 0))*0.1 AS 인상된금액
, ROUND(sal*12+nvl(comm, 0) + (sal*12+nvl(comm, 0))*0.1) AS 인상후연봉
FROM emp
WHERE TRUNC((sysdate-hiredate)/365) >= 15
ORDER BY 인상후연봉 DESC;
-------------------------------------------------------------------------------------------------------------
SELECT empno, ename, hiredate, sal, sal*12+NVL(comm, 0) AS 현재연봉, (sal*12+NVL(comm, 0))*1.1 AS 인상후연봉, (sal*12+NVL(comm, 0))*0.1 AS 인상된금액
FROM emp
WHERE TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12)>=15
ORDER BY 인상된금액 DESC;
select * from emp;
문44) 입사년도가 짝수인 직원들의 급여의 평균을 job별로 출력하시오
--직급, 입사일 조회하기
SELECT JOB, hiredate FROM emp;
--입사일에서 년도 추출하기
SELECT hiredate, EXTRACT(YEAR FROM hiredate) FROM emp;
--짝수년도 추출하기
SELECT job, hiredate, EXTRACT(YEAR FROM hiredate)
FROM emp
WHERE MOD(EXTRACT(YEAR FROM hiredate), 2)=0; --mod() 나머지 연산
--직급별 급여의 평균 구하기
SELECT job, AVG(sal)
FROM emp
WHERE MOD(EXTRACT(YEAR FROM hiredate), 2)=0
GROUP BY job;
연산자 우선순위
() 괄호 : 연산자 우선순위보다 우선함
1순위 : 비교연산자, SQL연산자, 산술연산자
2순위 : not
3순위 : and
4순위 : or
5순위 : 결합연산자