뭐든지 다 알아보자

https://school.programmers.co.kr/learn/courses/30/lessons/133027?language=oracle 

SELECT FLAVOR FROM (
SELECT A.FLAVOR AS FLAVOR , A.TOTAL_ORDER + B.TOTAL_ORDER AS TOTAL_ORDER FROM 
(SELECT FLAVOR , SUM(TOTAL_ORDER) AS TOTAL_ORDER FROM JULY
GROUP BY FLAVOR) A
LEFT JOIN FIRST_HALF B
ON A.FLAVOR = B.FLAVOR
ORDER BY TOTAL_ORDER DESC)
WHERE ROWNUM <=3

==>  서브쿼리 큰거 하나 ==> JOIN 이후

==> A.TOTAL_ORDER + B.TOTAL_ORDER ==> 더하기

https://school.programmers.co.kr/learn/courses/30/lessons/131114

VERSION 2.0

SELECT WAREHOUSE_ID , WAREHOUSE_NAME , ADDRESS , NVL(FREEZER_YN, 'N') AS FREEZER_YN FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기%'
ORDER BY WAREHOUSE_ID

VERSION 1.0

SELECT WAREHOUSE_ID , WAREHOUSE_NAME , ADDRESS , NVL(FREEZER_YN , 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE WAREHOUSE_NAME LIKE ('%경기%')
ORDER BY WAREHOUSE_ID ASC

https://school.programmers.co.kr/learn/courses/30/lessons/131530

SELECT RPAD(SUBSTR(PRICE , 0,1) ,5 , 0) AS PRICE_GROUP , COUNT(*) FROM PRODUCT
GROUP BY SUBSTR(PRICE , 0,1)
ORDER BY SUBSTR(PRICE , 0,1)

==> SUBSTR(열, 자를거 시작위치 , 자를거 끝 위치)

==> RPAD(열 , 전체문자열크기, 채울 숫자)

https://school.programmers.co.kr/learn/courses/30/lessons/59413

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

VER 3.0

SELECT A.HOUR AS HOUR  , COUNT(B.ANIMAL_TYPE) AS COUNT FROM 

(SELECT LEVEL -1 AS HOUR FROM DUAL 
CONNECT BY LEVEL <=24 ) A
LEFT JOIN ANIMAL_OUTS B ON 
TO_CHAR(DATETIME , 'HH24') = A.HOUR 
 GROUP BY HOUR
 ORDER BY A.HOUR

==> LEFT JOIN ==> CONNECT BY LEVEL

SELECT A.HOUR , COUNT(B.ANIMAL_ID) AS COUNT FROM
(SELECT LEVEL-1 AS HOUR FROM DUAL
CONNECT BY LEVEL<=24) A
FULL OUTER JOIN ANIMAL_OUTS B
ON A.HOUR = TO_CHAR(DATETIME , 'HH24')
GROUP BY A.HOUR
ORDER BY HOUR

VER 1.0

1. CONNECT BY LEVEL 절 연습!!!

SELECT LEVEL AS NO FROM DUAL
CONNECT BY LEVEL <=10

2. 2023년 12월까지의 열 생성해봐라! 

SELECT '2023 년 '||LPAD(LEVEL,2,0)||'월' AS YEAR
FROM DUAL
CONNECT BY LEVEL <=12

==> LPAD(변수, 2칸, 남은칸숫자(0)넣기) 

3. 특정 구간의 날짜 찾기

SELECT TO_DATE('20221001', 'YYYYMMDD') + (LEVEL-1) AS DT
   FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('20221011', 'YYYYMMDD') - TO_DATE('20221001', 'YYYYMMDD')) +1

4.  최종실습

SELECT A.HOUR AS HOUR, COUNT(B.ANIMAL_TYPE) AS COUNT FROM 
(SELECT DISTINCT (LEVEL-1) AS HOUR FROM ANIMAL_OUTS
      CONNECT BY LEVEL <= 24
       ) A
  LEFT JOIN ANIMAL_OUTS B
    ON TO_CHAR(DATETIME,'HH24') = A.HOUR
 GROUP BY HOUR
 ORDER BY HOUR

