SQL学习-sqlzoo练习题记录(1)

0-SELECT 基础
1)SELECT population FROM world WHERE name='Germany'
2)SELECT name, gdp/population FROM world WHERE area > 5000000
3)SELECT name, population FROM world WHERE name IN ('Ireland', 'Iceland', 'Denmark')
注意点：IN的用法，检查是否在列表中
4)SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000
注意点：BETWEEN…AND… 包括首尾

1-SELECT name
1)SELECT name FROM world WHERE name LIKE 'Y%'
2)SELECT name FROM world WHERE name LIKE '%Y'
3)SELECT name FROM world WHERE name LIKE '%x%'
4)SELECT name FROM world WHERE name LIKE '%land'
5)SELECT name FROM world WHERE name LIKE 'C%ia'
6)SELECT name FROM world WHERE name LIKE '%oo%'
7)SELECT name FROM world WHERE name LIKE '%a%a%a%'
注意点：% 替代0个或多个字符，注意第3题和第7题
常用通配符[],[^](任何单一字符)
8)SELECT name FROM world WHERE name LIKE '_t%' ORDER BY name
9)SELECT name FROM world WHERE name LIKE '%o__o%'
10)SELECT name FROM world WHERE name LIKE '____'
注意点：_ 替代1个字符
11)SELECT name FROM world WHERE capital=name
错误点：是’=’,非’==’
12)SELECT name FROM world WHERE capital=concat(name,' City')
注意点：concat(,) ,连接作用，注意str需要加上”
13)SELECT capital,name FROM world WHERE capital like concat('%',name,'%')
错误点：通配符没有加上’ ’
14)SELECT name,capital FROM world WHERE capital like concat(name,'_%')
15)SELECT name,replace(capital,name,'') FROM world WHERE capital LIKE concat(name,'%_')
错误点：读题仔细，concat在这里记得与LIKE搭配
注意点：REPLACE(家庭,原配,小三)；MID(字段,开始位置[长度])
问题：MID()如何解决

2-SELECT FROM world
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 OR population>250000000) and (name not IN ('United States','India','China') )
注意点：多个条件用()表示
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
注意点：ROUND(,返回小数位数|可为负数)
正数是保留的小数点的位数
负数是向左进行保留，默认0
11)

SELECT name, 
CASE WHEN continent='Oceania' THEN 'Australasia' 
ELSE continent END
FROM world WHERE name LIKE 'N%'

12)

SELECT name,
CASE WHEN continent IN('Europe','Asia')
     THEN 'Eurasia'
     WHEN continent IN('North America','South America','Caribbean')
     THEN 'America'
     ELSE continent END
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'

13)

SELECT name,continent,
CASE WHEN continent IN ('Eurasia', 'Turkey')
     THEN 'Europe/Asia'
     WHEN continent = 'Oceania' 
     THEN 'Australasia'
     WHEN continent = 'Caribbean'
          THEN
          CASE 
          WHEN name LIKE 'B%' 
          THEN 'North America'
          ELSE 'South America'
          END
     ELSE continent 
     END
FROM world
ORDER BY name ASC

注意点：
简单Case函数：

CASE XXX 
WHEN 条件1 THEN True返回值
WHEN 条件2 THEN True返回值
……   
ELSE 其他条件 END
WHERE

Case搜索函数

CASE  
WHEN XXX+条件1 THEN True返回值
WHEN XXX+条件2 THEN True返回值
……   
ELSE 其他条件 END
WHERE

用于计算条件列表的表达式，并返回可能的结果之一
case后加表达式——根据表达式结果返回
case 后不加表达式——根据When条件返回
Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略