sql線上練習網站(http://sqlzoo.cn)答案解析(3)
五:How to do joins(http://sqlzoo.cn/3b.htm)
1a. Show the athelete (who) and the country name for medal winners in 2000
SELECT who, country.name FROM ttms
JOIN country ON (ttms.country=country.id) WHERE games = 2000
1b. Show the who and the color of the medal for the medal winners from 'Sweden'
SELECT who, color FROM ttms
JOIN country ON (ttms.country=country.id) WHERE country.name = 'Sweden'
1c. Show the years in which 'China' won a 'gold' medal
SELECT games FROM ttms JOIN country ON (ttms.country = country.id)
WHERE name = 'China' AND color = 'gold'
2a. Show who won medals in the 'Barcelona' games
SELECT who FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city = 'Barcelona'
2b. Show which city 'Jing Chen' won medals. Show the city and the medal color
SELECT city,color FROM games JOIN ttws ON( ttws.games=games.yr) WHERE who = 'Jing Chen'
2c. Show who won the gold medal and the city
SELECT who, city FROM ttws JOIN games ON(ttws.games = games.yr) WHERE color = 'gold'
3a. Show the games and color of the medal won by the team that includes 'Sen Yan'
SELECT games,color FROM ttmd JOIN team ON(ttmd.team = team.id) WHERE team.name = 'Sen Yan'
3b. Show the 'gold' medal winners in 2004
SELECT name FROM team JOIN ttmd ON(ttmd.team = team.id) WHERE color = 'gold' AND games = 2004
3c. Show the name of each medal winner country 'FRA'
SELECT name FROM team JOIN ttmd ON(ttmd.team = team.id) WHERE country = 'FRA'
六:The JOIN operation(http://sqlzoo.cn/3.htm)
1a. 列出獲得不少於30000 votes(選票)的電影. [顯示 title, votes]
SELECT title, votes FROM movie WHERE votes>=30000
1b. 電影'Citizen Kane'的首映年份
SELECT yr FROM movie WHERE title = 'Citizen Kane'
1c. 列出包含the Police Academy(警校)字樣的title(電影名稱)和 score(得分) films. [The films have a title that is LIKE 'Police Academy%']
SELECT title,score FROM movie Where title LIKE 'Police Academy%'
1d. 列出所有the Star Trek movies(星際系列電影),顯示title(電影標題)和score(得分). 按電影的發行 yr(年份)排序
SELECT title,score FROM movie WHERE title LIKE '%Star Trek%' ORDER BY yr ASC
1e. 列出名稱中包含'Dog'的電影名和得分
SELECT title,score FROM movie WHERE title LIKE '%Dog%'
2a. 列出id為 1, 2, 3的電影的名稱
SELECT title FROM movie WHERE id in (1,2,3)
2b. 電影'Glenn Close' 的ID號是多少?
SELECT id FROM actor WHERE name= 'Glenn Close'
2c. 電影'Casablanca' 的ID號是多少?
SELECT id FROM movie WHERE title = 'Casablanca'
3a. 顯示id=1的電影'Star Wars'(星球大戰)中演員(角色)名單
SELECT name FROM casting, actor WHERE movieid=1 AND actorid=actor.id
or: SELECT name FROM movie, casting, actor WHERE movie.title = 'Star Wars' AND movie.id = casting.movieid AND casting.actorid = actor.id
3b. 顯示電影'Alien'的演員(角色)名單
SELECT name FROM actor, casting, movie
WHERE movie.title = 'Alien' AND movie.id = casting.movieid AND casting.actorid = actor.id
3c. 顯示有'Harrison Ford'參演的電影名稱
SELECT title FROM movie
JOIN actor ON actor.name = 'Harrison Ford'
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id
3d. 顯示有'Harrison Ford'參演的電影名稱,但'Harrison Ford'在該影片中不是主角(即在角色表中不是排名第一)
SELECT title FROM movie
JOIN actor ON actor.name = 'Harrison Ford'
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id AND casting.ord != 1
3e. 顯示1962年發行的電影以及該影片中主角(即在角色表中排名第一)
SELECT title,name FROM actor
JOIN movie ON movie.yr = 1962
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id AND casting.ord = 1
4a. 'John Travolta'哪一年最忙? 顯示出他每年的出演電影數量
SELECT yr,COUNT(title) FROM movie
JOIN actor ON actor.name = 'John Travolta'
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id
GROUP BY yr
4b. 列出有'Julie Andrews'參演的所有電影名稱以及該片的主角
SELECT title, name FROM movie, casting, actor WHERE casting.movieid = movie.id AND casting.actorid = actor.id AND casting.ord = 1 AND casting.movieid IN (SELECT movieid FROM casting, actor WHERE casting.actorid = actor.id AND actor.name = 'Julie Andrews')
4c. 列出那些最起碼在10部電影中出任過主角的演員
SELECT name FROM casting
JOIN actor ON casting.actorid = actor.id WHERE casting.ord=1
GROUP BY name HAVING COUNT(movieid)>=10
or: SELECT name FROM actor,casting,movie
WHERE casting.actorid = actor.id AND casting.ord = 1 AND movie.id = casting.movieid
GROUP BY name HAVING COUNT(casting.movieid)>=10
4d. 按演員人數的多少依次列出1978 的電影以及演員數.人數最多排在最前
SELECT title,count(actorid) FROM movie, casting WHERE casting.movieid = movie.id AND movie.yr = 1978
GROUP BY title ORDER BY 2 DESC
4e. 列出所有和'Art Garfunkel'合作過的演員(不要列出'Art Garfunkel'他自己)
SELECT DISTINCT name FROM actor,casting WHERE actor.id = casting.actorid
AND casting.movieid IN (SELECT movieid FROM casting,actor
WHERE casting.actorid = actor.id AND actor.name = 'Art Garfunkel')
AND name != 'Art Garfunkel'
1a. Show the athelete (who) and the country name for medal winners in 2000
SELECT who, country.name FROM ttms
JOIN country ON (ttms.country=country.id) WHERE games = 2000
1b. Show the who and the color of the medal for the medal winners from 'Sweden'
SELECT who, color FROM ttms
JOIN country ON (ttms.country=country.id) WHERE country.name = 'Sweden'
1c. Show the years in which 'China' won a 'gold' medal
SELECT games FROM ttms JOIN country ON (ttms.country = country.id)
WHERE name = 'China' AND color = 'gold'
2a. Show who won medals in the 'Barcelona' games
SELECT who FROM ttws JOIN games ON (ttws.games=games.yr) WHERE city = 'Barcelona'
2b. Show which city 'Jing Chen' won medals. Show the city and the medal color
SELECT city,color FROM games JOIN ttws ON( ttws.games=games.yr) WHERE who = 'Jing Chen'
2c. Show who won the gold medal and the city
SELECT who, city FROM ttws JOIN games ON(ttws.games = games.yr) WHERE color = 'gold'
3a. Show the games and color of the medal won by the team that includes 'Sen Yan'
SELECT games,color FROM ttmd JOIN team ON(ttmd.team = team.id) WHERE team.name = 'Sen Yan'
3b. Show the 'gold' medal winners in 2004
SELECT name FROM team JOIN ttmd ON(ttmd.team = team.id) WHERE color = 'gold' AND games = 2004
3c. Show the name of each medal winner country 'FRA'
SELECT name FROM team JOIN ttmd ON(ttmd.team = team.id) WHERE country = 'FRA'
六:The JOIN operation(http://sqlzoo.cn/3.htm)
1a. 列出獲得不少於30000 votes(選票)的電影. [顯示 title, votes]
SELECT title, votes FROM movie WHERE votes>=30000
1b. 電影'Citizen Kane'的首映年份
SELECT yr FROM movie WHERE title = 'Citizen Kane'
1c. 列出包含the Police Academy(警校)字樣的title(電影名稱)和 score(得分) films. [The films have a title that is LIKE 'Police Academy%']
SELECT title,score FROM movie Where title LIKE 'Police Academy%'
1d. 列出所有the Star Trek movies(星際系列電影),顯示title(電影標題)和score(得分). 按電影的發行 yr(年份)排序
SELECT title,score FROM movie WHERE title LIKE '%Star Trek%' ORDER BY yr ASC
1e. 列出名稱中包含'Dog'的電影名和得分
SELECT title,score FROM movie WHERE title LIKE '%Dog%'
2a. 列出id為 1, 2, 3的電影的名稱
SELECT title FROM movie WHERE id in (1,2,3)
2b. 電影'Glenn Close' 的ID號是多少?
SELECT id FROM actor WHERE name= 'Glenn Close'
2c. 電影'Casablanca' 的ID號是多少?
SELECT id FROM movie WHERE title = 'Casablanca'
3a. 顯示id=1的電影'Star Wars'(星球大戰)中演員(角色)名單
SELECT name FROM casting, actor WHERE movieid=1 AND actorid=actor.id
or: SELECT name FROM movie, casting, actor WHERE movie.title = 'Star Wars' AND movie.id = casting.movieid AND casting.actorid = actor.id
3b. 顯示電影'Alien'的演員(角色)名單
SELECT name FROM actor, casting, movie
WHERE movie.title = 'Alien' AND movie.id = casting.movieid AND casting.actorid = actor.id
3c. 顯示有'Harrison Ford'參演的電影名稱
SELECT title FROM movie
JOIN actor ON actor.name = 'Harrison Ford'
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id
3d. 顯示有'Harrison Ford'參演的電影名稱,但'Harrison Ford'在該影片中不是主角(即在角色表中不是排名第一)
SELECT title FROM movie
JOIN actor ON actor.name = 'Harrison Ford'
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id AND casting.ord != 1
3e. 顯示1962年發行的電影以及該影片中主角(即在角色表中排名第一)
SELECT title,name FROM actor
JOIN movie ON movie.yr = 1962
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id AND casting.ord = 1
4a. 'John Travolta'哪一年最忙? 顯示出他每年的出演電影數量
SELECT yr,COUNT(title) FROM movie
JOIN actor ON actor.name = 'John Travolta'
JOIN casting ON movie.id = casting.movieid AND casting.actorid = actor.id
GROUP BY yr
4b. 列出有'Julie Andrews'參演的所有電影名稱以及該片的主角
SELECT title, name FROM movie, casting, actor WHERE casting.movieid = movie.id AND casting.actorid = actor.id AND casting.ord = 1 AND casting.movieid IN (SELECT movieid FROM casting, actor WHERE casting.actorid = actor.id AND actor.name = 'Julie Andrews')
4c. 列出那些最起碼在10部電影中出任過主角的演員
SELECT name FROM casting
JOIN actor ON casting.actorid = actor.id WHERE casting.ord=1
GROUP BY name HAVING COUNT(movieid)>=10
or: SELECT name FROM actor,casting,movie
WHERE casting.actorid = actor.id AND casting.ord = 1 AND movie.id = casting.movieid
GROUP BY name HAVING COUNT(casting.movieid)>=10
4d. 按演員人數的多少依次列出1978 的電影以及演員數.人數最多排在最前
SELECT title,count(actorid) FROM movie, casting WHERE casting.movieid = movie.id AND movie.yr = 1978
GROUP BY title ORDER BY 2 DESC
4e. 列出所有和'Art Garfunkel'合作過的演員(不要列出'Art Garfunkel'他自己)
SELECT DISTINCT name FROM actor,casting WHERE actor.id = casting.actorid
AND casting.movieid IN (SELECT movieid FROM casting,actor
WHERE casting.actorid = actor.id AND actor.name = 'Art Garfunkel')
AND name != 'Art Garfunkel'
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-616528/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sql線上練習網站(http://sqlzoo.cn)答案解析(1)SQL網站HTTP
- sql線上練習網站(http://sqlzoo.cn)答案解析(2)SQL網站HTTP
- SQL學習和練習的好地方(http://sqlzoo.cn)SQLHTTP
- 19個練習黑客技術的線上網站黑客網站
- 推薦幾個SQL線上學習網站SQL學習網站
- sql 語句練習(3) In MySQLMySql
- [MySQL光速入門]009 SQL強化練習答案MySql
- sql 練習SQL
- 資料庫精通練習題答案資料庫
- sqlzoo練習答案--SELECT names/zhSQL
- pl/sql練習SQL
- pl/sql 練習SQL
- SQL 練習題SQL
- SQL練習題SQL
- Linux認證網路管理方面練習題《含答案》(轉)Linux
- java學習室SCJP線上練習Java
- Hive SQL必刷練習題:同時線上人數問題(*****)HiveSQL
- Python練習題(三)--視訊展示網站Python網站
- oracle sql練習題OracleSQL
- Opencv第三章練習題答案OpenCV
- python練習題解析Python
- 爬蟲:HTTP請求與HTML解析(爬取某乎網站)爬蟲HTTPHTML網站
- 5個免費練習黑客技術的網站!黑客網站
- SQL練習00015SQL
- SQL練習00012SQL
- sql 語句練習 In MySQLMySql
- sql 語句練習(2)SQL
- 團隊練習3
- C#網路應用程式設計基礎練習題與答案(1)C#程式設計
- 網站qq線上客服網站
- 學習 golang 中,寫了個 golang http client 練練手GolangHTTPclient
- 幾個有趣的線上python 程式碼學習網站Python學習網站
- 【CCCC】PAT : 團體程式設計天梯賽-練習集 L3 答案(01-23)程式設計
- SQL Server遊標使用練習SQLServer
- Oracle入門查詢練習題及參考答案Oracle
- go-admin線上開發平臺學習-3[細節解析]Go
- ThinkPHP3.2練手網站PHP網站
- CSS3 animation 練習CSSS3