目录
3. SQLZOO:SELECT from WORLD Tutorial/zh
4. SELECT from Nobel Tutorial/zh
5. SELECT within SELECT Tutorial/zh
1. SELECT basics/zh
這個例子顯示’France法國’的人口。字串應該在'單引號'中。
- 修改此例子,以顯示德國 Germany 的人口。
SELECT population FROM world
WHERE name = 'Germany'查詢顯示面積為 5,000,000 以上平方公里的國家,該國家的人口密度(population/area)。人口密度並不是 WORLD 表格中的欄,但我們可用公式(population/area)計算出來。
- 修改此例子,查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)。
SELECT name, gdp/population FROM world
WHERE area> 5000000檢查列表:單詞“IN”可以讓我們檢查一個項目是否在列表中。
此示例顯示了“Luxembourg 盧森堡”,“Mauritius 毛里求斯”和“Samoa 薩摩亞”的國家名稱和人口。
- 顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。
SELECT name, population FROM world
WHERE name IN ('Ireland','Iceland','Denmark');哪些國家是不是太小,又不是太大?
BETWEEN 允許範圍檢查 - 注意,這是包含性的。 此例子顯示面積為 250,000 及 300,000 之間的國家名稱和該國面積。
- 修改此例子,以顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。
SELECT name, area FROM world
WHERE area BETWEEN 200000 AND 2500002. SELECT names/zh
- 找出以 Y 為開首的國家。
SELECT name FROM world
WHERE name LIKE 'Y%'- 找出以 Y 為結尾的國家。
SELECT name FROM world
WHERE name LIKE '%Y'- 找出所有國家,其名字包括字母x。
SELECT name FROM world
WHERE name LIKE '%x%'- 找出所有國家,其名字以 land 作結尾。
SELECT name FROM world
WHERE name LIKE '%land'- 找出所有國家,其名字以 C 作開始,ia 作結尾。
SELECT name FROM world
WHERE name LIKE 'c%ia'- 找出所有國家,其名字包括字母oo。
SELECT name FROM world
WHERE name LIKE '%oo%'- 找出所有國家,其名字包括三個或以上的a。
SELECT name FROM world
WHERE name LIKE '%a%a%a%'- 找出所有國家,其名字以t作第二個字母。
SELECT name FROM world
WHERE name LIKE '_t%'
ORDER BY name- 找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。
SELECT name FROM world
WHERE name LIKE '%o__o%'- 找出所有國家,其名字都是 4 個字母的。
SELECT name FROM world
WHERE name LIKE '____'- 顯示所有國家名字,其首都和國家名字是相同的。
SELECT name
FROM world
WHERE name =capital“Mexico 墨西哥”的首都是”Mexico City”。
- 顯示所有國家名字,其首都是國家名字加上”City”。
SELECT name
FROM world
where capital like concat(name, ' city')- 找出所有首都和其國家名字,而首都要有國家名字中出現。
select capital,name
from world
where capital like concat('%',name,'%')- 找出所有首都和其國家名字,而首都是國家名字的延伸。
你應顯示 Mexico City,因它比其國家名字 Mexico 長。
你不應顯示 Luxembourg,因它的首都和國家名相是相同的。
select name,capital
from world
where capital like concat(name,'%') and capital !=name"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville".
- 顯示國家名字，及其延伸詞，如首都是國家名字的延伸。
SELECT name,replace(capital, name, '')
FROM world
WHERE capital LIKE concat(name,'_%')3. SQLZOO:SELECT from WORLD Tutorial/zh
- 觀察運行一個簡單的SQL命令的結果。
SELECT name, continent, population FROM world- 顯示具有至少2億人口的國家名稱。 2億是200000000，有八個零。
SELECT name FROM world
WHERE population>200000000- 找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。
求助：如何人均國內生產總值計算
人均國內生產總值，即是國內生產總值除以人口(GDP/population)。
select name,gdp/population from world
where population >=200000000- 顯示'South America'南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。
select name,population/1000000
from world
where continent = 'South America'- 顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口。
select name, population
from world
where name in ('France','Germany','Italy')- 顯示包含單詞“United”為名稱的國家。
select name
from world
where name like '%United%'成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。
- 展示大國的名稱，人口和面積。
SELECT name, population, area FROM world
WHERE area > 3000000 OR population > 250000000美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。
- 顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。
SELECT name, population, area FROM world
WHERE (area > 3000000 AND population < 250000000) OR(area < 3000000 AND population > 250000000)除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。
- 對於南美顯示以百萬計人口，以十億計2位小數GDP。
百萬和十億
除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。
select name,round(population/1000000,2) ,round(gdp/1000000000,2)
from world
where continent = 'South America'顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。
- 顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。
SELECT name, ROUND(gdp/population/1000,0)*1000 FROM world
WHERE gdp > 1000000000000- The CASE statement shown is used to substitute North America for Caribbean in the third column.
Show the name - but substitute Australasia for Oceania - for countries beginning with N.
SELECT name,
CASE WHEN continent='Oceania' THEN 'Australasia'
ELSE continent END
FROM world
WHERE name LIKE 'N%'- 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' THEN 'Eurasia'
WHEN continent='Asia' THEN 'Eurasia'
WHEN continent='North America' THEN 'America'
WHEN continent='South America' THEN 'America'
WHEN continent='Caribbean' THEN 'America'
else continent END
FROM world
WHERE name LIKE 'A%' or name LIKE 'B%'- 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
Show the name, the original continent and the new continent of all countries.
SELECT name,continent
,CASE WHEN continent='Oceania'
THEN 'Australasia'
WHEN continent IN ('Eurasia', '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 name4. SELECT from Nobel Tutorial/zh
- 更改查詢以顯示1950年諾貝爾獎的獎項資料
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950- 顯示誰贏得了1962年文學獎(Literature)。
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'- 顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項。
select yr,subject from nobel where winner = 'Albert Einstein'- 顯示2000年及以後的和平獎(‘Peace’)得獎者
select winner from nobel where yr>=2000 and subject='Peace'- 顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。
select * from nobel where yr between 1980 and 1989 and subject = 'Literature'- 顯示總統獲勝者的所有細節：
- 西奧多•羅斯福 Theodore Roosevelt
- 伍德羅•威爾遜 Woodrow Wilson
- 吉米•卡特 Jimmy Carter
SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt',
'Woodrow Wilson',
'Jimmy Carter')- 顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)
select winner from nobel where winner like 'John%'- 顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。
select * from nobel where (yr=1980 and subject='physics') or (yr=1984 and subject='chemistry')- 查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)。
select * from nobel where subject not in('Chemistry','Medicine') and yr=1980- 顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年）。
select * from nobel where (yr<1910 and subject='Medicine') or (yr>=2004 and subject='Literature')- Find all details of the prize won by PETER GRÜNBERG
select * from nobel where winner = 'Peter Grünberg'- 查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL
跳脫字符:單引號
你不能把一個單引號直接的放在字符串中。但您可連續使用兩個單引號在字符串中當作一個單引號。
select * from nobel where winner = 'EUGENE O''NEILL'- 騎士列隊 Knights in order
列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。
select winner,yr,subject from nobel where winner like 'Sir%'
order by yr desc,winner asc- 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('Phys ics','Chemistry'),subject asc,winner asc5. SELECT within SELECT Tutorial/zh
5.1 Using nested SELECT/zh
使用子查询
- List each country in the same continent as 'Brazil'.
SELECT name FROM world WHERE continent =
(SELECT continent
FROM world WHERE name = 'Brazil')- 列出與巴西'Brazil' 和 墨西哥'Mexico'相同洲份的每個國家名和洲份
SELECT name, continent FROM world
WHERE continent IN
(SELECT continent
FROM world WHERE name='Brazil'
OR name='Mexico')- 顯示中國人口是英國人口的多少倍。
SELECT
population/(SELECT population FROM world
WHERE name='United Kingdom')
FROM world
WHERE name = 'China'- 找出哪些國家的人口是高於歐洲每一國的人口。\
（留意，我們是找高於歐洲每一個單一國家的人口，不是歐洲各國合計的全部總人口）
SELECT name FROM world
WHERE population > ALL
(SELECT population FROM world
WHERE continent='Europe')练习题
- 列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。
SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name='Russia')- 列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。
select name
from world
where gdp/population>
(select gdp/population
from world
where name='United Kingdom')
and continent='Europe'- 在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序
select name,continent
from world
where continent in
(select continent
from world
where name='Argentina ' or name='Australia')- 哪一個國家的人口比加拿大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')Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。
- 顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。
小數位數
您可以使用函數ROUND 刪除小數。
百分號 %
您可以使用函數 CONCAT 增加的百分比符號。
select name,
concat(round(population/(select population from world where name='Germany')*100,0),'%')
from world
where continent ='Europe'- 哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)
select name from world
where gdp > all(select gdp from world where continent='Europe' and gdp>0)- 在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)
SELECT continent, name, area FROM world as x
WHERE area >= ALL
(SELECT area FROM world as y
WHERE y.continent=x.continent
AND area>0)
或者：
SELECT continent, name, max(area) FROM world as x
WHERE area >= all
(SELECT area FROM world as y
WHERE y.continent=x.continent
AND area>0)
group by continent ,name- 列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
select continent ,name
from world x
where name <=all (select name from world y where x.continent=y.continent ) order by name- 找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。
select name,continent,population
from world x
where 25000000>=all (select population from world y where x.continent=y.continent)- 有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。
select name ,continent
from world x
where population/3>=all(select population
from world y
where y.continent=x.continent
and population>0
and y.name != x.name )6. SUM and COUNT functions
- 找出總共有多少個獎頒發了。
select count (winner) from nobel- 列出每一個獎項(subject), 只列一次
select distinct subject from nobel- 找出物理獎的總頒發次數。
select count( subject) from nobel
where subject ='physics'- 對每一個獎項(Subject),列出頒發數目。
select subject,count(winner )from nobel
group by subject- 對每一個獎項(Subject),列出首次頒發的年份。
select subject,min(yr)
from nobel
group by subject- 對每一個獎項(Subject),列出2000年頒發的數目。
select subject ,count (winner)
from nobel
where yr=2000
group by subject- 對每一個獎項(Subject),列出有多少個不同的得獎者。
select subject ,count(distinct winner)
from nobel
group by subject- 對每一個獎項(Subject),列出有多少年曾頒發過。
select subject ,count(distinct yr)
from nobel
group by subject- 列出哪年曾同年有3個物理獎Physics得獎者。
select yr
from nobel
where subject='physics'
group by yr
having count(subject)=3- 列出誰得獎多於一次。
select winner
from nobel
group by winner
having count(winner)>1- 列出誰獲得多於一個獎項(Subject)
select winner
from nobel
group by winner
having count(distinct subject)>1- 哪年哪獎項，是同一獎項(subject)頒發給3個人。只列出2000年及之後的資料。
select yr,subject
from nobel
where yr >=2000
group by yr ,subject
having count( subject)=37. The JOIN operation/zh
第一個例子列出球員姓氏為'Bender'的入球數據。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player, gtime語句。
- 修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'
SELECT matchid,player FROM goal
WHERE teamid = 'GER'由以上查詢，你可見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我們可以利用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'使用上題相同的 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%'表格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要合拼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'- 列出場館 'National Stadium, Warsaw'的入球球員
select player
from game join goal on id=matchid
where stadium='National Stadium, Warsaw'以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
- 修改它，只列出全部賽事，射入德國龍門的球員名字。
找非德國球員的入球，德國可以在賽事中作team1 隊伍１（主）或team2隊伍２（客）。 你可以用teamid!='GER' 來防止列出德國球員。 你可以用DISTINCT來防止球員出現兩次以上。
SELECT distinct(player)
FROM game JOIN goal ON matchid = id
WHERE (team1='GER' or team2='GER') and teamid!='GER'- 列出隊伍名稱 teamname 和該隊入球總數
SELECT teamname, count(player)
FROM eteam JOIN goal ON id=teamid
group by teamname- 列出場館名和在該場館的入球數字。
select stadium,count(*)
from game join goal on id = matchid
group by stadium- 每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
SELECT matchid,mdate,count(teamid)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid,mdate- 每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字
SELECT matchid,mdate,count(teamid)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'GER' OR team2 = 'GER')
and teamid='GER'
group by matchid,mdate- 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 matchid = id
group by mdate, matchid, team1,team28. More JOIN operations/zh
- 列出1962年首影的電影， [顯示 id, title]
SELECT id,title
FROM movie
WHERE yr=1962- 電影大國民 'Citizen Kane' 的首影年份。
select yr
from movie
where title='Citizen Kane'- 列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
select id,title,yr
from movie
where title like 'Star Trek%'
order by yr- id是 11768, 11955, 21191 的電影是什麼名稱?
select title
from movie
where id in(11768,11955,21191)- 女演員'Glenn Close'的編號 id是什麼?
select id
from actor
where name='Glenn Close'- 電影北非諜影'Casablanca' 的編號 id是什麼?
SELECT id FROM movie
WHERE title='Casablanca'- 列出電影北非諜影 'Casablanca'的演員名單。
什麼是演員名單?
使用 movieid=11768, 這是你上一題得到的結果。
SELECT name
FROM actor JOIN casting
ON id =actorid
WHERE movieid=11768- 顯示電影異型'Alien' 的演員清單。
select name from casting
join actor a on (a.id = actorid)
join movie m on (m.id = movieid)
where title = 'Alien'- 列出演員夏里遜福 'Harrison Ford' 曾演出的電影。
select title from casting
join actor a on (a.id = actorid)
join movie m on (m.id = movieid)
where name='Harrison Ford'- 列出演員夏里遜福 'Harrison Ford' 曾演出的電影，但他不是第1主角。
select title from casting
join actor a on (a.id = actorid)
join movie m on (m.id = movieid)
where name='Harrison Ford' and ord!=1- 列出1962年首影的電影及它的第1主角。
select title,name from casting
join actor a on (a.id = actorid)
join movie m on (m.id = movieid)
where yr=1962 and ord=1- 尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。
SELECT yr,COUNT(title)
FROM movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.idwhere 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)- 列出按字母順序，列出哪一演員曾作30次第1主角。
select title,name
from casting join movie m on (m.id=movieid)
join actor a on (a.id=actorid)
where ord=1 and movieid in(
select movieid from casting join actor a on (a.id=actorid) where name='Julie Andrews')- 列出按字母順序，列出哪一演員曾作30次第1主角。
select distinct name
from casting join movie m on (m.id = movieid)
join actor a on (a.id = actorid)
where actorid in
(select actorid from casting where ord = 1
group by actorid
having count(actorid)
>=30)order by name- 列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。
select title,count(title) as c
from casting join movie m on (m.id = movieid)
where movieid in (select id from movie where yr = 1978)
group by title order by c desc- 列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。
select distinct name
from actor join casting on (id = actorid)
where name != 'Art Garfunkel' and movieid in
(select movieid from casting join actor on(id = actorid) where name = 'Art Garfunkel')9. Using Null
- List the teachers who have NULL for their department
select name from teacher
where dept is null- 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)- 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)- 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)- 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- 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)- Use COUNT to show the number of teachers and the number of mobile phones.
select count(teacher.name),count(mobile)
from teacher- 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.name)
FROM teacher right JOIN dept
ON (teacher.dept=dept.id)
group by dept.name- 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 dept=1 or dept =2 then 'Sci'
else 'Art' end
from teacher
或者
select name,
case when dept in (1,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, 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 teacher10. Self join
- How many stops are in the database.
select count(*)
from stops- Find the id value for the stop 'Craiglockhart'
select id
from stops
where name='Craiglockhart'- 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 company='LRT' AND num=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- 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=149- 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'- 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.num=b.num AND a.company=b.company) WHERE a.stop=115 AND b.stop=137- 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.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 stopa.name='Craiglockhart' AND stopb.name='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.
SELECT DISTINCT name, company, num
FROM route JOIN stops ON stop=id
WHERE num IN (SELECT num FROM route JOIN stops ON stop=id WHERE name='Craiglockhart') AND company='LRT'
6173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
