HELP ME SOLVE #1 AND 2 FOR 1 PLEASE SHOW AND EXPLAIN HOW TO DO SUBQUERY ESPECIALLY

/*

Advanced SQL

Put your SQL solutions below each problem statement. When

your solutions run without errors, run all of them via the

"run script" option in SQL Developer. Save the output to a

file.

Submit both your solution file and the output file online.

*/

show user;

select sysdate from dual;

PROMPT ***********************;

PROMPT 6.7.1;

PROMPT;

/* Videos

Type I

In 2018 show which countries have watched all the videos in the

database fundamentals series.

Show the region, country ID, name (geography) and count of

videos watched.

Sort by region, and country name.

Show the subquery code and output first then the whole query.

*/

PROMPT subquery;

PROMPT;

PROMPT full query;

PROMPT;

/*

SOLUTION OUTPUT:

subquery

PROMPT ***********************;

PROMPT 6.7.2;

PROMPT;

/*

Views

Make a view of the full query above.

Show the content of that view using SELECT * ....

Be sure to use CREATE OR REPLACE .... when creating the

view.

*/

PROMPT The view is created;

PROMPT;

PROMPT Show the view content;

PROMPT;

PROMPT ***********************;

PROMPT 6.7.3;

PROMPT;

/* Videos

Type I

Show which countries in central or east Asia watched videos that

are longer than 15 minutes in January 2018.

Show the region, country ID and name (geography).

Sort by region, and country name.

Do this using a Type I subquery.

Only show the output from the whole query.

*/

PROMPT subquery;

PROMPT;

PROMPT full query;

PROMPT;

PROMPT ***********************;

PROMPT 6.7.4;

PROMPT;

/* Videos

Show the top 5 countries in terms of total video views in 2018.

Show the top 5 ranking, the country ID, country name, region

and total views (sum of views).

Show output only from the full query. Do NOT show the subquery

separately.

Hint: Subquery in the FROM clause and the use of ROWNUM in the outer

query.

/*

VIDEOS.WATCH_DATA VIDEOS.VIDEOS WATCH DATE GEOGRAPHY ID VIDEO ID WATCH TIME MINUTES VIEWS AVERAGE_VIEW_DURATION_MINUTES NUMBER (8,2) LIKES DISLIKES WATCHID VIDEO ID VIDEO TITLE VIDEO LENGTH_MINUTES NUMBER (6,2) VIDEO CREATED SHORT TITLE MAINTOPIC VARCHAR2 (36 BYTE) VARCHAR2 (200 BYTE) DATE VARCHAR2 (5 BYTE) VARCHAR2 (35 BYTE) NUMBER (B) NUMBER (5) VARCHAR2 (20 BYTE VARCHAR2 (32 BYTE) VARCHAR2 (20 BYTE) NUMBER (5) NUMBER (5) NUMBER PK VIDEOID (MDEO_ID) ? PK VIDEOID(VIDEO-ID) - PK WATCHID (WATCH ID) UNQ_GEO VIDEO WATCH_DATE (WATCH DATE, GEOGRAPHY_ID, VIDEO ID) FK GEOID (GEOGRAPHY ID) FK VIDEOID MDEO_ID) PK WATCHID (WATCH ID) UNQ-GEO-VIDEO-WATCH-DATE(WATCH-DATE. GEOGRAPHY-ID. VIDEO-ID) VIDEOS.GEOGRAPHY GEOGRAPHY ID VARCHAR2 (5 BYTE) GEOGRAPHY REGION VARCHAR2 (80 BYTE) VARCHAR2 (25 BYTE) PK_GEOID (GEOGRAPHY_ID) FK GEO_REGION (REGION) PK GEOID (GEOGRAPHY_ID) VIDEOS.TRAFFIC_SOURCE VIDEOS.REGION VIDEO-ID GEO ID WATCH DATE TRAFFIC SOURCE VARCHAR2 (35 BYTE) VARCHAR2 (10 BYTE) VARCHAR2 (35 BYTE) VARCHAR2 (5 BYTE) DATE REGION VARCHAR2 (30 BYTE) PK REION (REGION) PK-REION (REGION) ? TRAFFIC_SOURCE_PK(TS_ID) FKTRAFFICE VIDEO (VIDEO ID) FK TRAFFIC GEO (GEO ID) TRAFFIC_SOURCE PK(TS_ID) VIDEOS.WATCH_DATA VIDEOS.VIDEOS WATCH DATE GEOGRAPHY ID VIDEO ID WATCH TIME MINUTES VIEWS AVERAGE_VIEW_DURATION_MINUTES NUMBER (8,2) LIKES DISLIKES WATCHID VIDEO ID VIDEO TITLE VIDEO LENGTH_MINUTES NUMBER (6,2) VIDEO CREATED SHORT TITLE MAINTOPIC VARCHAR2 (36 BYTE) VARCHAR2 (200 BYTE) DATE VARCHAR2 (5 BYTE) VARCHAR2 (35 BYTE) NUMBER (B) NUMBER (5) VARCHAR2 (20 BYTE VARCHAR2 (32 BYTE) VARCHAR2 (20 BYTE) NUMBER (5) NUMBER (5) NUMBER PK VIDEOID (MDEO_ID) ? PK VIDEOID(VIDEO-ID) - PK WATCHID (WATCH ID) UNQ_GEO VIDEO WATCH_DATE (WATCH DATE, GEOGRAPHY_ID, VIDEO ID) FK GEOID (GEOGRAPHY ID) FK VIDEOID MDEO_ID) PK WATCHID (WATCH ID) UNQ-GEO-VIDEO-WATCH-DATE(WATCH-DATE. GEOGRAPHY-ID. VIDEO-ID) VIDEOS.GEOGRAPHY GEOGRAPHY ID VARCHAR2 (5 BYTE) GEOGRAPHY REGION VARCHAR2 (80 BYTE) VARCHAR2 (25 BYTE) PK_GEOID (GEOGRAPHY_ID) FK GEO_REGION (REGION) PK GEOID (GEOGRAPHY_ID) VIDEOS.TRAFFIC_SOURCE VIDEOS.REGION VIDEO-ID GEO ID WATCH DATE TRAFFIC SOURCE VARCHAR2 (35 BYTE) VARCHAR2 (10 BYTE) VARCHAR2 (35 BYTE) VARCHAR2 (5 BYTE) DATE REGION VARCHAR2 (30 BYTE) PK REION (REGION) PK-REION (REGION) ? TRAFFIC_SOURCE_PK(TS_ID) FKTRAFFICE VIDEO (VIDEO ID) FK TRAFFIC GEO (GEO ID) TRAFFIC_SOURCE PK(TS_ID)