MYSQL : Query 연습_2

/* 1. ex_employees 테이블과 ex_salaries 테이블을 outer join하여 사원 이름(emp_name), 급여(amount) 필드를 포함하고, 급여(amount)가 높은 순서대로 정렬된 결과를 출력하는 쿼리를 작성하세요. */
     SELECT emp_name, amount
           FROM ex_employees a
LEFT OUTER JOIN ex_salaries b
          ON a.emp_id = b.salary_id
    ORDER BY amount DESC;

    
/* 2. ex_orders 테이블과 ex_order_items 테이블을 outer join하여 주문 ID(order_id), 주문 일자(order_date), 주문 상품(product_id), 주문 수량(quantity) 필드를 포함하고, 주문 일자(order_date)가 최신순으로 정렬된 결과를 출력하는 쿼리를 작성하세요. */
         SELECT b.order_id, order_date, product_id, quantity
           FROM ex_order_items a
LEFT OUTER JOIN ex_orders b
   ON b.order_id = a.order_id
       ORDER BY order_date DESC;


/* 3. ex_employees 테이블과 ex_salaries 테이블, ex_departments 테이블을 outer join하여 부서 이름(dept_name), 사원 이름(emp_name), 급여(amount) 필드를 포함하고, 급여(amount)가 높은 순서대로 정렬된 결과를 출력하는 쿼리를 작성하세요. 단, 급여(amount)가 없는 사원도 포함되어야 합니다. */
         SELECT dept_name, emp_name, amount 
           FROM ex_employees a
LEFT OUTER JOIN ex_salaries b 
 ON a.emp_id = b.salary_id
LEFT OUTER JOIN ex_departments c 
 ON a.emp_dept_id = c.dept_id
       ORDER BY amount DESC;
        

/* 4. ex_products 테이블과 ex_products_categories 테이블, ex_categories 테이블을 outer join하여 카테고리 이름(name), 상품 이름(product_name), 상품 가격(price) 필드를 포함하고, 카테고리 이름(name)이 'Electronics'인 상품만 출력하는 쿼리를 작성하세요. 단, 카테고리 이름(name)이 없는 상품도 포함되어야 합니다. */
         SELECT cname, pname, price 
           FROM ex_categories a
LEFT OUTER JOIN ex_products_categories b 
 ON a.category_id = b.product_id
LEFT OUTER JOIN ex_products c 
 ON a.category_id = c.product_id
    WHERE cname = "Electronics" OR cname is null
 ORDER BY price DESC;


/* 5. 'emp_dept_id' 값이 2인 사원들의 이름과 연봉(salary)을 조회하는 쿼리를 작성해보세요. (서브쿼리 사용) */
      SELECT emp_name, emp_salary
        FROM ex_employees 
       WHERE emp_dept_id IN ( SELECT emp_dept_id
                    FROM ex_employees 
             WHERE emp_dept_id = 2
        ); 
 


/* 6. 'emp_dept_id' 값이 1이면서, 'emp_salary' 값이 해당 부서 평균 급여보다 많은 사원들의 이름과 급여를 조회하는 쿼리를 작성해보세요. (서브쿼리 사용) */

 SELECT emp_name, emp_salary
  FROM ex_employees 
    WHERE emp_salary >= ( SELECT AVG(emp_salary)
       FROM ex_employees
      WHERE emp_dept_id = 1
   GROUP BY emp_dept_id
   ) 
    AND emp_dept_id = 1 ;


/* 7. 각 부서별 평균 급여보다 적은 급여를 받는 사원들의 이름과 급여를 조회하는 쿼리를 작성해보세요. (서브쿼리 사용) */

   SELECT emp_name, emp_salary, emp_dept_id
        FROM ex_employees e
WHERE EXISTS ( SELECT 1 
        FROM ex_employees
       WHERE emp_dept_id = e.emp_dept_id
      GROUP BY emp_dept_id
      HAVING AVG(emp_salary) > e.emp_salary
 );
    
    

   
