[DB] SELECT 구문 연습하기 (예쁘게수정필요)

SELECT *
FROM employees
ORDER BY last_name DESC, first_name ASC
LIMIT 10;

-- ORDER BY XXX DESC, 역순으로
--  		XXX ASC   오름차순으로
-- 게시판에서 최신글이 위로 올라올 때 쓰인다.

SELECT YEAR(NOW()) AS '년도'
FROM DUAL;


SELECT *, YEAR(NOW()) - YEAR(birth_date) AS '나이'
FROM employees
LIMIT 10;
-- 모든 * 항목 실행, 옆에 나이 출력


SELECT DISTINCT title
FROM titles;
-- DISTINCT 고유한 값 중복 없이 보기

SELECT *
FROM salaries
WHERE YEAR(to_date)=9999
ORDER BY salary DESC
LIMIT 10;
-- year 9999는 현재까지 근무하는 사람들,
-- 현재까지 근무하는 사람 중 salary가 높은 10명 뽑기


SELECT *
FROM salaries
WHERE YEAR(to_date)=9999
ORDER BY salary DESC
LIMIT 10, 10;
-- 그럼 그 다음 10명도 뽑아주세요
-- 11순위 부터 20순위 까지


SELECT *
FROM employees
WHERE YEAR(hire_date) = 1999
AND gender='f';
-- 1999년 입사한 여 직원


SELECT COUNT(*)
FROM employees
WHERE YEAR(hire_date) = 1997
AND gender='m';
-- 1997년 입사한 남자직원 수


SELECT *
FROM employees
WHERE YEAR(hire_date) = 1997 AND gender='f'
ORDER BY birth_date DESC
LIMIT 10;
-- 1997년 입사 직원 중 나이가 가장 어린 10명 출력


SELECT COUNT(*)
FROM employees
WHERE YEAR(hire_date) BETWEEN 1995 AND 2000;
-- BETWEEN A AND B
-- 1995~2000까지 입사한 직원은 총 몇명?


SELECT *
FROM employees
WHERE last_name IN('Makrucki', 'Zockler', 'Blokdijk');
-- IN(A, B, C) .. last_name이 'Makrucki', 'Zockler', 'Blokdijk' 인 사람 출력


SELECT title, COUNT(*)
FROM titles
WHERE to_date = '9999-01-01'
GROUP BY title;
--업무 별 직원의 수를 구해주세요 (titles)


SELECT if(gender='M','남자','여자') AS '성별', COUNT(*) AS '인원 수'
FROM employees
GROUP BY gender;
-- 남여 직원의 수를 뽑아주세요


-- join 테이블 결합
/* 기준에 따라 left join, right join 등
데이터 일치 여부에 따라서 inner, outer 등
*/
SELECT e.emp_no, e.first_name, e.last_name, s.salary
FROM salaries s JOIN employees e
ON s.emp_no = e.emp_no
WHERE s.to_date = '9999-01-01'
LIMIT 10;


SELECT e.*, d.dept_name
FROM departments d JOIN dept_emp e
ON d.dept_no = e.dept_no
WHERE e.to_date = '9999-01-01';
-- dept_emp 에 dept_name도 같이 출력해주세요.
-- 9999인것만...


SELECT dept_name, AVG(s.salary) avgsal
FROM departments d JOIN dept_emp e ON d.dept_no = e.dept_no
JOIN salaries s ON e.emp_no = s.emp_no 
WHERE e.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY d.dept_name
ORDER BY avgsal DESC
LIMIT 5;
-- 급여 평균이 가장 높은 부서 5개 출력



SELECT dm.emp_no, dm.dept_no, d.dept_name, e.first_name, e.last_name
FROM dept_manager dm JOIN departments d ON dm.dept_no = d.dept_no
JOIN employees e ON e.emp_no = dm.emp_no
WHERE to_date='9999-01-01';
-- dept_manager에 관리자가 있습니다. 관리자 이름과 같이 출력해주세요.


-- -------------------- sub Query -------------------
SELECT d.dept_name, (
SELECT COUNT(*) 
FROM dept_emp de
WHERE de.to_date='9999-01-01' AND de.dept_no=d.dept_no
) AS 'count'
FROM departments d

-- 위와 아래는 같은 문장이다!

SELECT d.dept_name, COUNT(*)
FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no
WHERE de.to_date='9999-01-01'
GROUP BY d.dept_name;



SELECT d.dept_name AS '부서'
,(
SELECT concat(e.first_name, ' ', e.last_name)
FROM dept_emp de JOIN employees e ON de.emp_no=e.emp_no
WHERE de.to_date='9999-01-01' AND d.dept_no=de.dept_no
ORDER BY de.from_date
LIMIT 1
)  AS '이름'
FROM departments d ;
-- concat() 문자열 합치기
-- 각 부서에서 가장 오래 일한 직원의 이름을 출력하세요.



SELECT d.dept_name, COUNT1
FROM departments d
JOIN 
(
SELECT de.dept_no, COUNT(*) COUNT1
FROM dept_emp de
WHERE de.to_Date = '9999-01-01'
GROUP BY de.dept_no
) dept
ON dept.dept_no = d.dept_no
ORDER BY d.dept_no;
--부서별 직원 수



CREATE VIEW VIEW1 AS 
SELECT d.dept_name, COUNT1
FROM departments d
JOIN 
(
SELECT de.dept_no, COUNT(*) COUNT1
FROM dept_emp de
WHERE de.to_Date = '9999-01-01'
GROUP BY de.dept_no
) dept
ON dept.dept_no = d.dept_no
ORDER BY d.dept_no;
-- view 만들기


SELECT *
FROM VIEW1;