subindev 님의 블로그

현재 Mybatis를 사용하여 진행중인 'A+ 마켓 플러터 앱 프로젝트' 에서 사용된 쿼리입니다.Quiz)  채팅방 Id를 이용하여 최근 메시지 30개를 포함한 채팅방 정보 SELECT c.id AS chatRoomId, tpi.uuid_name AS productThumbnail, p.product_name AS productName, p.price AS price, p.is_negotiable AS isNegotiable, p.id AS productId, u.id AS userId, u.name AS ..

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 c.customer_id, c.name, p.name AS most_purchased_product, COUNT(od.product_id) AS product_countFROM customers AS cJOIN orders AS o ON c.customer_id = o.customer_idJOIN orderdetails AS od ON o.order_id = od.order_idJOIN products AS p ON p.product_id = od.product_idWHERE c.customer_id IN ( SELECT DISTINCT o.customer_id FROM o..

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;