[50~55] 230310~230317 SQL 문제 풀어보기 (데이터: Employer, Department) [K-디지털 트레이닝 50일~55일]

참고: https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=zinuriu2&logNo=221659075746

230310 Fri 50th class

1. EMP 테이블을 만들어보자 (Employer)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17 0:00	800	NULL	20
중략
7839	KING	PRESIDENT	NULL	1981-11-17 0:00	5000	NULL	10

숫자로 구성된 것: EMPNO, MGR, SAL, COMM, DEPTNO
NULL 없는 열: EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO
NULL 있는 열: MGR, COMM

EMPNO 열은 기본키(UNIQUE & NOT NULL)임
나머지 제약 조건은 위 데이터를 보고 판단해보자.

2. DEPT 테이블을 만들어보자 (Department)

DEPTNO	DNAME	LOC
10	ACCOUNTANT	NEW YORK
중략
50	DEVELOPEMENT	KOREA

숫자로 구성된 것: DEPTNO
DEPTNO 열은 기본키임
나머지 제약 조건은 위 데이터를 보고 판단해보자.

참고

---

230313 Mon 51st class

문제 풀기)

INSERT INTO
참고: https://www.w3schools.com/sql/sql_insert.asp

데이터 
참고: https://bigdatansql.com/2021/08/16/emp-and-dept-datasets-for-mysql/

테이블 완성 코드)

-- Creating EMP table.
CREATE TABLE EMP(
    EMPNO INT, -- empno INT,
    ENAME varchar(30) NOT NULL, -- ename VARCHAR(10),
	JOB varchar(30) NOT NULL, -- job VARCHAR(9),
    MGR int, -- mgr INT,
    HIREDATE date NOT NULL, -- hiredate DATE,
    SAL DECIMAL(7,2), -- sal DECIMAL(7,2),
    COMM DECIMAL(7,2), -- comm DECIMAL(7,2),
    DEPTNO int NOT NULL, -- deptno INT,
    CONSTRAINT pkey_EMPNO PRIMARY KEY(EMPNO) -- constraint pk_emp primary key (empno),
    -- PRIMARY KEY(기본키) : UNIQUE & NOT NULL 
);

-- Creating DEPT table.
CREATE TABLE DEPT(
    DEPTNO int, -- deptno INT,
    DNAME varchar(30) NOT NULL, -- dname VARCHAR(14),
    LOC varchar(30) NOT NULL, -- loc VARCHAR(13),
    CONSTRAINT pkey_DEPTNO PRIMARY KEY(DEPTNO) -- constraint pk_dept primary key (deptno)
    -- PRIMARY KEY(기본키) : UNIQUE & NOT NULL
);

-- Data Insertion in EMP table
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02',2975, null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1981-04-19',3000, null, 20),
(7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-03-12', 950, null, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-03-12', 3000, null, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

-- Data Insertion in DEPT table
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES 
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON'),
(50, 'DEVELOPERS', 'KOREA');

결과)

EMP 테이블

DEPT 테이블

---

문제 풀어보기)

참고: https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=zinuriu2&logNo=221659075746

Q1 부서번호가 10번인 부서의 사람 중 사원번호, 이름, 월급을 출력하세요.

select EMPNO, ENAME, SAL from EMP where DEPTNO = 10;

---

Q2 사원번호가 7369인 사람 중 이름, 입사일, 부서번호를 출력하세요.

select ENAME, HIREDATE, DEPTNO from EMP where EMPNO = 7369;

정답에선 '7369'로 나온다. 이유는 정답에서는 EMPNO를 VARCHAR(4) PRIMARY KEY로 뒀고 나는 INT로 뒀기 때문이다.

select ENAME, HIREDATE, DEPTNO from EMP where EMPNO = '7369';

---

Q3 이름이 ALLEN인 사람의 모든 정보를 출력하세요.

select * from EMP where ENAME = 'ALLEN';

---

Q4 입사일이 83/01/12인 사람의 이름, 부서번호,월급을 출력하세요.

select ENAME, DEPTNO, SAL from EMP where HIREDATE = '1983-01-12';

---

Q5 직업이 MANAGER가 아닌 사람의 모든 정보를 출력하세요.

select * from EMP where JOB <> 'MANAGER';

Technically there is no difference between != and <>. 
정답에선 아래 코드도 나온다.

select * from EMP where JOB != 'MANAGER';

---

Q6 이름이 K로 시작하는 사람보다 높은 이름을 가진 사람의 모든 정보를 출력하세요.

select * from EMP where ENAME > 'K';

똑같은 답은 나오는데 정답에서는 'K%'이다.

