MySQL - DML 실습

2022-07-14(19일차)

📃 SQL 예제(1)

create database tutor;

create table dept (
   deptno decimal(2),
   dname varchar(14),
   loc varchar(13),
   constraint pk_dept primary key (deptno)
);

create table emp (
   empno decimal(4),
   ename varchar(10),
   job varchar(9),
   mgr decimal(4),
   hiredate date,
   sal decimal(7,2),
   comm decimal(7,2),
   deptno decimal(2),
   constraint pk_emp primary key (empno),
   constraint fk_deptno foreign key (deptno) references dept(deptno)
);

create table salgrade (
   grade tinyint,
   losal smallint,
   hisal smallint
);

tinyint : 1byte
smallint : 2byte

insert into dept values (10, 'accounting', 'new york');
insert into dept values (20, 'research', 'dallas');
insert into dept values (30, 'sales', 'chicago');
insert into dept values (40, 'operations', 'boston');

-- 컬럼 명 생략 --
insert into emp values (
	7369, 'smith', 'clerk', 7902, str_to_date('17-12-1980', '%d-%m-%Y'), 800, null, 20
);
insert into emp values (
	7499, 'allen', 'salesman', 7698, str_to_date('20-2-1981', '%d-%m-%Y'), 1600, 300, 30
);
insert into emp values (
	7521, 'ward', 'salesman', 7698, str_to_date('22-2-1981', '%d-%m-%Y'), 1250, 500, 30
);
insert into emp values (
	7566, 'jones', 'manager', 7839, str_to_date('2-4-1981', '%d-%m-%Y'), 2975,null, 20
);
insert into emp values (
	7654, 'martin', 'salesman', 7698, str_to_date('28-9-1981', '%d-%m-%Y'), 1250, 1400, 30
);

insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);

select str_to_date('20220714', '%Y%m%d');

---

📃 1. 사원 테이블(emp)의 모든 레코드를 조회하시오.

select * from emp;

---

📃 2. 사원명(ENAME)과 입사일(HIREDATE)을 조회하시오.

select ename, hiredate
	from emp;

---

📃 3. 사원번호(DEPTNO)와 이름(ENAME)을 조회하시오.

select deptno, ename
	from emp;

---

📃 4. 사원테이블(EMP)에 있는 직책(JOB)의 목록을 조회하시오.

select job
	from emp;

---

📃 5. 총 사원수를 구하시오.

select count(ename) from emp;

---

📃 6. EMP 테이블의 부서번호가 20인 사원을 조회하시오.

select ename
	from emp
	where deptno = 20;

---

📃 7. EMP 테이블의 월급여가 2500이상 되는 사원을 조회하시오.

select ename
	from emp
	where sal >= 2500;

---

📃8. EMP 테이블의 이름이 'allen'인 사원을 조회하시오.

---

📃 9. EMP 테이블의 사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름을 조회하시오.

select empno, ename
	from emp
	where ename like 's%';

---

📃10. EMP 테이블의 사원 이름에 T가 포함된 사원의 사원번호와 이름을 조회하시오.

select empno, ename
	from emp
	where ename like '%t%';

---

📃 11. EMP 테이블의 커미션(COMM)이 300, 500, 1400 인 사원의 사번(EMPNO),이름(ENAME),커미션(COMM)을 조회하시오.

select empno, ename, comm
	from emp
	where comm = 300 or comm = 500 or comm = 1400;

---

📃 12. EMP 테이블의 월급여(SAL)가 1200 에서 3500 사이의 사원의 사번(EMPNO),이름(ENAME),월급여(SAL)를 조회하시오.

select empno, ename, sal
	from emp
	where sal between 1200 and 3500;

---

📃 13. EMP 테이블의 직급(JOB)이 매니저이고 부서번호(DEPONO)가 30번인 사원의 이름(ENAME),사번(EMPNO),직급(JOB),부서번호(DEPTNO)

select ename, empno, job, deptno
	from emp
	where job = 'manager' and deptno = 30;

---

📃 14. EMP 테이블의 부서번호(DEPTNO)가 30인 아닌 사원의 사번(EMPNO),이름(ENAME),부서번호(DEPTNO)를 조회하시오.

select empno, ename, deptno
	from emp
	where deptno != 30;

---

📃 15. EMP 테이블의 커미션(COMM)이 300, 500, 1400 이 모두 아닌 사원의 사번(EMPNO),이름(ENAME),커미션(COMM)을 조회하시오.

select empno, ename, comm
	from emp
	where comm != 300 and comm != 500 and comm != 1400;

---

📃 16. EMP 테이블의 이름(ENAME)에 S가 포함되지 않는 사원의 사번(EMPNO),이름(ENAME)을 조회하시오.

select empno, ename
	from emp
	where ename not like ('%s%');

---

📃 17. EMP 테이블의 급여(SAL)가 1200보다 미만이거나 3700 초과하는 사원의 사번(EMPNO),이름(ENAME),월급여(SAL)를 조회하시오.

select empno, ename, sal
	from emp
	where sal < 1200 or sal > 3700;

---

📃 18. EMP 테이블의 직속상사(MGR)가 NULL 인 사원의 이름(ENAME)과 직급(JOB)을 조회하시오.

select ename, job
	from emp
	where mgr = null;

---

📃 19. EMP 테이블의 부서별(DEPTNO) 평균월급여를 구하시오.

select avg(deptno) from emp;

---

📃 20. EMP 테이블의 부서별(DEPTNO) 전체 사원수(DEPTNO)와 커미션(COMM)을 받는 사원들의 수를 구하시오.

select count(deptno), count(comm)
	from emp
	group by deptno;

---

📃 21. EMP 테이블의 부서별(DEPTNO) 최대 급여와 최소 급여를 구하시오.

select max(sal), min(sal)
	from emp
	group by deptno;

---

📃 22. EMP 테이블의 부서별(DEPTNO)로 급여 평균(단, 부서별 급여 평균이 2000 이상만)을 구하시오.

select avg(sal)
	from emp
	group by deptno
	having avg(sal) >= 2000;

---

📃 23. EMP 테이블의 월급여가 1000 이상인 사원만을 대상으로 부서별로 월급여 평균을 구하라. 단, 평균값이 2000 이상인 레코드만 구하라.

select avg(sal)
	from emp
	where sal >= 1000
	group by deptno
	having avg(sal) >= 2000;

---

📃 24. EMP 테이블의 급여가 높은 순으로 조회하되 급여가 같을 경우 이름의 철자가 빠른 사원순으로 사번(EMPNO),이름(ENAME),월급여(SAL)를 조회하시오.

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