sql 활용 연습문제.

sql  활용 연습문제 및 쿼리.

1.  각 [도/광역시]별 [시/군/구]의 개수를 조회하라.

--기준: 도/광역시별(province) 조건:- 컬럼: 도/광역시, 시군구 개수

SELECT prvn_name, COUNT(distc_cd) 

FROM province p JOIN district d ON p.prvn_cd = d.prvn_cd
GROUP BY p.prvn_cd;

2. 각 [도/광역시]의 [시/군/구]별 관광지 개수를 조회.

--기준:도광역시. 시군구 조건:- 컬럼: 도광 이름, 시군구 이름, 관광지개수 

SELECT p.prvn_cd,p.prvn_name,d.distc_cd,d.distc_name, COUNT(a.attrc_cd)
FROM attraction a JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
GROUP BY p.prvn_cd, d.distc_cd;

3.경기도 수원시의 2017년 1분기 내국인 방문자 평균을 조회

--조건: 경기도,수원시, 2017년, 1분기(quarter()=1) 기준: 도, 시, 분기..

컬럼: 도광이름. 시군구이름. 내국인방문자평균(반올림)

SELECT QUARTER(f.basis_date), p.prvn_name, d.distc_name, ROUND(AVG(f.native_cnt))
FROM figure f JOIN attraction a ON f.prvn_cd = a.prvn_cd AND f.distc_cd = a.distc_cd AND f.attrc_cd = a.attrc_cd
JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
WHERE p.prvn_name = '경기도' AND d.distc_name = '수원시' AND SUBSTR(f.basis_date,1,4) = '2017' AND QUARTER(f.basis_date)=1
GROUP BY p.prvn_cd, d.distc_cd,QUARTER(f.basis_date)

4.경기도 수원시의 2017년 하반기 내국인 방문자 합계를 조회

기준 경기도 수원시 17년 하반기, 조건 경기도, 수원시, 17년 하반기.(3,4분기.. ) 컬럼: 도이름 시이름  내궁인방문자합계

SELECT p.prvn_name, d.distc_name, round(SUM(f.native_cnt))
FROM figure f JOIN attraction a ON f.prvn_cd = a.prvn_cd AND f.distc_cd = a.distc_cd AND f.attrc_cd = a.attrc_cd
JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
WHERE p.prvn_name = '경기도' AND d.distc_name = '수원시' AND SUBSTR(f.basis_date,1,4) = 2017 AND QUARTER(f.basis_date) > 2
GROUP BY p.prvn_cd, d.distc_cd

5. 2017년, 경기도 수원시의 관광지 중 평균 내국인 방문객 수가 가장 많은 관광지 3개와 그 방문자 수를 조회.

기준: 년 경기도 수원시 관광지 

조건: 경기도 수원시 17년 

컬럼: 도이름/ 시이름/ 관광지이름 /내국인방문객 평균수 가장 많은 관광의 방문자 수

정렬: 내국인방문객 평균수 내림차순 정렬수 리밋 3.  

SELECT p.prvn_name, d.distc_name, a.attrc_name, round(AVG(f.native_cnt)) as figure
FROM figure f JOIN attraction a ON f.prvn_cd = a.prvn_cd AND f.distc_cd = a.distc_cd AND f.attrc_cd = a.attrc_cd
JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
WHERE p.prvn_name = '경기도' AND d.distc_name = '수원시' AND SUBSTR(f.basis_date,1,4) = 2017
GROUP BY p.prvn_cd, d.distc_cd, a.attrc_cd
ORDER BY AVG(f.native_cnt) DESC LIMIT 3;

6. 2017년, 경기도 관광지 중 내국인 방문객 수가 가장 많은 관광지명과 관광지가 속한 도시명, 방문객 수 조회.

기준: 경기도  

조건 경기도 17년 

칼럼 도명 /광광지명/ 내국인방문객수 / (그곳의)도시명/ 

정렬: 내국인방문객수 내림차순 리밋 1

SELECT p.prvn_name, a.attrc_name, sum(f.native_cnt) as figure, d.distc_name
FROM figure f JOIN attraction a ON f.prvn_cd = a.prvn_cd AND f.distc_cd = a.distc_cd AND f.attrc_cd = a.attrc_cd
JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
WHERE p.prvn_name = '경기도' AND SUBSTR(f.basis_date,1,4) = 2017
GROUP BY p.prvn_cd, d.distc_cd, a.attrc_cd
ORDER BY sum(f.native_cnt) DESC LIMIT 1; 

7.  2017년, 경기도에 속한 도시의 관광지별 내국인 방문객 수 합계를 각 도시 코드 별 오름차순 후 합계를 내림 차순하여 조회.

기준: 도/시/관광지 - 

조건: 경기도 17년 

컬럼: 도이름 시이름 광광지이름 내국인방문객 수의 합계 

정렬: 도시코드 별 오름차순, 내국방문객 합계 내림차순

SELECT p.prvn_name, d.distc_name, a.attrc_name, sum(f.native_cnt) as figure
FROM figure f JOIN attraction a ON f.prvn_cd = a.prvn_cd AND f.distc_cd = a.distc_cd AND f.attrc_cd = a.attrc_cd
JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
WHERE p.prvn_name = '경기도' AND SUBSTR(f.basis_date,1,4) = 2017
GROUP BY p.prvn_cd, d.distc_cd, a.attrc_cd
ORDER BY d.distc_cd, sum(f.native_cnt) DESC;

8. 2017년, 경기도에 속한 도시의 관광지 중 내국인 방문객 수가 가장 많은 관광지와 방문객 수를 조회.

SELECT p.prvn_name,d.distc_name, a.attrc_name, sum(f.native_cnt) as figure
FROM figure f JOIN attraction a ON f.prvn_cd = a.prvn_cd AND f.distc_cd = a.distc_cd AND f.attrc_cd = a.attrc_cd
JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
WHERE p.prvn_name = '경기도' AND SUBSTR(f.basis_date,1,4) = 2017
GROUP BY p.prvn_cd, d.distc_cd, a.attrc_cd
ORDER BY sum(f.native_cnt) DESC;

9. 경기도 김포시의 관광지 중

각 년도 별 평균 내국인 방문객 수가 높은 1~3위를 조회하라.

조건 경기도 김포시 

기준 도 시 관광지/ 년도/ 

컬럼 CONCAT(p.prvn_name,' ',d.distc_name, ,a.attrc_name)

/ 년도 / 평균내국인방문객수 

정렬. 평균내국인방문객수 내림차순. 리밋 2 

SELECT CONCAT(p.prvn_name,' ',d.distc_name,' ',a.attrc_name) as attrc_name, SUBSTR(f.basis_date,1,4) as year, ROUND(AVG(f.native_cnt)) as figure
FROM figure f JOIN attraction a ON f.prvn_cd = a.prvn_cd AND f.distc_cd = a.distc_cd AND f.attrc_cd = a.attrc_cd
JOIN district d ON a.prvn_cd = d. prvn_cd AND a.distc_cd = d.distc_cd
JOIN province p ON d.prvn_cd = p.prvn_cd
WHERE p.prvn_name = '경기도' AND d.distc_name = '김포시'
GROUP BY p.prvn_cd, d.distc_cd, a.attrc_cd, year
ORDER BY year, figure DESC LIMIT 3;