/* Assume that three tables, namely country,  city and river, has been created and populated.  */

/* Oracle SQL Queries.  */
/* Query numbers refer to those in the book & slides*/

/* Query1: list all the cities and the country they belong to in the CITY table */
/* this is a project operation */ 

select city.name, city.country
from city;

/* Query2: List the names of the capital cities in the CITY table */
/* this is select operation */

select *
from city
where capital='Y';

/* Query3: List the attributes of countries in the country relation where the life-expectancy is less than 70 years */ 

select country.name, country.life_exp
from country 
where country.life_exp < 70;

/* Query4: List the capital cities and populations of countries whose gdp exceeds one trillion dollars */

/* this implicit join operation. two tables are matched on their common attributes */

select city.name, country.pop
from city, country
where city.country = country.name AND
      country.gdp > 1000.0 AND
      city.capital='Y';

/* Query5: What is the name and population of the capitakl city in the country where the St. Lawrence river originates */
/* this join among 3 tables */

select city.name , city.pop
from city, country, river
where river.origin = country.name AND
      country.name = city.country AND
      river.name = 'St. Lawrence' AND
      city.capital='Y';


/* Query6: What is the average population of the noncapital cities listed in city table? */
/* this is an aggregate operation*/

select avg(city.pop)
from city
where city.capital='N';

/* Query7: for each continent, find the average gdp */
/* introduction of group by */
/* country table is operated in 2 separate tables */

select country.cont, avg(country.gdp)
from country
group by country.cont;

/* Query8: for each country in which at least two rivers originate, find the length of the smallest river */
/* having clause is introduced. it forces selection conditions on different groups.*/

select river.origin, min(river.length)
from river
group by river.origin
having count(*) > 1;

/* Query9: list the countries whose gdp is greater than that of Canada */

select country.name
from country
where country.gdp > ANY ( select country.gdp
                          from country
                          where country.name = 'Canada');