/* BOOTSTRAP */

CREATE TABLE Distances
(City1 VARCHAR2(35),
 City2 VARCHAR2(35),
 Distance NUMBER);

insert into distances
select A.name, B.name,
 sqrt(power(A.latitude-B.latitude,2)+power(A.longitude-B.longitude,2)) as distance
from city A, city B


/* EXERCISES 2 */

select population from city where name='Lisbon';

select /*+full(city)*/ population from city where name='Lisbon';

create index btree_cityname on city(name);

analyze index citykey compute statistics;
analyze index btree_cityname compute statistics;
analyze table city compute statistics;

select population from city where name like 'L%';
select population from city where name like 'Li%';
select population from city where name like 'Lis%';

/* EXERCISES 3 */

/* 3.1 */ 

select country.name, organization.name
from 
country inner join is_member on (country.code = is_member.country)
inner join organization on (is_member.organization = organization.abbreviation) 
where country.name = 'Portugal';

analyze table country compute statistics;
analyze table is_member compute statistics;
analyze table organization compute statistics;
analyze index memberkey compute statistics;

select num_rows, blocks from user_tables where table_name='ORGANIZATION' or table_name='COUNTRY' OR table_name='IS_MEMBER'

/* 3.2 */ 

1 - /*Original Query*/
select country.name, organization.name 
from country inner join is_member on (country.code = is_member.country)
inner join organization on (is_member.organization = organization.abbreviation)
where country.name = 'Portugal';

2 - /* No index files and nested loop (country, organization, is_member)*/

select /*+ ordered use_nl(is_member, organization, country) no_index(is_member) no_index(country) no_index(organization) */ country.name, organization.name
from country, organization, is_member
where country.code = is_member.country and is_member.organization = organization.abbreviation
and country.name = 'Portugal';

3 - /* No index files and nested loop (is_member, country, organization)*/

select /*+ ordered use_nl(is_member organization country) no_index(is_member) no_index(country) no_index(organization) */ country.name, organization.name
from is_member, country, organization
where country.code = is_member.country and is_member.organization = organization.abbreviation
and country.name = 'Portugal';

4 - /* No index files and nested loop (country, organization, is_member)*/

select /*+ ordered use_nl(is_member organization country) no_index(is_member) no_index(country) no_index(organization) */ country.name, organization.name
from country, organization, is_member
where country.code = is_member.country and is_member.organization = organization.abbreviation
and country.name = 'Portugal';

5 - /* Index files and nested loop (country, organization, is_member) */

select /*+ ordered use_nl(is_member organization country)   no_index(organization) */ country.name, organization.name
from country, organization, is_member
where country.code = is_member.country and is_member.organization = organization.abbreviation
and country.name = 'Portugal';

/* Index files and nested loop (is_member, country, organization) */

6 - select /*+ ordered use_nl(is_member organization country) no_index(organization) */ country.name, organization.name
from is_member, country, organization
where country.code = is_member.country and is_member.organization = organization.abbreviation
and country.name = 'Portugal';

7- /* No index files and hash join (is_member, country, organization) */

select /*+  use_hash(is_member organization country) no_index(is_member) no_index(country) no_index(organization) */ country.name, organization.name
from is_member, country, organization
where country.code = is_member.country and is_member.organization = organization.abbreviation
and country.name = 'Portugal';

8 -  /* No index files and hash join (is_member, country, organization) */

select /*+ ordered use_merge(is_member organization country) no_index(is_member) no_index(country) no_index(organization) */ country.name, organization.name
from organization,is_member, country 
where country.code = is_member.country and is_member.organization = organization.abbreviation
and country.name = 'Portugal';

/* More combinations can be analysed */


/*EXERCISES 3.3 */

select city.population, city1, city2, distance
from city, distances
where city.name = distances.city1

select /*+ ordered use_nl(city distances) no_index(city)*/*
from city, distances
where city.name = distances.city1;

select /*+ ordered use_nl(city distances) no_index(city)*/*
from distances, city
where city.name = distances.city1;

select /*+ ordered use_nl(city distances)*/*
from distances, city
where city.name = distances.city1;