select * from EMP where ENAME > 'K%';

---

Q7 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름, 월급, 부서번호를 출력하세요.

select ENAME, SAL, DEPTNO from EMP where '1981-04-02' < HIREDATE and HIREDATE < '1982-12-09';

---

Q8 이름 중 S자가 들어가 있는 사람만의 모든 정보를 출력하세요.

select * from EMP where ENAME like '%S%';

---

Q9 이름이 S로 시작하고 마지막 글자가 T인 사람의 모든 정보를 출력하세요(단 이름은 전체 5자리)

select * from EMP where ENAME like "S____" and ENAME like "____T";

정답에서는 두 조건을 합친 걸로 나온다.

select * from EMP where ENAME like "S___T";

---

Q10 첫 번째 문자는 관계없고 두 번째 문자가 A인 사람의 정보를 출력하세요.

select * from EMP where ENAME like "_A%";

---

Q11 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의 사원번호, 이름, 부서번호를 출력하세요.

select EMPNO, ENAME, DEPTNO from EMP where ENAME like "K%" or DEPTNO = 30;

---

Q12 급여가 1500이상이고, 부서번호가 30번인 사원 중 직업이 MANAGER인 사람의 정보를 출력하세요.

select * from EMP where SAL >= 1500 and DEPTNO = 30 and JOB = 'MANAGER';

---

Q13 사원번호가 7654와 7782 사이 이외의 사원의 모든 정보를 출력하세요.

select * from EMP where not EMPNO between 7654 and 7782;

---

Q14 이름이 B와 J사이의 모든 사원의 정보를 출력하세요.

select * from EMP where 'B' < ENAME and ENAME < 'J'; 
select * from EMP where ENAME between 'B' and 'J';

정답에서는 'B%', 'J%'를 썼다.

select * from EMP where 'B%' < ENAME and ENAME < 'J%';

---

Q15 입사일이 81년 이외에 입사한 모든 정보를 출력하세요.

select * from EMP where not HIREDATE between '1981-01-01' and '1981-12-31';

---

Q16 직업이 MANAGER와 SALESMAN인 사람의 모든 정보를 출력하세요.

select * from EMP where JOB = 'MANAGER' or JOB = 'SALESMAN';

---

Q17 부서번호가 20,30번을 제외한 모든 사람의 이름, 사원번호, 부서번호를 출력하세요.

select ENAME, EMPNO, DEPTNO from EMP where not (DEPTNO = 20 or DEPTNO = 30);
select ENAME, EMPNO, DEPTNO from EMP where not DEPTNO between 20 and 30;

---

Q18 이름이 S로 시작하는 사원의 사원번호, 이름, 입사일, 부서번호를 출력하세요.

select EMPNO, ENAME, HIREDATE, DEPTNO from EMP where ENAME like "S%";

---

Q19 입사일이 81년도인 사람의 모든 정보를 출력하세요.

select * from EMP where HIREDATE like '1981%';
select * from EMP where HIREDATE between '1981-01-01' and '1981-12-31';

---

Q20 이름 중 S자가 들어가 있는 사람인 모든 정보를 출력하세요.

select * from EMP where ENAME like "%S%";

---

Q21 이름이 S로 시작하고 마지막 글자가 T인 사람의 모든 정보를 출력하세요(단 이름은 전체 5자리이다.)

select * from EMP where ENAME like "S___T";

---

Q22 첫 번째 문자는 관계없고 두 번째 문자가 A인 사람의 정보를 출력하세요.

select * from EMP where ENAME like "_A%";

---

Q23 커미션이 NULL인 사람의 정보를 출력하세요

select * from EMP where COMM is NULL;

---

Q24 커미션이 NULL이 아닌 사람의 모든 정보를 출력하세요.

select * from EMP where not COMM is NULL;

결과는 같은데 정답에서는 is not을 썼다.

select * from EMP where COMM is not NULL;

---

Q25 부서가 30번 부서이고, 급여가 1500 이상인 사람의 이름, 부서, 월급을 출력하세요.

select ENAME, DEPTNO, SAL from EMP where DEPTNO = 30 and SAL >= 1500;

 

---

Q26 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의 사원번호, 이름, 부서번호를 출력하세요.

select EMPNO, ENAME, DEPTNO from EMP where ENAME like "K%" or DEPTNO = 30;

---

Q27 급여가 1500이상이고, 부서번호가 30번인 사원 중 직업이 MANAGER인 사람의 정보를 출력하세요.

select * from EMP where SAL >= 1500 and DEPTNO = 30 and JOB = 'MANAGER';

---

230314 TUE 52nd class

