[SQL] 데이터 분석 실습1 : 전국 캠핑장 데이터

전국 캠핑장 정보 테이블 및 데이터 생성하기

데이터는 공공데이터 '전국야영(캠핑)장표준데이터' 를 사용했습니다.

https://www.data.go.kr/data/15021139/standard.do

전국야영(캠핑)장표준데이터

해당 파일을 저장한 후, SQL 에 불러올 때 인코딩을 'euc-kr', Minimum column length를 100, 자본금 칼럼을 DOUBLE 로 바꿔서 열었습니다.

 

데이터를 확인해보면, 위와 같으며 총 4,258건이 있습니다(2024.10월 기준)

 

각 지역에 위치한 캠핑장 조회

1. 캠핑장의 사업장명과 소재지전체주소를 출력(단, 사업자명은 NAME, 소재지전체주소는 ADDRESS로 출력)

SELECT 사업장명 AS NAME, 소재지전체주소 AS ADDRESS
FROM camping_info;

2. 1번 데이터에 정상영업 하고 있는 캠핑장만 출력

SELECT 사업장명 AS NAME, 소재지전체주소 AS ADDRESS FROM camping_info WHERE 영업상태명 IN ('영업/정상');

SELECT 사업장명 AS NAME, 소재지전체주소 AS ADDRESS FROM camping_info WHERE 영업상태구분코드 = 1;

3. 양양에 위치한 캠핑장은 몇개인지 출력

select count(소재지전체주소) from camping_info where 소재지전체주소 like '%양양%';

4. 3번 데이터에서 폐업한 캠핑장은 몇개인지 출력

SELECT COUNT(*) FROM camping_info 
WHERE 소재지전체주소 LIKE '%양양%' 
AND 영업상태구분코드 = 3;

SELECT COUNT(*) FROM camping_info 
WHERE 소재지전체주소 LIKE '%양양%' 
AND 영업상태명 = '폐업';

5. 태안에 위치한 캠핑장의 사업자명 출력

SELECT 사업장명 
FROM camping_info 
WHERE 소재지전체주소 LIKE '%태안%';

6. 5번 테이블에서 2020년에 폐업한 캠핑장만 출력

SELECT 사업장명 
FROM camping_info 
WHERE YEAR(폐업일자) = 2020
and 소재지전체주소 LIKE '%태안%';

SELECT 사업장명 
FROM camping_info 
WHERE 폐업일자 like '2020%'
and 소재지전체주소 LIKE '%태안%';

7. 제주도와 서울에 위치한 캠핑장은 몇개인지 출력

SELECT COUNT(*) 
FROM camping_info 
WHERE 소재지전체주소 LIKE '%제주%'
OR 소재지전체주소 LIKE '%서울%';

 

해수욕장에 위치한 캠핑장 찾기

1. 해수욕장에 위치한 캠핑장의 사업자명과 인허가일자를 출력

SELECT 사업장명, 인허가일자
FROM camping_info 
WHERE 소재지전체주소 LIKE '%해수욕장%';

2. 제주도 해수욕장에 위치한 캠핑장의 소재지전체주소와 사업자명 출력

SELECT 사업장명, 소재지전체주소
FROM camping_info
WHERE 소재지전체주소 LIKE '%제주%'
AND 소재지전체주소 LIKE '%해수욕장%';

3. 2번 데이터에서 인허가일자가 가장 최근인 곳의 인허가일자 출력

SELECT MAX(인허가일자)
FROM camping_info 
WHERE 소재지전체주소 LIKE '%제주%'
or 소재지전체주소 like '%해수욕장%';

4. 강원도 해수욕장에 위치한 캠핑장 중 영업중인 곳만 출력

SELECT 사업장명
FROM camping_info 
WHERE 소재지전체주소 LIKE '%강원%' 
AND 영업상태구분코드 = 1;

5. 4번 데이터 중에서 인허가일자가 가장 오래된 곳의 인허가일자 출력

