mysql刷题问题集（不定期更新中）

1.找出所有首都和其國家名字,而首都要有國家名字中出現
select capital,name
from world
where capital like concat(’%’,name,’%’ )

2.找出所有首都和其國家名字,而首都是國家名字的延伸。
你應顯示 Mexico City,因它比其國家名字 Mexico 長。
你不應顯示 Luxembourg,因它的首都和國家名相是相同的。
关于()的使用，可以优先运算；!=和<>都是不等号
(1) select name,capital from world
where capital like concat (name,’%’) and (capital!= name)
(2) SELECT name, capital
FROM world
WHERE (capital LIKE concat(name, ‘%’)) AND (capital <> name)

3.顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
你可以使用SQL函數 REPLACE 或 MID。as 可以重新命名
replace(f, s1, s2) 指的是把f中的s1全部用s2替换
SELECT name, REPLACE(capital, name, ‘’) as ‘yanshen’
FROM world
WHERE capital LIKE concat(name, ‘%’) AND (capital <> name)
or
SELECT name, REPLACE(capital, name, ‘’) as ‘yanshen’
FROM world
WHERE capital LIKE concat(name, ‘_%’)

4.美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。
SELECT name, population, area
FROM world
WHERE (area > 3000000 AND population < 250000000) OR(area < 3000000 AND population > 250000000)

5.1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。,對於南美顯示以百萬計人口，以十億計2位小數GDP。
ROUND函数的使用 ROUND（f，p）返回f，四舍五入到小数点后p位。小数位数可能为负，这将舍入到最接近的10（当p为-1时）或100（当p为-2时）或1000（当p为-3时）等。
例如：ROUND（7253.86，0）-> 7254
ROUND（7253.86，1）-> 7253.9
ROUND（7253.86，-3）-> 7000
SELECT name, ROUND(population/1000000, 2), ROUND(gdp/1000000000, 2)
FROM world
WHERE continent = ‘South America’

6.The CASE statement shown is used to substitute North America for Caribbean in the third column.Show the name - but substitute Australasia for Oceania - for countries beginning with N.
Case函数的使用
CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE def_value
END
If none of the conditions match (and there is not ELSE) then NULL is returned.
SELECT name
,CASE WHEN continent=‘Oceania’ THEN ‘Australasia’
ELSE continent END
FROM world
WHERE name LIKE ‘N%’

7.Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B.
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%’

or

SELECT name
,CASE WHEN continent=‘Asia’ THEN ‘Eurasia’
WHEN continent=‘Europe’ THEN ‘Eurasia’
WHEN continent = ‘North America’ THEN ‘America’
WHEN continent = ‘South America’ THEN ‘America’
WHEN continent = ‘Caribbean’ THEN ‘America’
ELSE continent END
FROM world
WHERE name LIKE ‘A%’ OR name LIKE ‘B%’

8.Put the continents right…
Oceania becomes Australasia
Countries in Eurasia and Turkey go to Europe/Asia
Caribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.
SELECT name,continent as ‘original continent’
,CASE WHEN continent=‘Oceania’ THEN ‘Australasia’
WHEN continent IN (‘Eurasia’, ‘Turkey’) THEN ‘Europe/Asia’
WHEN continent = ‘Caribbean’ AND name LIKE ‘B%’ THEN ‘North America’
WHEN continent = ‘Caribbean’ THEN 'South America’
ELSE continent END as ‘new continent’
FROM world
ORDER BY name

9.顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。
select * from nobel
where (yr=1980 and subject = ‘physics’) or (yr=1984 and subject = ‘chemistry’)<