subindev 님의 블로그

Quiz 1) 현재 재직중인 사람들 중, Customer Service 부서의 Engineer 직원들의 수를 구하시오.SELECT d.dept_name AS '부서 이름', t.title AS '직급 명', count(t.emp_no) AS '직원수 'FROM dept_emp de JOIN departments d ON d.dept_no = de.dept_noJOIN titles t ON t.emp_no = de.emp_noWHERE d.dept_name = 'Customer Service'AND t.title = 'Engineer'AND t.to_date = '9999-01-01';

Quiz 1) 1월 생일자들의 이름, 현재 부서, 직책을 조회하시오.SELECT CONCAT(e.first_name, ' ', e.last_name) AS '이름' , d.dept_name , t.titleFROM employees eJOIN dept_emp de ON de.emp_no = e.emp_noJOIN departments d ON d.dept_no = de.dept_no JOIN titles t ON t.emp_no = e.emp_noWHERE date_format(e.birth_date, '%m') = 01AND de.to_date='9999-01-01'AND t.to_date='9999-01-01';

Quiz 1) 각 부서별 현재 재직중인 인원수를 계산하시오.SELECT dept_name AS '부서 이름', count(emp_no) AS '인원 수' FROM departments AS dJOIN dept_emp AS de ON de.dept_no=d.dept_noWHERE de.to_date = '9999-01-01'GROUP BY de.dept_no ;

Quiz 1) 가장 구매건이 많은 회원 뽑기 SELECT o.customer_id as 'ID', c.name, count(order_id) as '총 주문 건수' FROM customers AS cJOIN orders AS o ON c.customer_id = o.customer_idGROUP BY c.customer_idORDER BY count(order_id) DESCLIMIT 1;

Quiz 1) 오늘이 생일인 사람이 각 부서마다 몇명인가 ?SELECT d.dept_no AS '부서 번호', d.dept_name AS '부서 이름', COUNT(e.emp_no) AS '생일인 직원 수'FROM employees AS eJOIN dept_emp AS de ON e.emp_no = de.emp_noJOIN departments AS d ON de.dept_no = d.dept_noWHERE DATE_FORMAT(e.birth_date, '%m-%d') = DATE_FORMAT(CURDATE(), '%m-%d') AND de.to_date = '9999-01-01' -- 현재 부서인지 GROUP BY d.dept_no;  DATE_FORMAT() 함수

Quiz 1) 사원의 수가 한명도 없는 부서를 조회하여라.SELECT dept_name FROM departments AS dLEFT JOIN dept_emp AS de ON de.dept_no = d.dept_noWHERE de.emp_no IS NULL;