Joins....3

3a. Show the games and color of the medal won by the team that includes 'Yan Sen'.
=> select games, color from ttmdjoin team on ttmd.team = team.idwhere name = 'yan sen'

3b. Show the 'gold' medal winners in 2004.
=> select name from ttmd join team on ttmd.team = team.id where color = 'gold' and games = '2004'

3c. Show the name of each medal winner country 'FRA'.
=> select all name from ttmdjoin team on ttmd.team = team.idwhere country='FRA'

Joins....2

2a. Show who won medals in the 'Barcelona' games
SELECT who FROM ttws
JOIN games ON (ttws.games=games.yr)
WHERE city = 'Barcelona'

2b. Show which city 'Jing Chen' won medals. Show the city and the medal color.
select city, color from
ttws join games on ttws.games = games.yr
where who = 'jing chen'


2c. Show who won the gold medal and the city.
select who,city from
ttws join games on ttws.games = games.yr
where color = 'gold'

Joins....

1a. Show the athelete (who) and the country name for medal winners in 2000.
SELECT who, country.name FROM ttms JOIN country ON (ttms.country=country.id) WHERE games = 2000


1b. Show the who and the color of the medal for the medal winners from 'Sweden'.
SELECT who,color from ttms join country on ttms.country = country.idwhere name = 'sweden'


1c. Show the years in which 'China' won a 'gold' medal.
select games,country from ttms join country on ttms.country = country.idwhere name = 'china' andcolor = 'gold'order by games desc

SELECT within SELECT

1a. List each country name where the population is larger than 'Russia'.
SELECT name FROM bbc WHERE population> (SELECT population FROM bbc WHERE name='Russia')

1b. List the name and region of countries in the regions containing 'India', 'Iran'.
select name ,region from bbc where region in (SELECT region from bbc where name in ('India','Iran'))

1c. Show the European countries with a per capita GDP greater than 'United Kingdom'.
select name from bbc where region = (select region from bbc where name = 'United Kingdom') and gdp/population > (Select gdp/population from bbc where name = 'United Kingdom')

1d. Which country has a population that is more than Canada but less than Algeria?
select name from bbc where population > (Select population from bbc where name = 'canada') and population <(select population from bbc where name = 'algeria')

2a. Which countries have a GDP greater than any European country? [Give the name only.]
select name from bbc where gdp> (select max(gdp) from bbc where region = 'europe')

3a. Find the largest country in each region:
SELECT region, name, population FROM bbc x
WHERE population >= ALL
(SELECT population FROM bbc y
WHERE y.region=x.region
AND population>0)

BBC Country Profile: Aggregate functions

1a. Show the total population of the world.
Select sum(population) from bbc;

1b. List all the regions - just once each.
Select distinct(region) from bbc;

1c. Give the total GDP of Africa
Select sum(gdp) from bbc
where region = 'Africa';

1d. How many countries have an area of at least 1000000
select count(region) from bbc where area >= 1000000;

1e. What is the total population of ('France','Germany','Spain')
select sum(population) from bbc
where name in('France','Germany','Spain');

GROUP BY

In a GROUP BY statement only distinct values are shown for the column in the GROUP BY.
This example shows the continents hosting the Olympics with the count of the number of games held.

SELECT continent, COUNT(yr) FROM games
GROUP BY continent;

BBC table

name	region	area	population	gdp
Afghanistan	Asia	652225	26000000
Albania	Europe	28728	3200000	6656000000
Algeria	Middle East	2400000	32900000	75012000000
Andorra	Europe	468	64000
Angola	Africa	1250000	14500000	14935000000
Antigua and Barbuda	Americas	442	77000	770000000
Argentina	South America	2800000	39300000	146196000000
Armenia	Europe	29743	3000000	3360000000
Australia	Australasia	7700000	20300000	546070000000
Austria	Europe	83871	8100000	261630000000
Azerbaijan	Europe	86600	8500000
Bahamas	Americas	13939	321000	4789320000
Bahrain	Middle East	717	754000	9357140000
Bangladesh	Asia	143998	152600000	67144000000
Barbados	Americas	430	272000	2518720000
Belarus	Europe	207595	9800000	20776000000
Belgium	Europe	30528	10300000	319609000000
Belize	Americas	22965	266000
Benin	Africa	112622	7100000	3763000000
Bhutan	Asia	38364	2400000	1824000000
Bolivia	Americas	1100000	9100000
Bosnia-Hercegovina	Europe	51129	4200000	8568000000
Botswana	Africa	581730	1800000	7812000000
Brazil	South America	8550000	182800000	564852000000
Brunei	Asia	5765	374000
Bulgaria	Europe	110994	7800000	21372000000
Burkina Faso	Africa	274200	13800000	4968000000
Burma	Asia	676552	50700000
Burundi	Africa	27816	7300000
Cambodia	Asia	181035	14800000	4736000000
Cameroon	Africa	465458	16600000	13280000000
Canada	North America	9900000	32000000	908480000000
Cape Verde	Africa	4033	482000	853140000
Central African Republic	Africa	622984	3900000
Chad	Africa	1280000	9100000	2366000000
Chile	South America	756096	16200000	79542000000
China	Asia	9600000	1300000000	1677000000000
Colombia	Americas	1140000	45600000
Comoros	Africa	1862	812000
Costa Rica	Americas	51100	4300000
Croatia	Europe	56594	4400000	28996000000
Cuba	Americas	110860	11300000
Cyprus	Europe	9250	807000	14187060000
Czech Republic	Europe	78866	10200000	93330000000
Democratic Republic of Congo	Africa	2340000	56000000	6720000000
Denmark	Europe	43098	5400000	219510000000
Djibouti	Africa	23200	721000
Dominica	Americas	751	71000	259150000
Dominican Republic	Americas	48072	9000000
East Timor	Asia	14609	857000
Results truncated...