SQLZOO及经典面试50题刷题记录

SQLZOO

SELECT names

1.顯示所有國家名字,其首都和國家名字是相同的。

SELECT
	`name` 
FROM
	world 
WHERE
	name = capital

2.“Mexico 墨西哥”的首都是”Mexico City”。

顯示所有國家名字,其首都是國家名字加上”City”。

SELECT
	`name` 
FROM
	world 
WHERE
	capital = concat(
		`name`,
		' City' 
	)

3.找出所有首都和其國家名字,而首都要有國家名字中出現

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

4.找出所有首都和其國家名字,而首都是國家名字的延伸。

你應顯示 Mexico City,因它比其國家名字 Mexico 長，你不應顯示 Luxembourg,因它的首都和國家名相是相同的。

SELECT
	`name`,
	capital 
FROM
	world 
WHERE
	capital LIKE concat( `name`, '%' ) 
	AND (capital != `name`)

或

SELECT
	`name`,
	capital 
FROM
	world 
WHERE
	capital LIKE concat( `name`,
						 '_%')

5.“Monaco-Ville"是合併國家名字 “Monaco” 和延伸詞”-Ville". 顯示國家名字，及其延伸詞，如首都是國家名字的延伸。

你可以使用SQL函數 REPLACE 或 MID.

SELECT
	`name`,
	REPLACE ( capital, `name`, '' ) AS ext 
FROM
	world 
WHERE
	capital LIKE concat( `name`, '%' ) 
	AND (capital != `name`)

SELECT from WORLD Tutorial

1.顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。

SELECT name, population, area FROM world 
WHERE (area > 3000000 AND population < 250000000) OR(area < 3000000 AND population > 250000000)

2.round

ROUND(7253.86, 0)    ->  7254
ROUND(7253.86, 1)    ->  7253.9
ROUND(7253.86,-3)    ->  7000

3.Show the name - but substitute Australasia for Oceania - for countries beginning with N.

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

4.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='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%'

SELECT from Nobel Tutorial

1.Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

显示1984年获奖者和主题（按主题和获奖者名称排序）； 但最后化学和物理排在最后。
The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.

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

Nobel Quiz

1.顯示有多少年沒有頒發醫學獎。

SELECT COUNT(DISTINCT yr) 
FROM nobel
WHERE yr 
NOT IN (SELECT DISTINCT yr 
		FROM nobel 
		WHERE subject = 'Medicine')

2.選擇代碼以顯示哪一年沒有頒發物理獎，亦沒有頒發化學獎。

SELECT yr FROM nobel
WHERE yr 
NOT IN(SELECT yr 
        FROM nobel
        WHERE subject IN ('Chemistry','Physics'))

SELECT within SELECT Tutorial

1.在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

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

2.列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

SELECT continent,name 
FROM   world AS x 
WHERE  x.name=(SELECT y.name 
               FROM  world AS y  
               WHERE y.continent=x.continent 
               ORDER BY name limit 1)

Tips：limit用法

select * from Customer LIMIT 10：检索前10行数据，显示1-10条数据
select * from Customer LIMIT 1,10：检索从第2行开始，累加10条id记录，共显示id为2....11
select * from Customer LIMIT 5,10：检索从第6行开始向前加10条数据，共显示id为6,7....15
select * from Customer LIMIT 6,10：检索从第7行开始向前加10条记录，显示id为7,8...16
有 ORDER BY 时 limit 必须在其之后

2.找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

SELECT name,continent,population
FROM world as x
WHERE 25000000 >=ALL(SELECT population FROM world AS y WHERE x.continent = y.continent) 
#将250000000放到后不行

3.有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

SELECT name, continent FROM world x
  WHERE x.population > ALL(SELECT 3*population 
  							FROM world y 
                            WHERE x.continent