KJY.SQL

-- 회원정보 수정
UPDATE MEMBER SET 
MEMBER_NM = ?,
PROFILE_IMG = ?,
PROFILE_BACK_IMG = ? 
WHERE MEMBER_NO = ? ;

-- 회원정보 수정(테스트)
UPDATE MEMBER SET 
MEMBER_NM = '김담2'
WHERE MEMBER_NO = 23 ;


-- 회원 비밀번호 변경
UPDATE MEMBER SET
MEMBER_PW = 바꿀비번
WHERE MEMBER_NO = ?
AND MEMBER_PW = 현재비번 ;



-- 회원 비밀번호 변경(테스트)
UPDATE MEMBER SET
MEMBER_PW = 'qwer1234'
WHERE MEMBER_NO = '23'
AND MEMBER_PW = 'asdf1234' ;



-- profile_my 에서 쓸 것
-- 회원 넘버가 00일 때,
--	1)평가한 영화 정보 .. 를 가지고 영화(이름, 이미지, 년도, 국가 ) ( 평균별점 걍 빼자 )
SELECT MOVIE_NO
FROM EVALUATION
WHERE MEMBER_NO = 42;

SELECT MOVIE_TITLE, POSTER_IMG, RELEASE_YEAR, COUNTRY
FROM MOVIE
WHERE MOVIE_NO = 1;

SELECT MOVIE_NO, MOVIE_TITLE, POSTER_IMG, RELEASE_YEAR, COUNTRY
FROM EVALUATION
LEFT JOIN MOVIE USING(MOVIE_NO)
WHERE MEMBER_NO= 23
AND EVALUATION_FL = 'Y';

-- 4개만 조회  
SELECT * FROM ( 
    SELECT ROWNUM RNUM, A.* FROM(

        SELECT MOVIE_NO, MOVIE_TITLE, POSTER_IMG, RELEASE_YEAR, COUNTRY
        FROM EVALUATION
        JOIN MOVIE USING(MOVIE_NO)
        WHERE MEMBER_NO= 122
        AND EVALUATION_FL = 'Y'

    ) A
)
WHERE RNUM BETWEEN 1 AND 4 ;




SELECT * FROM ( 
SELECT ROWNUM RNUM, A.* FROM(

    SELECT MOVIE_NO, MOVIE_TITLE, POSTER_IMG, RELEASE_YEAR, COUNTRY, EVALUATION_FL
    FROM EVALUATION
    JOIN MOVIE USING(MOVIE_NO)
    WHERE MEMBER_NO= 122
    -- AND EVALUATION_FL = 'Y' 
    ) A
)
WHERE RNUM BETWEEN 1 AND 4 ;

SELECT * FROM EVALUATION ;



-- 프로필 페이지에 유저정보 끌고오기 위해서 사용.
-- 6) 팔로워수 / 팔로잉 수 / 회원이름 / 회원번호 / 프로필이미지 조회
SELECT MEMBER_NO, MEMBER_NM, PROFILE_IMG, PROFILE_BACK_IMG 
FROM MEMBER
WHERE MEMBER_NO = 42;
-- 성공 

-- 팔로잉 수
SELECT COUNT(TARGET_NO) FOLLOING_COUNT
FROM FOLLOW
WHERE MEMBER_NO = 42;

SELECT COUNT(MEMBER_NO)
FROM MEMBER
WHERE MEMBER_NO IN (SELECT TARGET_NO
               FROM FOLLOW
               WHERE MEMBER_NO = 23
               AND FOLLOW_FL='N')
ORDER BY MEMBER_NO;
--성공 


-- 나의 팔로워 조회
SELECT COUNT(MEMBER_NO) FOLLOWER_COUNT
FROM FOLLOW
WHERE TARGET_NO = 42
AND FOLLOW_FL='N';



SELECT COUNT(MEMBER_NO)
FROM MEMBER
WHERE MEMBER_NO IN (SELECT MEMBER_NO
               FROM FOLLOW
               WHERE TARGET_NO = 42
               AND FOLLOW_FL='N')
ORDER BY MEMBER_NO;
--성공 


-- 나의 팔로워 조회 
SELECT MEMBER_NO
FROM MEMBER
LEFT JOIN (SELECT * FROM EVALUATION WHERE EVALUATION_FL = 'N') USING(MEMBER_NO)
WHERE MEMBER_NO IN (SELECT MEMBER_NO
                    FROM FOLLOW
                    WHERE TARGET_NO = 62
                    AND FOLLOW_FL='N')
GROUP BY MEMBER_NO;


--  2) 찜한 영화 정보
-- 찜한영화 아직 없어서 패쓰~!

