mysql复杂查询练习题_MySQL之复杂查询-理解与练习

一、复杂查询理解

1、整理如图：

2、各种常用函数

二、练习

SQLZOO练习题：SELECT within SELECT

(1)列出每個國家的名字name，當中人口population是高於俄羅斯'Russia'的人口。

SELECT name FROM world

WHERE population >

(SELECT population FROM world

WHERE name='Russia');

(2)列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。

SELECT name FROM world

WHERE continent = 'Europe' AND gdp/population >

(SELECT gdp/population FROM world

WHERE name = 'United Kingdom');

(3)在阿根廷Argentina及 澳大利亞Australia所在的洲份中，列出當中的國家名字name及洲分continent。按國字名字順序排序

SELECT name,continent FROM world

WHERE continent in

(SELECT continent FROM world

WHERE name in ('Argentina','Australia')

group by continent)

ORDER BY name;

或者

SELECT name,continent FROM world

WHERE continent in

(SELECT continent FROM world

WHERE name = 'Argentina' OR name = 'Australia')

ORDER BY name;

(4)哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。

SELECT name,population FROM world

WHERE population >

(SELECT population FROM world

WHERE name = 'Canada')

AND population <

(SELECT population FROM world

WHERE name = 'Poland');

或者

SELECT name,population FROM world

WHERE population between

(SELECT population FROM world

WHERE name = 'Canada') +1 AND

(SELECT population FROM world

WHERE name = 'Poland')-1;

(5)顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

SELECT name,CONCAT(ROUND(100*population/

(SELECT population FROM world

WHERE name = 'Germany'),0),'%') as population

FROM world

WHERE continent = 'Europe';

(6)哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出name。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

SELECT name FROM world

WHERE gdp > ALL (

SELECT gdp FROM world

WHERE continent = 'Europe'

AND gdp > 0)

(7)在每一個州中找出最大面積的國家，列出洲份continent, 國家名字name及面積area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area FROM world as x

WHERE area >= ALL

(SELECT area FROM world as y

WHERE y.continent=x.continent

AND area>0);

或者

SELECT continent, name, area FROM world as x

WHERE area =

(SELECT max(area) FROM world as y

WHERE y.continent=x.continent

AND area>0);

(8)列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

SELECT continent, name FROM world as x

WHERE name =

(SELECT name FROM world as y

WHERE y.continent=x.continent

ORDER BY name

Limit 1);

或者

SELECT continent, name FROM world as x

WHERE name <= all

(SELECT name FROM world as y

WHERE y.continent=x.continent);

(9)找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent洲份和population人口。

SELECT name,continent,population FROM world

WHERE continent in (

SELECT continent FROM world as x

WHERE

(SELECT max(population) FROM world as y

WHERE x.continent = y.continent) < 25000000

AND population>0 );

或者

SELECT name,continent,population FROM world as x

WHERE 25000000 >= all

(SELECT population FROM world as y

WHERE x.continent = y.continent);

(10)有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

SELECT name,continent FROM world as x

WHERE population > all

(SELECT 3*population FROM world as y

WHERE x.continent = y.continent