select /*+ordered use_merge(city, distances)*/*
from distances, city
where city.name = distances.city1;

select /*+ordered use_merge(city, distances)*/ *
from city, distances
where city.name = distances.city1;

/* More combinations can be analysed */

select /*+ ordered use_nl(city distances) no_index(city)*/*
from city, distances
where city.name = distances.city1;

select /*+ ordered use_nl(city distances) no_index(city)*/*
from distances, city
where city.name = distances.city1;

select  city.name, city2, distance
from city,distances 
where city.name = distances.city1;

/* Exercise 3.4 */

  select * from borders where length < 0
  select * from borders where length < 1
  select * from country where exists (select * from borders where country.code=borders.country1 and length > 2000)

/*EXERCISES 3.5 */

select is_member.*
from organization, is_member
where organization.abbreviation = is_member.organization

/*Criar foreign key */ /*Deixa de precisar de realizar o join */

/* Outra forma de escrever a query*/
select A.*
from is_member A where exists 
	(select * from organization B where A.organization = B.abbreviation)
	
/*EXERCISES 3.6 */

select * 
from geo_island inner join island on (geo_island.island = island.name)
where country ='P'

select /*+ no_index(geo_island) no index(island) */ * 
from island inner join geo_island on (geo_island.island = island.name)
where country ='P'  

select /*+ use_nl(island geo_island) no_index(geo_island) no_index(island) */ * 
from island inner join geo_island on (geo_island.island = island.name)
where country ='P'  

/* More combinations can be analysed */

/*EXERCISES 4 */

/*What is the total population of the various cities for each country? */

select country.name as Country, sum(city.population)
from country inner join city on (city.country = country.code)
group by country.name

select country.name as Country, sum(city.population)
from country inner join city on (city.country = country.code)
group by country.name
having sum(city.population)>10000000
order by sum(city.population) desc

/*What are the countries with the largest border?*/

select *
from country
where code IN 
  (SELECT country1
      FROM (
			select country1, sum(length)
			from borders        
			group by  country1
			order by sum(length) desc
  )WHERE rownum <= 10
)


/*What are the countries with the largest shared border?*/
 select a.name, b.name, length as FronteiraPartilhada
 from borders, country a, country b
 where a.code= borders.country1 and b.code= borders.country2
 order by FronteiraPartilhada desc 
 
/*What is the river that crosses more capitals of countries?*/

create view capitals as
select city.name as cityname, city.province as provincename, city.country as countryname 
from country inner join city on (country.capital = city.name)

create view  citiesWithRivers as
select distinct river, city, province, country  from located  where river is not null

select river, count(city) as NumberCities
from citiesWithRivers inner join capitals  on (capitals.cityname = citiesWithRivers.city and 
                                                    capitals.provincename=citiesWithRivers.province and
                                                    capitals.countryname=citiesWithRivers.country)
group by river
order by NumberCities desc

/* To check for what cities the river cross by.. */

select river, city
from citiesWithRivers inner join capitals  on (capitals.cityname = citiesWithRivers.city and 
                                                    capitals.provincename=citiesWithRivers.province and
                                                    capitals.countryname=citiesWithRivers.country)
order by river, city


/* How many persons are there for each religion? */
 select  religion.name as Religion, sum(percentage*population) as NumberPersons
 from country inner join religion on (country.code = religion.country)
 group by  religion.name
 
 /* What are the countries that don’t have a sea front, and that further don’t have a border with a country
that has sea front (to go to the beach their citizens have to cross at least one country, and proceed to a third country)?*/

create view oceans as select * from sea;

create view semOceano as 
(select code from country) minus (select distinct country from geo_sea);

create view comfronteiraOceano as
select semOceano.code
from semOceano inner join borders on (semOceano.code = borders.country1) inner join geo_sea on (borders.country2 = geo_sea.country);

create view codSemOceano as
(select * from semOceano) minus (select * from comfronteiraOceano);

select *
from codSemOceano, country
where codSemOceano.code = country.code;

select  religion.name as Religion, sum(percentage*population) as NumberPersons
from country inner join religion on (country.code = religion.country)
group by  religion.name



