SQLZOO的练习

链接https://sqlzoo.net/SELECT basics1.以顯示德國 Germany 的人口。SELECT population FROM world WHERE name Germany2.,查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)。SELECT name, gdp/population FROM world WHERE area 50000003.顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。SELECT name, population FROM world WHERE name IN (Ireland, Iceland, Denmark);4.以顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000SELECT names1.找出以 Y 為開首的國家。SELECT name FROM world WHERE name LIKE Y%2.找出以 Y 為結尾的國家。SELECT name FROM world WHERE name LIKE %y3.找出所有國家,其名字包括字母x。SELECT name FROM world WHERE name LIKE %x%4.找出所有國家,其名字以 land 作結尾。SELECT name FROM world WHERE name LIKE %land5.找出所有國家,其名字以 C 作開始,ia 作結尾。SELECT name FROM world WHERE name LIKE C%ia6.找出所有國家,其名字包括字母oo。SELECT name FROM world WHERE name LIKE %oo%7.找出所有國家,其名字包括三個或以上的a。SELECT name FROM world WHERE name LIKE %a%a%a%8.找出所有國家,其名字以t作第二個字母。SELECT name FROM world WHERE name LIKE _t% ORDER BY name9.找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。SELECT name FROM world WHERE name LIKE %o__o%10.找出所有國家,其名字都是 4 個字母的。SELECT name FROM world WHERE name LIKE ____11.顯示所有國家名字,其首都和國家名字是相同的。SELECT name FROM world WHERE name LIKE capital12.顯示所有國家名字,其首都是國家名字加上”City”。SELECT name FROM world WHERE capital LIKE concat(name, City)13.找出所有首都和其國家名字,而首都要有國家名字中出現。SELECT capital,name FROM world WHERE capital LIKE concat(%,name,%)14.找出所有首都和其國家名字,而首都是國家名字的延伸。你應顯示 Mexico City,因它比其國家名字 Mexico 長。你不應顯示 Luxembourg,因它的首都和國家名相是相同的。SELECT name,capital FROM world WHERE capital LIKE concat(name,_%)15.顯示國家名字及其延伸詞如首都是國家名字的延伸。SELECT name,replace(capital,name,) FROM world WHERE capital LIKE concat(name,_%)SELECT from WORLD1.如何使用WHERE來篩選記錄。 顯示具有至少2億人口的國家名稱。 2億是200000000有八個零。SELECT name FROM world WHERE population2000000002.找出有至少200百萬(2億)人口的國家名稱及人均國內生產總值。SELECT name,GDP/population FROM world WHERE population2000000003.顯示South America南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。SELECT name,population/1000000 FROM world WHERE continentSouth America4.顯示法國德國意大利(France, Germany, Italy)的國家名稱和人口。SELECT name,population FROM world WHERE name IN (France,Germany,Italy)5.顯示包含單詞“United”為名稱的國家。SELECT name FROM world WHERE name LIKE %United%6.成為大國的兩種方式如果它有3百萬平方公里以上的面積或擁有250百萬(2.5億)以上人口。展示大國的名稱人口和面積。SELECT name,population,area FROM world WHERE area3000000 OR population2500000007.美國、印度和中國(USA, India, China)是人口又大同時面積又大的國家。排除這些國家。顯示以人口或面積為大國的國家但不能同時兩者。顯示國家名稱人口和面積。SELECT name,population,area FROM world WHERE (area3000000 and population250000000) OR (area3000000 and population250000000)8.除以為10000006個零是以百萬計。除以10000000009個零是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。對於南美顯示以百萬計人口以十億計2位小數GDP。百萬和十億SELECT name,ROUND(population/1000000,2),ROUND(GDP/1000000000,2) FROM world WHERE continentSouth America9.顯示國家有至少一個萬億元國內生產總值萬億也就是12個零的人均國內生產總值。四捨五入這個值到最接近1000。顯示萬億元國家的人均國內生產總值四捨五入到最近的$ 1000。SELECT name,round(GDP/population,-3) FROM world WHERE GDP100000000000010.The CASE statement shown is used to substituteNorth AmericaforCaribbeanin the third column.Show the name - but substituteAustralasiaforOceania- for countries beginning with N.SELECT name, CASE WHEN continentOceania THEN Australasia ELSE continent END FROM world WHERE name LIKE N%11.Show the name and the continent - but substituteEurasiafor Europe and Asia; substituteAmerica- for each country inNorth AmericaorSouth AmericaorCaribbean. Show countries beginning with A or BSELECT name, CASE WHEN continent IN (Europe,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%12.Put the continents right...Oceania becomes AustralasiaCountries in Eurasia and Turkey go toEurope/AsiaCaribbean islands starting with B go toNorth America, other Caribbean islands go toSouth AmericaShow the name, the original continent and the new continent of all countries.SELECT name,continent, CASE WHEN continentOceania THEN Australasia WHEN continent IN (Eurasia,Turkey) THEN Europe/Asia WHEN continentCaribbean AND name LIKE B% THEN North America WHEN continentCaribbean AND name NOT LIKE B% THEN South America ELSE continent END FROM world ORDER BY nameSELECT from Nobel1.查詢以顯示1950年諾貝爾獎的獎項資料。SELECT yr, subject, winner FROM nobel WHERE yr 19502.顯示誰贏得了1962年文學獎(Literature)。SELECT winner FROM nobel WHERE yr 1962 AND subject Literature3.顯示“愛因斯坦”(Albert Einstein) 的獲獎年份和獎項。SELECT yr,subject FROM nobel WHERE winner Albert Einstein4.顯示2000年及以後的和平獎(‘Peace’)得獎者。SELECT winner FROM nobel WHERE yr 2000 AND subject Peace5.顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節年主題獲獎者。SELECT yr,subject,winner FROM nobel WHERE yr 1980 AND yr1989 AND subject Literature6.顯示總統獲勝者的所有細節西奧多•羅斯福 Theodore Roosevelt伍德羅•威爾遜 Woodrow Wilson吉米•卡特 Jimmy CarterSELECT * FROM nobel WHERE winner IN (Theodore Roosevelt, Woodrow Wilson, Jimmy Carter)7.顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前姓氏(Last name)在後)SELECT winner FROM nobel WHERE winner LIKE John%8.顯示1980年物理學(physics)獲獎者及1984年化學獎(chemistry)獲得者。SELECT * FROM nobel WHERE (yr 1980 AND subject physics) OR (yr 1984 AND subject chemistry)9.查看1980年獲獎者但不包括化學獎(Chemistry)和醫學獎(Medicine)。SELECT * FROM nobel WHERE yr 1980 AND subject NOT IN (Chemistry,Medicine)10.顯示早期的醫學獎(Medicine)得獎者1910之前不包括1910及近年文學獎(Literature)得獎者2004年以後包括2004年。SELECT * FROM nobel WHERE (yr 1910 and subject Medicine) OR (yr 2004 and subject Literature)11.Find all details of the prize won by PETER GRÜNBERGSELECT * FROM nobel WHERE winner PETER GRÜNBERG12.查找尤金•奧尼爾EUGENE ONEILL得獎的所有細節 Find all details of the prize won by EUGENE ONEILLSELECT * FROM nobel WHERE winner EUGENE ONEILL13.騎士列隊 Knights in order列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者然後同年再按名稱順序排列。SELECT winner,yr,subject FROM nobel WHERE winner LIKE Sir% ORDER BY yr DESC,winner ASC14.The expressionsubject IN (Chemistry,Physics)can be used as a value - it will be0or1.Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.SELECT winner, subject FROM nobel WHERE yr1984 ORDER BY subject IN (Physics,Chemistry),subject,winnerSELECT in SELECT1.列出每個國家的名字name當中人口population是高於俄羅斯Russia的人口。SELECT name FROM world WHERE population (SELECT population FROM world WHERE nameRussia)2.列出歐州每國家的人均GDP當中人均GDP要高於英國United Kingdom的數值。SELECT name FROM world WHERE continent Europe AND GDP/population (SELECT GDP/population FROM world WHERE name United Kingdom)3.在阿根廷Argentina及 澳大利亞Australia所在的洲份中列出當中的國家名字name及洲分continent。按國字名字順序排序SELECT name,continent FROM world WHERE continent IN (SELECT continent FROM world WHERE name IN (Argentina,Australia)) ORDER BY name4.哪一個國家的人口比加拿大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*100/ (SELECT population FROM world WHERE name Germany),0),%) FROM world WHERE continent Europe6.哪些國家的GDP比Europe歐洲的全部國家都要高呢[只需列出name。]有些國家的記錄中GDP是NULL沒有填入資料的。)SELECT name FROM world WHERE GDP ALL(SELECT GDP FROM world WHERE GDP 0 AND continent Europe)7.在每一個州中找出最大面積的國家列出洲份continent 國家名字name及面積area。有些國家的記錄中AREA是NULL沒有填入資料的。)SELECT continent, name, area FROM world x WHERE area ALL (SELECT area FROM world y WHERE y.continentx.continent AND area0)8.列出洲份名稱和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)SELECT continent, name FROM world x WHERE x.name (SELECT name FROM world y WHERE y.continentx.continent LIMIT 1)9.找出洲份當中全部國家都有少於或等於 25000000 人口.在这些洲份中列出国家名字namecontinent洲份和population人口。SELECT name,continent,population FROM world x WHERE 25000000 ALL (SELECT population FROM world y WHERE x.continent y.continent AND population 0)10.有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字和 洲份 continent。SELECT name,continent FROM world x WHERE x.population ALL (SELECT population*3 FROM world y WHERE x.continent y.continent AND population 0 AND x.population ! y.population)SUM and COUNT1.展示世界的總人口。SELECT SUM(population) FROM world2.列出所有的洲份, 每個只有一次。SELECT DISTINCT(continent) FROM world3.找出非洲(Africa)的GDP總和。SELECT SUM(GDP) FROM world WHERE continent Africa4.有多少個國家具有至少百萬(1000000)的面積。SELECT COUNT(name) FROM world WHERE area 10000005.(France,Germany,Spain)“法國”“德國”“西班牙”的總人口是多少SELECT SUM(population) FROM world WHERE name IN (France,Germany,Spain)6.對於每一個洲份顯示洲份和國家的數量。SELECT continent,COUNT(name) FROM world x GROUP BY continent7.對於每一個洲份顯示洲份和至少有1000萬人(10,000,000)口國家的數目。SELECT continent,COUNT(name) FROM world WHERE population 10000000 GROUP BY continent8.列出有至少100百萬(1億)(100,000,000)人口的洲份。SELECT continent FROM world GROUP BY continent HAVING SUM(population) 100000000The JOIN operation1.第一個例子列出球員姓氏為Bender的入球數據。*表示列出表格的全部欄位簡化了寫matchid, teamid, player, gtime語句。修改此SQL以列出 賽事編號matchid和球員名player,該球員代表德國隊Germany入球的。要找出德國隊球員要檢查:teamid GERSELECT matchid,player FROM goal WHERE teamid GER2.由以上查詢你可見Lars Benders 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。留意在goal表格中的欄位matchid是對應表格game的欄位id。我們可以在表格game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2SELECT id,stadium,team1,team2 FROM game WHERE id 10123.我們可以利用JOIN來同時進行以上兩個步驟。SELECT * FROM game JOIN goal ON (idmatchid)語句FROM表示合拼兩個表格game 和 goal的數據。語句ON表示如何找出game中每一列應該配對goal中的哪一列 --goal的id必須配對game的matchid。 簡單來說就是ON (game.idgoal.matchid)以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)修改它來顯示每一個德國入球的球員名隊伍名場館和日期。SELECT player,teamid,stadium,mdate FROM game JOIN goal ON (idmatchid) WHERE goal.teamid GER4.使用上題相同的JOIN語句列出球員名字叫Mario (player LIKE Mario%)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 playerSELECT team1,team2,player FROM game JOIN goal ON (idmatchid) WHERE player LIKE Mario%5.表格eteam貯存了每一國家隊的資料包括教練。你可以使用語句goal JOIN eteam on teamidid來合拼JOIN表格goal到 表格eteam。列出每場球賽中首10分鐘gtime10有入球的球員player, 隊伍teamid, 教練coach, 入球時間gtimeSELECT player, teamid, coach,gtime FROM goal JOIN eteam ON (teamid id) WHERE gtime106.要合拼JOIN表格game和表格eteam你可以使用game JOIN eteam ON (team1eteam.id)或game JOIN eteam ON (team2eteam.id)注意欄位id同時是表格game和表格eteam的欄位你要清楚指出eteam.id而不是只用id列出Fernando Santos作為隊伍1 team1 的教練的賽事日期和隊伍名。SELECT mdate,teamname FROM game JOIN eteam ON (team1eteam.id) WHERE coach Fernando Santos7.列出場館 National Stadium, Warsaw的入球球員。SELECT player FROM game JOIN goal ON (idmatchid) WHERE stadium National Stadium, Warsaw8.以下例子找出德國-希臘Germany-Greece 的八強賽事的入球修改它只列出全部賽事射入德國龍門的球員名字。SELECT DISTINCT(player) FROM game JOIN goal ON id matchid WHERE teamid!GER AND (game.team1GER OR game.team2GER)9.列出隊伍名稱teamname和該隊入球總數SELECT teamname,COUNT(*) FROM eteam JOIN goal ON idteamid GROUP BY teamname10.列出場館名和在該場館的入球數字。SELECT stadium, COUNT(*) FROM game JOIN goal ON id matchid GROUP BY stadium11.每一場波蘭POL有參與的賽事中列出賽事編號 matchid, 日期date 和入球數字。SELECT matchid,mdate,COUNT(*) FROM game JOIN goal ON matchid id WHERE (team1 POL OR team2 POL) GROUP BY matchid,mdate12.每一場德國GER有參與的賽事中列出賽事編號 matchid, 日期date 和德國的入球數字SELECT matchid,mdate,COUNT(*) FROM game JOIN goal ON matchid id WHERE teamid GER GROUP BY matchid,mdate13.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.mdateteam1score1team2score21 July 2012ESP4ITA010 June 2012ESP1ITA110 June 2012IRL1CRO3...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 teamidteam1 THEN 1 ELSE 0 END) AS score1,team2, SUM(CASE WHEN teamidteam2 THEN 1 ELSE 0 END) AS score2 FROM game LEFT JOIN goal ON matchid id GROUP BY mdate,matchid,team1,teamMore JOIN operations1.列出1962年首影的電影 [顯示id,title]SELECT id,title FROM movie WHERE yr19622.電影大國民 Citizen Kane 的首影年份。SELECT yr FROM movie WHERE title Citizen Kane3.列出全部Star Trek星空奇遇記系列的電影包括id,title和yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。SELECT id,title,yr FROM movie WHERE title LIKE Star Trek% ORDER BY yr4.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 Close6.電影北非諜影Casablanca 的編號id是什麼?SELECT id FROM movie WHERE title Casablanca7.列出電影北非諜影 Casablanca的演員名單。什麼是演員名單?使用movieid11768, 這是你上一題得到的結果。SELECT name FROM actor JOIN casting ON id actorid WHERE movieid 117688.顯示電影異型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 WHERE id IN(SELECT movieid FROM actor JOIN casting ON actor.id actorid WHERE name Harrison Ford)10.列出演員夏里遜福 Harrison Ford 曾演出的電影但他不是第1主角。SELECT title FROM movie WHERE id IN(SELECT movieid FROM actor JOIN casting ON actor.id actorid WHERE name Harrison Ford AND ord!1)11.列出1962年首影的電影及它的第1主角。SELECT title,name FROM movie JOIN casting ON movie.id movieid JOIN actor ON actor.id actorid WHERE yr 1962 AND ord 112.尊·特拉華達John Travolta最忙是哪一年? 顯示年份和該年的電影數目。SELECT yr,COUNT(title) FROM movie JOIN casting ON movie.idmovieid JOIN actor ON actoridactor.id where nameJohn Travolta GROUP BY yr HAVING COUNT(title)(SELECT MAX(c) FROM (SELECT yr,COUNT(title) AS c FROM movie JOIN casting ON movie.idmovieid JOIN actor ON actoridactor.id where nameJohn Travolta GROUP BY yr) AS t)13.列出演員茱莉·安德絲Julie Andrews曾參與的電影名稱及其第1主角。是否列了電影 Little Miss Marker兩次?她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。 電影名稱不是獨一的。在子查詢中使用電影編號。SELECT title,name FROM movie JOIN casting ON movie.id movieid JOIN actor ON actor.id actorid WHERE ord 1 AND movieid IN (SELECT movie.id FROM movie JOIN casting ON movie.id movieid JOIN actor ON actor.id actorid WHERE name Julie Andrews)14.列出按字母順序列出哪一演員曾作30次第1主角。SELECT actor.name FROM movie JOIN casting ON movie.id movieid JOIN actor ON actor.id actorid WHERE ord 1 GROUP BY actorid,actor.name HAVING COUNT(movieid)30 ORDER BY name15.列出1978年首影的電影名稱及角色數目按此數目由多至少排列。SELECT title,COUNT(actorid) FROM movie JOIN casting ON movie.id movieid WHERE yr 1978 GROUP BY title ORDER BY COUNT(actorid) DESC16.列出曾與演員亞特·葛芬柯Art Garfunkel合作過的演員姓名。SELECT DISTINCT actor.name FROM movie JOIN casting ON movie.id movieid JOIN actor ON actor.id casting.actorid WHERE name ! Art Garfunkel AND movieid IN (SELECT movieid FROM movie JOIN casting ON movie.id movieid JOIN actor ON actor.id actorid WHERE name Art Garfunkel)SELECT DISTINCT a.name FROM casting c1 JOIN casting c2 ON c1.movieid c2.movieid -- 同一部电影 JOIN actor a ON c2.actorid a.id WHERE c1.actorid (SELECT id FROM actor WHERE name Art Garfunkel) AND a.name ! Art Garfunkel;Using Null1.列出學系department是NULL值的老師。SELECT name FROM teacher WHERE dept is NULL2.注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。SELECT teacher.name, dept.name FROM teacher INNER JOIN dept ON (teacher.deptdept.id)3.使用不同的JOIN(外連接)來列出全部老師。SELECT teacher.name, dept.name FROM teacher LEFT JOIN dept ON (teacher.deptdept.id)4.使用不同的JOIN(外連接)來列出全部學系。SELECT teacher.name, dept.name FROM teacher RIGHT JOIN dept ON (teacher.deptdept.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 2266SELECT name,COALESCE(mobile,07986 444 2266) FROM teacher6.Use the COALESCE function and a LEFT JOIN to print the teachernameand 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.deptdept.id)7.使用COUNT來數算老師和流動電話數目。SELECT COUNT(teacher.name), count(mobile) FROM teacher8.使用COUNT 和 GROUP BYdept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。SELECT dept.name,COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON (teacher.deptdept.id) GROUP BY dept.name9.Use CASE to show thenameof each teacher followed by Sci if the teacher is indept1 or 2 and Art otherwise.SELECT teacher.name, CASE WHEN dept IN (1,2) THEN Sci ELSE Art END FROM teacher10.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, CASE WHEN dept IN (1,2) THEN Sci WHEN dept 3 THEN Art ELSE None END FROM teacherSelf join1.數據庫中有多少個站stops。SELECT COUNT(id) FROM stops2.找出車站 Craiglockhart 的idSELECT id FROM stops WHERE name Craiglockhart3.列出巴士公司LRT的4號巴士線的站編號id和 站名nameSELECT id,name FROM stops JOIN route ON route.stop stops.id WHERE num 4 AND company LRT4.以下查詢列出途經 London Road (149) 或 Craiglockhart (53)的巴士線號碼。注意有兩條路線會經過這兩個站兩次。 加入 HAVING 語句來限制只列出這兩條路線。SELECT company, num, COUNT(*) FROM route WHERE stop149 OR stop53 GROUP BY company, num HAVING COUNT(*) 25.執行自我合拼來留意b.stop代表由Craiglockhart出發不用轉車可前住的地方。 修改它來顯示由Craiglockhart到 London Road的服務資料。SELECT a.company, a.num, a.stop, b.stop FROM route a JOIN route b ON (a.companyb.company AND a.numb.num) WHERE a.stop53 AND b.stop1496.此題和上題相似但是用兩個stops表來自我合拼。這樣我們可以用站名而非站編號。 修改它來顯示由Craiglockhart到 London Road的服務資料。 如你太悶可試一試由 Fairmilehead 到 Tollcross (系統會當答錯的。正確有3條路線:11,15,315)SELECT a.company, a.num, stopa.name, stopb.name FROM route a JOIN route b ON (a.companyb.company AND a.numb.num) JOIN stops stopa ON (a.stopstopa.id) JOIN stops stopb ON (b.stopstopb.id) WHERE stopa.nameCraiglockhart AND stopb.name London Road7.列出連接115 和 137 (Haymarket 和 Leith) 的公司名和路線號碼。不要重覆。SELECT DISTINCT a.company, a.num FROM route a JOIN route b ON (a.companyb.company AND a.numb.num) WHERE a.stop115 AND b.stop1378.列出連接車站stopsCraiglockhart 到 Tollcross 的公司名和路線號碼。SELECT a.company, a.num FROM route a JOIN route b ON (a.companyb.company AND a.numb.num) JOIN stops stopa ON (a.stopstopa.id) JOIN stops stopb ON (b.stopstopb.id) WHERE stopa.nameCraiglockhart AND stopb.name Tollcross9.不重覆列出可以由 Craiglockhart 乘一程車到達的站stops包括Craiglockhart本身。 列出站名公司名和路線號碼。SELECT DISTINCT stopb.name,b.company,b.num FROM route a JOIN route b ON (a.companyb.company AND a.numb.num) JOIN stops stopa ON (a.stopstopa.id) JOIN stops stopb ON (b.stopstopb.id) WHERE stopa.name Craiglockhart10.查找涉及两条公交车的路线这些公交车可以从克雷格洛克哈特到赛特希尔。显示公交车编号。以及第一班公交车的连属、换乘站的名称和公交车号。还有陪伴第二辆公交车的伙伴。SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company FROM (SELECT start1.num, start1.company, stop1.stop FROM route start1 JOIN route stop1 ON start1.num stop1.num AND start1.company stop1.company AND start1.stop ! stop1.stop WHERE start1.stop (SELECT id FROM stops WHERE name Craiglockhart) ) bus1 JOIN (SELECT start2.num, start2.company, start2.stop FROM route start2 JOIN route stop2 ON start2.num stop2.num AND start2.company stop2.company AND start2.stop ! stop2.stop WHERE stop2.stop (SELECT id FROM stops WHERE name Sighthill) ) bus2 ON bus1.stop bus2.stop JOIN stops ON bus1.stop stops.id