sql select 基础用法

1 数据集介绍

在线运行：

https://sqlzoo.net/wiki/SELECT_basics

2 练习题

2.1 知识储备

• 字符串需要使用单引号
• 使用in可以检查某一项是否位于某个列表内
• 使用between XXX and XXX可以检查某一项是否位于某个范围内

2.2 练习

show the population of Germany

select population from world where name = 'Germany';

Show the name and the population for ‘Sweden’, ‘Norway’ and ‘Denmark’.

select name,population from world where name in ('Sweden','Norway','Denmark');

show the country and the area for countries with an area between 200,000 and 250,000

select name,area from world where area between 200000 and 250000;

3 十三道和world表关联的sql语句

https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial

1

SELECT name, continent, population FROM world

2

select name from world where population > 200000000;

3

select name, gdp/population from world where population > 200000000;

4

select name,population/1000000 from world where continent = 'South America';

5

select name,population from world where name in ('France','Germany','Italy');

6

select name from world where name like '%United%';

7

select name,population,area from world where area > 3000000 or population > 250000000;

8

select name,population,area from world where area > 3000000 and population < 250000000 or area < 3000000 and population > 250000000;

9

select name,round(population/1000000,2),round(gdp/1000000000,2) from world where continent = 'South America';

10

select name,round(gdp/population,-3) from world where gdp > 1000000000000;

11

SELECT name,capital FROM world
WHERE LEN(name)=LEN(capital)

12

select name,capital from world where left(name,1) = left (capital,1) and name <> capital;

13

select name from world where name like '%a%' and name like '%e%' and name like '%i%' and name like '%o%' and name like '%u%' and name not like '% %';

4 十四道和nobel表关联的sql语句

https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

1

select * 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 yr between 1980 and 1989 and subject = 'literature'

6

select * from nobel where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama');

7

select winner from nobel where winner like 'John%';

8

select yr,subject,winner from nobel where (yr=1980 and subject='physics') or (yr=1984 and subject='chemistry')

9

select yr,subject,winner from nobel where yr=1980 and subject not in ('chemistry');

10

select yr,subject,winner from nobel where (subject='Medicine' and yr < 1910) or (subject='Literature' and yr>=2004)

11

select * from nobel where winner = 'PETER GRÜNBERG'

12

select * from nobel where winner = 'EUGENE O''NEILL'

13

select winner,yr,subject from nobel where winner like 'Sir%' order by yr desc,winner asc;

14

应该是正确答案，但是不对，不知道为啥：

SELECT winner, subject FROM nobel 
WHERE yr=1984 
ORDER BY subject IN ('Physics','Chemistry'),subject,winner