SELECT u.NAME AS '이름',
u.addr AS '주소',
CONCAT(u.mobile1, u.mobile2) AS '연락처',
b.prodName AS '주문 상품'
FROM usertbl u
INNER JOIN buytbl b ON u.userID = b.userID
WHERE u.userID = 'JYP';
Q2. employee 테이블과 department 테이블을 조인하여 보너스를 받는 사원들의 사번, 직원명, 보너스, 부서명을 조회하시오.
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
e.bonus AS '보너스',
d.dept_title AS '부서명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
WHERE e.bonus IS NOT NULL;
Q3. employee 테이블과 department 테이블을 조인하여 인사관리부가 아닌 사원들의 직원명, 부서명, 급여를 조회하시오.
SELECT e.emp_name AS '직원명',
d.dept_title AS '부서명',
e.salary AS '급여'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
WHERE d.dept_title != '인사관리부';
# ['employee' JOIN 'department'] JOIN 'job'
# 순서가 잘못되면 JOIN이 안될 수도 있음
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
d.dept_title AS '부서명',
j.job_name AS '직급명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code;
Q5. 70년대생 이면서 여자이고, 성이 전 씨인 직원들의 직원명, 주민번호, 부서명, 직급명을 조회하시요.
SELECT e.emp_name AS '직원명',
e.emp_no AS '주민번호',
d.dept_title AS '부서명',
j.job_name AS '직급명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code
WHERE e.emp_no LIKE '7%'
AND SUBSTRING(e.emp_no, 8, 1) = 2
AND e.emp_name LIKE '전%'
;
Q6. 각 부서별 평균 급여를 조회하여 부서명, 평균 급여를 조회 단 부서 배치가 안된 사원들의 평균도 같이 나오게끔 조회하시오.
SELECT IFNULL(d.dept_title, '부서없음')AS '부서명',
FLOOR(AVG(e.salary)) AS '평균 급여'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_ID
GROUP BY d.dept_title
ORDER BY d.dept_title
;
SELECT d.dept_title AS '부서명',
SUM(e.salary) AS '급여의 합'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_ID
GROUP BY d.dept_title
HAVING SUM(e.salary) >= 10000000
ORDER BY SUM(e.salary) DESC
;
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
j.job_name AS '직급명'
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
WHERE e.emp_name LIKE '%형%';
SELECT e.emp_name AS '직원명',
j.job_name AS '직급명',
e.dept_code AS '부서코드',
d.dept_title AS '부서명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code
WHERE d.dept_title LIKE '해외영업%'
ORDER BY d.dept_title
;
Q10. 각 부서별 최소 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 조회하시오.
SELECT emp_id AS '사번',
emp_name AS '사원명',
IFNULL(dept_code, '부서없음') AS '부서코드',
salary AS '급여'
FROM employee
WHERE (salary, IFNULL(dept_code, '부서없음')) IN (
SELECT MIN(salary), IFNULL(dept_code, '부서없음')
FROM employee
GROUP BY dept_code
)
ORDER BY dept_code
;
Q11. 각 직급별로 최소 급여를 받는 사원들의 사번, 이름, 직급코드, 급여 조회하시오.
SELECT emp_id AS '사번',
emp_name AS '사원명',
job_code AS '직급코드',
salary AS '급여'
FROM employee
WHERE (salary, job_code) IN (
SELECT MIN(salary), job_code
FROM employee
GROUP BY job_code
)
ORDER BY job_code
;
조인 중에서 가장 많이 사용되는 조인으로 일반적으로 조인이라고 이야기하는 것이 이 내부 조인(INNER JOIN)을 지칭하는 것
FROM 절 다음에 INNER JOIN구문을 통해 조인에 사용할 테이블을 기술하고 ON 절에 조인 조건을 작성
SELECT *
FROM employees e
INNER JOIN departments d ON e.dept_no = d.dept_no;
<ex1>
-- INNER JOIN 실습
-- 각 사원들의 사번, 직원명, 부서코드, 부서명을 조회
# e와 d는 각각 employee 테이블과 department 테이블의 별칭
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
dept_title AS '부서명'
FROM employee e
INNER JOIN department d ON dept_code = d.dept_id;
<ex2-1>
-- 각 사원들의 사번, 직원명, 직급 코드, 직급명을 조회
# job_code가 컬럼 이름이 두개의 테이블에서 모두 동일하기 때문에
# 에러가 발생
SELECT emp_id AS '사번',
emp_name AS '직원명',
job_code AS '직급 코드',
job_name AS '직급명'
FROM employee
INNER JOIN job ON job_code = job_code;
에러 발생
<ex2-2>
-- 각 사원들의 사번, 직원명, 직급 코드, 직급명을 조회
-- 방법 1) 테이블명 언급
SELECT employee.emp_id AS '사번',
employee.emp_name AS '직원명',
employee.job_code AS '직급 코드',
job.job_name AS '직급명'
FROM employee
INNER JOIN job ON employee.job_code = job.job_code;
방법 1
<ex2-3>
-- 각 사원들의 사번, 직원명, 직급 코드, 직급명을 조회
-- 방법 2) 별칭 사용
SELECT e.emp_id AS '사번',
e.emp_name AS '직원명',
e.job_code AS '직급 코드',
j.job_name AS '직급명'
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code;
방법 2
<ex2-4>
-- 각 사원들의 사번, 직원명, 직급 코드, 직급명을 조회
-- NATURAL JOIN : 동일한 컬럼명을 가지고 알아서 JOIN
-- 방법3) NATURAL JOIN > 하지만 잘 사용하지 않음
SELECT emp_id,
emp_name,
job_code,
job_name
FROM employee
NATURAL JOIN job;
<ex2-4>
<ex3>
-- join 조건을 where 절에서도 사용이 가능
-- 하지만 join 조건은 INNER JOIN에, 검색 조건은 WHERE절에 기입하는걸 추천
SELECT e.emp_id,
e.emp_name,
j.*
FROM employee e
INNER JOIN job j
WHERE e.job_code = j.job_code;
#1
SELECT *
FROM employee e;
#2
SELECT *
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id;
#1#2
- #1의 실행결과는 23행이 조회되고 #2는 21행이 조회된다. 이유는 JOIN 조건에서 조건값이 NULL값인 행들은 JOIN이 되지 않기때문이다.
3. 외부 조인(OUTER JOIN)
외부 조인(OUTER JOIN)은 조인의 조건에 만족되지 않는 행까지도 조회하기 위해서 사용되는 조인
LEFT OUTER JOIN은 왼쪽 테이블의 데이터는 모두 조회하려고 할 때 사용
SELECT *
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_no = d.dept_no;
#1
-- OUTER JOIN과 비교할 INNER JOIN 구문
-- 부서가 지정되지 않은 사원 2명에 대한 정보가 조회되지 않음
-- 부서가 지정되어 있어도 부서에 속해있는 사원이 없으면
-- 부서에 대한 정보가 조회되지 않는다.
SELECT e.emp_name,
d.dept_title,
e.dept_code,
e.salary
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
ORDER BY e.dept_code;
#2 LEFT OUTER JOIN
-- employee테이블을 기준으로 조인
-- JOIN 조건에 부합되지 않아도 employee테이블은 모두 나옴
-- 부서 코드가 없던 사원의 정보가 출력된다.
SELECT e.emp_name,
d.dept_title,
e.dept_code,
e.salary
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
ORDER BY e.dept_code;
#1 부서가 지정되지 않은 사원의 정보는 조회되지 않음#2 부서가 지정되지 않은 사원의 정보도 조회가 가능
RIGHT OUTER JOIN은 오른쪽 테이블의 데이터는 모두 조회하려고 할 때 사용
SELECT *
FROM employees e
RIGHT OUTER JOIN departments d ON e.dept_no = d.dept_no;
#1
-- OUTER JOIN과 비교할 INNER JOIN 구문
-- 부서가 지정되지 않은 사원 2명에 대한 정보가 조회되지 않음
-- 부서가 지정되어 있어도 부서에 속해있는 사원이 없으면
-- 부서에 대한 정보가 조회되지 않는다.
SELECT e.emp_name,
d.dept_title,
e.dept_code,
e.salary
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
ORDER BY e.dept_code;
#2 RIGHT OUTER JOIN
-- 부서에 속해있는 사원이 없어도 부서에 대한 정보가 출력된다.
SELECT e.emp_name,
d.dept_title,
e.dept_code,
e.salary
FROM employee e
RIGHT OUTER JOIN department d ON e.dept_code = d.dept_id
ORDER BY e.dept_code;
#1 부서가 지정되지 않은 사원의 정보는 조회되지 않음#2 부서에 속해있는 사원이 없어도 부서에 대한 정보가 출력
4. 상호 조인(CROSS JOIN)
상호 조인(CROSS JOIN)은 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인
거의 사용되지 않음
SELECT *
FROM employees e
CROSS JOIN departments d;
-- CROSS JOIN 실습
# 3개의 행을 가지고 있는 테이블A와
# 3개의 행을 가지고 있는 테이블B를 CROSS JOIN을 하면
# 총 9개의 행이 나옴
# 즉, 테이블A 행 개수 * 테이블B 행 개수
# employee 테이블은 23행이고
# department 테이블은 9행이므로
# 207행이 출력
SELECT e.emp_name,
d.dept_title
FROM employee e
CROSS JOIN department d
ORDER BY e.emp_name;
CROSS JOIN 결과 207행이 출
5. 자체 조인(SELF JOIN)
자체 조인(SELF JOIN)은 동일한 테이블을 가지고 조인하여 데이터를 조회
자기 자신과 JOIN
테이블 별칭을 각각 다르게 부여하여 두개의 테이블을 구별하여 JOIN
SELECT *
FROM employees e
INNER JOIN employees M ON e.manager_no = m.emp_no;
<ex1>
-- SELF JOIN
#1
# manager_id = 사수의 emp_id
SELECT *
FROM employee;
-- employee 테이블을 SELF JOIN하여 사번, 사원 이름, 부서 코드,
-- 사수 사번, 사수 이름 조회
#2
SELECT e.emp_id AS '사번',
e.emp_name AS '사원명',
e.dept_code AS '부서코드',
m.emp_id AS '사수 사번', -- e.manager_id와 동일
m.emp_name AS '사수명'
FROM employee e
INNER JOIN employee m ON e.manager_id = m.emp_id
;
#1 emp_id 와 manager_id 를 조건으로 JOIN할 것임#2 R: 직원정보 B: 사수정보
<ex2>
-- 사수가 없는 사원의 정보도 출력할 수 있도록
-- LEFT OUTER JOIN과 SELF JOIN 함께 사용
SELECT e.emp_id AS '사번',
e.emp_name AS '사원명',
e.dept_code AS '부서코드',
m.emp_id AS '사수 사번',
m.emp_name AS '사수명'
FROM employee e
LEFT OUTER JOIN employee m ON e.manager_id = m.emp_id
;
사수가 없는 사원의 정보도 LEFT OUTER JOIN을 사용하여 조회
6. UNION/UNION ALL 연산자
UNION연산자는 두 쿼리의 결과를 하나로 합치는 연산자
쿼리 A에서 2개의 행, 쿼리 B에서 3개의 행이 조회되면 총 5개의 행을 출력
두개의 쿼리문의 SELECT 절에서 사용한 컬럼의 개수가 동일하지 않으면 오류발생
UNION 연산자는 중복된 열은 제거되고 데이터가 정렬되어 조회
UNION ALL 연산자는 UNION 연산자와 다르게 중복된 열까지 모두 출력
SELECT *
FROM employees
WHERE gender = '남자'
UNION -- 또는 UNION ALL
SELECT *
FROM employees
WHERE salary > 3000000;
#0 UNION 연산자에 사용할 쿼리문
#0-1
-- employee 테이블에서 부서 코드가 D5인 사원들의 사번, 직원명, 부서코드,
-- 급여를 조회
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE dept_code = 'D5'
;
#0-2
-- employee 테이블에서 급여가 300만원 초과인 사원들의 사번, 직원명,
-- 부서 코드, 급여를 조회
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE salary > 3000000
;
# 0-1# 0-2
#1 UNION 연산자
<ex 1>
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE dept_code = 'D5'
UNION
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE salary > 3000000
;
-- WHERE 절로 변경
-- UNION 연산자를 사용하여 조회한 것과 결과 동일
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE dept_code = 'D5' OR salary > 3000000
사번 209, 215인 정보는 두 쿼리문에 모두 동일하게 조회되기 때문에 중복은 제거하고 출
#2 UNION ALL
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE dept_code = 'D5'
UNION
SELECT emp_id AS '사번',
emp_name AS '직원명',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE salary > 3000000
;
#2 UNION ALL은 중복된 값도 제거하지 않고 출력
서브쿼리(SubQuery )
1. 서브쿼리(SubQuery)
하나의 SQL문 안에 포함된 또 다른 SQL 문
-- 서브 쿼리 실습
#1. 노옹철 사원과 같은 부서원들을 조회
#1-1 노옹철 사원의 부서를 조회
SELECT emp_name,
dept_code
FROM employee
WHERE emp_name = '노옹철';
#1-2 부서 코드가 노옹철 사원의 부서 코드와 동일한 사원들을 조회
SELECT emp_name,
dept_code
FROM employee
WHERE dept_code = 'D9';
#1-3 위의 2단계를 서브 쿼리를 사용하여 하나의 쿼리문을 ㅗ작성
SELECT emp_name,
dept_code
FROM employee
WHERE dept_code = (
SELECT dept_code
FROM employee
WHERE emp_name = '노옹철'
);
#1-1
#1-2#1-3
#2
-- 전 직원의 평균 급여보다 더 많은 급여를 받고 있는 직원들의 사번,
-- 직원명, 직급 코드, 급여를 조회
#2-1 전 직원의 평균 급여 조회
SELECT FLOOR(AVG(salary))
FROM employee
;
#2-2 평균 급여보다 더 많은 급여를 받고 있는 직원들을 조회
SELECT emp_id AS '사번',
emp_name AS '사원명',
job_code AS '직급 코드',
salary AS '급여'
FROM employee
WHERE salary > 3047662
;
#2-3 위의 2단계를 서브 쿼리를 사용하여 하나의 쿼리문으로 작성
SELECT emp_id AS '사번',
emp_name AS '사원명',
job_code AS '직급 코드',
salary AS '급여'
FROM employee
WHERE salary > (
SELECT FLOOR(AVG(salary))
FROM employee
);
#2-1#2-2#2-3
2. 서브 쿼리 구분
서브 쿼리는 서브 쿼리를 수행한 결과값의 행과 열의 개수에 따라서 분류 가능
단일행 서브쿼리 : 서브 쿼리의 조회 결과 값의 개수가 1개일 경우
#1
-- 노옹철 사원의 급여보다 더 많이 받는 사원의 사번, 직원명,
-- 부서명, 급여 조회
#1-1 노옹철 사원의 급여
SELECT salary
FROM employee
WHERE emp_name = '노옹철'
;
#1-2 노옹철 사원의 급여보다 더 ㅁ낳이 받는 사원을 조회
SELECT e.emp_id AS '사번',
e.emp_name AS '사원명',
d.dept_title AS '부서명',
e.salary AS '급여'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
WHERE salary > 3700000
;
#1-3 서브쿼리를 활용하여 #1-1과 #1-2를 취합
SELECT e.emp_id AS '사번',
e.emp_name AS '사원명',
d.dept_title AS '부서명',
e.salary AS '급여'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
WHERE salary > (
SELECT salary
FROM employee
WHERE emp_name = '노옹철'
)
;
#1-1#1-2#1-3
다중행 서브쿼리 : 서브 쿼리의 조회 결과 값의 개수가 여러 행일 경우
다중행 서브쿼리는 조회 결과값이 여러개이기 때문에 단순비교 연산자를 사용 불가능(에러 발생)
ANY 연산자
ALL 연산자
#1
-- 각 부서별 최고 급여를 받는 직원의 이름, 직급 코드, 부서코드, 급여 조회
#1
-- 각 부서별 최고 급여를 받는 직원의 이름, 직급 코드, 부서코드, 급여 조회
#1-1 부서별 최고 급여 조회
SELECT MAX(salary) AS '해당 부서의 최고 급여'
FROM employee
GROUP BY dept_code
;
#1-2 위 급여를 받는 사원들을 조회
SELECT emp_name AS '사원명',
job_code AS '직급코드',
dept_code AS '부서 코드',
salary AS '급여'
FROM employee
WHERE salary IN (2890000, 3660000, 2490000, 3760000, 3900000, 2550000, 8000000)
ORDER BY dept_code
;
#1-3 #1-1과 #1-2를 서브쿼리를 사용하여 취합
SELECT emp_name AS '사원명',
job_code AS '직급코드',
dept_code AS '부서 코드',
salary AS '급여'
FROM employee
WHERE salary IN (
SELECT MAX(salary) AS '해당 부서의 최고 급여'
FROM employee
GROUP BY dept_code
)
ORDER BY dept_code
;
#1-1#1-2#1-3
#2
-- 직원에 대해 사번, 이름, 부서코드, 구분(사원/사수) 조회
#2-1 사수에 해당하는 사번을 조회
SELECT DISTINCT manager_id AS '사수번호'
FROM employee
WHERE manager_id IS NOT NULL
;
#2-2 사수/ 사원을 구분하여 조회
SELECT emp_id AS '사번',
emp_name AS '사원명',
dept_code AS '부서코드',
CASE WHEN emp_id IN (200, 201, 204, 207, 211, 214,100) THEN '사수'
ELSE '사원'
END AS '구분'
FROM employee
;
#2-3 SELECT 절에 서브 쿼리를 사용
SELECT emp_id AS '사번',
emp_name AS '사원명',
dept_code AS '부서코드',
CASE WHEN emp_id IN (
SELECT DISTINCT manager_id AS '사수번호'
FROM employee
WHERE manager_id IS NOT NULL
) THEN '사수'
ELSE '사원'
END AS '구분'
FROM employee
;
#2-1#2-2#2-3 #2-2와 결과 동일
ANY 연산자는 서브쿼리의 결과 중 하나라도 조건을 만족하면 TRUE를 반환
#1
-- 대리 직금임에도 과장 직급들의 최소 급여보다
-- 많이 맏는 직원의 사번, 이름, 직급코드, 급여 조회
#1-1 과장 직급들의 급여 조회
SELECT salary AS '급여'
FROM employee
WHERE job_code = 'J5'
;
#1-2 WHERE 절에 ANY를 이용하여 서브 쿼리를 사용
SELECT emp_id AS '사번',
emp_name AS '이름',
job_code AS '직급코드',
salary AS '급여'
FROM employee
WHERE job_code = 'J6' AND
-- AND salary > 2200만 OR salary > 250만 OR salary > 376만 과 동일
salary > ANY (
SELECT salary AS '급여'
FROM employee
WHERE job_code = 'J5'
)
;
#1-1#1-2
ALL 연산자는 서브쿼리의 결과 모두가 조건을 만족하면 TRUE를 반환한다.
= 기호는 논리적 오류가 있으므로 사용이 불가
#1
-- 과장 직급임에도 차장 직급의 최대 급여보다 더 많이 받는ㄴ
-- 직원들의 사번, 이름, 직급, 급여 조회
#1-1 차장 직급들의 급여 조회
SELECT salary
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
WHERE j.job_name = '차장'
;
#1-2 WHERE 절에 ANY를 이용하여 서브 쿼리를 사용
SELECT e.emp_id AS '사번',
e.emp_name AS '사원명',
j.job_name AS '직급명',
e.salary AS '급여'
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
WHERE j.job_name = '과장'
AND salary > ALL ( -- salary > 280만 AND salary > 155만 AND salary > 249만 AND salary > 248만
SELECT salary
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
WHERE j.job_name = '차장'
)
;
#1-1#1-2
다중열 서브쿼리 : 서브 쿼리의 조회 결과 값은 한 행이지만 열의 수가 여러개일 경우
#1
-- 하이유 사원과 같은 부서코드, 직급 코드에 해당하는 사원들을 조회
#1-1 하이유 사원의 부서 코드와 직급 코드 조회
SELECT dept_code AS '부서코드',
job_code AS '직급 코드'
FROM employee
WHERE emp_name = '하이유'
;
#1-2 부서 코드가 D5이면서 직급 코드가 J5인 사람들을 조회
SELECT emp_name AS '사원명',
dept_code AS '부서코드',
job_code AS '직급 코드'
FROM employee
WHERE dept_code = 'D5' AND job_code = 'J5'
;
#1-3 2개의 서브 쿼리를 사용하여 부서 코드가 D5이면서 직급 코드가 J5인 사람들을 조회
SELECT emp_name AS '사원명',
dept_code AS '부서코드',
job_code AS '직급 코드'
FROM employee
WHERE dept_code = (
SELECT dept_code
FROM employee
WHERE emp_name = '하이유'
)
AND job_code = (
SELECT job_code
FROM employee
WHERE emp_name = '하이유'
)
;
#1-4 1개의 서브 쿼리를 사용하여 부서 코드가 D5이면서 직급 코드가 J5인 사람들을 조회
# 쌍 비교 방식 : (,) = (,)형식으로 여러개의 컬럼을 동시에 비교
SELECT emp_name AS '사원명',
dept_code AS '부서코드',
job_code AS '직급 코드'
FROM employee
WHERE (dept_code, job_code) = (
SELECT dept_code AS '부서코드',
job_code AS '직급 코드'
FROM employee
WHERE emp_name = '하이유'
)
;
#1-1#1-2#1-3#1-4
#2
-- 박나라 사원과 직급 코드가 일치하면서 같은 사수를 가지고 있는
-- 사원의 사번, 이름, 직급 코드, 사수 사번 조회
#2-1 박나라 사원의 직급 코드와 사수의 사번을 조회
SELECT job_code AS '직급 코드',
manager_id AS '사수 사번'
FROM employee
WHERE emp_name = '박나라'
;
#2-2 서브쿼리사용하여 작성
SELECT emp_id AS '사번',
emp_name AS '사원명',
job_code AS '직급 코드',
manager_id AS '사수 사번'
FROM employee
WHERE (job_code, manager_id) = (
SELECT job_code,
manager_id
FROM employee
WHERE emp_name = '박나라'
)
;
#2-1#2-2
다중행 다중열 서브쿼리 : 서브 쿼리의 조회 결과값이 여러 행, 여러 열일 경우
#1
-- 각 부서별 최고 급여를 받는 직원의 사번, 이름, 직급 코드, 부서코드, 급여 조회
#1-1 부서별 최고 급여 조회
SELECT dept_code AS '부서코드',
MAX(salary) AS '최고급여'
FROM employee
GROUP BY dept_code;
#1-2 각 부서별 최고 급여를 받는 직원들의 정보 조회
# 비효울적
SELECT emp_id AS '사번',
emp_name AS '사원명',
job_code AS '직급코드',
dept_code AS '부서코드',
salary AS '급여'
FROM employee
WHERE dept_code = 'D1' AND salary = 3660000
OR dept_code = 'D2' AND salary = 2490000
OR dept_code = 'D5' AND salary = 3760000
OR dept_code = 'D6' AND salary = 3900000
OR dept_code = 'D8' AND salary = 2550000
OR dept_code = 'D9' AND salary = 8000000
OR dept_code IS NULL AND salary = 2890000
;
#1-3 다중행, 다중열 서브쿼리를 사용하여 각 부서별 최고 급여를 받는 직원들의 정보 조회
SELECT emp_id AS '사번',
emp_name AS '사원명',
job_code AS '직급코드',
IFNULL(dept_code, '부서없음') AS '부서코드',
salary AS '급여'
FROM employee
WHERE ( IFNULL(dept_code, '부서없음'), salary) IN (
SELECT IFNULL(dept_code, '부서없음'),
MAX(salary)
FROM employee
GROUP BY dept_code
)
ORDER BY dept_code
;
#1-1#1-2#1-3
인라인 뷰 : FROM절에 서브 쿼리를 제시하고, 서브 쿼리를 수행한 결과를 테이블 대신에 사용
#1
SELECT emp_id AS '사번',
emp_name AS '사원명',
salary AS '급여',
salary * 12 AS '연봉'
FROM employee
;
SELECT e.사번,
e.사원명,
e.급여,
e.연봉
FROM (
SELECT emp_id AS '사번',
emp_name AS '사원명',
salary AS '급여',
salary * 12 AS '연봉'
FROM employee
)e;
#1 결과
-- employee 테이블에서 급여로 순위를 사원의 정보 출력
#2-1 employee 테이블에서 급여로 순위를 매겨서 출력
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS '순위',
emp_name AS '사원명',
salary AS '급여'
FROM employee
;
#2-2 서브쿼리(인라인 뷰)를 사용하여 급여 순위 5위인 사원의 정보 출력
SELECT e.순위,
e.사원명,
e.급여
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS '순위',
emp_name AS '사원명',
salary AS '급여'
FROM employee
)e
WHERE e.순위 = 5
;
#2-3 서브쿼리(인라인 뷰)를 사용하여 급여 순위 5위에서 10위인 사원의 정보 출력
SELECT e.순위,
e.사원명,
e.급여
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS '순위',
emp_name AS '사원명',
salary AS '급여'
FROM employee
)e
WHERE e.순위 BETWEEN 5 AND 10
;
-- CONCAT(), CONCAT_WS()
# CONCAT() 함수
SELECT CONCAT('2024', '05', '20');
# CONCAT_WS() 함수
SELECT CONCAT_WS('/', '2024', '05', '20');
CONCAT() 함수CONCAT_WS() 함
<ex1>
-- usertbl 테이블에서 아이디, 이름, 전화번호를 조회
SELECT userID AS "아이디",
NAME AS "이름",
CONCAT( mobile1, mobile2) AS 전화번호
FROM usertbl;
mobile1 과 mobile2를 CONCAT() 함수를 사용하여 이어줌
<ex2>
-- employee 테이블에서 급여 조회
SELECT CONCAT(emp_name, '님의 급여는 ', salary, '입니다.') AS "급여"
FROM employee;
CONCAT() 함수를 통해 조회결과를 문장으로 출력
-- ELT(), FIELD(), FIND_IN_SET(), INSTR(), LOCATE() 함수
# ELT() : 위치번째에 해당하는 문자열을 반환
# 2가 입력되었기 때문에 2에 해당하는 '둘'을 반환
SELECT ELT(2, '하나', '둘', '셋');
# FIELD() : 찾을 문자열의 위치를 찾아서 반환
# '둘' 에 해당하는 문자열이 2번째에 있기 때문에 2를 반환
SELECT FIELD('둘', '하나', '둘', '셋');
# FIND_IN_SET() : 찾을 문자얼의 위치를 찾아서 반환
# 하나의 문자열을 ,(콤마)로 구분하여 찾음
# '둘' 에 해당하는 문자열이 ,(콤마)를 기준으로 2번째에 있기 때문에
# 2 를 반환
SELECT FIND_IN_SET('둘', '하나,둘,셋');
# INSTR() : 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환
# '둘'이 시작위치가 3번째이기 때문에 3을 반환
SELECT INSTR('하나둘셋', '둘');
# LOCATE() : INSTR()과 순서만 다르고 결과가 동일
# '둘'이 시작위치가 3번째이기 때문에 3을 반환
SELECT LOCATE('둘', '하나둘셋');
ELT() 함수 결과FIELD() 함수 결과FIND_IN_SET() 함수 결과INSTR() 함수 결과LOCATE() 함수 결과
<ex1>
-- employee 테이블에서 이메일의 @ 위치값 출력
SELECT email,
INSTR(email, '@')
FROM employee;
@ 문자의 위치를 찾아 위치값 출력
-- INSERT() 함수
# INSERT('기준 문자열', 삽입 시작 위치, 삭제될 문자 개수, '삽입할 문자')
#1 : 문자 4개 삭제
SELECT INSERT('ABCEDFGHI', 3, 4, '@@@@');
#2 : 문자 2개 삭제
SELECT INSERT('ABCEDFGHI', 3, 2, '@@@@');
#1#2
<ex1>
-- employee 테이블에서 사원명, 주민등록번호 조회
SELECT emp_name AS "이름",
INSERT(emp_no, 9, 6, '******') AS "주민등록번호"
FROM employee;
주민등록번호를 앞자리 6 자리와 성별을 제외하고 가려서 출력
-- LEFT(), RIGHT() 함수
# LEFT() : 기준문자열를 왼쪽에서 3개 잘라내어 출력
SELECT LEFT('ABCEDFGHI', 3);
# RIGHT() : 기준문자열를 오른쪽에서 3개 잘라내어 출력
SELECT RIGHT('ABCEDFGHI', 3);
LEFT() 함수 결과RIGHT() 함수 결과
<ex1>
-- employee 테이블에서 사원명, email의 ID만을 출력
SELECT emp_name,
LEFT(email, INSTR(email, '@')-1) AS 'ID'
FROM employee;
INSTR() 함수로 @ 기호의 위치값을 얻어 LEFT() 함수를 통해 ID만을 출력
-- UPPER(), LOWER() 함수
# UPPER() : 소문자를 모두 대문자를 변환
SELECT UPPER('abcDEF');
# LOWER() : 대문자를 모두 소문자를 변환
SELECT LOWER('abcDEF');
UPPER() 함수 결과LOWER() 함수 결과
-- LPAD(), RPAD() 함수
# LPAD() : 왼쪽에 남은 문자열 수 넣음
# 문자열을 넣어주지 않으면 공백
SELECT LPAD('HELLO', 10), LPAD('HELLO', 10, '#');
# RPAD() : 오른쪽에 남은 문자열 수 넣음
# 문자열을 넣어주지 않으면 공백
SELECT RPAD('HELLO', 10), RPAD('HELLO', 10, '#');
LPAD() 함수 결RPAD() 함수 결과
<ex1>
-- employee 테이블에서 사원명, 주민등록번호 출력
SELECT emp_name,
RPAD(LEFT(emp_no, 8), 14, '*') AS "주민등록번호"
FROM employee;
LPAD()와 RPAD()를 중첩하여 사용
-- LTRIM(), RTRIM(), TRIM() 함수 사용
# LTRIM(): 왼쪽 공백만 제거
SELECT LTRIM(' HELLO ');
# RTRIM(): 오른쪽 공백만 제거
SELECT RTRIM(' HELLO ');
# TRIM(): 양쪽 공백만 제거
SELECT TRIM(' HELLO ');
LTRIM() 함수 결과RTRIM() 함수 결과TRIM() 함수 결과
-- TRIM(방향 자를 문자열 FROM 문자열)
#1
# 양쪽 공백을 모두 지움
SELECT TRIM(BOTH ' ' FROM ' HELLO ');
#2
# 양쪽 'Z'문자를 모두 지움
SELECT TRIM(BOTH 'Z' FROM 'ZZZZZHELLOZZZZZ');
#3
# 중간에 삽입된 'Z'는 지워지지 않음
SELECT TRIM(BOTH 'Z' FROM 'ZZZZZHEZLLOZZZZZ');
#4
# 왼쪽에 삽입된 'Z'만 지움
SELECT TRIM(LEADING 'Z' FROM 'ZZZZZHEZLLOZZZZZ');
#5
# 오른쪽에 삽입된 'Z'만 지움
SELECT TRIM(TRAILING 'Z' FROM 'ZZZZZHEZLLOZZZZZ');
<ex1>
-- employee 테이블에서 이름, 아이디, 성별 조회
SELECT emp_name,
SUBSTRING(email, 1, INSTR(email, '@')-1) AS '아이디',
IF(SUBSTRING(emp_no, 8, 1) = 1, '남자', '여자') AS '성별'
FROM employee;
SUBSTRING() 함수를 통해 ID 출력, 주민등록번호로 성별 출력
-- SUBSTRING_INDEX() 함수
#1
# 양수 : 왼쪽에서 부터 첫번째 구분자를 찾고 오른쪽 부분을 삭제
SELECT SUBSTRING_INDEX('cafe.naver.com', '.', 1);
#2
# 음수 : 오른쪽에서 부터 첫번째 구분자를 찾고 왼쪽 부분을 삭제
SELECT SUBSTRING_INDEX('cafe.naver.com', '.', -1);
#1#2
<ex1>
-- employee 테이블에서 아이디 조회
SELECT emp_name AS '사원명',
SUBSTRING_INDEX(email, '@', 1) AS '아이디'
FROM employee;
SUBSTRING_INDEX() 함수를 통해 아이디를 조회
4. 수학 함수
-- ABS() 함수: 절대값 출력 함수
SELECT ABS(100), ABS(-100),
ABS(10.9), ABS(-10.9);
ABS() 함수 결과
-- CEILING(), FLOOR(), ROUND(), TRUNCATE() 함수
# CEILING() : 올림 함수
SELECT CEILING(4.3);
# FLOOR() : 내림 함수
SELECT FLOOR(4.7);
# ROUND(x) : ROUND(X, 0)과 동일
SELECT ROUND(4.355), ROUND(4.355, 0);
# ROUND(x, D) : 소수점 D자리까지 반올림
# D 양수 : 소수점 기준
# D 음수 : 자연수 기준
SELECT ROUND(4.355, 2), ROUND(14.355, -1);
# TRUNCATE() : 소수점 기준으로 정수 위치까지 구하고 나머지를 삭제
SELECT TRUNCATE(123.456, 0), TRUNCATE(123.456, 1);
CEILING() 함수 결FLOOR() 함수 결과ROUND(X) 함수 결과ROUND(X, D) 함수결과TRUNCATE()함수 결과
-- MOD() 함수
# MOD(숫자1, 숫자2) : 나머지 함수
# 숫자1을 숫자2로 나눈 나머지값을 반환
SELECT MOD(157, 10) , 157 % 10, 157 MOD 10;
3개의 결과값이 모두 동일
-- RAND() 함수
#1
# RAND() : 0<=X<=1 범위의 값이 랜덤으로 출력
SELECT RAND(), RAND(), RAND();
#2
# RAND() 함수 범위지정
SELECT RAND() * 100 -- 0.0 ~ 99.99999
FLOOR(RAND() * 100), -- 0 ~99
FLOOR(RAND() * 100) +1 ; -- 1 ~ 100
#1#2
-- POW(), SQRT(), SIGN() 함수
# POW() : 거듭 제곱 함수
SELECT POW(2,3);
# SQRT() : 제곱근 함수
SELECT SQRT(9);
# SIGN() : 양수, 0, 음수 판별하는 함수
SELECT SIGN(100), SIGN(0), SIGN(-100);
#1
# ADDTIME() : 시간을 더하는 함수
SELECT ADDTIME('2024-05-20 09:00:00', '01:10:30'), -- 1시간 10분 30초 더하기
ADDTIME('09:00:00', '02:20:50'); -- 2시간 20분 50초 더하기
#2
# ADDTIME() : 시간을 더하는 함수
SELECT SUBTIME('2024-05-20 09:00:00', '01:10:30'), -- 1시간 10분 30초 빼기
SUBTIME('09:00:00', '02:20:50'); -- 2시간 20분 50초 빼기
#1#2
<ex1>
-- employee테이블에서 사원명, 입사일, 인턴(3개월) 종료일 구하기
SELECT emp_name AS '사원명',
hire_date AS '입사일',
ADDDATE(hire_date, INTERVAL 3 MONTH) AS '인턴 종료일'
FROM employee;
ADDDATE()함수를 통해 인턴종료일 출력
-- CURDATE(), CURTIME(), NOW(), SYSDATE() 함수
SELECT CURDATE(), -- 현재 날짜
CURTIME(), -- 현재 시간
NOW(), -- 현재 날짜와 시간
SYSDATE(); -- 현재 날짜와 시간
CURDATE(), CURTIME(), NOW(), SYSDATE() 함수 결과
#1
-- YEAR(), MONTH(), DAY() 함수 : DATE 타입을 입력 받음
SELECT YEAR(CURDATE()), -- 년을 출력
MONTH(CURDATE()), -- 월을 출력
DAY(CURDATE()); -- 일을 출력
#2
-- HOUR(), MINUTE(), SECOND() : TIME 타입을 입력받음
SELECT HOUR(CURTIME()), -- 시간을 출력
MINUTE(CURTIME()), -- 분을 출력
SECOND(CURTIME()); -- 초를 출력
<ex1>
-- employee 테이블에서 직원명, 입사일, 근무 일수 조회
SELECT emp_name AS '직원명',
hire_date AS '입사일' ,
DATEDIFF(CURDATE(), hire_date) AS '근무 일수'
FROM employee;
DATEDIFF()함수로 근무일수 조회
-- DAYOFWEEK(), MONTHNAME(), DAYOFYEAR(), LAST_DAY() 함수 : 날짜 데이터를 입력받음
#1
# DAYOFWEEK() : 날짜의 요일을 반환
# 1: 일요일, 2: 월요일, ..., 7: 토요일
SELECT DAYOFWEEK(CURDATE());
#2
# MONTHNAME() : 날짜의 월의 이름을 반환
SELECT MONTHNAME(CURDATE());
#3
# DAYOFYEAR() : 1년 중 몇 번째 날짜인지를 반환
SELECT DAYOFYEAR(CURDATE());
#4
# LAST_DAY() : 주어진 날짜의 달에 해당하는 마지막 날짜를 반환
SELECT LAST_DAY(CURDATE());
#1#2#3#4
<ex1>
-- employee 테이블에서 직원명, 입사일, 급여일(매달 마지막 날) 조회
SELECT emp_name AS '직원명',
hire_date AS '입사일',
LAST_DAY(CURDATE()) AS '급여일'
FROM employee;
LAST_DAY() 함수를 통해 급여일 지정
-- MAKEDAY(), MAKETIME() 함수
#1
# MAKEDATE(연도, 정수) : 해당 '연도'에 '정수'만큼 지난 날짜를 반환
SELECT MAKEDATE(2024, 100);
#2 MAKETIME(시, 분, 초) : 입력값에 해당하는 시간을 반환
SELECT MAKETIME(22, 58, 25);
#1#2
-- PERIOD_ADD(), PERIOD_DIFF(), QUARTER(), TIME_TO_SEC() 함수
#1
# PERIOD_ADD(연월, 개월 수) : 연월에서 개월만큼의 개월이 지난 연월을 반환
SELECT PERIOD_ADD(202405, 11);
#2
# PERIOD_DIFF(연월 1, 연월 2) : 연월 1 - 연월 2의 개울 수 반환
SELECT PERIOD_DIFF(202305, 202405);
#3
# QUARTER(날짜) : 날짜가 4분기 중 몇 분기에 해당하는지 반환
SELECT QUARTER(CURDATE());
#4
# TIME_TO_SEC(시간) : 시간을 초 단위로 반환
SELECT TIME_TO_SEC(CURTIME());
#1#2#3#5
6. 시스템 정보 함수
7. 윈도우 함수
7.1. 순위 함수
순위 윈도우 함수에는ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()등의 함수가 존재
순위 윈도우 함수를 사용하면 데이터를 순서대로 번호 매기거나, 특정 조건에 따라 순위(등수)를 매길 수 있음
-- 윈도우 함수 "순위 함수"
-- ROW_NUMBER() 함수 : 순번을 매김
<ex1>
-- usertbl 테이블에서 키가 큰 순으로 순위를 매겨서 순위, 이름, 주소,
-- 키를 조회
# OVER절에 정렬 조건 기입
SELECT ROW_NUMBER() OVER(ORDER BY height DESC, NAME ASC) AS 'RANK',
NAME AS "이름",
addr AS "주소",
height AS "키"
FROM usertbl;
<ex2>
-- usertbl 테이블에서 지역별로 순위를 배겨서 주소 순위, 이름, 키 조회
SELECT addr AS '주소',
ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height DESC, NAME ASC) AS '주소별 순위',
NAME AS '이름',
height AS '키'
FROM usertbl
ORDER BY addr;
<ex1><ex2>
-- 윈도우 함수 "순위 함수"
# RANK() 함수 : 동일한 순위 이후의 등수를 동일한 인원수만큼 건너뛰고 증가
-- usertbl 테이블에서 키가 큰 순으로 순위를 매겨서 순위, 이름, 주소, 키를 조회
SELECT RANK() OVER(ORDER BY height DESC, NAME ASC) AS 'RANK',
NAME AS "이름",
addr AS "주소",
height AS "키"
FROM usertbl;
<ex1>
<ex2>
-- employee 급여가 높은 순서대로 순위 매겨서 순위, 직원명, 급여 조회
SELECT RANK() OVER(ORDER BY salary DESC, emp_name ASC) AS 'RANK',
emp_name AS '직원명',
salary AS '급여'
FROM employee;
<ex2>
-- 윈도우 함수 "순위 함수"
# DENSE_RANK() 함수 : 동일한 순위 이후의 등수를 1 증가
<ex1>
-- usertbl 테이블에서 키가 큰 순으로 순위를 매겨서 순위, 이름, 주소, 키를 조회
SELECT DENSE_RANK() OVER(ORDER BY height DESC, NAME ASC) AS 'RANK',
NAME AS "이름",
addr AS "주소",
height AS "키"
FROM usertbl;
<ex1>
-- 윈도우 함수 "순위 함수"
# NTILE(x) 함수 : 전체 인원을 어떠한 기준 순서로 세운 후에 x개의 그룹으로 분할
<ex1>
# usertbl 테이블에서 키가 순서로 세운 후에 3개의 그룹을 분할
SELECT NTILE(2) OVER(ORDER BY height DESC, NAME ASC) AS 'RANK',
NAME AS "이름",
addr AS "주소",
height AS "키"
FROM usertbl;
<ex1>
7.2. 분석 함수
분석 윈도우 함수에는LEAD(),LAG(),FIRST_VALUE(),LAST_VALUE(),CUME_DIST()등의 함수가 존재
분석 윈도우 함수를 사용하면 현재 행의 이전 행이나 다음 행의 데이터를 참조하거나, 윈도우의 첫 값이나 마지막 값을 가져올 수 있음
-- 윈도우 함수 "분석 함수"
# LEAD(column, n) : n 단위로 다음 등수의 데이터를 가져옴
<ex1>
-- usertbl 테이블에서 키 순서대로 정렬 후 다음 사람과 키 차이를 조회
SELECT NAME AS '직원명',
addr AS '주소',
height AS '키',
height - LEAD(height, 1) OVER(ORDER BY height DESC) AS '다음 등수 키 차이'
FROM usertbl;
<ex1>
-- 윈도우 함수 "분석 함수"
# LAG(column, n) : n 단위로 이전 등수의 데이터를 가져옴
<ex1>
-- usertbl 테이블에서 키 순서대로 정렬 후 다음 사람과 키 차이를 조회
SELECT NAME AS '직원명',
addr AS '주소',
height AS '키',
height - LAG(height, 1) OVER(ORDER BY height DESC) AS '이전 등수 키 차이'
FROM usertbl;
<ex1>
-- 윈도우 함수 "분석 함수"
# FIRT_VALUE(column) : 특정 column의 데이터 중 순위의 가장 첫 번째 값을 가져옴
<ex1>
-- 지역별로 가장 키가 큰 사람과의 키 차이를 조회
SELECT addr AS '주소',
NAME AS '이름',
height AS '키',
height - FIRST_VALUE(height) OVER(PARTITION BY addr ORDER BY height DESC) AS '지역별로 가장 키가 큰 사람과의 키 차이'
FROM usertbl
ORDER BY addr;
