【SQLZOO学习打卡】Tutorial：Section B

Part 4. SELECT within SELECT Tutorial

@ World table: (name, continent, area, population, gdp)

# Using SELECT in SELECT

• The result of a SELECT statement may be used as a value in another statement. 
• put somename after the closing bracket as Alias
• Using IN to cope with multiple results
• subquery on the SELECT line: only one value will be returned 
• operators over a set ( = equals;  > greater than;  < less than;  >= greater or equal;  <= less or equal;  ALL or ANY multiple values ) 

1. SELECT as a value

SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')

2. Operator as a set 

select name from world
where continent='Europe' and
GDP/population>
(select GDP/population from world
where name='United Kingdom'
)

select name, population from world
where population >=
(select population from world
where name='Canada'
) and population <=
(select name from world
where name='Poland'
)

3. IN multiple results 

select name, continent from world
where continent in (
select continent from world 
where name='Argentina' or name= 'Australia'
)
order by name

4. CONCAT(s1, s2) : concatenation; to stick 2 or more strings together

select name, concat(round(population/(select population from world where name='Germany')*100,0),'%') percentage from world
where continent='Europe' 

5.  ALL or ANY multiple values  

select name from world
where GDP > ALL (select GDP from world where continent='Europe' and GDP>0)

6. correlated or synchronized sub-query 

A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery. One way to interpret the line in the WHERE clause that references the two table is “… where the correlated values are the same”.

SELECT continent, name, area FROM world x
  WHERE area >= ALL (SELECT area FROM world y
        WHERE y.continent=x.continent and area>0)

7. alphabetically 

select continent, name from world x
where x.name<=ALL(select y.name from world y
where x.continent=y.continent
order by name)

8. Operator 

select name, continent, population from world x
where 25000000>=ALL (select population from world y
where x.continent = y.continent)

select name, continent from world x
where x.population/3>=ALL (select population from world y 
where x.continent=y.continent and x.name!=y.name and population>0)

Part 5. SUM and COUNT

 @ World table: (name, continent, area, population, gdp)

 1. Aggregate Functions (COUNT, SUM , MAXand AVG)

An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11. 

The functions SUM, COUNT, MAX and AVG are "aggregates", each may be applied to a numeric attribute resulting in a single row being returned by the query. (These functions are even more useful when used with the GROUP BY clause.)

select sum(population) from world

select sum(GDP) from world
where continent='Africa'

select count(name) from world
where area>=1000000

select sum(population) from world
where name in ('Estonia', 'Latvia', 'Lithuania')

 2. Distinct

 By default the result of a SELECT may contain duplicate rows. We can remove these duplicates using the DISTINCT key word.

select distinct continent from world

3. Order By

ORDER BY permits us to see the result of a SELECT in any particular order. We may indicate ASC or DESC for ascending (smallest first, largest last) or descending order. 

select name, population from bbc
where population > 100000000
order by  population desc

4. Group By and Having 

By including a GROUP BY clause functions such as SUM and COUNT are applied to groups of items sharing values. When you specify GROUP BY continent the result is that you get only one row for each different value of continent. All the other columns must be "aggregated" by one of SUM, COUNT ...

The HAVING clause allows use to filter the groups which are displayed. The WHERE clause filters rows before the aggregation, the HAVING clause filters after the aggregation.

If a ORDER BY clause is included we can refer to columns by their position.

The HAVING clause is tested after the GROUP BY. You can test the aggregated values with a HAVING clause.

select distinct continent from world
group by continent
having sum(population) >=100000000

 5. WHERE and GROUP BY

The WHERE filter takes place before the aggregating function. 

select continent, count(name) from world
where population>=10000000
group by continent