MySQL 進階——SQL ZOO 練習

04-29

前兩天學習了一下MySQL的基本語法，筆記見：MySQL入門

趁熱打鐵又開始繼續在SQL ZOO 練習，我敲完我筆記的代碼，做練習的時候最起碼有70%的題是沒有問題的，也會有不會的題，在大家的幫助下我做完了90%的練習，剩下的題會繼續做的，下面是我覺得有問題或者是需要注意的答案，我做起來覺得很簡單的就沒有寫，當然等我時間充裕會把答案補全，我也在網上查了一些答案，覺得不是很全而且也會有一些錯誤，希望這些答案可以幫助大家。（ps：這個練習題是英文的，你可以用谷歌瀏覽器打開，它可以把英文翻譯成漢語，當然這個網站也給大家提供了中文翻譯，但是繁體字看得我好難受）

1.Show the name and population for France, Germany, Italy

SELECT name ,population from world where name IN (France,Germany,Italy)

2.Show the countries which have a name that includes the word United

SELECT NAME FROM world where name like %United%

這道題的意思是查找名字里包含『United』的，可以用%來完成，%United%表示不管前後是什麼，只要包含United就可以，%United表示已United結尾，那你肯定知道United%是什麼意思了！

3.Exclusive OR (XOR). Show the countries that are big by area or big by population but not both. Show name, population and area.

Australia has a big area but a small population, it should be included.
Indonesia has a big population but a small area, it should be included.
China has a big population and big area, it should be excluded.
United Kingdom has a small population and a small area, it should be excluded.

SELECT name, population, area FROM world where ( area>3000000 and population <= 250000000)OR (area<= 3000000 and population > 250000000)

4.Show the name and population in millions and the GDP in billions for the countries of the continent South America. Use the ROUNDfunction to show the values to two decimal places.

SELECT name,round(population/1000000,2) AS POPULATION,round(GDP/1000000000,2) AS GDP from world where continent =South America

5.Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

Show per-capita GDP for the trillion dollar countries to the nearest $1000.

SELECT name,ROUND((GDP/population),-3 )from world where gdp>=1000000000000

注意：round的用法：

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

6.Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.

SELECT yr,subject,winner from nobel where (yr between 1980 and 1989) and subject = Literature

7.Show the name and capital where the name and the capital have the same number of characters.

SELECT name, capital FROM world where length(name)=length(capital)

8.Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine

SELECT YR,SUBJECT,WINNER from nobel where YR=1980 and subject not in (Chemistry,Medicine)

9.List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

SELECT name,continent from world where continent in(select continent from world where name in (Argentina,Australia))order by name

10.Which country has a population that is more than Canada but less than Poland? Show the name and the population.

select name,population from world where (population > (select population from world where name=canada) and population <(select population from world where name=Poland))

11.Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

select name,concat(round( population/ (select population from world where name=Germany)*100 ,0),%) population from world where continent=Europe

12.Find the largest country (by area) in each continent, show the continent, the name and the area:

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

any,all的用法：

any 可以與=、>、>=、<、<=、<>結合起來使用，分別表示等於、大於、大於等於、小於、小於等於、不等於其中的任何一個數據。

all可以與=、>、>=、<、<=、<>結合是來使用，分別表示等於、大於、大於等於、小於、小於等於、不等於其中的其中的所有數據。

13.Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

select name from world where gdp >all(select gdp from world where continent =Europe and GDP>0)

14.Find the largest country (by area) in each continent, show the continent, the name and the area:

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

15.List each continent and the name of the country that comes first alphabetically.

SELECT continent,name from world a where name <=ALL(select name from world b where a.continent=b.continent)order by name

16.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 x.name, x.continent, x.populationFROM world AS xWHERE 25000000 > ALL ( SELECT y.population FROM world AS y WHERE x.continent = y.continent);

17.For each continent show the continent and number of countries with populations of at least 10 million.

select continent,count(name) from world where population >=10000000 group by continent

18.List the continents that have a total population of at least 100 million.

select continentfrom world group by continentHAVING SUM(POPULATION)>=100000000

19.The table eteam gives details of every national team including the coach. You can JOIN goal to eteam using the phrase goal JOIN eteam on teamid=id

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

SELECT player, teamid, coach,gtime FROM goal JOIN eteam ON teamid = id WHERE gtime<=10

20.Use a different JOIN so that all teachers are listed.

SELECT teacher.name, dept.nameFROM teacherLEFT JOIN deptON teacher.dept = dept.id;

21.Use a different JOIN so that all departments are listed.

SELECT teacher.name, dept.nameFROM teacherRIGHT JOIN deptON teacher.dept = dept.id;

22.Use COALESCE to print the mobile number. Use the number 07986 444 2266 if there is no number given. Show teacher name and mobile number or 07986 444 2266

SELECT name,coalesce(mobile,07986 444 2266) from teacher

COALESCE的用法：

COALESCE(x,y,z) = x if x is not NULL COALESCE(x,y,z) = y if x is NULL and y is not NULL COALESCE(x,y,z) = z if x and y are NULL but z is not NULL COALESCE(x,y,z) = NULL if x and y and z are all NULL

23.Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string None where there is no department.

select teacher.name,coalesce(dept.name, None) FROM teacher LEFT JOIN deptON teacher.dept = dept.id

24.Use CASE to show the name of each teacher followed by Sci if the teacher is in dept 1 or 2 and Art otherwise.

SELECT teacher.name,casewhen teacher.dept in(1,2)then SciELSE Artendfrom teacher;

25.Use CASE to show the name of each teacher followed by Sci if the teacher is in dept 1 or 2, show Art if the teachers dept is 3 and None otherwise.

SELECT teacher.name,casewhen teacher.dept in (1,2)then Sciwhen teacher.dept =3then Artelse Noneend

練習網址：SELECT basics - SQLZOO