分布式数据库技术.作业1-3

作业1

针对world数据库、进行如下查询：

1. 查询Asia的所有国家名，国家代码，城市名

SELECT country.`Name` AS CountryName,country.`Code`,city.`Name` as CityName
FROM COUNTRY,city
WHERE country.`Code`=city.CountryCode AND country.Continent='Asia';

2.查询Asia中每个国家的城市数量，按查询结果降序排列

SELECT country.`Name` AS CountryName,country.`Code`,COUNT(city.`Name`) AS CityNum
FROM COUNTRY,city
WHERE country.`Code`=city.CountryCode AND country.Continent='Asia'
GROUP BY `Code`
ORDER BY CityNum DESC;

3. 查询CHN中每个District的人口总数，按降序排列

SELECT District,SUM(Population) AS DistrictPopulationSum
FROM CITY
WHERE CountryCode="CHN"
GROUP BY District
ORDER BY DistrictPopulationSum DESC;

作业2

针对 world 数据库、进行如下查询：

USE world;

1、查询每个 Continent 中 GNP 最高和最低的国家信息及其对应的 GNP 的值

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MAX(GNP) AS GNP_MAX
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.GNP=assist.GNP_MAX
AND country.Continent=assist.Continent
ORDER BY GNP DESC;

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MIN(GNP) AS GNP_MIN
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.GNP=assist.GNP_MIN
AND country.Continent=assist.Continent
ORDER BY GNP DESC;

2、查询每个 Continent 中人均 GNP 最高和最低的国家信息及其对应的人均 GNP 的值

SELECT country.*
FROM
  (SELECT GNP/Population AS PerCapitaGNP,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MAX(GNP/Population) AS PerCapitaGNP
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaGNP=assist.PerCapitaGNP
AND country.Continent=assist.Continent
ORDER BY PerCapitaGNP DESC;

SELECT country.*
FROM
  (SELECT GNP/Population AS PerCapitaGNP,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MIN(GNP/Population) AS PerCapitaGNP
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaGNP=assist.PerCapitaGNP
AND country.Continent=assist.Continent
ORDER BY PerCapitaGNP DESC;

3、查询每个 Continent 中总面积（SurfaceArea）最高和最低的国家及其对应的 SurfaceArea 的值

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MAX(SurfaceArea) AS SurfaceArea_MAX
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.SurfaceArea=assist.SurfaceArea_MAX
AND country.Continent=assist.Continent
ORDER BY SurfaceArea DESC;

SELECT country.*
FROM country
RIGHT JOIN
  (SELECT Continent,
          MIN(SurfaceArea) AS SurfaceArea_MIN
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.SurfaceArea=assist.SurfaceArea_MIN
AND country.Continent=assist.Continent
ORDER BY SurfaceArea DESC;

4、查询每个 Continent 中人均面积（SurfaceArea）最高和最低的国家及其对应的人均 SurfaceArea 的值

SELECT country.*
FROM
  (SELECT SurfaceArea/Population AS PerCapitaSurfaceArea,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MAX(SurfaceArea/Population) AS PerCapitaSurfaceArea
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaSurfaceArea=assist.PerCapitaSurfaceArea
AND country.Continent=assist.Continent
ORDER BY PerCapitaSurfaceArea DESC;

SELECT country.*
FROM
  (SELECT SurfaceArea/Population AS PerCapitaSurfaceArea,
          country.*
   FROM country) AS country
RIGHT JOIN
  (SELECT Continent,
          MIN(SurfaceArea/Population) AS PerCapitaSurfaceArea
   FROM country
   WHERE GNP!=0
     AND Population!=0
   GROUP BY Continent) AS assist ON country.PerCapitaSurfaceArea=assist.PerCapitaSurfaceArea
AND country.Continent=assist.Continent
ORDER BY PerCapitaSurfaceArea DESC;

*注意：population 为 0 不记录