sql基础语句

SELECT * FROM bbc;

#1题

SELECT NAME AS "国家名称"FROM bbc WHERE population>=200000000;

#2题

SELECT NAME "国家名称",gdp/population AS"人均GDP" FROM bbc WHERE population>=200000000;

#3题

SELECT NAME "国家名称",population/1000000 "人口数量（百万）" FROM bbc WHERE region="South Asia";

#4题

SELECT NAME "国家名称",population "人口数量" FROM bbc WHERE NAME in("France","Germary","Italy");

#5题

SELECT NAME "国家名称" FROM bbc WHERE NAME LIKE "%United%";

#6题

SELECT DISTINCT region "地区" FROM bbc;

#7题

SELECT NAME "国家名称",population "人口数量" FROM bbc WHERE population>100000000 ORDER BY population DESC;

SELECT * FROM nobel;

#1题

SELECT winner FROM nobel WHERE yr=1950;

#2题

SELECT winner FROM nobel WHERE yr=1962 AND subject="Literature";

#3题

SELECT yr,subject FROM nobel WHERE winner="Albert Einstein";

#4题

SELECT winner FROM nobel WHERE yr>=2000 AND `subject`="Peace";

#5题

SELECT * FROM nobel WHERE `subject`="Literature" AND yr BETWEEN 1980 AND 1989;

#6题

SELECT * FROM nobel WHERE winner in("Theodore Roosevelt","Woodrow Wilson","Jed Bartlet","Jimmy Carter");

#7题

SELECT winner FROM nobel WHERE winner LIKE "Join%";

#8题

SELECT DISTINCT subject FROM nobel;

SELECT * FROM bbc;

#1题

SELECT SUM(population) "世界人口总和" FROM bbc;

#2题

SELECT DISTINCT region FROM bbc;

#3题

SELECT NAME "国家名称"FROM bbc WHERE (gdp>(SELECT sum(gdp) FROM bbc WHERE region = "Africa"));

#4题

SELECT region "地区",count(*) "国家总数" FROM bbc GROUP BY region;

#5题

SELECT region "地区",SUM(population) "总人口",SUM(AREA) "总面积" FROM bbc GROUP BY region ORDER BY SUM(population) ASC;

#6题

SELECT region "地区",COUNT(*) "国家总数"FROM bbc GROUP BY region HAVING SUM(population)>=10000000;

#7题

SELECT region "地区"FROM bbc GROUP BY region HAVING SUM(population)>=100000000;

#8题

SELECT region "地区",SUM(population) "人口总数", SUM(gdp) "总GDP"FROM bbc WHERE region="Europe";

#9题

SELECT region "地区",SUM(population) "人口总数",SUM(AREA) "总面积"FROM bbc GROUP BY region HAVING SUM(AREA)>1000000 ORDER BY SUM(population);

SELECT * FROM nobel;

#1题

SELECT count(winner) "获奖总人数" FROM nobel;

#2题

SELECT `subject`,COUNT(*) "获奖总次数" FROM nobel WHERE `subject`="Physics";

#3题

SELECT subject "奖项",COUNT(*)"获奖总次数"FROM nobel GROUP BY `subject`;

#4题

SELECT subject "奖项",MIN(yr) "第一次获奖年份"FROM nobel GROUP BY `subject`;

#5题

SELECT subject "奖项",COUNT(winner) "获奖总人数"FROM nobel WHERE yr=2000 GROUP BY `subject`;

#6题

SELECT subject "奖项",COUNT(DISTINCT winner)"不同获奖者人数"FROM nobel GROUP BY `subject`;

#7题

SELECT subject "奖项",COUNT(DISTINCT yr)"获奖年数"FROM nobel GROUP BY `subject`;

#8题

SELECT subject "奖项",yr "年份",COUNT(subject) "获奖次数 "FROM nobel WHERE `subject`="Physics" GROUP BY yr HAVING COUNT(subject)=3;

#9题

SELECT winner "获奖者",COUNT(*) "获奖次数" FROM nobel GROUP BY winner HAVING COUNT(*)>1;

#10题

SELECT winner "获奖者",COUNT(DISTINCT subject) "获得奖项数量"FROM nobel GROUP BY winner HAVING COUNT(DISTINCT subject)>1;

#11题

SELECT subject "奖项",yr "年份"FROM nobel WHERE yr>=2000 GROUP BY `subject`,yr HAVING count(DISTINCT winner)=3;