한화시스템 BEYOND SW Camp_0626

졸려요. 예.. 졸려요.. 흐으응아ㅏㅇ아ㅏㅇ마 그치만 수업은 재미나용

5. 날짜 및 시간 함수

• ADDDATE, SUBDATE 함수

SELECT ADDDATE('2025-01-01', INTERVAL 10 DAY) -- 적은 날짜에 10을 더해서 출력해줌.
			ADDDATE('2025-01-01', INTERVAL 1 MONTH), -- 월에 +1
			ADDDATE('2025-01-01', INTERVAL 2 YEAR); -- 연도에 +2

ADDDATE(날짜, 차이) 날짜를 기준으로 차이를 더한 날짜를 반환한다.
ADDDATE() 함수와 DATE_ADD() 함수는 동일한 함수이다.
SUBDATE(날짜, 차이) 날짜를 기준으로 차이를 뺀 날짜를 반환한다.
SUBDATE() 함수와 DATE_SUB() 함수는 동일한 함수이다.

?) 굳이 날짜 사이에 하이픈을 넣어야 인식되는 걸까? : 으로 쓰면 안될까? 되긴 하는데, 하이픈으로 나오는디 왜 그러징 -> 그냥 그렇다고 한다.

• CURDATE()함수랑 같이 사용해보기.

SELECT ADDDATE(CURDATE(), INTERVAL 10 DAY), 
			ADDDATE(CURDATE(), INTERVAL 1 MONTH),
			ADDDATE(CURDATE(), INTERVAL 2 YEAR);

• SUBDATE 사용해서 출력해보기.

SELECT SUBDATE('2025-01-01', INTERVAL 10 DAY),
			SUBDATE('2025-01-01', INTERVAL 1 MONTH),
			SUBDATE('2025-01-01', INTERVAL 2 YEAR);
 
SELECT SUBDATE(CURDATE(), INTERVAL 10 DAY), 
			SUBDATE(CURDATE(), INTERVAL 1 MONTH),
			SUBDATE(CURDATE(), INTERVAL 2 YEAR);

?) ADDDATE랑 SUBDATE의 차이점이 뭘까요?
=> ADDDATE는 차이를 더해주는 거고, SUBDATE는 차이를 빼주는 거임.

• 예제) employee 테이블에서 직원명, 입사일, 입사 후 3개월 된 날짜 조회

SELECT emp_name, -- 직원명
			hire_date, -- 입사일
			ADDDATE(hire_date, INTERVAL 3 MONTH) -- 입사 후 3개월 후
FROM employee;

• ADDTIME, SUBTIME 함수 사용

SELECT ADDTIME('2025-01-01 23:59:59', '1:1:1'),
			ADDTIME('15:00:00', '2:10:30');

시간이 더해지니까, 날짜도 변경된 걸 확인 할 수 있어요!

SELECT SUBTIME('2025-01-01 23:59:59', '1:1:1'),
			SUBTIME('17:10:30', '2:10:30');

• NOW(), CURTIME() 함수랑 같이 써보기.

SELECT ADDTIME(NOW(), '1:1:1'),
			ADDTIME(CURTIME(), '2:10:30');
			
SELECT SUBTIME(NOW(), '1:1:1'),
			SUBTIME(CURTIME(), '2:10:30');		

• CURDATE, CURTTIME, NOW, SYSDATE 함수 사용

SELECT CURDATE(),
			CURTIME(),
			NOW(),
			SYSDATE();

CURDATE() 현재 연-월-일을 조회하는 함수이다.
CURDATE() 함수와 CURRENT_DATE() 함수, CURRENT_DATE는 모두 동일하다.
NOW()
SYSDATE() 현재 연-월-일 시:분:초를 조회하는 함수이다.
NOW() 함수, LOCALTIME() 함수, LOCALTIME, LOCALTIMESTAMP() 함수, LOCALTIMESTAMP는 모두 동일하다.

• 연도, 월, 일 출력 -> YEAR, MONTH, DAY, DATE 함수

SELECT YEAR(CURDATE()),
			MONTH(CURDATE()),
			DAY(CURDATE()),
			DATE(NOW());
            DAYOFMONTH(CURDATE());

YEAR(날짜)
MONTH(날짜)
DAY(날짜)

• 시간 정보 출력해보기. -> HOUR, MINUTE, SECOND, TIME 함수

SELECT HOUR(CURTIME()),
			MINUTE(CURTIME()),
			SECOND(CURTIME()),
			TIME(NOW());

HOUR(시간)
MINUTE(시간)
SECOND(시간)
MICROSECOND(시간) 날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초를 구하는 함수이다.
DAY()와 DAYOFMONTH()는 동일한 함수이다.