==> CONNECT BY LEVEL <= 24를 통해 1부터 24까지의 LEVEL 변수 생성

==> A 테이블에 0부터 23까지의 열 이 생성되었다. 

==>ANIMAL_OUTS(B) 테이블을 A테이블에 LEFT JOIN한다. ==> DATETIME의 24시간제(HH24) 와 A테이블(0~23)이 일치하는 것들을 머리를 집어넣는다.

==> COUNT(ANIMAL_OUTS(B)) ==> GROUP 으로 HOUR간 정렬이 완료가 된다. COUNT가 가능해진다.

VER 2.0  FROM 절 서브쿼리문 활용

SELECT A.HOUR , NVL(B.COUNT , 0) AS COUNT FROM
(SELECT LEVEL-1 AS HOUR FROM DUAL
CONNECT BY LEVEL<=24) A FULL OUTER JOIN (
SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HOUR , COUNT(*) AS COUNT  FROM ANIMAL_OUTS
GROUP BY TO_NUMBER(TO_CHAR(DATETIME, 'HH24'))
) B ON A.HOUR = B.HOUR 
ORDER BY HOUR

===================================

-- 코드를 입력하세요
-- SELECT TO_NUMBER(TO_CHAR(DATETIME , 'HH')) AS HOUR, COUNT(*) FROM ANIMAL_OUTS
-- GROUP BY TO_CHAR(DATETIME , 'HH')
-- ORDER BY HOUR
-- SELECT  TO_CHAR(DATETIME , 'HH24') AS HOUR , COUNT(*) AS COUNT FROM ANIMAL_OUTS
-- GROUP BY TO_CHAR(DATETIME , 'HH24')
-- ORDER BY HOUR

-- SELECT A.HOUR AS HOUR, COUNT(B.ANIMAL_TYPE) AS COUNT FROM 
-- (SELECT DISTINCT (LEVEL-1) AS HOUR FROM ANIMAL_OUTS
--       CONNECT BY LEVEL <= 24
--        ) A
--   LEFT JOIN ANIMAL_OUTS B
--     ON TO_CHAR(DATETIME,'HH24') = A.HOUR
--  GROUP BY HOUR
--  ORDER BY HOUR
-- SELECT LEVEL AS NO FROM DUAL
-- CONNECT BY LEVEL <=10
-- SELECT '2023 년 ' ||LPAD(LEVEL, 2, 0)||'월' AS NO 
-- FROM DUAL 
-- CONNECT BY LEVEL <=12

-- SELECT '2023 년 '||LPAD(LEVEL,2,0)||'월' AS YEAR
-- FROM DUAL
-- CONNECT BY LEVEL <=12

-- SELECT TO_DATE('20221001', 'YYYYMMDD') + (LEVEL-1) AS DT
--    FROM DUAL
-- CONNECT BY LEVEL <= (TO_DATE('20221011', 'YYYYMMDD') - TO_DATE('20221001', 'YYYYMMDD')) +1

SELECT A.HOUR , NVL(B.COUNT , 0) AS COUNT FROM
(SELECT LEVEL-1 AS HOUR FROM DUAL
CONNECT BY LEVEL<=24) A FULL OUTER JOIN (
SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HOUR , COUNT(*) AS COUNT  FROM ANIMAL_OUTS
GROUP BY TO_NUMBER(TO_CHAR(DATETIME, 'HH24'))
) B ON A.HOUR = B.HOUR 
ORDER BY HOUR

https://school.programmers.co.kr/learn/courses/30/lessons/131532

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

VERSION 2.0

SELECT TO_CHAR(SALES_DATE , 'YYYY') AS YEAR , TO_NUMBER(TO_CHAR(SALES_DATE ,'MM')) AS MONTH ,
B.GENDER AS GENDER, COUNT(DISTINCT(A.USER_ID)) AS USERS
FROM ONLINE_SALE A
INNER JOIN USER_INFO B ON B.USER_ID = A.USER_ID
GROUP BY TO_CHAR(SALES_DATE , 'YYYY') , TO_NUMBER(TO_CHAR(SALES_DATE ,'MM')) , B.GENDER 
HAVING B.GENDER IS NOT NULL
ORDER BY YEAR , MONTH , GENDER

