Oracle SQL developer query 질문좀 올리겠씁니다. 0 2 500

by 머루원해 [SQL Developer] [2019.02.26 08:51:44]


사용하고 있는 프로그램은 oracle sql developer (oracle 11g) 입니다.

Table 정보:

CREATE TABLE actor(
id INTEGER NOT NULL,
name VARCHAR2(100),
CONSTRAINT actor_PK PRIMARY KEY (id));


CREATE TABLE movie(
id INTEGER NOT NULL,
title VARCHAR2(100),
yr NUMBER(4,0),
score NUMBER,
votes INTEGER,
director VARCHAR2(100),
CONSTRAINT movie_PK PRIMARY KEY (id));


CREATE TABLE casting(
movie_id INTEGER NOT NULL,
actor_id INTEGER NOT NULL,
ord INTEGER,
CONSTRAINT casting_PK PRIMARY KEY (movie_id, actor_id),
CONSTRAINT casting_FK1 FOREIGN KEY (movie_id) REFERENCES movie(id),
CONSTRAINT casting_FK2 FOREIGN KEY (actor_id) REFERENCES actor(id));
 

query 문제:

Find the top 10 most popular words used as the first word in a movie title. Exclude ‘A’, ‘An’, ‘The’ and ‘I’. List the words by their number of appearances from high to low. You may ignore movie titles with only one word.

영화 제목에서 가장 인기 있는 단어 10개를 찾아보세요. ('A', 'A', 'The', 'I'는 제외.) 높은 순에서 낮은 순으로 단어 출현 수를 기준으로 단어를 나열하십시오. 한 단어로 된 영화 제목을 무시할 수 있습니다.

advanced sql 배우고 있는 데 어떻게 풀어야할 지 도저히 감이 안오네요ㅜㅠ

by 마농 [2019.02.26 13:19:36]
WITH movie AS
(
SELECT 'Roman Holiday' title FROM dual
UNION ALL SELECT 'Batman' FROM dual
UNION ALL SELECT 'Gone With The Wind' FROM dual
UNION ALL SELECT 'When Harry Met Sally' FROM dual
UNION ALL SELECT 'Harry Potter And The Goblet Of Fire' FROM dual
UNION ALL SELECT 'Harry Potter And The Sorcerer''s Stone' FROM dual
UNION ALL SELECT 'Harry Potter And The Half-Blood Prince' FROM dual
UNION ALL SELECT 'Harry Potter And The Chamber Of Secrets' FROM dual
UNION ALL SELECT 'Harry Potter And The Prisoner Of Azkaban' FROM dual
UNION ALL SELECT 'Harry Potter And The Order Of The Phoenix' FROM dual
UNION ALL SELECT 'Harry Potter And The Deathly Hallows: Part 2' FROM dual
UNION ALL SELECT 'Harry Potter And The Deathly Hallows: Part 1' FROM dual 
)
SELECT *
  FROM (SELECT word
             , COUNT(*) cnt
             , RANK() OVER(ORDER BY COUNT(*) DESC) rk
             , ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC, word) rn
          FROM (SELECT REGEXP_SUBSTR(title, '[^ ]+', 1, lv) word
                  FROM movie
                     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 20)
                 WHERE lv <= REGEXP_COUNT(title, '[^ ]+')
                   AND title LIKE '% %'
                )
         WHERE word NOT IN ('A', 'An', 'The', 'I')
         GROUP BY word
        )
 WHERE rn <= 10
;

 


by 머루원해 [2019.02.27 02:25:48]

감사합니다 !

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입