05.3 查询练习1

-- 1. 导入world数据库
-- world.sql
 
-- 查询所有城市的数量
SELECT COUNT(city.ID) FROM city;
 
-- 查询所有国家的数量
SELECT COUNT(country.Code) FROM country;
 
-- 查询所有语言的数量
SELECT COUNT(countrylanguage.Language) FROM countrylanguage;
SELECT COUNT(countrylanguage.CountryCode) FROM countrylanguage;
 
-- 查询China的详细信息(country表)
SELECT * FROM country WHERE country.name = 'China';
 
-- 查询属于China的城市信息
SELECT * FROM city WHERE city.CountryCode = (SELECT country.Code FROM country WHERE country.Name = 'China');
 
-- * 查询各大洲的国家数量
SELECT COUNT(country.Code) FROM country WHERE Continent = 'Africa';
SELECT COUNT(country.Code) FROM country WHERE Continent = 'Antarctica';
SELECT COUNT(country.Code) FROM country WHERE Continent = 'Asia';
SELECT COUNT(country.Code) FROM country WHERE Continent = 'Europe';
SELECT COUNT(country.Code) FROM country WHERE Continent = 'North America';
SELECT COUNT(country.Code) FROM country WHERE Continent = 'Oceania';
SELECT COUNT(country.Code) FROM country WHERE Continent = 'South America';
 
-- * 查询所有欧洲国家(Code 名字 首都 领导人)
SELECT country.Code,country.Name AS '名字',city.Name AS '首都',HeadOfState AS '领导人' FROM country,city WHERE Continent = 'Europe' AND country.Capital = city.ID;
 
-- 查询所有使用Spanish语言的国家及在此国家使用的比率
SELECT country.Name AS '国家',countrylanguage.Percentage AS 'Spanish使用比率' FROM country,countrylanguage WHERE country.Code IN (SELECT CountryCode FROM countrylanguage WHERE countrylanguage.Language = 'Spanish');