BASHA TECH
수정할 거임 0802 본문
728x90
-- Department ID
SELECT a.EMPLOYEE_ID,
a.EMP_NAME ,
b.DEPARTMENT_ID,
a.JOB_ID
FROM EMPLOYEES a ,
departments b
WHERE (
EMPLOYEE_id, job_id
)
IN
(SELECT a.EMPLOYEE_ID,
JOB_ID
FROM JOB_HISTORY a
) ;
SELECT *
FROM JOB_HISTORY a
-- WHERE
;
--SELECT DISTINCT a.DEPARTMENT_ID
SELECT a.DEPARTMENT_ID
FROM JOB_HISTORY a
-- WHERE
-- ORDER BY a.DEPARTMENT_ID DESC
;
SELECT a.EMPLOYEE_ID ,
a.EMP_NAME ,
( SELECT b.DEPARTMENT_ID
FROM JOB_HISTORY b
WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID
)
AS did
FROM EMPLOYEES a
WHERE a.DEPARTMENT_ID = 20 ;
SELECT a.EMPLOYEE_ID ,
a.EMP_NAME ,
( SELECT b.DEPARTMENT_ID
FROM JOB_HISTORY b
WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID
)
AS did
FROM EMPLOYEES a
WHERE a.DEPARTMENT_ID = 110 -- where 절에 110 => 2개. 에러인 이유 다중 값이라서 select 안됨.
-- 따라서 scalar sub query는 단일값이어야함
;
SELECT a.EMPLOYEE_ID ,
a.EMP_NAME ,
( SELECT b.DEPARTMENT_ID
FROM JOB_HISTORY b
WHERE 110 = b.DEPARTMENT_ID
)
AS did
FROM EMPLOYEES a
WHERE a.DEPARTMENT_ID = 110 --에러인 이유 row가 2개라 다중 값이라서
;
-- INNER JOIN
SELECT c.EMPLOYEE_ID,
c.DEPARTMENT_ID ,
a.EMPLOYEE_ID ,
b.DEPARTMENT_NAME
FROM EMPLOYEES a --107
,
departments b --27
,
JOB_HISTORY c --10
WHERE c.EMPLOYEE_ID = a.EMPLOYEE_ID
AND c.DEPARTMENT_ID = b.DEPARTMENT_ID ;
SELECT a.EMPLOYEE_ID ,
a.DEPARTMENT_ID ,
( SELECT b.EMP_NAME
FROM EMPLOYEES b
WHERE b.EMPLOYEE_ID = a.EMPLOYEE_ID
)
AS EMP_NAME
FROM JOB_HISTORY a ;
SELECT a.EMPLOYEE_ID ,
a.DEPARTMENT_ID ,
( SELECT b.EMP_NAME
FROM EMPLOYEES b
WHERE b.EMPLOYEE_ID = a.EMPLOYEE_ID
)
AS EMP_NAME ,
( SELECT a.DEPARTMENT_NAME
FROM DEPARTMENTS a
WHERE b.DEPARTMENT_ID = a.DEPARTMENT_ID
)
AS DEPARTMENT_NAME
FROM JOB_HISTORY a ;
-- 평균 급여보다 많이 받는 사원이 존재하는 부서번호, 부서명 추출 해라
--SELECT b.DEPARTMENT_ID, b.DEPARTMENT_NAME
-- ,( SELECT a.SALARY
-- FROM EMPLOYEES a
-- WHERE a.SALARY > AVG(SALARY)
-- ) AS SALARY
-- FROM EMPLOYEES a
-- ,DEPARTMENTS b
--;
SELECT a.DEPARTMENT_ID,
a.DEPARTMENT_NAME
FROM DEPARTMENTS a
WHERE a.DEPARTMENT_ID IN
(-- IN = >ANY
SELECT b.DEPARTMENT_ID
FROM EMPLOYEES a
WHERE b.salary >
( -- 단일 값 연산자
SELECT AVG(c.salary)
FROM EMPLOYEES c
) -- IN = >ANY
) ;
SELECT a.DEPARTMENT_ID,
a.DEPARTMENT_NAME
FROM DEPARTMENTS a
WHERE EXISTS
( SELECT 1
FROM EMPLOYEES a
WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID
AND b.salary >
( -- 단일 값 연산자
SELECT AVG(c.salary)
FROM EMPLOYEES c
) -- IN = >ANY
) ;
SELECT DISTINCT b.DEPARTMENT_ID -- exists 할 땐 => 12번에 끝남. distinct => 11번에 끝남
FROM EMPLOYEES b
WHERE b.SALARY >
( SELECT AVG (c.salary)
FROM EMPLOYEES c
) ;
-- 부서테이블 => 부서번호 => 사원테이블 => group by => avg(salary)
-- 문제: 상위 부서가 기획부에 속하는 사원들의 부서별 평균 급여를 구해라
-- 1. 상위 부서가 기획부 => 부서번호
SELECT a.DEPARTMENT_ID
FROM DEPARTMENTS a
WHERE a.DEPARTMENT_NAME = '기획부';
-- 하위 부서의 부서번호 추출
SELECT a.DEPARTMENT_ID
FROM DEPARTMENTS a
WHERE a.PARENT_ID = 90 ;
SELECT a.DEPARTMENT_ID
FROM DEPARTMENTS a
WHERE a.PARENT_ID =
( SELECT a.DEPARTMENT_ID
FROM DEPARTMENTS a
WHERE a.DEPARTMENT_NAME = '기획부'
) ;
-- 사원 테이블 연결 부서별 평균 급여
SELECT a.DEPARTMENT_ID,
ROUND(AVG(a.SALARY)) ,
MAX(a.SALARY),
MIN(a.SALARY)
FROM EMPLOYEES a
WHERE a.DEPARTMENT_ID IN
( SELECT b.DEPARTMENT_ID
FROM DEPARTMENTS a
WHERE b.PARENT_ID =
( SELECT c.DEPARTMENT_ID -- 제일 안쪽 select문부터 보기
FROM DEPARTMENTS c
WHERE c.DEPARTMENT_NAME = '기획부'
)
)
GROUP BY a.DEPARTMENT_ID ;
-- 기획부 하위부서에 속한 사원들의 평균 급여로 해당 부서 급여를 갱신하시오.
UPDATE EMPLOYEES a
SET a.SALARY =
(
SELECT sal
FROM (
SELECT c.DEPARTMENT_ID,
ROUND(AVG(c.SALARY)) AS sal
FROM EMPLOYEES c
WHERE c.DEPARTMENT_ID IN
(
SELECT b.DEPARTMENT_ID
FROM DEPARTMENTS a
WHERE b.PARENT_ID = 90
)
GROUP BY c.DEPARTMENT_ID
) d
WHERE a.DEPARTMENT_ID = d.DEPARTMENT_ID
)
WHERE a.DEPARTMENT_ID
(
SELECT e.DEPARTMENT_ID
FROM DEPARTMENTS b
WHERE
)
;
ROLLBACK;
SELECT b.DEPARTMENT_ID
FROM DEPARTMENTS b
, EMPLOYEES c
WHERE b.PARENT_ID = 90
AND b.DEPARTMENT_ID = c.DEPARTMENT_ID
;
SELECT AVG(c.SALARY)
FROM DEPARTMENTS b
, EMPLOYEES c
WHERE b.PARENT_ID = 90
AND b.DEPARTMENT_ID = c.DEPARTMENT_ID
;
-- 사원번호, 사원명
SELECT a.EMPLOYEE_ID, a.EMP_NAME
-- , b.DEPARTMENT_NAME -- 부서명
, (
SELECT e.DEPARTMENT_NAME
FROM DEPARTMENTS e
WHERE e.DEPARTMENT_ID = a.DEPARTMENT_ID
) AS dept_name
FROM EMPLOYEES a
-- , DEPARTMENTS b
WHERE a.SALARY > (
SELECT AVG(c.SALARY) -- 평균 급여
FROM DEPARTMENTS b
, EMPLOYEES c
WHERE b.PARENT_ID = 90 -- 90 하위 부서 (60, 70, 100, 110)
AND b.DEPARTMENT_ID = c.DEPARTMENT_ID -- 하위부서에 속한 사원 추출
)
-- WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID
;
-- InLine View
-- 90번 부서 하위 부서 사원의 평균 급여 보다 많이 받는 사원 조회
-- 사원번호, 사원명, 부서명
SELECT a.EMPLOYEE_ID, a.EMP_NAME
, b.DEPARTMENT_NAME
FROM EMPLOYEES a
, DEPARTMENTS b
, ( -- 90번 부서 아휘 부서 사원 평균 급여
SELECT AVG(d.SALARY) AS avg_sal
FROM DEPARTMENTS c
, EMPLOYEES d
WHERE c.PARENT_ID = 90
AND c.DEPARTMENT_ID = d.DEPARTMENT_ID
) c
WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID
AND a.SALARY > c.avg_sal
;
-- InLine View
-- 90번 부서 하위 부서 사원의 평균 급여 보다 많이 받는 사원 조회
-- 사원번호, 사원명, 부서명
-- Scalar SubQuery
-- Sub Query
-- InLine View 활용 문제
-- 매출 => sales table
-- 2000년도 연평균 매출보다 큰 월평균 매출 추출
-- 월, 평균매출 조회
SELECT b.*
FROM (
SELECT ROUND(AVG(a.AMOUNT_SOLD)) AS year_avg
FROM SALES a
WHERE a.SALES_month >= '200001'
AND a.SALES_month <= '200012'
) a -- 2000년도 연 평균 매출
, (
SELECT a.SALES_month, ROUND(AVG(a.AMOUNT_SOLD)) AS month_avg
FROM SALES a
WHERE a.SALES_month BETWEEN '200001' AND '200012'
GROUP BY a.SALES_month
) b -- 2000년도 월별 평균 매출
WHERE a.year_avg < b.month_avg
;
-- 2000년도 연 평균 매출
SELECT ROUND(AVG(a.AMOUNT_SOLD)) AS year_avg
FROM SALES a
WHERE a.SALES_month >= '200001'
AND a.SALES_month <= '200012'
;
SELECT COUNT(*)
FROM SALES s
WHERE s.SALES_month >= '200001'
AND s.SALES_month <= '200012'
;
--2000년도 월 평균 매출
SELECT a.SALES_month, ROUND(AVG(a.AMOUNT_SOLD)) AS month_avg
FROM SALES a
WHERE a.SALES_month BETWEEN '200001' AND '200012'
GROUP BY a.SALES_month
;
--SELECT s
-- FROM SALES a
-- , CUSTOMERS b
-- , (
-- SELECT AVG(d.
-- FROM SALES c
-- , CUSTOMERS d
-- WHERE c.CUST_ID = d.CUST_ID
-- AND AVG(c.SALES_MONTH) > AVG(c.AMOUNT_SOLD)
-- )
-- WHERE sales_date IN 2000
--;
-- 연평균이 나오는 인라인 뷰, 월평균이 큰 인라인 뷰.
-- 월평균 인라인 뷰 > 연평균 인라인 뷰
SELECT * FROM SALES;
--31 6828 2 156 1998-01-23 199801 1 9.85 2014-01-08 2014-01-08
--31 885 4 156 1998-01-23 199801 1 9.85 2014-01-08 2014-01-08
--31 2758 4 156 1998-01-23 199801 1 9.85 2014-01-08 2014-01-08
-- 6828 고객(customers)이 구매(sales)한 제품(prouducts)
SELECT COUNT(*), MAX(a.AMOUNT_SOLD)
, MIN(a.AMOUNT_SOLD), AVG(a.AMOUNT_SOLD)
, SUM(a.AMOUNT_SOLD)
--b.CUST_NAME, c.PROD_NAME
FROM SALES a
, CUSTOMERS b
, PRODUCTS c
WHERE a.CUST_ID = b.CUST_ID -- 구매한 고객 연결
AND a.PROD_ID = c.PROD_ID -- 판매된 상품 연결
AND a.CUST_ID = 6828
;
DESC sales;
--quiero irme a casa
-- InLine View 활용 문제
-- 매출 => sales table
-- 이탈리아에서 2000년도 연평균 매출보다 큰 월평균 매출 추출
-- 월, 평균매출 조회
SELECT b.*
FROM (
SELECT ROUND(AVG(a.AMOUNT_SOLD)) AS year_avg -- 전체 나라에서 판매된 평균
FROM SALES a
WHERE a.SALES_month >= '200001'
AND a.SALES_month <= '200012'
) a -- 2000년도 연 평균 매출
, (
SELECT a.SALES_month, ROUND(AVG(a.AMOUNT_SOLD)) AS month_avg
FROM SALES a
WHERE a.SALES_month BETWEEN '200001' AND '200012'
GROUP BY a.SALES_month
) b -- 2000년도 월별 평균 매출
WHERE a.year_avg < b.month_avg
;
-- 이탈리아 2000년도 연 평균매출
SELECT ROUND(AVG(a.AMOUNT_SOLD)) AS year_avg -- 이탈리아에서 판매된 평균
FROM SALES a -- 고객 코드
, CUSTOMERS b -- 국가 코드
, COUNTRIES c -- 국가 명 = '이탈리아'
WHERE a.CUST_ID = b.CUST_ID -- 판매와 고객이 연결됨
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
AND a.SALES_MONTH BETWEEN '200001' AND '200012'
;
SELECT COUNT(*)
FROM SALES a -- 고객 코드
, CUSTOMERS b -- 국가 코드
, COUNTRIES c -- 국가 명 = '이탈리아'
WHERE a.CUST_ID = b.CUST_ID -- 판매와 고객이 연결됨
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
AND a.SALES_MONTH BETWEEN '200001' AND '200012'
;
SELECT COUNT(*) -- 전체에서 이탈리아에서 판매된 건수
FROM SALES a -- 고객 코드
, CUSTOMERS b -- 국가 코드
, COUNTRIES c -- 국가 명 = '이탈리아'
WHERE a.CUST_ID = b.CUST_ID -- 판매와 고객이 연결됨
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
;
-- 인라인뷰는 단독 실행이 되어야함
-- 이탈리아 2000년도 월별 평균매출
SELECT a.SALES_MONTH, ROUND(AVG(a.AMOUNT_SOLD)) AS month_avg
FROM SALES a
, CUSTOMERS b
, COUNTRIES c
WHERE a.CUST_ID = b.CUST_ID
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
AND a.SALES_MONTH BETWEEN '200001' AND '200012'
GROUP BY a.SALES_MONTH
ORDER BY month_avg DESC
;
SELECT a.SALES_MONTH, ROUND(AVG(a.AMOUNT_SOLD)) AS month_avg
FROM SALES a
, CUSTOMERS b
, COUNTRIES c
WHERE a.CUST_ID = b.CUST_ID
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
AND a.SALES_MONTH BETWEEN '200001' AND '200012'
GROUP BY a.SALES_MONTH
HAVING ROUND(AVG(a.AMOUNT_SOLD)) > 108 (--month_avg > 108 이건 error. alias 못쓴다
SELECT ROUND(AVG(a.AMOUNT_SOLD)) AS year_avg -- 연매출
FROM SALES a
, CUSTOMERS b
, COUNTRIES c
WHERE a.CUST_ID = b.CUST_ID
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
AND a.SALES_MONTH BETWEEN '200001' AND '200012'
)
ORDER BY month_avg DESC
;
--이게 더 낫다...? 왜?
SELECT a.SALES_MONTH, ROUND(AVG(a.AMOUNT_SOLD)) AS month_avg
FROM sales a
, CUSTOMERS b
, COUNTRIES c
WHERE a.CUST_ID = b.CUST_ID
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
AND a.SALES_MONTH BETWEEN '200001' AND '200012'
GROUP BY a.SALES_MONTH
HAVING ROUND(AVG(a.AMOUNT_SOLD)) > (
SELECT ROUND(AVG(a.AMOUNT_SOLD)) AS year_avg
FROM sales a -- 고객코드
, CUSTOMERS b -- 국가코드
, COUNTRIES c -- 국가명 = '이탈리아'
WHERE a.CUST_ID = b.CUST_ID
AND b.COUNTRY_ID = c.COUNTRY_ID
AND c.COUNTRY_NAME = 'Italy'
AND a.SALES_MONTH BETWEEN '200001' AND '200012'
)
ORDER BY month_avg DESC
;
-- 연도별로 이탈리아 매출 데이터를 살펴 매출실적이 가장 많은 사원의 목록과 매출액을 구하는 쿼리
-- 출력항목: 연도 최대 매출 사원명 최대매출액
/*******************************************************
* 이탈리아에서 연도별로 매출실적이 가장 많은 사원과 매출액 추출
* 1. 연도별 사원 별 개별적 매출
* 2. 연도별 사원 중 최대 매출
* 3. 합치기 => 1의 결과와 2의 결과 같은 사원 추출 (join)
********************************************************/
SELECT * FROM SALES c;
--1. 연도별 사원 별 개별적 매출
SELECT COUNT(*)
,SUM(a.AMOUNT_SOLD)
FROM SALES a
, EMPLOYEES b
WHERE a.EMPLOYEE_ID = b.EMPLOYEE_ID -- 사원 연결
;
--2. 연도별 사원 중 최대 매출
SELECT MAX (a.am
FROM SALES a
, EMPLOYEES b728x90
반응형
'Computer > DB' 카테고리의 다른 글
| DB#CH06. 조인과 서브쿼리 이해 (0) | 2022.07.29 |
|---|---|
| DB#CH05. 그룹 쿼리와 집합 연산자 이해 (0) | 2022.07.28 |
| DB#CH04. SQL 함수 이해 (0) | 2022.07.27 |
| DB#CH03. SQL 문장 이해 (0) | 2022.07.26 |
| DB#CH02. 데이터베이스를 구성하는 객체 이해 (0) | 2022.07.26 |
'Computer/DB' Related Articles
more
Comments