/* 8. 부서 이름이 'Department 2' 이면서, 해당 부서의 사원 중 가장 급여가 높은 사원의 이름과 급여를 조회하는 쿼리를 작성해보세요. (서브쿼리 사용) */


/* 컬럼 x */




/* 9. ex_employees 테이블과 ex_salaries 테이블을 employee_id로 조인하고, 해당 employee_id의 salary가 해당 연도(2022)의 평균보다 높은 직원들의 이름과 salary를 출력하는 쿼리를 작성하세요. */
/*
SELECT a.emp_name, b.amount
FROM ex_employees a
JOIN ex_salaries b
ON a.emp_id = b.employee_id
WHERE b.amount > (SELECT AVG(amount)
FROM ex_salaries
WHERE to_date LIKE '2022%'
GROUP BY to_date);
*/



/* 10. ex_orders 테이블과 ex_order_items 테이블을 order_id로 외부조인하고, 해당 order_id의 total price가 2000보다 큰 주문의 customer_id와 total price를 출력하는 쿼리를 작성하세요. */ /* total price 없어서 product_id 대체 */
SELECT a.customer_id, b.rproduct_id
  FROM ex_orders a
LEFT OUTER JOIN ( SELECT product_id AS rproduct_id, order_id
     FROM ex_order_items 
    WHERE product_id > 2000) b
    ON a.order_id = b.order_id;



/* 11. 모든 부서(department)에 속한 직원의 이름과 급여(salary) 정보를 조회하되, 각 부서별 평균 급여보다 많은 급여를 받는 직원의 정보만 출력하세요. */

  /* SELECT emp_name, temp_salary
FROM ex_employees 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.emp_dept_id = b.temp_dept_id
WHERE
; */

SELECT emp_name, emp_salary
           FROM ex_employees e
   WHERE EXISTS (
          SELECT 1
             FROM ex_employees
            WHERE emp_dept_id = e.emp_dept_id
    GROUP BY emp_dept_id
                  HAVING AVG(emp_salary) < e.emp_salary
 );




/* 12. 부서(department)별로 가장 높은 급여(salary)를 받는 직원의 이름과 해당 부서 이름을 조회하세요. 단, 모든 부서의 이름과 함께 조회해야 하며, 해당 부서에 직원이 없는 경우 "No employee in this department" 라는 문구를 출력해야 합니다. */

    SELECT emp_name, dept_name, emp_dept_id, IFNULL(emp_dept_id, 'No employee in this department') AS no_employee  
     FROM ex_departments a
LEFT OUTER JOIN ex_employees b
         ON a.dept_id = b.emp_dept_id
WHERE EXISTS ( SELECT 1
  FROM ex_employees
 WHERE emp_dept_id = b.emp_dept_id
 GROUP BY emp_dept_id
      HAVING emp_salary = MAX(emp_salary)
 );


/* 13. ex_employees 테이블에서 emp_dept_id 가 ex_departments 테이블의 dept_id 와 일치하는 행들만 선택하고, 해당 부서의 평균 연봉보다 많은 급여를 받는 사원의 이름과 부서 이름을 출력하는 쿼리를 작성하세요. */

SELECT a.emp_name, b.dept_name 
  FROM ex_employees a
  JOIN ex_departments b
      ON b.dept_id = a.emp_dept_id
WHERE EXISTS ( SELECT 1
  FROM ex_employees
 WHERE emp_dept_id = a.emp_dept_id
 GROUP BY emp_dept_id
HAVING emp_salary > AVG(emp_salary) 
 );


/* 14. ex_orders 테이블에서 order_date 가 가장 최근인 3개의 주문과 그 주문을 한 고객의 이름과 이메일, 주문한 상품의 개수를 출력하는 쿼리를 작성하세요. */

SELECT a.c_name, a.email, b.order_date , c.quantity, b.customer_id
  FROM customers a
  JOIN ex_orders b 
       ON a.customer_number = b.customer_id
  JOIN ex_order_items c 
       ON  a.customer_number = c.order_id
 ORDER BY b.order_date DESC LIMIT 3;

/* ex_order_items에서 order_id가 중복되는 값이 있어서 값이 두개 나옴.*/