[MySQL] #6 SQL 기본 쿼리 실습을 해보자~ -2-

-- 1) emp 테이블 정보를 확인하세요.

DESC emp;

-- 2) 부서번호가 20번인 부서의 사람 중 사원번호, 이름, 월급을 출력하라

SELECT empno, ename, sal
FROM emp
WHERE deptno = 20;

-- 3) 사원번호가 7521인 사람 중 이름, 입사일, 부서번호를 출력하라.

SELECT ename, hiredate, deptno
FROM emp
WHERE empno = 7521;

-- 4) 이름이 JONES인 사람의 모든 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE ename = 'JONES';

-- 5) 직업이 MANAGER인 사람의 모든 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE job = 'MANAGER';

-- 6)직업이 MANAGER가 아닌 사람의 모든 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE job != 'MANAGER';

-- 7) 급여가 $1000 이상인 사람의 이름, 급여, 부서번호를 출력하라.

SELECT ename, sal, deptno
FROM emp
WHERE sal >= 1000;

-- 8) 급여가 $1,600보다 크고 $3,000보다 작은 사람의 이름, 직업, 급여를 출력하라.

SELECT ename, job, sal
FROM emp
WHERE sal > 1600 AND sal < 3000;

-- 9) 입사일이 80년도인 사람의 모든 정보를 출력하라

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE hiredate BETWEEN 19800101 AND 19801231;
# date_format(hiredate, "%Y") = 1980
# YEAR(hiredate) = 1980
# substring(hiredate, 3, 2) = 80
# hiredate LIKE "1980%"

-- 10) 입사일이 81/12/09 보다 먼저 입사한 사람들의 모든 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE hiredate < 19811209;

-- 11) 이름이 S로 시작하는 사원의 사원번호, 이름, 입사일, 급여을 출력하라.

SELECT empno, ename, hiredate, sal
FROM emp
WHERE ename LIKE 'S%';

-- 12) 이름 중 S자가 들어가 있는 사람만 모든 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE ename LIKE '%S%';

-- 13) 커미션이 NULL인 사람의 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE comm IS NULL;

-- 14) 커미션이 NULL이 아닌 사람의 모든 정보를 출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE comm IS NOT NULL;

-- 15) 부서가 30번 부서이고 급여가 $1,500 이상인 사람의 이름, 부서 ,월급을 출력하라.

SELECT e.ename, d.dname, e.sal
FROM emp e, dept d
WHERE e.deptno = d.deptno 
AND d.deptno = 30 AND e.sal >= 1500;

-- 16) 부서번호가 30인 사원을 검색,  사원번호로 정렬.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE deptno = 30
ORDER BY empno;

-- 17) 급여가 많은 순으로 SORT(정렬)하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
ORDER BY sal DESC;

-- 18) 부서번호로 ASCENDING SORT한 후 급여가 많은 사람 순으로  출력하라.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
ORDER BY deptno, sal DESC;

-- 19) 부서번호가 DESCENDING SORT하고, 이름 순으로 ASCENDING SORT,급여 순으로 DESCENDING SORT 하라

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
ORDER BY deptno DESC, ename, sal DESC;

-- 20) emp Table에서 이름, 급여, 커미션 금액, 총액(sal + comm)을  구하여 총액이 많은 순서로 출력하라. 
-- 단, 커미션이 NULL인 사람은 제외한다.(커미션금액: sal*comm/100)

SELECT ename, sal, sal*comm/100 commition, sal + comm total
FROM emp
WHERE comm IS NOT NULL
ORDER BY total DESC;

-- 21)  10번 부서의 모든 사람들에게 급여의 13%를 보너스로 지불하기로 하였다. 이름, 급여, 보너스 금액, 부서번호를 출력하라.

SELECT ename, sal, if(deptno = 10, sal*0.13, 0) bonus, deptno
FROM emp;

-- 22)  30번 부서의 연봉을 계산하여 이름, 부서번호, 급여, 연봉을 출력하라. 단, 연말에 급여의 150%를 보너스로 지급한다.

SELECT ename, deptno, sal, sal*12+IFNULL(comm, 0)+(sal*1.5) 연봉
FROM emp
WHERE deptno = 30;

-- 23) 부서번호가 20인 부서의 시간당 임금을 계산하여 출력하라. 단, 1달의 근무일수는 12일이고, 1일 근무시간은 5시간이다. 
-- 출력양식은 이름, 급여, 시간당 임금(소수이하 첫 번째 자리에서 반올림)을 출력하라

SELECT ename, sal, round(sal/12/5) 시간당임금
FROM emp
WHERE deptno = 20;

# round() 0 defalut

-- 24)  급여가 $1,500부터 $3,000 사이의 사람은 급여의 15%를 회비로 지불하기로 하였다. 
-- 이를 이름, 급여, 회비(소수점 두자리아래에서 반올림)를 출력하라.

SELECT ename, sal, if(sal >= 1500 AND sal <= 3000, round(sal*0.15 , 1), 0) 회비
FROM emp;

-- 25) 급여가 $1500 ~ $2000 이 아닌 사원의 ename 과 SAL를 검색
--  각각 별칭을 NAME, SALARY로 합니다.

SELECT ename NAME, sal SALARY
FROM emp
WHERE sal < 1500 OR sal > 2000;
# sal NOT BETWEEN 1500 AND 2000;