MYSQL : Query 연습_1

/* 1. 부서별 평균 급여가 가장 높은 부서의 이름과 평균 급여를 출력하는 쿼리 */
  SELECT a.dept_name, b.temp_salary
    FROM ex_departments a
    JOIN ( SELECT AVG(emp_salary) AS temp_salary, emp_dept_id AS temp_dept_id 
             FROM ex_employees 
      GROUP BY emp_dept_id) b
      
ON a.dept_id = b.temp_dept_id
ORDER BY b.temp_salary DESC LIMIT 1;


/* 2. 한 고객이 가장 많이 주문한 제품의 이름과 주문 수량을 출력하는 쿼리 */
/* 컬럼 x */


/* 3. 연봉이 가장 높은 직원의 이름과 연봉을 출력하는 쿼리 */
SELECT emp_name, emp_salary
     FROM ex_employees
    WHERE emp_salary = (
    SELECT MAX(emp_salary) 
      FROM ex_employees
     );


/* 4. 카테고리별 제품 가격이 가장 높은 제품의 이름, 가격, 그리고 카테고리 이름을 출력하는 쿼리 */
   SELECT cname, b.pname, b.tprice
     FROM ex_categories a
     JOIN (
           SELECT MAX(price) AS tprice, pname, product_id
    FROM ex_products 
   GROUP BY product_id) b
       ON a.category_id = b.product_id;


/* 5. 부서별 최고 연봉자 찾기 */
/* 이게 5번과 7번의 차이 */
    SELECT a.dept_name, b.emp_name, temp_salary
      FROM ex_departments a
      JOIN ( SELECT MAX(emp_salary) AS temp_salary, emp_name, emp_dept_id
            FROM ex_employees 
  GROUP BY emp_dept_id) b
        ON a.dept_id = b.emp_dept_id;


/* 6. 카테고리별 판매 금액 순위 */
/* 컬럼 x */


/* 7. 각 부서에서 가장 높은 급여를 받는 직원의 정보 */
/* 이게 5번과 7번의 차이 */
  SELECT a.dept_name, b.emp_name, b.emp_salary
    FROM ex_departments a
    JOIN ex_employees b
      ON a.dept_id = b.emp_dept_id
   WHERE b.emp_salary IN ( SELECT MAX(emp_salary) 
  FROM ex_employees 
 GROUP BY emp_dept_id
     )
ORDER BY b.emp_salary DESC;


/*ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ*/


/* 8. ex_employees와 ex_salaries 테이블을 outer join하여 모든 직원 정보를 가져와서, 해당 직원의 급여가 있는 경우 급여 정보도 함께 가져오는 쿼리를 작성해보세요. */
/* ex_employees에 LEFT OUTER JOIN한 이유는 직원의 급여가 없어도 출력될 수 있게 하기 위해서  */
    SELECT *
           FROM ex_employees a
LEFT OUTER JOIN ex_salaries b
             ON a.emp_id =b.employee_id ;


/* 9. ex_products와 ex_products_categories 테이블을 outer join하여 모든 제품 정보를 가져와서, 해당 제품의 카테고리가 있는 경우 카테고리 정보도 함께 가져오는 쿼리를 작성해보세요. */
    SELECT *
           FROM ex_products a
LEFT OUTER JOIN ex_products_categories b
             ON a.product_id = b.product_id ;
             

/* 10. ex_employees와 ex_departments 테이블을 outer join하여 모든 직원과 그들이 속한 부서의 이름과 위치를 가져와보세요. 만약 부서 정보가 없는 경우에도 모든 직원 정보를 가져와야 합니다. */

/* 모든 직원의 정보를 가져와야하기 때문에 직원 정보를 가만히 두고 departments를 LEFT OUTER 조인 */
SELECT a.emp_name, b.dept_name, b.dept_location
           FROM ex_employees a
LEFT OUTER JOIN ex_departments b
             ON b.dept_id = a.emp_dept_id;
             
             

/* 11. ex_orders와 ex_order_items 테이블을 outer join하여 모든 주문과 그 주문에 포함된 모든 아이템의 정보를 가져오세요. 만약 주문 아이템 정보가 없는 경우에도 모든 주문 정보를 가져와야 합니다. */
SELECT *
           FROM ex_orders a
LEFT OUTER JOIN ex_order_items b
             ON a.order_id = b.order_id;
             



/* 12. 모든 카테고리와 해당 카테고리에 해당하는 상품의 정보를 가져오는 왼쪽 외부 조인을 사용하는 쿼리 */
          SELECT * 
            FROM ex_products a
LEFT  OUTER JOIN ex_categories b
              ON a.product_id = b.category_id;
              

/* 13. 모든 사원과 해당 사원의 부서의 정보를 가져오는 오른쪽 외부 조인을 사용하는 쿼리 */
   
          SELECT a.dept_name, a.dept_location, b.emp_name 
            FROM ex_departments a
RIGHT OUTER JOIN ex_employees b
              ON a.dept_id = b.emp_id;
/*ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ*/


/* 14. ex_departments 테이블과 ex_employees 테이블을 outer join하여 각 부서의 인원 수와 평균 급여를 구하는 쿼리 */
  SELECT COUNT(emp_id), AVG(emp_salary)
             FROM ex_departments a
 LEFT  OUTER JOIN ex_employees b
               ON a.dept_id = b.emp_dept_id
   GROUP BY emp_dept_id;



/* 15. ex_products 테이블과 ex_products_categories 테이블을 outer join하여 각 제품의 카테고리를 포함한 정보를 모두 출력하는 쿼리 */
 SELECT *
            FROM ex_products a
 LEFT OUTER JOIN ex_products_categories b
              ON a.product_id = b.category_id;