SQLZOO刷题记录

此文记录sqlzoo的全部刷题记录,不包括那些比较简单的题

1.SELECT names

表名: world
字段名: name continent area population gdp capital tld flag

Find the capital and the name where the capital includes the name of the country.

SELECT capital, name
FROM world
WHERE capital like concat('%',name,'%')

Find the capital and the name where the capital is an extension of name of the country.

SELECT capital, name
FROM world
WHERE captial like concat('%', name, '%') and capital <> name

Show the name and the extension where the capital is an extension of name of the country.

SELECT name, replace(capital, name, '') as extension
FROM world
WHERE capital like conat('%', name, '%') and capital <> name

2.SELECT FROM world

表结构同上

Show the name and the capital where the first letters of each match. Don’t include countries where the name and the capital are the same word.

SELECT name, capital
FROM world
WHERE LEFT(name,1) = LEFT(capital,1)
AND name <> capital

Find the country that has all the vowels and no spaces in its name.

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 '% %'

3.Select within select

表结构同上

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
注意NULL和ALL连用的坑

SELECT name 
FROM world 
WHERE gdp > all (SELECT gdp 
					FROM world 
					WHERE continent='europe' 
					AND gdp IS NOT NULL)

Find the largest country (by area) in each continent, show the continent, the name and the area
关联子查询

SELECT continent, name, area
FROM world x
WHERE area = (SELECT MAX(area) 
				FROM world y 
				WHERE x.continent = y.continent) 

or

SELECT continent, name, area
FROM world x
WHERE area >= ALL (SELECT area 
					FROM world y 
					WHERE x.continent = y.continent)

Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

SELECT name, continent, population
FROM world x
WHERE 25000000 >= ALL(SELECT population 
						FROM world y 
						WHERE x.continent = y.continent)

Some countries have

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值