문제 풀어보기)
emp 테이블 데이터를 사용

Q1 10번 부서와 같은 일을 하는 사원의 사원번호, 이름, 부서명, 급여를 조회하고 급여가 많은 순으로 출력하세요.

---

Q2 급여가 3000이상이면, 급여 + 급여의 15%의 격려금을,
급여가 2000이상이면, 급여 + 급여의 10%의 격려금을,
급여가 1000이상이면, 급여 + 급여의 5%의 격려금을,
그렇지 않으면 급여를 구하여, 이름, 직업, 급여, 격려금을 표시하시오.

정답처럼 해도 값은 같다.

---

Q3 사원번호, 이름, 연봉을 연봉이 높은 순으로 정렬하여 출력하세요.
(연봉은 sal * 12 + comm으로 계산)

COALESCE(COMM, 0)으로 결과를 도출했다.

IFNULL(COMM, 0)으로도 결과를 도출할 수 있다.

처음에 한글로 변수명을 적었는데 "" 안 쓰니까 정답 나왔다.
교재에서는 한글 변수로 쓰는 거 비추니까 안 써야겠다.

아래가 이상적인 정답이다.

두 코드의 답은 똑같이 나온다.

---

학원의 정답

---

230317 Fri 55th Class

1) 'ALLEN'의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하세요.

2) JONES가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일, 급여를 출력하세요.

3) 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하세요.

4) 10번 부서와 같은 일을 하는 사원의 사원번호, 이름, 부서명, 지역, 급여를 급여가 많은 순으로 출력하세요.

5) 'MARTIN'이나 'SCOTT'의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하세요.

6) 부서번호가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하세요.

7) 사원중에서 급여(sal)와 보너스(comm)을 합친 금액이 가장 많은 경우와 가장 적은 경우, 평균 금액을 구하세요.
HINT : IFNULL사용(구글로 검색해보세요.)

8) 부서별로 급여합계를 구하세요.

9) 급여가 3000이상이면, 급여+급여의 15%의 격려금을, 급여가 2000이상이면, 급여+급여의 10%의 격려금을,급여가 1000이상이면, 급여+급여의 5%의 격려금을, 그렇지 않으면 급여를 구하여,
   이름, 직업, 급여,격려금을 표시하시오.
HINT : CASE WHEN사용(구글로 검색해보세요.)

10) 'MARTIN'과 같은 매니저와 일하는 이름, 직업, 급여, 부서명, 지역을 구하세요.

11) 부서명이 'RESEARCH'인 사람의 이름, 직업, 급여,부서명을 표시하시오.

12) 각 부서별 평균 급여를 구하고, 그 중에서 평균 급여가 가장 적은 부서    의 평균 급여보다 적게 받는 사원들의 부서명, 지역, 급여를 구하세요.

13) 'BLAKE'와 같은 부서에 있는 사원들의 이름과 고용일을 뽑는데 'BLAKE'는 빼고 출력하라.

14) 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고있는 사원의 사원번호와 이름을 출력하라.

15) 자신의 급여가 평균 급여보다 많고, 이름에 'S'가 들어가는 사원과 동일한 부서에서 근무하는 모든 사원의 사원번호, 이름, 급여를 출력하라.

16) 커미션을 받는 사원과 부서번호, 월급이 같은 사원의 이름, 월급, 부서번호를 출력하라.

17) 직업명과 사원의 등급을 직업이 'PRESIDENT' 이면 'A', 직업이 'ANALYST' 이면 'B', 직업이 'MANAGER' 이면 'C', 직업이 'SALESMAN' 이면 'D', 직업이 'CLEARK' 이면 'E' 로 표시하시오.

18) 10번 부서중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명,입사일, 지역을 출력하라.

19) 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.

20) 급여가 30번 부서의 최저 급여보다 낮은 사원의 사원번호, 이름, 급여를 출력하라.

21) 사원 중에서 입사일이 가장 빠른 사원의 사원번호, 이름, 입사일, 부서명을 출력하세요.

22) 평균 연봉보다 많이 받는 사원들의 사원번호, 이름, 연봉을 연봉이 높은 순으로 정렬하여 출력하세요.
(연봉은 sal*12+comm으로 계산)
HINT : IFNULL사용(구글로 검색해보세요.)

23) EMP와 DEPT TABLE을 JOIN하여 부서 번호, 부서명, 이름, 급여를 출력하라.

24) 이름이 'ALLEN'인 사원의 부서명을 출력하라.

25) DEPT Table 에는 존재하는 부서코드이지만 해당부서에 근무하는 사람이 존재하지 않는 경우의 결과를 출력하라.