SQL學習

05-03

學習資料主要是w3school，codecademy以及sqlzoo上的練習

https://www.codecademy.com/learn/learn-sql?

www.codecademy.com

SELECT basics/zh?

sqlzoo.net

下面是一些sqlzoo里的題目幫助鞏固

1.找出所有首都和其國家名字,而首都是國家名字的延伸。

你應顯示 Mexico City,因它比其國家名字 Mexico 長。

你不應顯示 Luxembourg,因它的首都和國家名相是相同的。

select name,capital from worldwhere capital like concat(name,%) and capital!=name;

concat函數可以拼接2個及以上的字元串

2.顯示國家名字，及其延伸詞，如首都是國家名字的延伸。

SELECT name, REPLACE(capital,name,) FROM world WHERE capital LIKEconcat(name,_%);

replace函數（原字元串，要被替換的字元串，替換字元串）

3.Show the name - but substitute Australasia for Oceania - for countries beginning with N.

SELECT name, CASE WHEN continent=Oceania THEN Australasia ELSE continent ENDFROM world WHERE name LIKE N%;

4.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 IN (Eurasia, Turkey) THEN Europe/Asia WHEN continent = Oceania THEN Australasia WHEN continent = Caribbean THEN CASE WHEN name LIKE B% THEN North America ELSE South America END ELSE continent ENDFROM worldORDER BY name ASC;

5.Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject from nobelwhere yr=1984ORDER BY subject IN (Chemistry,Physics), subject,winner

order by中用in，當subject是Chemistry,Physics時，會返回1，不是的話會返回0，進行ASC排序，因此這兩個值會被放在最後，排完這個之後會再進行subject,winner的排序。

MySQL ORDER BY 排序 IF 及 IN?

blog.csdn.net

6.顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

SELECT name,CONCAT(ROUND(population/(SELECT population FROM world WHERE name=Germany)*100),%)FROM worldWHERE continent=Europe ;

7.哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

select namefrom worldwhere gdp>all(select gdp from world where continent=Europe and gdp>0);

8.在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area FROM world x WHERE area >= ALL (SELECT area FROM world y WHERE y.continent=x.continent AND population>0)；

我們可以用ALL 這個詞對一個列表進行>=或>或<或<=充當比較。例如，你可以用此查詢找到世界上最大的國家(以人口計算)

9.列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

SELECT continent,name FROM world aWHERE name <= ALL(SELECT name from world b WHERE a.continent = b.continent )ORDER by name ;

10.找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

SELECT name,continent,population FROM world xWHERE 25000000 >= ALL(SELECT population FROM world y WHERE y.continent = x.continent);

11.有些國家的人口是同洲份的所有其他國的3倍或以上。列出國家名字name 和洲份 continent。

SELECT name,continent FROM world xWHERE x.population/3 >= ALL(SELECT population FROM world y WHERE y.continent = x.continent AND y.name != x.name);

12.列出有至少100百萬(1億)(100,000,000)人口的洲份。

SELECT continent FROM world xWHERE (SELECT SUM(population) FROM world y WHERE y.continent=x.continent) GROUP BY continentHAVING SUM(population)>=100000000;

13.Instead show the name of all players who scored a goal against Germany.

SELECT DISTINCT player FROM game JOIN goal ON goal.matchid = game.id WHERE (team1=GER OR team2=GER) AND(teamid!=Germany);

14.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,team2;

15.Obtain the cast list for the film Alien

SELECT name FROM actor JOIN casting ONcasting.actorid = actor.idJOIN movie ON movie.id = casting.movieidWHERE title = Alien;

16.List the films in which Harrison Ford has appeared

SELECT title FROM movie JOIN actorON name = Harrison FordJOIN casting ON casting.movieid=movie.id AND casting.actorid = actor.id ;SELECT title FROM movie JOIN casting ONmovie.id=casting.movieid JOIN actor ON casting.actorid = actor.id WHERE name =Harrison Ford;

兩種寫法都可以

17.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.

SELECT yr,COUNT(title) FROM movie JOIN castingON movie.id = casting.movieidJOIN actor ON actor.id = casting.actoridWHERE name = John TravoltaGROUP BY yrHAVING COUNT(title)=(SELECT MAX(a) FROM (SELECT yr,COUNT(title) AS a FROM movie JOIN casting ON movie.id = casting.movieid JOIN actor ON actor.id = casting.actoridWHERE name = John Travolta GROUP BY yr) AS t);

19.List all the people who have worked with Art Garfunkel.

SELECT name FROM actor JOIN castingON casting.actorid = actor.id WHERE movieid IN(SELECT casting.movieid FROM actor JOIN casting ON casting.actorid = actor.id WHERE name = Art Garfunkel ) AND name !=Art Garfunkel;

20.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 ;

coalesce(x,y）如果x不是null，就等於x，如果是null，就等於y

21.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 IN(1,2) THEN SciELSE Art ENDFROM teacher;

22.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;

24. 關於date

SQL Date 函數?

www.w3school.com.cn