ProgrammingSQL

SQL Advanced

my sql, my sql 조인문, query, sql

고급 SQL 실습

sales , geography 테이블, 데이터 직접 생성하신 후 실습하세요.(이미 있다면 삭제 후 생성해주세요.) 아래의 SQL을 실행한 후에 문제에 답을 생각해봅시다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
drop table sales;

drop table geography;



create table sales (

  dosi varchar2(20),

  sales number,

  sale_date date

) ;



INSERT INTO sales (dosi,sales,sale_date) values ('seoul',1500,'2015/02/02');

INSERT INTO sales (dosi,sales,sale_date) values ('pusan',1200,'2015/01/01');

INSERT INTO sales (dosi,sales,sale_date) values ('pusan',1100,'2015/02/08');

INSERT INTO sales (dosi,sales,sale_date) values ('inchon',500,'2015/01/30');



create table geography (

  region varchar2(10),

  dosi varchar2(20)

) ;



INSERT INTO geography (region,dosi) VALUES ('east','pusan');

INSERT INTO geography (region,dosi) VALUES ('east','sokcho');

INSERT INTO geography (region,dosi) VALUES ('west','seoul');

INSERT INTO geography (region,dosi) VALUES ('west','inchon');



COMMIT;

위의 쿼리문을 잘 따라하셨으면 아래의 테이블과 같은 결과를 확인할 수 있습니다.

sales 테이블 조회 결과
dosi sales sale_date
seoul 1500 2015/02/02
pusan 1200 2015/01/01
pusan 1100 2015/02/08
inchon 500 2015/01/30
geography 테이블 조회 결과
region dosi
east pusan
east sokcho
west seoul
west inchon

문제

  1. sales,geography 테이블에 PK Index, Normal Index 를 생성 후 인덱스를 조회하는 SQL문장과 실행결과를 작성하세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE UNIQUE INDEX IDX_GEOGRAPHY_REGION_DOSI ON GEOGRAPHY(REGION, DOSI);
CREATE INDEX IDX_SALES_DOSI ON SALES(DOSI);


SELECT A.INDEX_NAME
FROM USER_IND_COLUMNS A
	, USER_INDEXES B
WHERE A.TABLE_NAME = 'SALES'
    AND A.INDEX_NAME = B.INDEX_NAME
    AND A.COLUMN_NAME = 'DOSI';

INDEX_NAME
--------------------
IDX_SALES_DOSI


SELECT A.INDEX_NAME
FROM USER_IND_COLUMNS A, USER_INDEXES B
WHERE A.TABLE_NAME = 'GEOGRAPHY'
    AND A.INDEX_NAME = B.INDEX_NAME
    AND A.COLUMN_NAME = 'REGION';

INDEX_NAME
--------------------
IDX_GEOGRAPHY_REGION_DOSI
  1. sales, geography 테이블을 조인하여 조회하는 뷰를 생성하는 SQL문장을 작성하세요.
1
2
3
4
5
6
CREATE OR REPLACE VIEW V_SALES_GEOGRAPHY (SALES, DOSI, SALES_DATE, REGION)
AS
SELECT S.SALES, S.DOSI, S.SALE_DATE, G.REGION
FROM SALES S, GEOGRAPHY G
WHERE S.DOSI = G.DOSI
ORDER BY S.DOSI;
  1. 다음 결과를 출력하는 SQL을 작성하세요.(집합연산자 사용)

기간별, 지역별 매출합계

yyyymm region sales
201501 east 1200
201501 west 500
201502 east 1100
201502 west 1500
  • 조인 SQL문장
1
2
3
4
5
6
7
SELECT TO_CHAR(S.SALE_DATE, 'YYYYMM') AS YYYYMM
	  , G.REGION
	  , SUM(S.SALES)    AS SALES
FROM SALES S
	 , GEOGRAPHY G
WHERE S.DOSI = G.DOSI
GROUP BY TO_CHAR(S.SALE_DATE,'YYYYMM'), G.REGION;
  • 서브쿼리 SQL문장
1
2
3
4
5
6
7
SELECT TO_CHAR(S. SALE_DATE, 'YYYYMM') AS YYYYMM
    , ( SELECT REGION
        FROM GEOGRAPHY G
        WHERE G.DOSI = S.DOSI ) REGION
    , S.SALES
FROM SALES S
ORDER BY TO_CHAR(S.SALE_DATE,'YYYYMM'), REGION ASC;
  1. 도시별 매출합계
  • 아래 결과를 조인쿼리로 작성해 보세요.
dosi sales
sokcho 매출없음
inchon 500
pusan 2300
seoul 1500 
1
2
3
4
5
6
7
8
9
10
11
12
SELECT G.DOSI
	 , NVL( TO_CHAR(Z.SUM_SALES),  '매출없음' )
FROM GEOGRAPHY G
LEFT OUTER JOIN
	( SELECT DOSI
            , SUM(SALES) AS SUM_SALES
	FROM SALES
    GROUP BY DOSI
    ORDER BY DOSI
) Z
ON G.DOSI = Z.DOSI
ORDER BY Z.SUM_SALES ASC NULLS FIRST;
  1. 도시별 매출합계
  • 아래 결과를 from 절에 geography 테이블 하나만 위치시키고 쿼리를 작성해 보세요.
dosi sales
sokcho 매출없음
inchon 500
pusan 2300
seoul 1500 
1
2
3
4
5
6
7
SELECT G.DOSI	AS DOSI
	, NVL(( SELECT TO_CHAR( SUM(S.SALES) )
			FROM SALES S
			GROUP BY S.DOSI
			HAVING  G.DOSI = DOSI), '매출없음') AS SALES
FROM GEOGRAPHY G
ORDER BY SALES DESC NULLS FIRST;
  1. WITH구문을 이용하여 다음 결과를 출력하는 SQL을 작성하세요.

(지역별, 기간별(월별) 합계를 출력하는데 월별매출액이 1200 이상 데이터 추출)

region yyyymm sales
east 201501 1200
east 201501 1200 
1
2
3
4
5
6
7
8
9
10
WITH W_SALES AS (
	SELECT G.REGION
		 , TO_CHAR(S.SALE_DATE,'YYYYMM') YYYYMM
		 , S.SALES
	FROM SALES S, GEOGRAPHY G
	WHERE S.DOSI = G.DOSI
		AND S.SALES >= 1200
	ORDER BY G.REGION, TO_CHAR(S.SALE_DATE,'YYYYMM')
)
SELECT * FROM W_SALES;
Share