==> COUNT(DISTINCT) ==> 중복값 제거해줘야 한다.

VERSION 1.0

1. 

SELECT TO_CHAR(B.SALES_DATE , 'YYYY') AS YEAR , TO_NUMBER(TO_CHAR(B.SALES_DATE , 'MM')) AS MONTH ,
A.GENDER AS GENDER  AS USERS 
FROM ONLINE_SALE B 
INNER JOIN USER_INFO A  ON  B.USER_ID = A.USER_ID
ORDER BY YEAR ASC , MONTH ASC , A.GENDER ASC

A ==> USER_INFO 테이블

B ==> ONLINE_SALE 테이블 

INNER JOIN ==> ONLINE_SALE(B)에 있는 USER_ID와 USER_INFO(A)에 있는 USER_ID가 동일한거에 대해 JOIN

교집합을 추출하는 것이다.

2.

SELECT TO_CHAR(B.SALES_DATE , 'YYYY') AS YEAR , TO_NUMBER(TO_CHAR(B.SALES_DATE , 'MM')) AS MONTH ,
A.GENDER AS GENDER  AS USERS , B.PRODUCT_ID , A.AGE , A.USER_ID , B.USER_ID
FROM ONLINE_SALE B 
INNER JOIN USER_INFO A  ON  B.USER_ID = A.USER_ID
ORDER BY YEAR ASC , MONTH ASC , A.GENDER ASC

ONLINE_SALE 테이블(B) 과 USER_INFO(A) 테이블의 USER_ID가 동일한 데이터에 대해 교집합하여 데이터 추출 

==> age에 null값 ==> A테이블에 이미 비어있던것!

==> INNER JOIN을 통해 GROUP절 사용시 A와 B 상관없이 GROUP 사용가능하다.

3.

SELECT TO_CHAR(B.SALES_DATE , 'YYYY') AS YEAR , TO_NUMBER(TO_CHAR(B.SALES_DATE , 'MM')) AS MONTH ,
A.GENDER AS GENDER , COUNT(DISTINCT(A.USER_ID)) AS USERS 
FROM ONLINE_SALE B 
INNER JOIN USER_INFO A  ON  B.USER_ID = A.USER_ID
GROUP BY  TO_CHAR(B.SALES_DATE , 'YYYY') , TO_NUMBER(TO_CHAR(B.SALES_DATE , 'MM')) , A.GENDER
HAVING A.GENDER IS NOT NULL
ORDER BY YEAR ASC , MONTH ASC , A.GENDER ASC

==> GROUP BY ==> ONLINE_SALE(B) 테이블의 SALES_DATE 의 연도별 , ONLINE_SALE(B) 테이블의 월별 , 

USER_INFO(A) 테이블의 성별 데이터를 GROUP 화 

==> HAVING 절을 통해 USER_INFO(A)테이블의 성별데이터가 NULL값이 아닌것에 대해서만 출력하기

select food_type, LPAD(REST_ID , 5 , '0') AS REST_ID , rest_name, favorites
from rest_info
where (food_type, favorites) in 
(SELECT FOOD_TYPE  , MAX(FAVORITES) AS FAVORITES  FROM REST_INFO
GROUP BY FOOD_TYPE)

ORDER BY FOOD_TYPE DESC

==> LPAD(REST_ID , 5 , '0') ==> 총 5개의 칸중에 빈공간에 0으로 채워넣기

==> WHERE 절 IN ==> 서브쿼리안의 값에 들어있는 거 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/59408

SELECT COUNT(DISTINCT(NAME)) AS COUNT FROM ANIMAL_INS

==> COUNT(*)을 하면 NULL 값도 다 COUNT 한다.

https://school.programmers.co.kr/learn/courses/30/lessons/59415

VERSION 2.0

SELECT MAX(DATETIME) AS 시간 FROM ANIMAL_INS

==> 시간에 MAX 활용

VERSION 1.0

SELECT DATETIME AS 시간 FROM
(SELECT DATETIME  FROM ANIMAL_INS
ORDER BY DATETIME DESC)
WHERE ROWNUM<2

==> FROM 절 서브쿼리 활용 , ROWNUM 활용