subindev 님의 블로그

Quiz 1) 한번도 구매되지 않은 상품을 조회하여라. SELECT p.product_id, p.nameFROM PRODUCTS AS PLEFT JOIN orderdetails AS OD ON P.PRODUCT_ID = OD.PRODUCT_IDWHERE OD.PRODUCT_ID IS NULL;  Quiz 2) 가장 많이 구매된 상품과 판매된 수량을 출력하라.SELECT P.PRODUCT_ID, P.name, SUM(OD.QUANTITY) AS TOTAL_QUANTITYFROM PRODUCTS AS PJOIN ORDERDETAILS AS OD ON P.PRODUCT_ID = OD.PRODUCT_IDGROUP BY P.PRODUCT_ID, P.nameORDER BY TOTAL_QUANTITY DESCLIMI..

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;

Quiz 1) 최연소 부서 매니저의 이름, 성별, 근무 연수를 조회하시오.SELECT CONCAT(e.first_name, ' ', e.last_name ) as 'name', gender, TIMESTAMPDIFF(YEAR, e.hire_date, NOW()) AS workYearsFROM dept_manager AS dmJOIN employees AS e ON e.emp_no = dm.emp_no ORDER BY birth_date DESC LIMIT 1;

Quiz 1) 사원 번호가 110420인 직원의 이름, 직급, 역대 연봉 이력을 구하여라SELECT e.emp_no, e.first_name, e.last_name, d.dept_name, t.title, sa.salary, sa.from_date AS salary_from_date, sa.to_date AS salary_to_dateFROM employees eJOIN titles t ON e.emp_no = t.emp_noJOIN dept_emp de ON e.emp_no = de.emp_noJOIN departments d ON de.dept_no = d.dept_noJOIN salaries sa ON e.emp_no = sa.emp_noWHERE..

Quiz 1) 오늘이 생일인 사람이 각 부서마다 몇명인지 오늘 날짜, 부서 이름, 생일자 순으로 출력하여라.SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d') as '오늘 날짜', d.dept_name AS '부서이름' , COUNT(e.emp_no) AS '생일자 수' FROM employees AS eJOIN dept_emp AS de ON de.emp_no = e.emp_noJOIN departments AS d ON d.dept_no = de.dept_noWHERE DATE_FORMAT(e.birth_date, '%m-%d') = DATE_FORMAT(CURDATE(), '%m-%d') AND de.to_date = '9999-01-01'GROUP BY d.dep..