현재 내 상태.. 이러고 찾는 중. 1교시 쉬는 시간 끝

• DATEDIFT, TIMEDIFT 함수 사용해보기.

SELECT DATEDIFF(CURDATE(), '2024-06-26'),
			TIMEDIFF(CURTIME(), '09:00:00');

DATE(DATETIME) DATETIME 형식에서 연-월-일을 반환한다.
TIME(DATETIME) DATETIME 형식에서 시:분:초를 반환한다.

• employee 테이블에서 직원명, 입사일 근무 일 조회

SELECT emp_name,
			hire_date,
		DATEDIFF(CURDATE(), hire_date)
FROM employee;

• DATEDIFF(CURDATE(), hire_date)
  : (현재 날짜) - (입사일)를 계산해준다.

• DAYOFWEEK, MONTHNAME, DAYOFYEAR, LAST_DAY 함수

SELECT DAYOFWEEK(CURDATE()),
			MONTHNAME(CURDATE()),
			DAYOFYEAR(CURDATE()),
			LAST_DAY(CURDATE());

DAYOFWEEK 5라고 뜨는 건 목요일이라는 뜻.
DAYOFYEAR는 2025-01-01 기준으로 177일이 지났다.

DAYOFWEEK(날짜) 날짜의 요일(1:일, 2:월, ... 7:토)을 반환한다.
MONTHNAME(날짜) 날짜의 월의 이름을 반환한다.
DAYOFYEAR(날짜) 날짜가 1년 중 몇 번째 날짜 인지를 반환한다.
LAST_DAY(날짜) 주어진 날짜의 마지막 날짜를 구한다.

• MAKEDATE, MAKETIME, PERIOD_ADD, PERIOD_DIFF 함수

SELECT MAKEDATE(2025, 177),
			MAKETIME(23, 59, 59),
			PERIOD_ADD(202506, 11),
			PERIOD_DIFF(202506, 202405);

MAKEDATE(연도, 정수) 연도에서 정수만큼 지난 날짜를 구한다.
MAKETIME(정수, 정수, 정수) 시, 분, 초를 이용해서 시:분:초의 TIME 형식을 만든다.
PERIOD_ADD(연월, 개월 수) 연월에서 개월만큼의 개월이 지난 연월을 구한다.
연월은 YYYY 또는 YYYYMM 형식을 사용한다.
PERIOD_DIFF(연월 1, 연월 2) 연월 1 - 연월 2의 개월 수를 구한다.

• QUARTER, TIME_TO_SEC 함수

SELECT QUARTER(CURDATE()),
			TIME_TO_SEC(CURTIME());

QUARTER(날짜) 날짜가 4분기 중에서 몇 분기인지 반환한다.
TIME_TO_SEC(시간) 시간을 초 단위로 구한다.

6. 시스템 정보 함수

• DATABASE랑 USER 확인

SELECT USER(), DATABASE();

• ROW가 몇 개고

SELECT * FROM employee;
SELECT FOUND_ROWS();

7. 윈도우 함수

• 순위 함수
• usertbl 테이블에서 키가 큰 순으로 순위 매겨서, 순위, 이름, 주소, 키를 조회.

SELECT ROW_NUMBER() OVER(ORDER BY height DESC) AS 'num', 
		'name',
		'address',
		'height'
FROM usertbl;

• 순위 윈도우 함수에는 ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() 등의 함수가 있다.
  순위 윈도우 함수를 사용하면 데이터를 순서대로 번호 매기거나, 특정 조건에 따라 순위(등수)를 매길 수 있다.
• 윈도우 함수는 OVER절과 같이 사용해야한다. (현재 난 test_db 데이터시트가 없어서, 출력은 안됨. 오늘 해결해보겠어여)

• 지역별로 순위를 매겨서 순위, 이름 주소, 키를 조회

SELECT ROW_NUMBER() OVER(PARTITION BY addr 
ORDER BY height DESC) AS 'num', 
		'name',
		'address',
		'height'
FROM usertbl;

• 동순위를 처리하는 함수: RANK()
• 키가 큰 순으로 순위를 매겨서 순위, 이름, 키를 조회
  단, 동일한 순위 이후의 등수를 동잃한 인원수만큼 건너뛰고 증가

SELECT RANK() OVER(ORDER BY height DESC) AS 'rank'
			'name'
			'addr'
			'height'
FROM usertbl;

• employee 테이블에서 급여가 높은 상위 10명의 순위, 직원명, 급여조회

SELECT emp.rank, emp.emp_name, emp.salary
FROM(
	SELECT RANK() OVER(ORDER BY salary DESC) AS 'rank',
			emp_name,
			salary
FROM employee
) emp
WHERE emp.rank BETWEEN 1 AND 10;

