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

-- 1) emp 테이블에서 30번 부서가 아닌 사원들의 이름, 급여, 부서번호를 조회

SELECT ename, sal, deptno
FROM emp
WHERE deptno != 30;

-- 2) 급여가 보너스(comm)보다 더 작은 사원의 급여,이름,업무, 보너스를 검색

SELECT sal, ename, job, comm
FROM emp
WHERE sal < ifnull(comm, 0);

# WHERE에서는 별칭 사용 불가

-- 3) 급여가 2800이상이고 JOB이 MANAGER인 사원의 사번, 이름, 담당업무, 급여, 입사일자, 부서번호를 검색

SELECT empno, ename, job, sal, hiredate, deptno
FROM emp
WHERE sal >= 2800
AND job = 'MANAGER';

-- 4) ename이 A문자로 시작되지 않는 사원들만 조회

SELECT empno, ename
FROM emp
WHERE ename not like 'A%';

empno	ename
7369	SMITH
7521	WARD
7566	JONES
7654	MARTIN
7698	BLAKE
7782	CLARK
7788	SCOTT
7839	KING
7844	TURNER
7900	JAMES
7902	FORD
7934	MILLER

-- 5) comm이 null이 아닌 사원들의 이름, 급여, comm, 부서번호를 조회
-- comm의 별칭을 보너스로 하고 보너스가 높은 사원순으로(desc) 정렬

SELECT ename, sal, comm 보너스, deptno
FROM emp
WHERE comm is not NULL
ORDER BY comm DESC;

-- 6) 사원중에서 담당업무가 SALESMAN이거나 사장(PRESIDENT)이면서 동시에 급여가 1500 이상이 되는 사원을 검색

SELECT empno, ename, job, sal
FROM emp
WHERE job IN ('SALESMAN', 'PRESIDENT') 
AND sal >= 1500;

-- 7) 부서번호로 기본 정렬한후, 부서번호가 같을경우 급여가 많은 순으로 정렬하여 사번, 이름, 업무, 부서번호,급여를 조회

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

 

	empno	ename	job	deptno	sal
	7839	KING	PRESIDENT	10	5000
	7782	CLARK	MANAGER	10	2450
	7934	MILLER	CLERK	10	1300
	7788	SCOTT	ANALYST	20	3000
	7902	FORD	ANALYST	20	3000
	7566	JONES	MANAGER	20	2975
	7876	ADAMS	CLERK	20	1100
	7369	SMITH	CLERK	20	800
	7698	BLAKE	MANAGER	30	2850
	7499	ALLEN	SALESMAN	30	1600
	7844	TURNER	SALESMAN	30	1500
	7521	WARD	SALESMAN	30	1250
	7654	MARTIN	SALESMAN	30	1250
	7900	JAMES	CLERK	30	950

-- 8) 부서별로 담당하는 업무를 한번씩 조회하시오
-- 단, 업무 기준으로 정렬합니다.

SELECT DISTINCT job, deptno
FROM emp
ORDER BY job;

-- 9) 입사일이 가장 오래된 사원 5명만 조회하시오

SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate
LIMIT 5;