SELECT * FROM ( 
SELECT ROWNUM RNUM, A.* FROM(

    SELECT MOVIE_NO, MOVIE_TITLE, POSTER_IMG, RELEASE_YEAR, COUNTRY
    FROM MOVIE
    JOIN MOVIE_LIKE USING(MOVIE_NO)
    WHERE MEMBER_NO= 122
    -- AND EVALUATION_FL = 'Y' 
    ) A
)
WHERE RNUM BETWEEN 1 AND 4 ;


    SELECT MOVIE_NO, MOVIE_TITLE, POSTER_IMG, RELEASE_YEAR, COUNTRY
    FROM MOVIE_LIKE
    JOIN MOVIE USING(MOVIE_NO)
    WHERE MEMBER_NO= 42
    AND ROWNUM <=4;
--이걸루

    SELECT MOVIE_NO, MOVIE_TITLE, POSTER_IMG, RELEASE_YEAR, COUNTRY, 
            (SELECT NVL(COUNT(*),0) FROM MOVIE_LIKE where member_no = 122 ) LIKE_COUNT
    FROM MOVIE
    left JOIN MOVIE_LIKE USING(MOVIE_NO)
    WHERE MEMBER_NO= 122
    AND ROWNUM <=4;


SELECT COUNT(MEMBER_NO)
FROM MEMBER
WHERE MEMBER_NO IN (SELECT TARGET_NO
               FROM FOLLOW
               WHERE MEMBER_NO = 23
               AND FOLLOW_FL='N')
ORDER BY MEMBER_NO;



-- 찜한 영화의 갯수 
     SELECT COUNT(*)
     FROM MOVIE_LIKE
     WHERE MEMBER_NO = 122;


--  3) 취향분석  - ANALYSIS, PROFILEMY  사용될것임.

-- 1. 내가 평가한 영화의 모든 갯수 ( 프로필 오른쪽에 사용할 것임 )
-- 회원 번호 00일 때,
-- 별점을 몇개 사용했는가?
-- 결과 : 10 
SELECT COUNT(STAR_RATING)
FROM EVALUATION
WHERE MEMBER_NO = 122;

-- 2. 평가한 점수의 각각의 갯수 ( 0.5점은 3개, 1점은 2개 .. )
-- 그래프에 사용될 SQL
-- 회원 번호가 42 일 때, 1.5 점의 갯수 구하기.
-- * 각 점수대 별로 VO만들어야할듯..?
SELECT STAR_RATING, COUNT(*)
FROM EVALUATION
WHERE MEMBER_NO = 42
GROUP BY STAR_RATING;

SELECT STAR_RATING, COUNT(*)
FROM EVALUATION
WHERE MEMBER_NO = ?
GROUP BY STAR_RATING;



-- 가장 선호하는 국가에 쓸 sql
-- 3. 내가 가장 많은 평가를 한
-- 영화의 나라 는?
-- 1) 내가 평가한 무비의 넘버와 나라
SELECT MOVIE_NO, COUNTRY
FROM EVALUATION
JOIN MOVIE USING (MOVIE_NO)
WHERE MEMBER_NO = 42;


-- 1-1) 여기서 가장 많은 나라는?
SELECT MAX(COUNTRY)
FROM EVALUATION
JOIN MOVIE USING (MOVIE_NO)
WHERE MEMBER_NO = 84;
-- 성공!


-- 가장 선호하는 국가에 쓸 sql
-- 1-2) 여기서 가장 많은 나라를 조회 후
--      해당 국가 영화의 갯수(컨트리 카운트 하면될듯?)
SELECT COUNT(COUNTRY)
FROM EVALUATION
JOIN MOVIE USING (MOVIE_NO)
WHERE MEMBER_NO = 84;
-- 성공!

-- 가장 선호하는 국가에 쓸 sql
-- 1-3) 여기서 가장 많은 나라를 조회 후
--      해당 국가 영화의 갯수(컨트리 카운트 하면될듯?)
SELECT AVG(STAR_RATING)
FROM EVALUATION
WHERE MOVIE_NO = 2;
                

SELECT AVG(STAR_RATING)
FROM EVALUATION
JOIN MOVIE USING(MOVIE_NO)
WHERE COUNTRY = '미국';


SELECT AVG(STAR_RATING)
FROM EVALUATION
JOIN MOVIE USING(MOVIE_NO)
WHERE COUNTRY = (SELECT MAX(COUNTRY)
                FROM EVALUATION
                JOIN MOVIE USING (MOVIE_NO)
                WHERE MEMBER_NO = 84);
-- 성공!           