홀리 이번엔 어려웠다.. 다음에 사용할 것 같으니, 기억하도록!!!
근데 쉽게 작성하는 법이 있당!!

SELECT RANK() OVER(ORDER BY salary DESC) AS 'rank',
			emp_name,
			salary
FROM employee
LIMIT 10;

이것도 같은 결과를 나옴.

• usertbl 테이블에서 키가 큰 순으로 4개의 그룹으로 분할

SELECT NTILE(4) OVER(ORDER BY height DESC) AS 'rank'
			'name'
			'addr'
			'height'
FROM usertbl;

• 분석함수

-- usertbl 테이블에서 키 순서대로 정렬 후 다음사람과 키 차이를 조회
SELECT 'name',
			'addr',
			'height',
			-- 현재 행의 height와 다음 1번째 행의 height의 차이를 구한다.
			'height' - LEAD('height', 1) OVER(ORDER BY 'height' DESC) AS 'num'
FROM usertbl;

-- usertbl 테이블에서 키 순서대로 정렬 후 다음사람과 키 차이를 조회
SELECT 'name',
			'addr',
			'height',
			-- 현재 행의 height와 다음 1번째 행의 height의 차이를 구한다.
			'height' - LAG('height', 1) OVER(ORDER BY 'height' DESC) AS 'num'
FROM usertbl;

• 키 차이까지 나타내기

SELECT 'name',
			'addr',
			'height',
			FIRST_VALUE() OVER(ORDER BY height DESC) - 'height'
FROM usertbl;

• 지역별로 가장 키가 큰 사람과의 카 차이를 조회

SELECT 'name',
			'addr',
			'height',
			FIRST_VALUE('height') OVER(PARTITION BY 'height' DESC) - 'height'
FROM usertbl;

조인(JOIN)과 UNION 연산자

1. 조인

2. 내부 조인(INNER JOIN)

• 각 사원들의 사번, 직원명, 부서 코드, 부서명 조회

SELECT *
FROM employee
INNER JOIN department ON dept_code = dept_id;

dept_code랑 dept_id가 같은 애들끼리 행을 이어서 같은 행에 붙여주기.

SELECT emp_id,
			emp_name,
			dept_code,
			dept_title
FROM employee
INNER JOIN department ON dept_code = dept_id;

• 각 사원들의 사번, 직원명, 직급코드, 직급명 조회
• 연결할 테이블의 열 이름이 같은 경우
• 방법 1) 테이블 이름을 이용하는 방법

SELECT employee.emp_id,
			employee.emp_name,
			job.job_code,
			job.job_name
FROM employee
INNER JOIN job ON employee.job_code = job.job_code;

• 방법 2) 테이블 별칭을 이용하는 방법

SELECT e.emp_id,
		e.emp_name,
		j.job_code,
		j.job_name
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code;

• 방법 3) NATURAL JOIN을 이용하는 방법(참고)

SELECT emp_id,
			emp_name,
			job_code,
			job_name
FROM employee
NATURAL JOIN job;

• 실습문제
• 1) usertbl 테이블과 buytbl 테이블을 조인하여, JYP라는 아이디를 가진 회원의 이름, 주소, 연락처, 주문 상품 이름을 조회.

SELECT ut.name,
			ut.addr,
			CONCAT(ut.mobile1, ut.mobile2) AS 'mobile',
			bt.prodName
FROM usertbl ut
INNER JOIN buytbl bt ON. ut.usertid = bt.userid
WHERE ut.userid = 'JYP';

• 2) employee 테이블과 department 테이블을 조인하여 보너스를 받는 사원들의 사번, 직원명, 보너스, 부서명을 조회

SELECT employee.emp_id,
			employee.emp_name,
			employee.bonus,
			department.dept_title
FROM employee
INNER JOIN department ON employee.dept_code = department.dept_id
WHERE employee.bonus IS NOT NULL;

• 3) employee 테이블과 department 테이블을 조인하여 인사관리부가 아닌 사원들의 직원명, 부서명, 급여를 조회.

SELECT employee.emp_name,
		department.dept_title,
		employee.salary
FROM employee
INNER JOIN department ON department.dept_id != 'D1';

• 4) employee 테이블과 department 테이블, job 테이블을 조인하여 사번, 직원명, 부서명, 직급명 조회.

SELECT *
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;

3. 외부 조인(OUTER JOIN)

SELECT e.emp_name,
		d.dept_id,
		d.dept_title,
		e.salary
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id;

• 1) LEFT OUTER JOIN

SELECT e.emp_name,
		d.dept_id,
		d.dept_title,
		e.salary
FROM employee e
LEFT /*OUTERR */ JOIN department d ON e.dept_code = d.dept_id;
ORDER BY d.dept_id;

