SQL Lab

TODO until next week

1. select distinct on (Country.capital) Country.capital from Country, geo_sea where geo_sea.sea ='Baltic Sea' and geo_sea.country = Country.code;
2. select name,area, coordinates from Desert x where x.coordinates is not NULL  order by  ((coordinates).latitude) DESC limit 1;
(for 2, remove the coordinates during exam).

3. 

select *
from
(
	select distinct mountains,  count(name) as no_mountains
	from Mountain
	where mountains is not NULL
	group by mountains
	having count(name) > 2
) s natural join
(
	select distinct mountains, count(distinct country) as no_countries
	from (Mountain join geo_mountain on name = mountain)
	where mountains is not NULL
	group by mountains
) t
order by no_mountains;



4.

select lakearea / desertarea as ratio, hemisphere
from
(
select *,'eastern' as hemisphere
from
(
	SELECT SUM(area) as desertarea FROM Desert
	where (coordinates).longitude >= 0 -- eastern
) e1, 
(
	SELECT SUM(area) as lakearea FROM Lake
	where (coordinates).longitude >= 0 -- eastern
) e2
union
select *,'western' as hemisphere
from
(
	SELECT SUM(area) as desertarea FROM Desert
	where (coordinates).longitude < 0 
) w1, 
(
	SELECT SUM(area) as lakearea FROM Lake
	where (coordinates).longitude < 0 
) w2

) r
order by ratio DESC
limit 1
;


5.

select name, count
from 
(
	select count(City.name), encompasses.continent as b
	from City, encompasses
	where ABS (latitude) <= 35 and
	encompasses.country = City.country
	group by encompasses.continent
) a join Continent b on a.b = b.name
where  b.area > 9000000


6. select st.name, count(distinct st.river)
from
(
	select * from Country, encompasses, geo_river
	where Country.code = encompasses.country and
	encompasses.continent = 'Europe' and
	geo_river.country = encompasses.country
	
) st
group by st.name
order by st.count DESC;

7. select st.name
from
(
	select * from encompasses, Organization
	where encompasses.continent = 'Europe' and
	Organization.country = encompasses.country
	and Organization.name LIKE '%Nuclear%'
	
) st
group by st.name;

8.
select c.name, c.population,
round (c.population * exp (p.population_growth/10) as in_ten_years,
round (c.population * exp (25*p.population_growth/100) as in_twentyfive_years,
round (c.population * exp (50*p.population_growth/100) as in_fifty_years,
round (c.population * exp (p.population_growth) as in_ten_years
from Country c join Population p on c.country = p.code;

9.
EightThousanders(name,mountains,height,coordinates)

create view EightThousanders AS
select name, mountains,height, coordinates
from Mountain
where height >= 8000;



10.


WITH RECURSIVE border(countryin) AS (
	select distinct country from (select country2::character varying(4) as country from borders where country1 = 'S'  union  select country1::character varying(4) as country from borders where country2 = 'S' ) a
	UNION 
	select distinct sp.country::varchar(4) from (select country1::varchar(4) as country, country2 as n from borders) sp join (select country2::varchar(4) as country, country1 as n from borders) st on sp.country = st.country or sp.n = st.n, border
	where 
	sp.country = border.countryin or st.country =border.countryin or
	sp.n = border.countryin or st.n = border.countryin
)
SELECT distinct countryin, name FROM border, country where countryin = code ;

//version 2: with a lot of help from: http://stackoverflow.com/questions/35232275/recursion-in-postgresql

WITH RECURSIVE border(countryin) AS (
    select distinct case when country1 = 'S' THEN country2
					     else country1 end as country
    from borders
	where country1 = 'S' or country2 = 'S'
    UNION 
    select distinct case when borders.country1 = border.countryin then borders.country2
					     else borders.country1 end as country 
    from borders join border on borders.country1 = border.countryin or borders.country2 = border.countryin
	and borders.country1 <> 'S' and borders.country2 <> 'S'
    where true              
)
SELECT distinct countryin, name 
FROM border, country 
where countryin = code ;