SQLZOO的练习
SQLZOO网址：https://sqlzoo.net/wiki/SQL_Tutorial
4.SELECT within SELECT Tutorial
7.在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)
select continent, name, area from world x where area = (select max(area) from world y where x.continent = y.continent)
8.列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
select continent, name from world x where name = (select name from world y where x.continent = y.continent order by name limit 1)
错题分析，没有想到limit的用法
9.找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent洲份和population人口。
select name, continent, population from world x where (select max(population) from world y where x.continent = y.continent) <= 25000000
错题分析，没有利用两个州是否相同来找出一个州的全部国家
10.有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。
select name, continent from world x where x.population > (select max(population*3) from world y where x.continent = y.continent and x.name <> y.name)
错题分析，忘记判断不是自身x.name <> y.name，导致一个都查不到
5.SUM and COUNT
1.展示世界的總人口。
SELECT sum(population) FROM world
2.列出所有的洲份, 每個只有一次。
select continent from world group by continent
3.找出非洲(Africa)的GDP總和。
select sum(GDP) from world where continent = 'Africa'
4.有多少個國家具有至少百萬(1000000)的面積。
select count(name) from world where area >= 1000000
5.(‘France’,‘Germany’,‘Spain’)（“法國”，“德國”，“西班牙”）的總人口是多少？
select sum(population) from world where name in ('France','Germany','Spain')
6.對於每一個洲份，顯示洲份和國家的數量。
select continent, count(name) from world group by continent
7.對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目。
select continent, count(name) from world where population > 10000000 group by continent
8.列出有至少100百萬(1億)(100,000,000)人口的洲份。
select continent from world group by continent having sum(population) > 100000000
6.JOIN
1.第一個例子列出球員姓氏為’Bender’的入球數據。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player, gtime語句。
修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'
select matchid, player from goal where teamid='GER'
2.由以上查詢，你可見Lars Bender’s 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。
留意在 goal 表格中的欄位 matchid ，是對應表格game的欄位id。我們可以在表格 game中找出賽事1012的資料。
只顯示賽事1012的 id, stadium, team1, team2
select id, stadium, team1, team2 from game where id = 1012
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.使用上題相同的 JOIN語句，
列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select team1, team2, player from game join goal on id = matchid where player like 'Mario%'
5.表格eteam 貯存了每一國家隊的資料，包括教練。你可以使用語句 goal JOIN eteam on teamid=id來合拼 JOIN 表格goal 到 表格eteam。
列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
select player, teamid, coach, gtime from eteam join goal on id = teamid where gtime <= 10
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 eteam.id = team1 where coach = 'Fernando Santos'
7.列出場館 'National Stadium, Warsaw’的入球球員。
select player from goal join game on id = matchid where stadium = 'National Stadium, Warsaw'
8.以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
修改它，只列出全部賽事，射入德國龍門的球員名字。
select distinct(player) from game join goal on matchid = id where (team1 = 'GER' or team2 = 'GER') and teamid <> 'GER'
9.列出隊伍名稱 teamname 和該隊入球總數
COUNT and GROUP BY
你應該在SELECT語句中使用COUNT(*)和使用GROUP BY teamname
select teamname, count(gtime) from eteam join goal on goal.teamid = eteam.id group by teamname
10.列出場館名和在該場館的入球數字。
select stadium, count(stadium) from game join goal on matchid = id group by stadium
错题分析，区分count和sum的区别，此处应该应count统计进球个数，用sum不行
11.每一場波蘭’POL’有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
select matchid, mdate, count(gtime) from game join goal on matchid = id where (team1 = 'POL' or team2 = 'POL') group by matchid, mdate
错题分析，刚开始没有将mdate加入到group by中，导致无法select中mdate
12.每一場德國’GER’有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
select matchid, mdate, count(gtime) from goal join game on id = matchid where teamid = 'GER' group by matchid, mdate
技巧分析，表二goal中全是进球的记录，只需要将teamid='GER’即可查到德国全部的进球数，再利用matchid, mdate组合
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.
| mdate | team1 | score1 | team2 | score2 |
|---|---|---|---|---|
| 1 July 2012 | ESP | 4 | ITA | 0 |
| 10 June 2012 | ESP | 1 | ITA | 1 |
| 10 June 2012 | IRL | 1 | CRO | 3 |
| … |
Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.
select 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 id = matchid
group by mdate, matchid, team1, team2
这个太难了，不懂left join的操作，没有进球的比赛在goal中没有记录，因此应该使用对game使用left join将goal加入，
不明白将多个变量进行group by
7.MORE JOIN
热身
1.列出1962年首影的電影， [顯示 id, title]
SELECT id, title
FROM movie
WHERE yr=1962
2.電影大國民 ‘Citizen Kane’ 的首影年份。
select yr from movie where title = 'Citizen Kane'
3.列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
select id, title, yr from movie where title like 'Star Trek%' order by yr
Looking at the id field.
4.id是 11768, 11955, 21191 的電影是什麼名稱?
select title from movie where id in (11768, 11955, 21191)
5.女演員’Glenn Close’的編號 id是什麼?
select id from actor where name = 'Glenn Close'
6.電影北非諜影’Casablanca’ 的編號 id是什麼?
select id from movie where title = 'Casablanca'
合并语法
7.列出電影北非諜影 'Casablanca’的演員名單。
什麼是演員名單?
演員名單,即是電影中各演員的真實姓名清單。
使用 movieid=11768, 這是你上一題得到的結果
select name from casting join actor on actor.id = actorid where movieid = 11768
8.顯示電影異型’Alien’ 的演員清單。
select name from actor join casting on id = actorid where movieid = (select id from movie where title = 'Alien')
9.列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。
select title from movie join casting on movieid = id where actorid = (select id from actor where name = 'Harrison Ford' )
10.列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。
select title from movie join casting on movieid = id where ord != 1 and actorid = (select id from actor where name = 'Harrison Ford')
11.列出1962年首影的電影及它的第1主角。
select title, name from casting join movie m on movieid = m.id join actor a on actorid = a.id where ord = 1 and yr = 1962
错题分析，join多个表的时候直接join，不需要+and
困难题目
12.尊·特拉華達’John Travolta’最忙是哪一年? 顯示年份和該年的電影數目。
select yr, count(title) from
casting join movie m on m.id = movieid
join actor a on a.id = actorid
where a.name = 'John Travolta' group by yr
having count(title) = (select max(c) from
(select yr, count(title) as c from
casting join movie on movie.id = movieid
join actor on actor.id = actorid
where actor.name = 'John Travolta' group by yr ) as t)
错题分析，做到having count(title)时思路断了，知道要查找最大的count(title)，但没想到将当前表作为副表然后从中查出最大的count(title)
13.列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。
是否列了電影 "Little Miss Marker"兩次?
她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。 電影名稱不是獨一的。在子查詢中使用電影編號。
select title, name from
casting
join movie on movieid = movie.id
join actor on actorid = actor.id
where
movieid in (select movieid from casting
where actorid = (select id from actor where name = 'Julie Andrews'))
and ord = 1
利用演员的id和casting表找出演员演过的movieid
14.列出按字母順序，列出哪一演員曾作30次第1主角。
select name from casting
join actor on actorid = actor.id
where ord = 1
group by name
having count(name) >= 30
15.列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。
select title, count(actorid) from casting
join movie on movieid = movie.id
where yr = 1978
group by title
order by count(actorid), title desc
不知道答案是什么鬼顺序，按角色数目排序完对应不上
16.列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。
select name from casting
join actor on actorid = actor.id
where name != 'Art Garfunkel'and
movieid in (select movieid from casting where actorid = (select id from actor where name = 'Art Garfunkel'))
8.Using Null
NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
1.List the teachers who have NULL for their department.
select name from teacher where dept is null
使用 is null 而非 = null
2.Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
select teacher.name, dept.name from teacher inner join dept on teacher.dept = dept.id
3.Use a different JOIN so that all teachers are listed.
select teacher.name, dept.name from teacher left join dept on teacher.dept = dept.id
4.Use a different JOIN so that all departments are listed.
select teacher.name, dept.name from teacher right join dept on teacher.dept = dept.id
Using the COALESCE function
COALESCE takes any number of arguments and returns the first value that is not null.
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
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
7.Use COUNT to show the number of teachers and the number of mobile phones.
select count(name), count(mobile) from teacher
使用COUNT函数不要求先GROUP BY
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(dept) from teacher right join dept on teacher.dept = dept.id group by dept.name
Using CASE
CASE allows you to return different values under different conditions.
If there no conditions match (and there is not ELSE) then NULL is returned.
CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE def_value END
9.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 name,
case
when teacher.dept = 1 or teacher.dept = 2 then 'Sci'
else 'Art'
end
from teacher
10.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 teacher’s dept is 3 and ‘None’ otherwise.
select name,
case
when dept = 1 or dept = 2 then 'Sci'
when dept = 3 then 'Art'
else 'None'
end
from teacher
9.Self join
1.How many stops are in the database.
select count(id) from stops
2.Find the id value for the stop ‘Craiglockhart’
select id from stops where name = 'Craiglockhart'
3.Give the id and the name for the stops on the ‘4’ ‘LRT’ service.
select id, name from stops join route on id = stop where num = '4' and company = 'LRT'
Routes and stops
4.The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.
select company, num, count(*) from route where stop = 149 or stop = 53 group by company, num having count(*) = 2
5.Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.
select a.company, a.num, a.stop, b.stop from route a join route b on (a.company = b.company and a.num = b.num) where a.stop=53 and b.stop = (select id from stops where name = 'London Road')
6.The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between ‘Craiglockhart’ and ‘London Road’ are shown. If you are tired of these places try ‘Fairmilehead’ against ‘Tollcross’
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' and stopb.name='London Road'
Using a self join
7.Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)
select distinct a.company, a.num from route a
join route b on (a.company = b.company and a.num = b.num)
where a.stop = 115 and b.stop = 137
or
select distinct a.company, a.num from route a
join route b on (a.company = b.company and a.num = b.num)
join stops stopa on (stopa.id = a.stop)
join stops stopb on (stopb.id = b.stop)
where stopa.name = 'Haymarket' and stopb.name = 'Leith'
8.Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’
select a.company, a.num from route a
join route b on (a.company = b.company and a.num = b.num)
join stops stopa on (stopa.id = a.stop)
join stops stopb on (stopb.id = b.stop)
where stopa.name = 'Craiglockhart' and stopb.name = 'Tollcross'
9.Give a distinct list of the stops which may be reached from ‘Craiglockhart’ by taking one bus, including ‘Craiglockhart’ itself, offered by the LRT company. Include the company and bus no. of the relevant services.
select stopb.name, b.company, b.num from route a
join route b on (a.company = b.company and a.num = b.num)
join stops stopa on (stopa.id = a.stop)
join stops stopb on (stopb.id = b.stop)
where stopa.name = 'Craiglockhart' and a.company = 'LRT'
10.Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.
select a.num, a.company, stopa.name, b.num, b.company from route a
join route b on (a.company = b.company and a.num = b.num)
join stops stopa on (stopa.id = a.stop)
join stops stopb on (stopb.id = b.stop)
where stopb.name = 'Lochend'
and stopa.name in
((select stopb.name from route a
join route b on (a.company = b.company and a.num = b.num)
join stops stopa on (stopa.id = a.stop)
join stops stopb on (stopb.id = b.stop)
where stopa.name = 'Craiglockhart' ) )
-------------
select * from (select b.num, b.company, stopa.name from route a
join route b on (a.company = b.company and a.num = b.num)
join stops stopa on (stopa.id = a.stop)
join stops stopb on (stopb.id = b.stop)
where stopb.name = 'Lochend' ) as t
join
((select a.num, a.company, stopb.name from route a
join route b on (a.company = b.company and a.num = b.num)
join stops stopa on (stopa.id = a.stop)
join stops stopb on (stopb.id = b.stop)
where stopa.name = 'Craiglockhart') as u)
on t.name = u.name
----
-- 正确的答案如下
SELECT m.num,m.company,name,n.num,n.company
FROM (SELECT a.num,a.company,b.stop FROM route a JOIN route b ON a.company=b.company AND a.num=b.num AND a.stop!=b.stop WHERE a.stop=(SELECT id FROM stops WHERE name='Craiglockhart')) AS m
JOIN (SELECT d.num,d.company,c.stop FROM route c JOIN route d ON c.company=d.company AND c.num=d.num AND c.stop!=d.stop WHERE d.stop=(SELECT id FROM stops WHERE name='Lochend')) AS n
ON m.stop=n.stop
JOIN stops
ON m.stop=stops.id
WHERE m.num!=n.num
ORDER BY m.num,stops.name,n.num;
太难了，不会
1053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
