MSSQL 데이터 추출

2010년부터 2020년까지 도서 선호 장르 데이터 추출하기

-통계자료 sql로 데이터를 추출 후 excel과 powerpoint로 sql 데이터를 이미지로 추출

라이브러리 DB
년도별 거래수

select transaction_year, count(*) from transaction group by transaction_year;

가장 많이 대여된 책

select TRANSACTION_YEAR, b.title ,b.GENRE, count(*) from transaction t, books b 
where t.book_id = b.book_id
group by TRANSACTION_YEAR,  b.title, b.GENRE order by count(*) desc;

SQL 쿼리 정리

select distinct TRANSACTION_YEAR "년도 중복 해제", max(count(b.GENRE)) "장르 카운트" from transaction t, books b 
where t.book_id = b.book_id
group by TRANSACTION_YEAR, b.GENRE order by TRANSACTION_YEAR;

동호 테이블 생성

CREATE TABLE 동호 AS (
select TRANSACTION_YEAR "년도", b.GENRE "장르" , count(*)"장르 카운트" from books b, transaction t
where t.book_id = b.book_id
group by transaction_year, b.genre

---데이터 정렬

--10Y

>CREATE TABLE TBL2011 AS (SELECT * FROM 동호 WHERE 년도=2010);
SELECT * FROM TBL2011;
CREATE TABLE A AS (SELECT * FROM TBL2011 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL2011));
SELECT * FROM A;

--11Y

>CREATE TABLE TBL_11 AS (SELECT * FROM 동호 WHERE 년도=2011);
SELECT * FROM TBL_11;
CREATE TABLE B AS (SELECT * FROM TBL_11 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_11));
SELECT * FROM B;

--12Y

>CREATE TABLE TBL_12 AS (SELECT * FROM 동호 WHERE 년도=2012);
SELECT * FROM TBL_12;
CREATE TABLE C AS (SELECT * FROM TBL_12 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_12));
SELECT * FROM C;

--13Y

>CREATE TABLE TBL_13 AS (SELECT * FROM 동호 WHERE 년도=2013);
SELECT * FROM TBL_13;
CREATE TABLE D AS (SELECT * FROM TBL_13 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_13));
SELECT * FROM D;

--14Y

>CREATE TABLE TBL_14 AS (SELECT * FROM 동호 WHERE 년도=2014);
SELECT * FROM TBL_14;
CREATE TABLE E AS (SELECT * FROM TBL_14 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_14));
SELECT * FROM E;

--15Y

>CREATE TABLE TBL_15 AS (SELECT * FROM 동호 WHERE 년도=2015);
SELECT * FROM TBL_15;
CREATE TABLE F AS (SELECT * FROM TBL_15 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_15));
SELECT * FROM F;

--16Y

>CREATE TABLE TBL_16 AS (SELECT * FROM 동호 WHERE 년도=2016);
SELECT * FROM TBL_16;
CREATE TABLE G AS (SELECT * FROM TBL_16 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_16));
SELECT * FROM G;

--17Y

>CREATE TABLE TBL_17 AS (SELECT * FROM 동호 WHERE 년도=2017);
SELECT * FROM TBL_17;
CREATE TABLE H AS (SELECT * FROM TBL_17 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_17));
SELECT * FROM H;

--18Y

>CREATE TABLE TBL_18 AS (SELECT * FROM 동호 WHERE 년도=2018);
SELECT * FROM TBL_18;
CREATE TABLE Y AS (SELECT * FROM TBL_18 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_18));
SELECT * FROM Y;

--19Y

>CREATE TABLE TBL_19 AS (SELECT * FROM 동호 WHERE 년도=2019);
SELECT * FROM TBL_19;
CREATE TABLE J AS (SELECT * FROM TBL_19 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_19));
SELECT * FROM J;

--20Y

>CREATE TABLE TBL_20 AS (SELECT * FROM 동호 WHERE 년도=2020);
SELECT * FROM TBL_20;
CREATE TABLE K AS (SELECT * FROM TBL_20 WHERE "장르 카운트" = (SELECT MAX("장르 카운트") FROM TBL_20));
select * from k;