SELECT Min(인허가일자)
FROM camping_info 
WHERE 소재지전체주소 LIKE '%강원%' 
AND 영업상태구분코드 = 1;

6. 해수욕장에 위치한 캠핑장 중 시설면적이 가장 넓은 곳의 시설면적 출력

SELECT MAX(시설면적)
FROM camping_info 
WHERE 소재지전체주소 LIKE '%해수욕장%';

7. 해수욕장에 위치한 캠핑장의 평균 시설면적 출력

SELECT AVG(시설면적)
FROM camping_info 
WHERE 소재지전체주소 LIKE '%해수욕장%';

 

면적이 가장 넓은 캠핑장 TOP 10

1. 캠핑장의 사업자명과 시설면적을 시설면적이 가장 넓은 순으로 출력

SELECT 사업장명,시설면적
FROM camping_info
ORDER by 시설면적 DESC;

2. 1번 데이터에서 10위까지만 출력

SELECT 사업장명,시설면적
FROM camping_info
ORDER by 시설면적 desc LIMIT 10;

3. 경기도 캠핑장중에 면적이 가장 넓은 순으로 5개만 출력

SELECT 사업장명,시설면적
FROM camping_info
WHERE 소재지전체주소 LIKE '%경기%'
order by 시설면적 desc limit 5;

4. 3번 데이터에서 1위는 제외

SELECT 사업장명,시설면적
FROM camping_info
WHERE 소재지전체주소 LIKE '%경기%'
order by 시설면적 desc limit 1,4;

5. 영업중인 캠핑장 중에서 인허가일자가 가장 오래된 순으로 20개 출력

SELECT * 
FROM camping_info 
WHERE 영업상태구분코드 = 1
order by 인허가일자 limit 20;

6. 2020년 10월 - 2021년 3월 사이에 폐업한 캠핑장의 사업자명과 소재지전체주소 출력

SELECT 사업장명, 소재지전체주소
FROM camping_info 
WHERE 영업상태구분코드 = 3
and 폐업일자 between '2020-10-01' and '2021-03-31';

7. 폐업한 캠핑장 중에서 시설면적이 가장 컸던 곳의 사업자명과 시설면적 출력

SELECT 사업장명, 시설면적
FROM camping_info 
WHERE 영업상태구분코드 = 3
order by 시설면적 desc limit 1;

 

캠핑장이 가장 많은 지역은 어디?

1. 각 지역별 캠핑장 수 출력(단, 지역은 LOCATION 으로 출력)

SELECT SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' ')) AS LOCATION, COUNT(*)  
FROM camping_info 
GROUP BY SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' '));

2. 1번 데이터를 캠핑장 수가 많은 지역부터 출력

SELECT SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' ')) AS LOCATION, COUNT(*)  
FROM camping_info 
GROUP BY SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' '))
order by COUNT(*) DESC;

3. 각 지역별 영업중인 캠핑장 수 출력

SELECT SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' ')) AS LOCATION, COUNT(*)  
FROM camping_info 
where 영업상태구분코드 = 1 
GROUP BY SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' '))
order by COUNT(*) DESC;

4. 3번 데이터에서 캠핑장 수가 300개 이상인 지역만 출력

SELECT SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' ')) AS LOCATION, COUNT(*)  
FROM camping_info 
where 영업상태구분코드 = 1 
GROUP BY SUBSTR(소재지전체주소, 1, INSTR(소재지전체주소, ' '))
having COUNT(*) >= 300
order by COUNT(*) DESC;

5. 년도별 폐업한 캠핑장 수 출력(단, 년도는 YEAR로 출력)

SELECT SUBSTR(폐업일자, 1,4) as year, COUNT(*)
FROM camping_info 
where 영업상태구분코드 = 3 
GROUP BY SUBSTR(폐업일자, 1,4);

6. 5번 데이터를 년도별로 내림차순하여 출력

SELECT SUBSTR(폐업일자, 1,4) as YEAR, COUNT(*)
FROM camping_info 
where 영업상태구분코드 = 3 
GROUP BY YEAR 
order by YEAR DESC;