Functions

ABS Function:
ABS returns the absolute value. The output is positive even if the input is negative:

  ABS(x) = x if x>=0
 ABS(x) = -x if x<0 data-blogger-escaped-pre="pre">    ABS can be useful for testing values that are "close". For example   this query shows each country that has area that is roughly   70 thousand. The value 70000 is the target value, 500 is the "tolerance"   so the test ABS(area-70000)<500 data-blogger-escaped-69500="69500" data-blogger-escaped-70000-500="70000-500" data-blogger-escaped-70000="70000" data-blogger-escaped-and="and" data-blogger-escaped-area="area" data-blogger-escaped-between="between" data-blogger-escaped-br="br" data-blogger-escaped-is="is" data-blogger-escaped-tests="tests" data-blogger-escaped-that="that" data-blogger-escaped-the="the">
SELECT name, area FROM bbc
 WHERE ABS(area-70000)<500 data-blogger-escaped-br="br">Result:

name
 
area
 
 
Georgia
 
69700
 
 
Ireland
 
70182


CASE
CASE allows you to return different values under different conditions.      

If there no conditions match (and there is not ELSE) then NULL is      returned.

  CASE WHEN condition1 THEN value1
      WHEN condition2 THEN value2 
      ELSE def_value
 END

SELECT name, population
     ,CASE WHEN population<1000000 data-blogger-escaped-br="br" data-blogger-escaped-small="small" data-blogger-escaped-then="then">           WHEN population<10000000 data-blogger-escaped-br="br" data-blogger-escaped-medium="medium" data-blogger-escaped-then="then">           ELSE 'large'
      END
AS result

 FROM bbc where population like '3%' order by population asc ;

this query will list name, population and the result of CASE. It will consider only population
starting with number 3 and will order the results by ascending order of population.

Result:


name
 
population
 
result
 
 
Monaco
 
32000
 
small
 
 
Liechtenstein
 
34000
 
small
 
 
Bahamas
 
321000
 
small
 
 
The Maldives
 
338000
 
small
 
 
Brunei
 
374000
 
small
 
 
Malta
 
397000
 
small
 
 
Armenia
 
3000000
 
medium



 

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;