8.
美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。
顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。
（成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口）
SELECT `name`, `population`, `area` FROM `world` WHERE (area>3000000 AND population<250000000) OR (area<3000000 AND population>250000000)
9.
除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。
SELECT `name`, ROUND(population/1000000,2) AS population, ROUND(gdp/1000000000,2) AS gdp FROM `world` WHERE `continent`='South America'
10.
顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。
顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。
SELECT `name`, ROUND(gdp/population,-3) FROM `world` WHERE `gdp`>1000000000000
11.
The CASE statement shown is used to substitute North America for Caribbean in the third column.
/*SELECT name,
CASE WHEN continent='Oceania' THEN 'Australia'
ELSE continent END
FROM world
WHERE name LIKE 'N%'*/
SELECT name,
CASE WHEN continent='Oceania' THEN 'Australasia'
ELSE continent END
FROM world
WHERE name LIKE 'N%'
12.
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=('Europe' OR 'Asia') THEN 'Eurasia' WHEN continent=('North America' OR 'South America' OR 'Caribbean') THEN 'America' ELSE continent END FROM `world' WHERE `name` LIKE '[AB]%';*/
# 正确
SELECT name,
CASE WHEN continent='Europe' or continent='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%' # name LIKE '[AB]%' 其他SQL语言中允许这种形式，MySQL似乎不允许
13.
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
/*SELECT
`name',
`continent`
CASE
WHEN continent='Eurasia' THEN 'Europe/Asia'
WHEN continent='Oceania' THEN 'Australasia'
WHEN name='Turkey' THEN 'Europe/Asia'
WHEN continent='Caribbean' AND name LIKE 'B%' THEN 'North America'
WHEN continent='Caribbean' THEN 'South America'
ELSE continent
END
FROM
world*/
SELECT name, continent, CASE
WHEN continent = 'Oceania' THEN 'Australasia'
WHEN continent = 'Eurasia' THEN 'Europe/Asia'
WHEN name = 'Turkey' THEN 'Europe/Asia'
WHEN continent = 'Caribbean' AND name LIKE 'B%' then 'North America'
WHEN continent = 'Caribbean' THEN 'South America'
ELSE continent END
FROM world ORDER BY name
SELECT_from_Nobel_Tutorial
8.
Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.
select yr, subject, winner from nobel where (subject='Physics' AND yr='1980' or (subject='Chemistry' and yr='1984');
9.
Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine
select yr, subject, winner from nobel where yr='1980'and not subject in ('Chemistry','Medicine')
12.
Find all details of the prize won by EUGENE O'NEILL
select * from nobel where winner='EUGENE O'NEILL'
13
Knights in order
List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order
select winner, yr, subject from nobel where winner like 'Sir%' order by yr DESC,winner;
14.
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
SELECT within SELECT Tutorial/zh
2.
列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。
SELECT name from world where continent='Europe' and gdp/population> ( select gdp/population from world where name='United Kingdom');
4.
哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。
SELECT name, population from world where population> (select population from world where name='Canada') and population< (select population from world where name='Poland')
5.
Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。
顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。
SELECT name, concat
(round(population/ (select population from world where name='Germany')*100,0）,'%') from world where continent='Europe'
6.
哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)
SELECT name FROM world WHERE gdp>ALL (SELECT gdp FROM world WHERE continent='Europe' AND gdp>0);
我們可以在子查詢，參閱外部查詢的數值。我們為表格再命名，便可以分別內外兩個不同的表格。
7.
在每一個州中找出最大面積的國家，列出洲份 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)
8.
列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
select continent, name from world as a where name<=all (select name from world as b where a.continent=b.continent)
9.
找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。
##################错误###################
select name, continent, population from world as A where continent in (select continent from world where population>=ALL (SELECT population from world where population<=25000000));
####################正确#########################
SELECT name, continent, population FROM world x
WHERE 25000000 >= ALL(SELECT population
FROM world y
WHERE x.continent = y.continent
AND y.population>0);
10.
有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。
select name, continent from world as a where population>=all( select population*3 from world as b where a.continent=b.continent and b.population>0)
###################################
select
name,
continent
from
world as a
where
population>=all(
select population*3 from world as b
where a.continent=b.continent
and b.name != a.name)
SUM and COUNT/zh
2
列出所有的洲份, 每個只有一次。
SELECT distinct continent FROM world
8.
列出有至少100百萬(1億)(100,000,000)人口的洲份。
SELECT continent FROM world GROUP BY continent HAVING SUM(population)>'100000000';
The JOIN operation
3.
我們可以利用JOIN來同時進行两个表格的查询操作。
SELECT * FROM game JOIN goal ON (id=matchid)
語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 -- goal的 id 必須配對game的 matchid。 簡單來說，就是 ON (game.id=goal.matchid)
以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player, teamid, stadium, mdate FROM game JOIN goal ON (id=matchid) WHERE teamid = 'GER'
4.
列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
SELECT team1,team2,player FROM game JOIN goal ON (id=matchid) WHERE player LIKE 'Mario%';
6.
要合拼JOIN 表格game 和表格 eteam，你可以使用game JOIN eteam ON (team1=eteam.id)
或game JOIN eteam ON (team2=eteam.id)
注意欄位id同時是表格game 和表格 eteam的欄位，你要清楚指出eteam.id而不是只用id
列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
SELECT mdate,teamname FROM game JOIN eteam ON (team1=eteam.id) WHERE coach='Fernando Santos'
8.
修改它，只列出全部賽事，射入德國龍門的球員名字。
找非德國球員的入球，德國可以在賽事中作team1 隊伍１（主）或team2隊伍２（客）。 你可以用teamid!='GER' 來防止列出德國球員。 你可以用DISTINCT來防止球員出現兩次以上。
# 错误：
/*SELECT DISTINCT player FROM game JOIN goal ON (matchid = game.id) WHERE (team1='GER' AND team2='GRE' OR (team1='GRE' AND team2='GER')) AND teamid!='GER'*/ # 正确： SELECT DISTINCT player FROM game JOIN goal ON matchid = id WHERE (team1= 'GER' OR team2='GER') AND teamid != 'GER'
9.
SELECT teamname, COUNT(player) FROM goal JOIN eteam ON (teamid=eteam.id) GROUP BY teamname
10.
SELECT stadium, COUNT(player) FROM game JOIN goal ON (matchid=game.id) GROUP BY stadium;
11.
每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字
# 不知是否理解
SELECT matchid,mdate,COUNT(*) FROM game JOIN goal ON matchid = id WHERE (team1 = 'POL' OR team2 = 'POL') GROUP BY mdate,matchid # 被查询对象分散在两个表中，用group by排序也应该对两个对象排序,两个对象的顺序似乎不重要。
12.
每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
#错误：
/*SELECT matchid,mdate,COUNT(*) FROM game JOIN goal ON (game.id = matchid) WHERE (team1='GER' OR team2='GER') GROUP BY matchid,mdate */ # 正确 SELECT matchid, mdate, COUNT(*) FROM goal JOIN game ON (matchid=id) WHERE teamid = 'GER' GROUP BY matchid, mdate # 本题与11题，有差异! 本题求的是某一个队的入球，要限定条件（teamid = 'GER'）；11题不限定条件，任何队入球数字都算。
13.
List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.
# 错误：
/* SELECT mdate,
DISTINCT team1,
SUM(CASE
WHEN teamid=team1 THEN 1
ELSE 0
END score1),
DISTINCT team2,
SUM(CASE
WHEN teamid=team2 THEN 1
ELSE 0
END score2)
FROM
game JOIN goal ON
game.id = goal.matchid
GROUP BY
mdate, teamid,team1, team2;*/
## 正确：
SELECT DISTINCT mdate, team1,
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
team2,
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM game
LEFT JOIN goal ON game.id = goal.matchid
GROUP BY id, mdate, team1, team2
ORDER BY mdate, matchid, team1, team2;
More JOIN operations
8.
顯示電影異型'Alien' 的演員清單。
SELECT name FROM casting JOIN actor ON (actor.id=actorid) JOIN movie ON (movie.id=movieid) WHERE title = 'Alien'
9.
列出演員夏里遜福 'Harrison Ford' 曾演出的電影。
# 解法1
SELECT title FROM movie JOIN casting ON (casting.movieid=movie.id) JOIN actor ON (actor.id=casting.actorid) WHERE name='Harrison Ford'
#解法2
SELECT title FROM casting
JOIN movie ON (movie.id = movieid)
JOIN actor ON (actor.id = actorid)
WHERE name = 'Harrison Ford'
# 表之间的连接顺序可以有差异
10.
列出演員夏里遜福 'Harrison Ford' 曾演出的電影，但他不是第1主角。
SELECT title FROM movie JOIN casting ON(id=movieid) JOIN actor ON(actorid=actor.id) WHERE name='Harrison Ford' AND ord>1;
11.
列出1962年首影的電影及它的第1主角。
SELECT title, name FROM movie JOIN casting ON(id=movieid) JOIN actor ON(actorid=actor.id) WHERE yr=1962 AND ord=1;
12.
尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。
SELECT yr,COUNT(title) FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id where name='John Travolta' GROUP BY yr #根据要求对年份进行分组，方便确定年份。这里查找出来的是全部年份及当年出演电影数量 HAVING COUNT(title)=(SELECT MAX(c) # 对前面的结果做第二次分组
FROM (SELECT yr,COUNT(title) AS c
FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actorid=actor.id where name='John Travolta' GROUP BY yr) AS t )# 从HAVING 到最后只为求出 MAX(X)=3
13.
列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。 電影名稱不是獨一的。在子查詢中使用電影編號
## 错误
SELECT title, name from movie JOIN casting ON(id=movieid) JOIN actor ON(actorid=actor.id) WHERE ord=1 AND title IN ( SELECT title, FROM movie JOIN casting ON(movie.id=movieid) JOIN actor ON(casting.actorid=actor.id) WHERE name='Julie Andrews')) ## 正确： SELECT title, name FROM casting JOIN movie ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE ord = 1 AND movie.id IN (SELECT movie.id FROM movie JOIN casting ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE actor.name = 'Julie Andrews')
# 这里应该用主键作为关联的条件，错误例中以title作为查询条件没有意义，而且过于功利。
14
列出按字母順序，列出哪一演員曾作30次第1主角。
# 错误：
SELECT DISTINCT name FROM casting JOIN actor ON(actor.id=actorid) JOIN movie ON(movie.id=movieid) WHERE actorid IN( SELECT actorid FROM casting WHERE ord =1 GROUP BY actorid HAVING COUNT(actorid)>30) ORDER BY name # 正确： SELECT DISTINCT name FROM casting JOIN movie ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE actorid IN ( SELECT actorid FROM casting WHERE ord = 1 GROUP BY actorid HAVING COUNT(actorid) >= 30) ORDER BY name
……
Using Null
1.
List the teachers who have NULL for their department.
You might think that the phrase dept=NULL would work here but it doesn't
SELECT name FROM teacher WHERE dept IS NULL
2.&3.
左右连接的区别
# 左连接 即以左边为标准，新添加的列如果条目少了，对不上，以 NULL 补充 SELECT teacher.name,dept.name FROM teacher LEFT JOIN dept ON (teacher.dept=dept.id)； # 右连接 即以右边为标准，新添加的列如果条目少了，对不上，以 NULL 补充
SELECT teacher.name, dept.name
FROM teacher RIGHT JOIN dept
ON (teacher.dept=dept.id)
5.
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
6.
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 dept ON(teacher.dept=dept.id)
8.
Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.
SELECT
dept.name,
COUNT(teacher.dept)
FROM
teacher
RIGHT JOIN dept
ON(dept.id=teacher.dept)
GROUP BY dept.name;