/*
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.6.1;
PROMPT;

/*  Videos

Type I

Show which countries in central or east Asia watched 
videos that are longer than 15 minutes in Janauary 2017.

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

Sort by region, and country name.

Do this using a Type I subquery.  

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


*/

PROMPT subquery;
PROMPT;

--subquery:  Show which videos longer than 15 minutes 
--have watch data in January 2017.




PROMPT full query;
PROMPT;









PROMPT  ***********************; 
PROMPT 6.6.2;
PROMPT;
/*  
Type II subquery

Solve the above problem statement using a Type II (correlated) 
subquery.

In a Type II subquery you CANNOT show the subquery separately 
because it must run in conjunction with the outer query.

Hint 1:  Remove the Type I "IN" criterion and replace it with 
the Type II "EXISTS" criterion.  

Hint 2: In the subquery join the appropriate table's field with 
the appropriate field in the outer query.  Add this to the 
WHERE clause of the subquery.

*/






PROMPT  ***********************;  
PROMPT 6.6.3;
PROMPT;
/*  Videos

TYPE I subquery.

Show which countries have had more views of videos than Germany 
in 2017.

Show the ID, country name, region and total views (sum of views).

Sort by total views in descending order.

First, show the subquery solution and output.

Second, show the full query solution and output.

/*


PROMPT subquery;
PROMPT;



PROMPT full query;
PROMPT;