sqlzoo是一个SQL语句练习网站。
SELECT from Nobel
- 更改查詢以顯示1950年諾貝爾獎的獎項資料。
1
2
3SELECT *
FROM nobel
where yr=1950
- 更改查詢以顯示1950年諾貝爾獎的獎項資料。
- 顯示誰贏得了1962年文學獎(Literature)。
1
2
3
4SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'
- 顯示誰贏得了1962年文學獎(Literature)。
- 顯示“愛因斯坦”(‘Albert Einstein’) 的獲獎年份和獎項。
1
2
3SELECT yr, subject
FROM nobel
WHERE winner='Albert Einstein'
- 顯示“愛因斯坦”(‘Albert Einstein’) 的獲獎年份和獎項。
- 顯示2000年及以後的和平獎(‘Peace’)得獎者。
1
2
3
4SELECT winner
FROM nobel
WHERE yr>1999
AND subject='Peace'
- 顯示2000年及以後的和平獎(‘Peace’)得獎者。
- 顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節(年,主題,獲獎者)。
1
2
3
4select *
from nobel
where yr >=1980 and yr <= 1989
and subject='Literature'
- 顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節(年,主題,獲獎者)。
顯示總統獲勝者的所有細節:
- 西奧多•羅斯福 Theodore Roosevelt
- 伍德羅•威爾遜 Woodrow Wilson
- 吉米•卡特 Jimmy Carter
1
2
3
4SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt',
'Woodrow Wilson',
'Jimmy Carter')
- 顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前,姓氏(Last name)在後)
1
2
3select winner
from nobel
where winner like 'John%'
- 顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前,姓氏(Last name)在後)
- 顯示1980年物理學(physics)獲獎者,及1984年化學獎(chemistry)獲得者。
1
2
3select *
from nobel
where (yr=1980 and subject='physics') or (yr=1984 and subject='chemistry')
- 顯示1980年物理學(physics)獲獎者,及1984年化學獎(chemistry)獲得者。
- 查看1980年獲獎者,但不包括化學獎(Chemistry)和醫學獎(Medicine)。
1
2
3select *
from nobel
where subject not in ('chemistry', 'medicine') and yr=1980
- 查看1980年獲獎者,但不包括化學獎(Chemistry)和醫學獎(Medicine)。
- 顯示早期的醫學獎(Medicine)得獎者(1910之前,不包括1910),及近年文學獎(Literature)得獎者(2004年以後,包括2004年)。
1
2
3
4select *
from nobel
where (yr<1910 and subject='Medicine')
or (yr >= 2004 and subject='Literature')
- 顯示早期的醫學獎(Medicine)得獎者(1910之前,不包括1910),及近年文學獎(Literature)得獎者(2004年以後,包括2004年)。
- Find all details of the prize won by PETER GRÜNBERG
1
2
3select *
from nobel
where winner='PETER GRÜNBERG'
- Find all details of the prize won by PETER GRÜNBERG
- 查找尤金•奧尼爾EUGENE O’NEILL得獎的所有細節 Find all details of the prize won by EUGENE O’NEILL
1
2
3select *
from nobel
where winner='EUGENE O\'NEILL'
- 查找尤金•奧尼爾EUGENE O’NEILL得獎的所有細節 Find all details of the prize won by EUGENE O’NEILL
- 騎士列隊 Knights in order
- 列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者,然後同年再按名稱順序排列。
1
2
3
4select winner, yr, subject
from nobel
where winner like 'Sir%'
order by yr DESC, winner
- 列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者,然後同年再按名稱順序排列。
- 騎士列隊 Knights in order
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.
1
2
3
4SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject in ('Physics', 'Chemistry'), subject, winner
- Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
SELECT IN SELECT
- List each country name where the population is larger than that of ‘Russia’.
1
2
3
4SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name='Russia')
- List each country name where the population is larger than that of ‘Russia’.
- Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.
1
2
3
4select name
from world
where continent = 'Europe'
and gdp/population > (select gdp/population from world where name = 'United Kingdom')
- Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.
- List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.
1
2
3
4
5SELECT name, continent
FROM world
WHERE continent IN
(SELECT continent FROM world WHERE name IN ('Argentina', 'Australia'))
ORDER BY name
- List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.
- Which country has a population that is more than Canada but less than Poland? Show the name and the population.
1
2
3
4SELECT name, population
FROM world
WHERE population > (SELECT population FROM world WHERE name = 'Canada')
AND population < (select population FROM world WHERE name='Poland')
- Which country has a population that is more than Canada but less than Poland? Show the name and the population.
- 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.
1
2
3SELECT name, CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name = 'Germany'), 0), '%')
FROM world
WHERE continent = 'Europe'
- 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.
- Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
1
2
3
4
5SELECT name
FROM world
WHERE gdp > (
SELECT MAX(gdp) FROM world WHERE continent='Europe'
AND gdp IS NOT NULL)
- Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
- Find the largest country (by area) in each continent, show the continent, the name and the area:
1
2
3
4
5SELECT continent, name, area FROM world x
WHERE area>= ALL
(SELECT area FROM world y
WHERE y.continent=x.continent
AND area>0)
- Find the largest country (by area) in each continent, show the continent, the name and the area:
List each continent and the name of the country that comes first alphabetically.
1
2
3
4SELECT continent, name
FROM world x
WHERE name <= ALL (
SELECT name FROM world y WHERE x.continent=y.continent)或者
1
2
3
4
5
6
7SELECT continent, name
FROM world x
WHERE x.name = (
SELECT name FROM world y WHERE x.continent=y.continent
ORDER BY name
LIMIT 1
)
- 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.
1
2
3
4
5
6SELECT name, continent, population
FROM world x
WHERE 25000000 >= ALL (
SELECT y.population FROM world y WHERE x.continent=y.continent
AND y.population >=0
)
- 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.
- Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
1
2
3
4
5SELECT name, continent
FROM world x
WHERE x.population/3 >= ALL (
SELECT population FROM world y WHERE x.continent=y.continent AND y.name <> x.name
)
- Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
SUM and COUNT
This tutorial is about aggregate functions such as COUNT, SUM and AVG. An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11.
- Show the total population of the world.
1
2SELECT SUM(population)
FROM world
- Show the total population of the world.
- List all the continents - just once each.
1
2SELECT DISTINCT continent
FROM world
- List all the continents - just once each.
- Give the total GDP of Africa
1
2
3SELECT SUM(gdp)
FROM world
WHERE continent='Africa'
- Give the total GDP of Africa
- How many countries have an area of at least 1000000
1
2
3SELECT COUNT(name)
FROM world
WHERE area >= 1000000
- How many countries have an area of at least 1000000
- What is the total population of (‘Estonia’, ‘Latvia’, ‘Lithuania’)
1
2
3SELECT SUM(population)
FROM world
WHERE name in ('Estonia', 'Latvia', 'Lithuania')
- What is the total population of (‘Estonia’, ‘Latvia’, ‘Lithuania’)
- For each continent show the continent and number of countries.
1
2
3SELECT continent, COUNT(name)
FROM world
GROUP BY continent
- For each continent show the continent and number of countries.
- For each continent show the continent and number of countries with populations of at least 10 million.
1
2
3
4SELECT continent, COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY continent
- For each continent show the continent and number of countries with populations of at least 10 million.
- List the continents that have a total population of at least 100 million.
1
2
3
4SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) >= 100000000
- List the continents that have a total population of at least 100 million.
SUM and COUNT Quiz
选择题答案:
Q\A | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|
1 | √ | ||||
2 | √ | ||||
3 | √ | ||||
4 | √ | ||||
5 | √ | ||||
6 | √ | ||||
7 | √ | ||||
8 | √ |
JOIN
- The first example shows the goal scored by a player with the last name ‘Bender’. The * says to list all the columns in the table - a shorter way of saying matchid, teamid, player, gtime
Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = ‘GER’
1
2SELECT matchid, player FROM goal
WHERE teamid='GER'
- From the previous query you can see that Lars Bender’s scored a goal in game 1012. Now we want to know what teams were playing in that match.
Notice in the that the column matchid in the goal table corresponds to the id column in the game table. We can look up information about game 1012 by finding that row in the game table.
Show id, stadium, team1, team2 for just game 1012
1
2
3SELECT id,stadium,team1,team2
FROM game ga
WHERE id=1012
- 3.You can combine the two steps into a single query with a JOIN.
1
2SELECT *
FROM game JOIN goal ON (id=matchid)
The FROM clause says to merge data from the goal table with that from the game table. The ON says how to figure out which rows in game go with which rows in goal - the id from goal must match matchid from game. (If we wanted to be more clear/specific we could say
ON (game.id=goal.matchid)
The code below shows the player (from the goal) and stadium name (from the game table) for every goal scored.
Modify it to show the player, teamid, stadium and mdate for every German goal.
1
2SELECT player, teamid, stadium, mdate
FROM game JOIN goal ON (game.id=goal.matchid and goal.teamid='GER')
- Use the same JOIN as in the previous question.
Show the team1, team2 and player for every goal scored by a player called Mario player LIKE ‘Mario%’
1
2
3SELECT team1, team2, player
FROM game JOIN goal
ON (id=matchid AND player LIKE 'Mario%')
- 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
1
2
3SELECT player, teamid, coach, gtime
FROM goal JOIN eteam
ON (teamid=id AND gtime<=10)
- To JOIN game with eteam you could use either
game JOIN eteam ON (team1=eteam.id) or game JOIN eteam ON (team2=eteam.id)
- To JOIN game with eteam you could use either
Notice that because id is a column name in both game and eteam you must specify eteam.id instead of just id
List the the dates of the matches and the name of the team in which ‘Fernando Santos’ was the team1 coach.
1
2
3SELECT mdate, teamname
FROM game JOIN eteam
ON (team1=eteam.id AND coach='Fernando Santos')
- List the player for every goal scored in a game where the stadium was ‘National Stadium, Warsaw’
1
2
3SELECT player
FROM goal JOIN game
ON (matchid = id AND stadium='National Stadium, Warsaw')
- List the player for every goal scored in a game where the stadium was ‘National Stadium, Warsaw’
- The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.1
2
3SELECT DISTINCT player
FROM goal JOIN game
ON matchid=id AND teamid <> 'GER' AND (team1='GER' OR team2='GER')
- The example query shows all goals scored in the Germany-Greece quarterfinal.
- Show teamname and the total number of goals scored.
1
2
3SELECT teamname, COUNT(gtime)
FROM eteam JOIN goal ON id=teamid
GROUP BY teamname
- Show teamname and the total number of goals scored.
- Show the stadium and the number of goals scored in each stadium.
1
2
3
4SELECT stadium, COUNT(*) as goals
FROM game JOIN goal
ON id=matchid AND (team1=teamid OR team2=teamid)
GROUP BY stadium
- Show the stadium and the number of goals scored in each stadium.
- For every match involving ‘POL’, show the matchid, date and the number of goals scored.
1
2
3
4SELECT matchid, mdate, COUNT(*)
FROM game JOIN goal
ON id=matchid AND (team1 = 'POL' OR team2='POL')
GROUP BY matchid, mdate
- For every match involving ‘POL’, show the matchid, date and the number of goals scored.
- For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by ‘GER’
1
2
3
4SELECT matchid, mdate, COUNT(*)
FROM game JOIN goal
ON id=matchid AND(teamid='GER')
GROUP BY matchid, mdate
- For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by ‘GER’
- 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.
1 | SELECT mdate, team1, |
JOIN_Quiz
选择题答案:
Q\A | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|
1 | √ | ||||
2 | √ | ||||
3 | √ | ||||
4 | √ | ||||
5 | √ | ||||
6 | √ | ||||
7 | √ |
More JOIN
- List the films where the yr is 1962 [Show id, title]
1
2
3SELECT id, title
FROM movie
WHERE yr=1962
- List the films where the yr is 1962 [Show id, title]
- Give year of ‘Citizen Kane’.
1
2
3SELECT yr
FROM movie
WHERE title='Citizen Kane'
- Give year of ‘Citizen Kane’.
- List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.
1
2
3
4SELECT id, title, yr
FROM movie
WHERE title LIKE '%Star Trek%'
ORDER BY yr
- List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.
- What id number does the actor ‘Glenn Close’ have?
1
2
3SELECT id
FROM actor
WHERE name='Glenn Close'
- What id number does the actor ‘Glenn Close’ have?
- What is the id of the film ‘Casablanca’
1
2
3SELECT id
FROM movie
WHERE title='Casablanca'
- What is the id of the film ‘Casablanca’
6.Obtain the cast list for ‘Casablanca’.
what is a cast list?
The cast list is the names of the actors who were in the movie.
Use movieid=11768, (or whatever value you got from the previous question)1
2
3SELECT name
FROM actor JOIN casting ON actorid=id
WHERE movieid=11768
- Obtain the cast list for the film ‘Alien’
1
2
3
4
5SELECT DISTINCT name
FROM actor a JOIN casting c ON c.actorid=a.id
WHERE c.movieid=(
SELECT id FROM movie WHERE title='Alien'
)
- Obtain the cast list for the film ‘Alien’
- List the films in which ‘Harrison Ford’ has appeared
1
2
3
4
5SELECT title
FROM movie m JOIN casting c ON m.id=c.movieid
WHERE c.actorid=(
SELECT id FROM actor WHERE name='Harrison Ford'
)
- List the films in which ‘Harrison Ford’ has appeared
- List the films where ‘Harrison Ford’ has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]
1
2
3
4
5SELECT title
FROM movie m JOIN casting c ON m.id=c.movieid
WHERE c.actorid=(
SELECT id FROM actor WHERE name='Harrison Ford'
) AND c.ord <> 1
- List the films where ‘Harrison Ford’ has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]
- List the films together with the leading star for all 1962 films.
1
2
3SELECT title, name
FROM ( movie m JOIN casting c ON m.id=c.movieid) JOIN actor a ON c.actorid=a.id
WHERE c.ord=1 AND m.yr=1962
- List the films together with the leading star for all 1962 films.
- Which were the busiest years for ‘John Travolta’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.
1
2
3
4
5
6
7
8
9
10
11
12SELECT 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
)
- Which were the busiest years for ‘John Travolta’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.
- 12.
List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.
- 12.
Did you get “Little Miss Marker twice”?1
2
3
4
5
6
7
8
9SELECT title, name
FROM movie m
JOIN casting c ON m.id=c.movieid
JOIN actor a ON c.actorid=a.id
WHERE ord=1 AND movieid IN (
SELECT movieid
FROM casting JOIN actor
ON actorid=id AND name='Julie Andrews'
)
- Obtain a list, in alphabetical order, of actors who’ve had at least 30 starring roles.
1
2
3
4
5SELECT name
FROM actor
JOIN casting ON id = actorid
AND (SELECT COUNT(ord) FROM casting WHERE actorid = actor.id AND ord=1)>=30
GROUP BY name
- Obtain a list, in alphabetical order, of actors who’ve had at least 30 starring roles.
- List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
1
2
3
4
5SELECT title, COUNT(actorid) as cast
FROM movie JOIN casting on id=movieid
WHERE yr = 1978
GROUP BY title
ORDER BY cast DESC, title
- List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
- List all the people who have worked with ‘Art Garfunkel’.
1
2
3
4
5SELECT name
FROM actor JOIN casting ON actor.id=casting.actorid
WHERE movieid IN (
SELECT movieid FROM casting JOIN actor ON casting.actorid=actor.id AND actor.name='Art Garfunkel'
) AND name <> 'Art Garfunkel'
- List all the people who have worked with ‘Art Garfunkel’.
Quiz MORE JOIN
选择题答案:
Q\A | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|
1 | √ | ||||
2 | √ | ||||
3 | √ | ||||
4 | √ | ||||
5 | √ | ||||
6 | √ | ||||
7 | √ |
USING NULL
- List the teachers who have NULL for their department.
1
2
3SELECT name
FROM teacher
WHERE dept is NULL
- List the teachers who have NULL for their department.
- Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
1
2
3SELECT teacher.name, dept.name
FROM teacher INNER JOIN dept
ON (teacher.dept=dept.id)
- Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
- Use a different JOIN so that all teachers are listed.
1
2
3SELECT teacher.name, dept.name
FROM teacher
LEFT JOIN dept ON (teacher.dept=dept.id)
- Use a different JOIN so that all teachers are listed.
- Use a different JOIN so that all departments are listed.
1
2
3SELECT teacher.name, dept.name
FROM dept
LEFT JOIN teacher ON (dept.id=teacher.dept)
- Use a different JOIN so that all departments are listed.
- 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’
1
2SELECT teacher.name, COALESCE(mobile, '07986 444 2266')
FROM teacher
- 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’
- 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.
1
2
3SELECT teacher.name, COALESCE(dept.name, 'None')
FROM teacher
LEFT JOIN dept ON (teacher.dept = dept.id)
- 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.
- Use COUNT to show the number of teachers and the number of mobile phones.
1
2SELECT COUNT(name), COUNT(mobile)
FROM teacher
- Use COUNT to show the number of teachers and the number of mobile phones.
- 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.
1
2
3
4SELECT dept.name, COUNT(teacher.name)
FROM teacher
RIGHT JOIN dept ON (dept.id = teacher.dept)
GROUP BY dept.name
- 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.
- Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.
1
2
3
4SELECT teacher.name,
CASE WHEN (teacher.dept = 1 or teacher.dept = 2) THEN 'Sci'
ELSE 'Art' END
FROM teacher
- Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.
- 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.
1
2
3
4
5
6SELECT teacher.name,
CASE
WHEN (teacher.dept=1 OR teacher.dept=2) THEN 'Sci'
WHEN teacher.dept=3 THEN 'Art'
ELSE 'None' END
FROM teacher
- 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.
Using Null Quiz
选择题答案:
Q\A | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|
1 | √ | ||||
2 | √ | ||||
3 | √ | ||||
4 | √ | ||||
5 | √ | ||||
6 | √ |
Self join
这个有点难,从第5题开始我是参考别人的答案写的,我还不太理解,so bad!
- How many stops are in the database.
1
SELECt COUNT(*) from stops
- How many stops are in the database.
- Find the id value for the stop ‘Craiglockhart’
1
2
3SELECT id
FROM stops
WHERE name='Craiglockhart'
- Find the id value for the stop ‘Craiglockhart’
- Give the id and the name for the stops on the ‘4’ ‘LRT’ service.
1
2SELECT id, name
FROM stops JOIN route ON id=stop WHERE company='LRT' AND num=4
- Give the id and the name for the stops on the ‘4’ ‘LRT’ service.
- 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.
1
2
3
4SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*) = 2
- 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.
- 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.
1
2
3
4SELECT 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=149
- 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.
- 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’
1
2
3
4
5
6SELECT 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'
- 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’
- Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)
1
2
3
4
5
6SELECT 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 (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Haymarket' AND stopb.name='Leith'
- Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)
- Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’
1
2
3
4
5
6SELECT a.company, a.num
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='Tollcross'
- Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’
- 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.
1
2
3
4
5
6SELECT stopa.name, a.company, a.num
FROM route a
JOIN route b ON (a.num=b.num AND a.company=b.company)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopb.name = 'Craiglockhart'
- 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.
- Find the routes involving two buses that can go from Craiglockhart to Sighthill.
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.1
2
3
4
5
6
7
8
9
10
11
12SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company FROM (
SELECT start1.num, start1.company, stop1.stop FROM route AS start1 JOIN route AS 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')) AS bus1
JOIN (SELECT start2.num, start2.company, start2.stop FROM route AS start2
JOIN route AS 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')) AS bus2
ON bus1.stop = bus2.stop JOIN stops ON bus1.stop = stops.id
- Find the routes involving two buses that can go from Craiglockhart to Sighthill.
Self join Quiz
选择题答案:
Q\A | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|
1 | √ | ||||
2 | √ | ||||
3 | √ |