사용하고 있는 프로그램은 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 배우고 있는 데 어떻게 풀어야할 지 도저히 감이 안오네요ㅜㅠ
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 ;