-- Q) 2019년도에 입사한 ID가 'SYS'또는 'MKT'인 직원 조회
SELECT *
FROM employee
WHERE dept_id IN ('SYS','MKT')
AND hire_date BETWEEN '20190101' AND '20191231';
SELECT *
FROM employee
WHERE (dept_id = 'SYS' OR dept_id = 'MKT')
AND hire_date BETWEEN '20190101' AND '20191231';
-- Q) 2019년도에 입사한 연봉이 6,000 이상인 근무중인 직원 정보 조회
SELECT *
FROM employee
WHERE hire_date BETWEEN '20190101' AND '20191231'
AND salary >= 6000
AND retire_date IS NULL;
- 잘못된 예! or뒷부분에 and가 붙어서 안된다.
SELECT *
FROM employee
WHERE dept_id = 'SYS' OR dept_id = 'MKT'
AND hire_date BETWEEN '20190101' AND '20191231';
- NULL값 바꾸어주기
IFNULL() : 다른 SQL에서는 사용X
-- IFNULL() 함수 사용
SELECT emp_name, emp_id, IFNULL(eng_name, 'N/A') AS eng_name, gender, dept_id, hire_date
FROM employee
WHERE retire_date IS NULL;
COALESCE() : 범용성을 위해 사용
-- COALESCE() 함수 사용
SELECT emp_name, emp_id, COALESCE(eng_name, '') AS eng_name, gender, dept_id, hire_date
FROM employee
WHERE retire_date IS NULL;
순서대로 NULL값 아닌거 가져오기
- IFNULL : 3개는 안됌
/*
SELECT emp_id, IFNULL(score02, score01, score03) AS toeic
FROM toeic;
*/
SELECT emp_id, IFNULL(score02, score01) AS toeic
FROM toeic;
SELECT emp_id, COALESCE(score02, score03, score01) AS SCORE, score01, score02, score03
FROM toeic;
- 데이터 결합
-- NULL 값 처리 #2
SELECT CONCAT(emp_name, IFNULL(CONCAT('(', eng_name, ')'), '')) AS emp_name, dept_id, gender,
hire_date, email
FROM employee
WHERE retire_date IS NULL;
SELECT CONCAT_WS('/', score01, score02, score03) AS score
FROM toeic;
SELECT CONCAT_WS(score01, score02, score03) AS score
FROM toeic;
GROUP_CANCAT
SELECT city,
SUM(point) AS point,
GROUP_CONCAT(customer_name ORDER BY customer_name ASC SEPARATOR '/') AS name
FROM customer
GROUP BY city;
- 데이터 변경
CASE THEN, ELSE END
SELECT customer_name AS 이름,
customer_id AS 번호,
CASE WHEN gender='M' THEN '남자'
WHEN gender='F' THEN '여자'
ELSE NULL END AS 성별,
CASE WHEN city='서울' THEN '서울'
ELSE '기타' END AS 지역,
city AS 지역,
register_date AS 가입일,
COALESCE(point, 10000) AS 포인트
FROM customer
WHERE city IN ('서울', '부산', '대구', '광주', '인천')
AND register_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY 이름 ASC;
IF
SELECT city,
SUM(IF(gender = 'M',point,0)) AS M,
SUM(CASE WHEN gender = 'F' THEN point ELSE 0 END) AS F,
SUM(point) AS point
FROM customer
GROUP BY city
ORDER BY city ASC;
- 테이블 정보
DESCRIBE department;
- 데이터 삭제
DELETE FROM vacation
WHERE end_date <= '2017-12-31';
4. RANK
RANK() : 1, 2, 2, 4
DENSE_RANK() : 1, 2, 2, 3
ROW_NUMBER() : 1, 2, 3, 4
NTILE(n) : 1, 1, 1, 2, 2, 2, 3, 3, 3
-- 남녀별 급여 순위 구하기
SELECT emp_name, emp_id, gender, dept_id, hire_date, salary,
RANK() OVER(PARTITION BY gender ORDER BY salary DESC) AS rnk
FROM employee
WHERE retire_date IS NULL AND salary IS NOT NULL;
-- 급여를 기준으로 3 그룹으로 분류
SELECT emp_name, emp_id, gender, dept_id, hire_date, salary,
NTILE(3) OVER(ORDER BY salary DESC) AS grp
FROM employee
WHERE retire_date IS NULL AND salary IS NOT NULL;
5. 하위쿼리
-- 휴가를 간 적이 있는 정보시스템 직원 #1
SELECT emp_id, emp_name, dept_id, phone, email
FROM employee
WHERE dept_id = 'SYS'
AND emp_id IN (SELECT emp_id FROM vacation);
상관하위쿼리
-- 휴가를 간 적이 있는 정보시스템 직원 #2 : 상관 하위 쿼리
SELECT emp_id, emp_name, dept_id, phone, email
FROM employee AS e
WHERE dept_id = 'SYS'
AND EXISTS (SELECT *
FROM vacation
WHERE emp_id = e.emp_id);
