Biological Databases Practical 2016 Day 2

ExerciseSolutions.sql

/* How many countries exist */
select count(*) as num_countries
from country;

/* Select the first 10 countries and order them by name */
select * 
from country
order by country.Name
limit 10;

/* How many countries exists per Contitent */
select country.Continent, count(country.Code)
from country
group by country.Continent;

/* What is the min,max,average and standard deviation surface area of all country. */
select min(country.SurfaceArea), max(country.SurfaceArea), avg(country.SurfaceArea), std(country.SurfaceArea)
from country;

/* How many languages exists per country */
select country.Name, count(countryLanguage.Language)
from country
join countryLanguage on (country.Code = countryLanguage.CountryCode)
group by country.Name;

Script.ipynb

alternatively the second query could be construed as select 10, then order. Depending on the use case, this statement could be written as

select * 
from (select * from country limit 10)
order by country.Name;