oraclf 复杂查询练习_【二】Sqlzoo 练习 复杂查询

1.汇总分析；

2.对数据进行分组；

3.对分组结果指定条件；

4.对查询结果进行排序；

• 子查询、标量子查询、关联子查询三者的关系：

SQLZOO 部分练习题

标量子查询练习：

1. Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

SELECT name FROM world
WHERE continent= 'Europe' 
AND GDP/population > (SELECT GDP/population FROM world WHERE name ='united kingdom')

用子查询查询某一个值，用于比较。

2.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

SELECT name, 
concat(round(population/(select population from world where name='germany')*100),'%')as percentage
from world
where continent= 'europe'

创建一个合并字段concat（列名1，'符号'，列名2）→输出：列名1符号列名2

Round 函数不写小数位数代表取整

子查询练习：

3.Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

SELECT name FROM world
WHERE gdp >ALL (SELECT gdp FROM world WHERE continent='europe' AND gdp>0)

ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。

关联子查询练习：

4.List each continent and the name of the country that comes first alphabetically.

错误：这样只有第一个国家
select distinct x.continent,y.name
from world x join (select name from world order by name
                limit 1) as y
on x.continent=y.continent
正确：
SELECT continent,name
FROM world AS x
WHERE x.name = (SELECT y.name FROM world AS y  
                
WHERE x.continent=y.continent
                
ORDER BY name
limit 1)

当只有部分信息需要分组，不能用自连接，只有name要排名，continent不需要，所以无法用自连接。

5.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population

SELECT name,continent,population 
FROM world AS x
WHERE 25000000>all(SELECT population FROM world AS y
 
WHERE x.continent=y.continent
and population>0)

6. Somecountries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

SELECT name,continent FROM world a
WHERE population/3>all(SELECT population FROM world b
WHERE a.continent=b.continent AND a.name <> b.name AND population>0)

注意

聚合函数 Sum count avg distinct

count（）：如果指定列名count（列名）则会忽略空值行，如果count（*）则不会忽略。

不能只写count（）空白是error

List the continents that have a total population of at least 100 million.

SELECT continent
FROM world
GROUP BY continent
HAVING sum(population)>=100000000 ----过滤分组结果