DAY.04

-- 11/06
-- 성이 'Radwan'이고 성별이 남자인 사원을 조회

SELECT *
FROM employees
WHERE CONCAT(gender, last_name) = 'MRadwan';


EXPLAIN 
SELECT *
FROM employees
WHERE gender = 'M'
AND last_name = 'Park';


SELECT COUNT(1) FROM employees;


/* 부서 관리자의 사원번호, 이름, 성, 부서번호 데이터를 중복 제거하여 조회  */
EXPLAIN 
SELECT DISTINCT E.emp_no, E.first_name, E.last_name, DM.dept_no
FROM employees E
INNER JOIN dept_manager DM
ON DM.emp_no = E.emp_no;

EXPLAIN
SELECT E.emp_no, E.first_name, E.last_name, DM.dept_no
FROM employees E
INNER JOIN dept_manager DM
ON DM.emp_no = E.emp_no;


/* 4.2.6 UNION 이용 
성이 'Baba'이면서 성별이 남자인 사원과 
성이 'Baba'이면서 성별이 여자인 사원을 조회 
226 rows
*/
EXPLAIN 
SELECT gender, emp_no
FROM employees
WHERE last_name = 'Baba';


EXPLAIN 
SELECT gender, emp_no
FROM employees
WHERE gender = 'M'
AND last_name = 'Baba'

UNION 

SELECT gender, emp_no
FROM employees
WHERE gender = 'F'
AND last_name = 'Baba';


SET optimizer_switch='skip_scan=on';

EXPLAIN  
SELECT gender, last_name, emp_no
FROM employees 
WHERE last_name = 'Baba';

/* 4.2.7 직원의 성과 성별 순서로 그루핑하여 몇 건의 데이터가 있는지 구하시오.  */
EXPLAIN 
SELECT last_name, gender, COUNT(1) cnt
FROM employees
GROUP BY last_name, gender;

EXPLAIN 
SELECT last_name, gender, COUNT(1) cnt
FROM employees
GROUP BY gender, last_name;


/* 4.2.8 사원의 입사일자 값이 '1989'로 시작하면서 
사원번호가 100_000를 초과하는 데이터를  조회 
*/
EXPLAIN 
SELECT emp_no
FROM employees 
WHERE hire_date LIKE '1989%'
AND emp_no > 100000;

-- 300,024 rows
SELECT COUNT(1) FROM employees;
-- 28,394 rows
SELECT COUNT(1) FROM employees WHERE hire_date LIKE '1989%';
-- 210,024 rows
SELECT COUNT(1) FROM employees WHERE emp_no > 100000;


EXPLAIN 
SELECT emp_no
FROM employees USE INDEX (I_입사일자)
WHERE hire_date LIKE '1989%'
AND emp_no > 100000;


SELECT emp_no
FROM employees USE INDEX (I_입사일자)
WHERE (hire_date >= '1989-01-01' AND hire_date < '1990-01-01')
AND emp_no > 100000;
 
SELECT emp_no
FROM employees
WHERE (hire_date >= '1989-01-01' AND hire_date < '1990-01-01')
AND emp_no > 100000;

 
SELECT emp_no
FROM employees USE INDEX (PRIMARY)
WHERE emp_no > 100000
AND (hire_date >= '1989-01-01' AND hire_date < '1990-01-01');


/* 4.2.9 emp_access_logs(사원출입기록), door(출입문)
'B' 출입문으로 출입한 이력이 있는 정보를 모두 조회 
*/

SELECT * 
FROM emp_access_logs IGNORE INDEX (I_출입문)
WHERE door = 'B';


SELECT * 
FROM emp_access_logs
WHERE door = 'B';

SELECT door, COUNT(1) cnt
FROM emp_access_logs
GROUP BY door;


-- 660,000 rows
SELECT COUNT(1) FROM emp_access_logs;

-- 300,000 rows
SELECT COUNT(1) FROM emp_access_logs WHERE door = 'B';



/* 4.2.10 입사일자가 1994년 1월 1일부터 2000년 12월 31일까지인 사원들의 이름과 성을 출력
*/
-- ALL 테이블 풀 스캔 

SELECT first_name, last_name
FROM employees
WHERE hire_date BETWEEN STR_TO_DATE('1994-01-01', '%Y-%m-%d') 
                    AND STR_TO_DATE('2000-12-31', '%Y-%m-%d'); 
                    
                    
-- range 인덱스 사용                

SELECT first_name, last_name
FROM employees FORCE INDEX(I_입사일자)
WHERE hire_date BETWEEN STR_TO_DATE('1994-01-01', '%Y-%m-%d') 
                    AND STR_TO_DATE('2000-12-31', '%Y-%m-%d');                     

-- 48,875 rows
SELECT COUNT(1)
FROM employees
WHERE hire_date BETWEEN STR_TO_DATE('1994-01-01', '%Y-%m-%d') 
                    AND STR_TO_DATE('2000-12-31', '%Y-%m-%d'); 

-- 300,024 rows
SELECT COUNT(1) FROM employees;


SELECT 48875/300024 * 100;

 
SELECT first_name, last_name
FROM employees
WHERE year(hire_date) BETWEEN '1994' AND '2000';