• 2) RIGHT OUTER JOIN

-- 부서에 속해있는 사원이 없어도 부서에 대한 정보가 출력된다.
SELECT e.emp_name,
		d.dept_id,
		d.dept_title,
		e.salary
FROM employee e
RIGHT /*OUTER */ JOIN department d ON e.dept_code = d.dept_id;
ORDER BY d.dept_id;

4. 상호 조인(CROSS JOIN)

SELECT emp_name,
		dept_title
FROM employee
CROSS JOIN department;

5. 자체 조인(SELF JOIN)

• employee 테이블을 SELF JOIN해서, 사번, 직원명, 부서 코드, 사수 사번, 사수명 조회

SELECT e.emp_id,
		e.emp_name,
		e.dept_code,
		e.manager_id,
		m.emp_name
FROM employee e -- 사원에 대한 정보를 조회할 때 사용함.
INNER JOIN employee m ON e.manager_id = m.emp_id;-- 사수에 대한 정보를 조회할 때 사용할 거임.

Why? 사수에 대한 걸 어떻게 아는 거지? 적혀있었나..?

+) 밑에 이걸 추가하면, 사수가 없는 사원들도 출력해줌.

LEFT OUTER JOIN employee m ON e.manager_id =m.emp_id;

NULL로 표시되는 것을 확인할 수 있다.

• NON EQUAL JOIN 실습

  -- 조인 조건에 등호(=)를 사용하지 않는 조인문을 비등가 조인이라고 한다.
  -- employee 테이블과 sal_grade 테이블을 비등가 조인하여 직원명, 급여, 급여등급 조회

SELECT e.emp_name,
		e.salary,
		s.sal_level
FROM employee e
-- INNER JOIN sal_grade s ON e.salary BETWEEN s.min_sal AND s.max_sal;
LEFT OUTER JOIN sal_grade s ON e.salary BETWEEN s.min_sal AND s.max_sal;

• 실습문제

• 이름에 '형'자가 들어있는 직원들의 사번, 직원명, 직급명을 조회

SELECT e.emp_id,
		e.emp_name,
		j.job_name
FROM employee e
INNER JOIN job j ON e.job_code=j.job_code
WHERE e.emp_name LIKE '%형%'; -- 왜 '%형%'이라고 적어야 되는걸까

• 70년대생 이면서 여자이고, 성이 전 씨인 직원들의 직원명, 주민번호, 부서명, 직급명을 조회하세요.

SELECT e.emp_name,         -- 직원명
       e.emp_no,           -- 주민번호
       d.dept_title,       -- 부서명
       j.job_name          -- 직급명
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_name LIKE '전%'                                -- 성이 '전'
  AND SUBSTR(e.emp_no, 1, 2) BETWEEN '70' AND '79'         -- 70년대생
  AND SUBSTR(e.emp_no, 8, 1) IN ('2', '4');                -- 여자

SELECT *
FROM employee;

• 각 부서별 평균 급여를 조회하여 부서명, 평균 급여를 조회
  단, 부서 배치가 안된 사원들의 평균도 같이 나오게끔 조회해 주세요.

-- gpt한테 물어봤어여
SELECT 
    COALESCE(d.dept_title, '부서 없음'),
    AVG(e.salary) AS avg_salary
FROM employee e
LEFT JOIN department d ON e.dept_code = d.dept_id
GROUP BY d.dept_title;

• 각 부서별 총 급여의 합이 1000만원 이상인 부서명, 급여의 합을 조회

SELECT SUM(e.salary),
		d.dept_title
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
GROUP BY d.dept_title
HAVING SUM(e.salary) >= 10000000;

-- 쿼리 작성할 때, 순서가 중요함. 순서 바뀌면 실행 안됨..ㅎ
-- SELECT -- 1단계: 최종 출력할 컬럼 지정
-- FROM -- 2단계: 테이블(들) 지정
-- JOIN -- 2단계: 조인 처리 (FROM과 함께)
-- WHERE -- 3단계: 행 필터링
-- GROUP BY -- 4단계: 그룹핑 수행
-- HAVING -- 5단계: 그룹 필터링
-- ORDER BY -- 6단계: 정렬

• 해외영업팀에 근무하는 직원들의 직원명, 직급명, 부서 코드, 부서명을 조회

• 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명, 국가명 조회

• 테이블을 다중 JOIN 하여 사번, 직원명, 부서명, 지역명, 국가명, 급여 등급 조회

• 부서가 있는 직원들의 직원명, 직급명, 부서명, 지역명을 조회하시오.

• 한국과 일본에서 근무하는 직원들의 직원명, 부서명, 지역명, 근무 국가를 조회하세요.