-- 4. 내가 평가한 모든 영화의 모든 RUNNING_TIME
SELECT FLOOR(( SUM( SUBSTR(RUNNING_TIME, 0, INSTR(RUNNING_TIME, '시간')-1) ) * 60 +
      SUM(  SUBSTR(RUNNING_TIME, INSTR(RUNNING_TIME, ' ')+1 , INSTR(RUNNING_TIME, '분')-1-INSTR(RUNNING_TIME, ' ')) ) ) / 60)
    || '시간 '
    || MOD(( SUM( SUBSTR(RUNNING_TIME, 0, INSTR(RUNNING_TIME, '시간')-1) ) * 60 +
      SUM(  SUBSTR(RUNNING_TIME, INSTR(RUNNING_TIME, ' ')+1 , INSTR(RUNNING_TIME, '분')-1-INSTR(RUNNING_TIME, ' ')) ) ) , 60)
    || '분'  AS ALL_TIME
FROM MOVIE
JOIN EVALUATION USING (MOVIE_NO)
WHERE MEMBER_NO = 84;


-- 5. 내가 평가한 모든 평점의 평균
SELECT AVG(STAR_RATING)
FROM EVALUATION
WHERE MEMBER_NO = 84;


-- 6. 회원정보 조회
SELECT MEMBER_NO, MEMBER_NM;






-- 전체 영화 조회
SELECT 
    MOVIE_NO, MOVIE_TITLE,
    POSTER_IMG, RELEASE_YEAR,
    COUNTRY, TICKETING, 
    AUDIENCE, STAR_RATING
FROM MOVIE
JOIN (SELECT MOVIE_NO, ROUND(AVG(STAR_RATING),1) STAR_RATING
        FROM EVALUATION
        WHERE EVALUATION_FL ='N'
        GROUP BY MOVIE_NO) USING (MOVIE_NO);






-- 전체영화 조회 DAO
-- 가나다
SELECT 
      MOVIE_NO,
      MOVIE_TITLE,
      POSTER_IMG,
      RELEASE_YEAR,
      COUNTRY,
      TICKETING, 
      AUDIENCE,
      STAR_RATING
FROM MOVIE
JOIN (SELECT MOVIE_NO, ROUND(AVG(STAR_RATING),1) STAR_RATING
         FROM EVALUATION
         WHERE EVALUATION_FL ='N'
         GROUP BY MOVIE_NO) USING (MOVIE_NO)
ORDER BY MOVIE_TITLE;

-- 인기순 
SELECT 
      MOVIE_NO,
      MOVIE_TITLE,
      POSTER_IMG,
      RELEASE_YEAR,
      COUNTRY,
      TICKETING, 
      AUDIENCE,
      STAR_RATING
FROM MOVIE
JOIN (SELECT MOVIE_NO, ROUND(AVG(STAR_RATING),1) STAR_RATING
         FROM EVALUATION
         WHERE EVALUATION_FL ='N'
         GROUP BY MOVIE_NO) USING (MOVIE_NO)
ORDER BY  STAR_RATING DESC;

-- 년도순 
SELECT 
      MOVIE_NO,
      MOVIE_TITLE,
      POSTER_IMG,
      RELEASE_YEAR,
      COUNTRY,
      TICKETING, 
      AUDIENCE,
      STAR_RATING
FROM MOVIE
JOIN (SELECT MOVIE_NO, ROUND(AVG(STAR_RATING),1) STAR_RATING
         FROM EVALUATION
         WHERE EVALUATION_FL ='N'
         GROUP BY MOVIE_NO) USING (MOVIE_NO)
ORDER BY  RELEASE_YEAR DESC;










-- 실패 
-- -- 2) 특정 영화 번호에서 나라 조회
-- SELECT COUNTRY
-- FROM MOVIE
-- JOIN EVALUATION USING (MOVIE_NO)
-- WHERE MEMBER_NO = 42
-- AND MOVIE_NO = (SELECT MOVIE_NO
--                 FROM EVALUATION
--                 WHERE MEMBER_NO = 42);



-- -- 악악 이거아님!!
-- -- 2) 평가한 점수의 각각의 갯수 ( 0.5점은 3개, 1점은 2개 .. )
-- SELECT COUNT(STAR_RATING),MOVIE_NO
-- FROM EVALUATION
-- WHERE MEMBER_NO = 42
-- GROUP BY MOVIE_NO ;

-- SELECT MOVIE_NO, COUNTRY
-- FROM EVALUATION
-- JOIN EVALUATION USING (MOVIE_NO)
-- WHERE STAR_RATING = (SELECT COUNT(MAX(STAR_RATING)) AS HIGHT_COUNT
--         FROM EVALUATION
--         WHERE MEMBER_NO = 42
--         GROUP BY STAR_RATING );


-- SELECT DEPT_CODE, FLOOR(AVG(SALARY))
-- FROM EMPLOYEE
-- WHERE DEPT_CODE IN ('D5', 'D6')