SQL基礎語法練習

2 人贊了文章

以此記錄一下做SQL習題時的點，沒有把所有題目都寫進來，但是我把我覺得需要記住的點寫了一下。

SQL練習稍難部分：SQL基礎語法練習-2

SELECT FROM WORLD

使用的表：

http://1.IN

要求：查詢（Brazil , Russia , India , China）四個國家的國家名，人口總數

SELECT name,population FROM world WHERE name IN (Brazil,Russia,India,China);

返回：

SELECT 在SQL中是幾乎最為常見的語法之一。

2.BETWEEN

要求：查詢面積在200000 - 250000 之間的國家名和area

SELECT name,area FROM worldWHERE area BETWEEN 2000000 AND 2500000;

返回：

3.LIKE 和 %（通配符）

要求：查找國家名包含"United」的國家和面積

SELECT name,area FROM worldWHERE name LIKE united%;

返回：

4.AND 和 OR

要求：查找人口大國或者國土大國和其人口和國土面積，但是當一個國家既是人口大國又是國土大國要排除。

SELECT name,population,area, FROM worldWHERE (area>=3000000 AND population<= 250000000) OR (area<3000000 AND population>=250000000);

返回：

5.ROUND 用於把數值舍入為指定的小數位數。round（數值,小數位數）其中當小數位數填寫為負數時，表示四捨五入到多少位，例如-3表示四捨五入到千位。

要求：篩選出South America國家的人口（百萬為單位）和GDP（十億為單位），保留兩位小數。

SELECT name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2)FROM world WHERE continent like south america;

返回：

6.LENGTH 查詢字元串的長度。

要求：查找name和capital字元串一樣長的國家。

SELECT name,capital FROM world WHERE length(name)=length(capital);

7.LEFT 返回字元串左邊開始指定數量的字母。LEFT(列名，返回位數）

要求：返回城市名字的開頭字母和首都開頭字母相同的城市名和首都名，並且不返回城市名和首都名相同的結果。

SELECT name,capitalFROM worldWHERE LEFT(name,1)=LEFT(capital,1)and name!=capital;

返回：

SELECT from Nobel

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

select yr,subject,winner from nobel where subject like literature% and (yr between 1980 and 1989);

返回：

2.Show all details of the presidential winners:

• Theodore Roosevelt
• Woodrow Wilson
• Jimmy Carter
• Barack Obama

SELECT * FROM nobel WHERE winner IN (Theodore roosevelt, woodrow wilson, jimmy carter,barack obama)

3.Show year, subject, and name of people who won a Medicine prize in an early year (before 1910, not including 1910) together with winners of a Literature prize in a later year (after 2004, including 2004)

select * from nobel where (yr<1910 and subject like medicine) or (yr>=2004 and subject like literature);

4.Find all details of the prize won by EUGENE ONEILL

select * from nobel where winner like eugene oneill;

此處查詢語句中不能直接輸入 ，如果要查的內容中包含 ，可以輸入，這樣sql就會認為這是一個文本的 。

5.List the winners, year and subject where the winner starts withSir. Show the the most recent first, then by name order.

select winner,yr,subject from nobelwhere winner like sir%order by yr desc,winner;

此處記住，order by 默認是按照升序排列，如果要降序排列，可以在後面輸入 desc 。

6.The expression subject IN (Chemistry,Physics) can be used as a value - it will be 0 or 1.

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

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

此處是說，可以使用 subject IN (Chemistry,Physics) 來將其定義為布爾值，如果in裡面，就是1，不in，就是0 我們可以以此來進行排序。將為1 的放到最後。

另外要注意order by的排序順序，會依次進行排序。

SELECT within SELECT（子查詢）

1.Show the countries in Europe with a per capita GDP greater than United Kingdom.

Per Capita GDP is the gdp/population

select name from worldwhere gdp/population > (select gdp/population from world where name=united kingdom) and continent like europe;

2.List thenameandcontinentof countries in the continents containing eitherArgentinaorAustralia. Order by name of the country.

select name,continent from world where continent in (select continent from world where name like argentinaor name like australia)order by name ;

本題中，就是利用子查詢，先篩選出Argentina和Australia所在的大陸，然後再根據這個大陸篩選大陸中的國家。

3.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 like canada)and population<(select population from world where name like poland);

本題就是根據子查詢找出兩個對比值、相當於是將要比較的值用一條select來代替了。

4.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.

Decimal places

You can use the function ROUND to remove the decimal places.

Percent symbol %

You can use the function CONCAT to add the percentage symbol.

select name,concat(round(population/(select population from world where name like germany)*100),%) from world where continent like europe;

此處出現了 concat，這個可以進行字元串的拼接。

5.Which countries have a GDP greater than every country in Europe? [Give thenameonly.] (Some countries may have NULL gdp values)

select name from worldwhere gdp> all(select gdp from world where continent=europe and gdp>0);

其中出現了ALL函數，這個和ANY函數一樣，使用與子查詢。

all是所有，any是任意一個。

6.Find the largest country (by area) in each continent, show thecontinent, thenameand thearea:

select continent,name,area from world xwhere area >= all(select area from world ywhere x.continent=y.continent)order by name;

返回：

此題是要將每個洲area最大的那個國家選出來，我們可以world表進行自聯結，將其複製成兩個表 x和y。然後用x表的各個國家的area，和y表國家的area進行比較，用 >=來篩選出最大的那個即可。

7.List each continent and the name of the country that comes first alphabetically(按字母順序).

select continent,name from world xwhere x.name= (select y.name from world ywhere x.continent=y.continent order by name limit 1);

本題中要查看每個洲的按字母順序拍的第一個國家。其中讓表x和表y進行比較，找出按字母排序(order by name)的第一個(limit 1)

8.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Showname,continentandpopulation.

select name,continent,population from world xwhere 25000000>= all(select population from world y where y.continent = x.continent and population >0);

本題是讓我們先找出所有國家人口都少於2500000的大陸，然後再把大陸相關的國家列出來。

9.Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

select name,continent from world x where population/3 > all(select population from world y where x.continent=y.continent and x.name!= y.name);

此題思路為 將x中的population和y中的進行比較，比較的條件就是，和自己同一個洲的不同但是name相同，然後x的population是其3倍以上。

SUM、COUNT、MAX、DISTINCT、Group by

1.SUM

要求：Give the total GDP of Africa

select sum(gdp) from worldwhere continent = africa;

SUM就是對指定的列的數值進行求和。

2.COUNT

How many countries have an area of at least 1000000；

select count(name) from world where area >= 1000000;

COUNT是對列中的單元個數進行求和。

3.Distinct 就是返回唯一的值

通常用在 select 後面， 例如

select DISTINCT continent from world

就會返回不重複的continent的值、

4.GROUP BY

是將列分組，有group by的語句中再附加條件時需要注意：

在group by之前是使用 where

題：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;

此處使用where是因為條件是對於每個單元的，所以要在group by之前。

在group by之後是使用 HAVING

題目：List the continents that have a total population of at least 100 million.

SELECT continent from worldgroup by continent having sum(population)>100000000;

此處使用HAVING是因為，要計算的是各大洲的人口數，所以要在分組後對組進行計算，對組進行計算就用having。