튜터링 7주차 - SQL 실습

아래 링크에서 실습에 사용할 데이터를 다운로드 받으세요.
http://naver.me/GUDeCYu9
$\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad$ employee 테이블

http://naver.me/5T3pUJK4
$\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad\quad$ department 테이블

1. 사원번호, 사원이름, 직급을 조회하시오.

SELECT
	empno
	, empname
	, title
FROM
	employee
;	

2. 부서번호, 부서 이름을 조회하시오.

SELECT
	deptno
	, deptname
FROM
	department
;

3. 사원번호, 사원이름, 직급을 조회하는데, 테이블 이름은 [사용자]로 별칭을 주고, 사원번호는 [사원번호], 사원이름은 [사원 이름]이라는 별칭을 주시오.

SELECT
	empno AS "사원번호"
	, empname AS "사원 이름"
	, title
FROM
	employee "사용자"
;

(Access에서

4. 직원 테이블의 모든 컬럼 조회하시오.

SELECT
	*
FROM
	employee
;

5. 직급을 중복 제거하여 조회하시오.

SELECT
	DISTINCT title
FROM
	employee
;

6. 급여가 3,000,000 이상이고, 사원이름 마지막 글자가 '아'인 사원에 대해 사원번호, 사원이름, 직급, 급여를 조회하시오.(Access에서는 %가 아닌 *를 써야합니다.)

SELECT
	empno
	, empname
	, title
	, salary
FROM
	employee
WHERE
	salary >= 3000000
	AND empname LIKE '*아'
;

7. 사원번호가 1000과 3000 사이인 직원에 대하여 사원번호, 사원이름, 직급, 급여를 조회하시오.

SELECT
	empno
	, empname
	, title
	, salary
FROM
	employee
WHERE
	empno BETWEEN 1000 AND 3000	
;

8. 사원의 부서번호가 2 이상인 직원들에 대하여 직급, 급여, 부서번호를 조회하시오.

SELECT
	title
	, salary
	, dno
FROM
	employee
WHERE
	dno >= 2
;

9. 이름에 '영'이 포함된 직원들 중에서, 직급이 대리 혹은 과장인 직원들에 대하여 모든 컬럼을 검색하시오.(Access에서는 '--'로 사용한 주석을 제거하고 코드를 실행하세요.)

-- 방법 1 : 논리 연산자로만 조건 제시하기
SELECT
	*
FROM
	employee
WHERE
	empname LIKE '*영*'
	AND (title = '대리' OR title = '과장')
;

-- 방법 2 : IN 연산자 사용하기 
SELECT
	*
FROM
	employee
WHERE
	empname LIKE '*영*'
	AND title IN ('대리', '과장')
;

10. 상사번호(manager)가 NULL인 직원에 대하여 모든 컬럼을 조회하시오.

SELECT
	*
FROM
	employee
WHERE
	manager IS NULL
;

11. 직급이 사원인 직원에 대해 모든 컬럼을 조회하시오.

SELECT
	*
FROM
	employee
WHERE
	title = '사원'
;

12. 직급이 사원이 아닌 직원에 대해 모든 컬럼을 조회하시오.(Access에서는 '<>'연산자만 사용 가능합니다.)

SELECT
	*
FROM
	employee
WHERE
	title <> '사원'
;

13. 직급이 과장, 대리, 부장인 직원들에 대하여, 사원번호, 사원이름, 직급, 급여를 조회하시오.

SELECT
	empno
	, empname
	, title
	, salary
FROM
	employee
WHERE
	title IN ('과장', '대리', '부장')
;

14. 시스템 오류로 사원들의 부서번호가 2배씩 커졌다. 이 때 사원이름, 직급, 원래 부서번호, 오류가 난 부서번호를 조회하고, 오류가 난 부서번호는 [확인 요망]이라는 별칭을 주시오

SELECT
	empname
	, title
	, dno
	, dno * 2 AS '확인 요망'
FROM
	employee
;

15. 급여를 오름차순 정렬하여 사원번호, 사원이름, 직급, 급여를 조회하시오. (ASC는 생략 가능)

SELECT
	empno
	, empname
	, title
	, salary
FROM
	employee
ORDER BY
	salary ASC
;

16. 부서번호가 3인 직원들에 대하여 사원번호를 내림차순 정렬하여 사원이름, 직급, 부서번호를 조회하시오.

SELECT
	empname
	, title
	, dno
FROM
	employee
WHERE
	dno = 3
ORDER BY
	empno DESC
;

17. 모든 직원들에 대하여 이름의 개수(COUNT 함수 사용)와 상사번호의 개수, 급여의 평균, 사원번호의 최대 최소값을 조회하시오.

SELECT
	COUNT(empname)
	, COUNT(manager)
	, AVG(salary)
	, MAX(empno)
	, MIN(empno)
FROM
	employee
;

18. 사원의 부서번호가 3인 직원들에 대하여 이름의 개수, 급여의 평균, 급여의 총합, 급여의 최대값, 급여의 최소값을 조회하시오.

SELECT
	COUNT(empname)
	, AVG(salary)
	, SUM(salary)
	, MAX(salary)
	, MIN(salary)
FROM
	employee
WHERE
	dno = 3
;

19. 직급을 기준으로 그룹화하고, 직급, 급여의 평균, 급여의 총합을 조회하시오.

SELECT
	title
	, AVG(salary)
	, SUM(salary)
FROM
	employee
GROUP BY
	title
;

20. 직급이 '사원' 혹은 '대리'인 직원들에 대하여 사원의 부서번호를 기준으로 그룹화하고, 사원의 부서번호, 급여의 평균, 급여의 최대값, 급여의 최소값을 조회하시오.

SELECT
	dno
	, AVG(salary)
	, MAX(salary)
	, MIN(salary)
FROM
	employee
WHERE
	title IN ('사원', '대리')
GROUP BY
	dno
;

21. 사원의 부서번호가 2 이상인 직원들에 대하여, 사원의 부서번호를 기준으로 그룹화하고, 사원의 부서번호, 급여의 평균, 급여의 최대값, 급여의 최소값을 조회하시오.

-- 방법 1 : HAVING절 사용 
SELECT
	dno
	, AVG(salary)
	, MAX(salary)
	, MIN(salary)
FROM
	employee
GROUP BY
	dno
HAVING
	dno >= 2
;

-- 방법 2 : WHERE절 사용
SELECT
	dno
	, AVG(salary)
	, MAX(salary)
	, MIN(salary)
FROM
	employee
WHERE
	dno >= 2
GROUP BY
	dno
;

22. 사원이름이 '김'씨이며 사원의 부서번호가 1이나 3인 직원들에 대하여, 사원의 부서번호를 기준으로 그룹화하고, 사원의 부서번호, 급여의 평균, 급여의 최대값, 급여의 최소값을 조회하는데, 부서 번호를 기준으로 오름차순 정렬하시오.(ACCESS는 %가 아니라 *을써야함)

-- 방법 1 : HAVING절 사용
SELECT
	dno
	, AVG(salary)
	, MAX(salary)
	, MIN(salary)
FROM
	employee
WHERE
	empname LIKE '김*'
GROUP BY
	dno
HAVING
	dno IN (1, 3)
ORDER BY
	dno
;

-- 방법 2 : WHERE절 사용
SELECT
	dno
	, AVG(salary)
	, MAX(salary)
	, MIN(salary)
FROM
	employee
WHERE
	empname LIKE '김*'
	AND dno IN (1, 3)
GROUP BY
	dno
ORDER BY
	dno
;

23. (부서번호 : 5 , 부서이름: 마케팅, 층 : 12층)인 새로운 부서를 추가하시오.

INSERT INTO department
VALUES
	(5, '마케팅', 12)
;

24. 마케팅 부서 이름을 'R&D'로 변경하시오.

UPDATE
	department
SET
	deptname = 'R&D'
WHERE
	deptname = '마케팅'
;

25. 'R&D'부서를 삭제하시오

DELETE FROM 
	department
WHERE
	deptname = 'R&D'
;

26. 부서 테이블에 대하여 부서번호가 사원의 부서번호와 일치하는 부서번호, 부서이름을 조회하시오.

SELECT
	d.deptno
	, d.deptname
FROM	
	department d
INNER JOIN employee e
	ON d.deptno = e.dno
;

27. 부서 테이블에 대하여 부서번호가 사원의 부서번호와 일치하는 부서번호, 부서이름을 조회하시오.(부서번호에 중복 제거를 해보시오. 그리고 26번 문제와 어떻게 결과가 달라지는지 확인하시오.)

SELECT
	DISTINCT d.deptno
	, d.deptname
FROM	
	department d
INNER JOIN employee e
	ON d.deptno = e.dno
;

28. 부서 테이블을 기준으로 직원 테이블을 조인하고(=left join or right join) 부서번호, 부서이름, 사원번호, 사원이름을 조회하시오.

SELECT
	d.deptno
	, d.deptname
	, e.empno
	, e.empname
FROM	
	department d
LEFT JOIN employee e
	ON d.deptno = e.dno
;

29. 부서 테이블을 기준으로 직원테이블과 조인하여 직원 테이블에 없는 부서번호는 제외하고 부서번호, 부서이름, 사원번호, 사원이름을 조회하시오.

SELECT
	d.deptno
	, d.deptname
	, e.empno
	, e.empname
FROM	
	department d
LEFT JOIN employee e
	ON d.deptno = e.dno
WHERE
	e.dno IS NOT NULL
;

30. 직원 테이블과 부서 테이블의 합집합을 구하시오.

SELECT
	d.deptno
	, d.deptname
	, e.empno
	, e.empname
FROM	
	department d
FULL JOIN employee e
	ON d.deptno = e.dno
;

31. 직원 테이블에서는 사원번호, 사원이름을 조회하고 부서 테이블에서는 부서번호, 부서이름을 조회하여 두 결과를 합치시오.

SELECT
	empno
	, empname
FROM
	employee
	
UNION

SELECT
	deptno
	, deptname
FROM
	department
;

32. 사원의 부서이름이 '개발'인 직원들에 대하여 사원이름, 직급, 사원의 부서번호를 조회하시오.

-- 1단계 : 부서 이름이 '개발'인 부서번호 반환하기 => 부서번호 : 3
SELECT
	deptno
FROM
	department
WHERE
	deptname = '개발'
;

-- 2단계 : 부서 번호를 위에서 가져온 부서번호로 데이터 조회
SELECT
	empname
	, title
	, dno
FROM
	employee
WHERE
	dno = 3
;


-- 위 단계를 하나로 합치기 -> 서브쿼리로 만들기
SELECT
	empname
	, title
	, dno
FROM
	employee
WHERE
	dno IN (
		SELECT
			deptno
		FROM
			department
		WHERE
			deptname = '개발'
		)
;