/* 부서사원 테이블(dept_emp)과 부서(departments) 테이블을 조인하여 부서 시작일자가
'2002-03-01'일 부터인 사원의 데이터를 조회하는 쿼리
표시컬럼: 사원번호, 부서번호 
*/
EXPLAIN 
 SELECT DE.emp_no, D.dept_no
   FROM dept_emp DE
  INNER JOIN departments D
     ON D.dept_no = DE.dept_no
  WHERE DE.from_date >= '2002-03-01';


-- 331,603 rows
SELECT COUNT(1) FROM dept_emp;

-- 1,341 rows
SELECT COUNT(1) FROM dept_emp WHERE from_date >= '2002-03-01';

-- 9 rows
SELECT COUNT(1) FROM departments;

-- no straight
 SELECT DE.emp_no, D.dept_no
   FROM dept_emp DE
  INNER JOIN departments D
     ON D.dept_no = DE.dept_no
  WHERE DE.from_date >= '2002-03-01';
  

-- straight
 SELECT STRAIGHT_JOIN DE.emp_no, D.dept_no
   FROM dept_emp DE
  INNER JOIN departments D
     ON D.dept_no = DE.dept_no
  WHERE DE.from_date >= '2002-03-01';


/* 4.3.2 사원번호가 450,000보다 크고 최대 연봉이 100,000보다 큰 데이터를 찾아 출력하시오.
즉, 사원번호가 450,000번을 초과하면서 그동안 받은 연봉 중 한 번이라도 100,000달러를
초과한 적이 있는 사원의 정보를 출력
표시컬럼: 사원번호, 이름, 성
3,155 rows
*/
-- dependent subquery
EXPLAIN 
SELECT E.emp_no, E.first_name, E.last_name
FROM employees E
WHERE E.emp_no > 450000
  AND ( SELECT MAX(S.salary) FROM salaries S WHERE S.emp_no = E.emp_no );


-- 300,024
SELECT COUNT(1) FROM employees;
-- 49,999
SELECT COUNT(1) FROM employees WHERE emp_no > 450000;

-- 2,844,047
SELECT COUNT(1) FROM salaries;

-- 94,696
SELECT COUNT(1) FROM salaries WHERE salary > 100000;



EXPLAIN 
 SELECT E.emp_no, E.first_name, E.last_name
   FROM employees E
  INNER JOIN salaries S
  ON S.emp_no = E.emp_no
  WHERE E.emp_no > 450000
  GROUP BY E.emp_no
 HAVING MAX(S.salary) > 100000;


/* 4.3.3 'A'츨입문으로 출입한 사원이 총 몇 명인지 구하시오.
*/
-- 150,000
-- 0.11
SELECT COUNT(DISTINCT emp_no)
FROM emp_access_logs
WHERE door = 'A';

-- 5.1.1
SELECT E.emp_no, S.avg_salary, S.max_salary, S.min_salary
FROM employees E
INNER JOIN ( SELECT emp_no
                  , ROUND(AVG(salary), 0) AS avg_salary
                  , ROUND(max(salary), 0) AS max_salary
                  , ROUND(min(salary), 0) AS min_salary
               FROM salaries 
              GROUP BY emp_no 
) S
ON S.emp_no = E.emp_no
WHERE E.emp_no BETWEEN 10001 AND 10100;              

-- JOIN 

 SELECT E.emp_no, E.first_name, E.last_name
      , ROUND(AVG(S.salary), 0) AS avg_salary
     , ROUND(MAX(S.salary), 0) AS max_salary
   , ROUND(MIN(S.salary), 0) AS min_salary
   FROM employees E
  INNER JOIN salaries S
     ON S.emp_no = E.emp_no
  WHERE (E.emp_no >= 10001 AND E.emp_no < 10101)
  GROUP BY E.emp_no;

-- straight
EXPLAIN 
SELECT straight_join E.emp_no, E.first_name, E.last_name
     , ROUND(AVG(S.salary), 0)
  , ROUND(MAX(S.salary), 0)
  , ROUND(MIN(S.salary), 0)
FROM salaries S
INNER JOIN employees E
ON S.emp_no = E.emp_no
WHERE E.emp_no >= 10001 AND E.emp_no < 10101
GROUP BY E.emp_no;


-- Scalar
SELECT E.emp_no, E.first_name, E.last_name
     , (SELECT ROUND(AVG(S1.salary), 0) FROM salaries S1 WHERE S1.emp_no = E.emp_no)
     , (SELECT ROUND(MAX(S2.salary), 0) FROM salaries S2 WHERE S2.emp_no = E.emp_no)
     , (SELECT ROUND(MIN(S3.salary), 0) FROM salaries S3 WHERE S3.emp_no = E.emp_no)
FROM employees E
WHERE E.emp_no >= 10001 AND E.emp_no < 10101;


-- 5.1.2 비효율적인 페이징 수행

-- original 
EXPLAIN
SELECT E.emp_no, E.first_name, E.last_name, E.hire_date
FROM employees E
INNER JOIN salaries S
ON S.emp_no = E.emp_no
WHERE E.emp_no BETWEEN 10001 AND 50000
GROUP BY E.emp_no
ORDER BY SUM(S.salary) DESC
LIMIT 150, 10;

-- result
EXPLAIN 
 SELECT E.emp_no, E.first_name, E.last_name, E.hire_date
   FROM employees E
  INNER JOIN (
SELECT emp_no
  FROM salaries 
 WHERE emp_no >= 10001 AND emp_no < 50001
 GROUP BY emp_no
 ORDER BY SUM(salary) DESC
 LIMIT 150, 10
   ) S
     ON S.emp_no = E.emp_no;