SQL符合函数查询

CREATE TABLE bbc(
contryName VARCHAR(50) NOT NULL  -- 国家名称
,region VARCHAR(60)   -- 地区
,contryArea DECIMAL(10)  -- 面积
,population DECIMAL(11) -- 人口
,gdp DECIMAL(14) -- GDP
,PRIMARY KEY (contryName)        

)DEFAULT CHARSET=utf8;

SELECT * FROM bbc
-- 显示世界人口总和
SELECT SUM(population) FROM bbc


-- 列出所有的地区，每个地区只显示一次
SELECT region FROM bbc GROUP BY region;
SELECT DISTINCT region FROM bbc;


-- 显示GDP超过所有Africa国家总和的国家
SELECT * FROM bbc WHERE gdp >
(SELECT SUM(gdp) FROM bbc WHERE region = 'Africa'); 


-- 显示每个地区及该地区国家总数
SELECT region,COUNT(*) FROM bbc GROUP BY region;


-- 显示每个地区的总人口数和总面积，以总人口排序
SELECT region,SUM(population),SUM(contryArea) 
FROM bbc GROUP BY region ORDER BY SUM(population) ;


-- 显示每个地区以及该地区国家总人口不少于10000000的国家总数
SELECT region,SUM(population),COUNT(contryName) FROM bbc GROUP BY region 
HAVING SUM(population)>=10000000;


-- 列出人口总数不少于100000000的地区
SELECT region,SUM(population) FROM bbc GROUP BY region HAVING SUM(population)>=100000000;


-- 显示欧洲人口总数和总GDP
SELECT region,SUM(population),SUM(gdp) FROM bbc WHERE region = 'Europe';


-- 显示每个地区的总人口数和总面积，以总人口排序，仅显示那些面积超过1000000的地区
SELECT region,SUM(population),SUM(contryArea) FROM bbc 
GROUP BY region HAVING SUM(contryArea)>1000000 ORDER BY SUM(population) ;