SQL基礎語法練習-2

1 人贊了文章

基礎練習第一部分：SQL基礎語法練習

JOIN

1.Modify it to show the player, teamid, stadium and mdate and for every German goal.

SELECT player,teamid,stadium,mdate FROM game JOIN goal ON (game.id=goal.matchid)where teamid = ger;

JOIN的基本思路可以理解為，將兩個表進行鏈接，每一行的定位條件就是ON後面的語句，將兩個表通過一個共有的列進行鏈接。例如此處就是使用兩個表共有的id數值進行配對。

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

意思是要找出和德國隊比賽中進球的非德國球員。

SELECT DISTINCT(player) FROM goal JOIN game ON (game.id = goal.matchid)WHERE teamid != GER and (team1 = ger or team2 = ger);

為剔除重複進球的球員，我們使用DISTINCT，然後將game表和goal表進行鏈接，然後找出goal中非德國的球員，game中的team1或者team2，意思為包含了德國的比賽。兩個結合就可以過濾出結果。

3.For every match involving POL, show the matchid, date and the number of goals scored.

SELECT matchid,mdate,count(teamid) from game join goal on(game.id=goal.matchid)where team1=pol or team2=pol group by matchid;

4.CASE WHEN

此題使用到了CASE WHEN ，這個相當於Python中的if函數，

當某條件成立，則返回某值，否則返回另一個值。

#語法 CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE def_value END

解題：

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 = idgroup by matchid,team1,team2

此題解題思路就是，將利用case when 將goal中出現了的球隊+1分。

MORE JOIN

1.Obtain the cast list for Casablanca.

cast list = 演員表

or Use movieid=11768,

select name from actor join casting on actor.id=casting.actorid where movieid=(select movie.id from movie where title like casablanca);

本題要找出卡薩布蘭卡電影的演員表，我們可以通過join讓actor表和casting表通過 actorid和id進行鏈接。

2.List the films in which Harrison Ford has appeared.

找出演員「Harrison Ford」演過的電影，本題就需要三個表進行鏈接。

可以在第一個join寫完後，寫第二個join，語法相同。

select movie.title from movie join casting on movieid=movie.idjoin actor on actorid=actor.idwhere actor.name like harrison ford;

3.困難的問題

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.

本題是要找出「John travolta」最忙的一年，找出他一年拍的超過2場電影的那一年。

select yr,count(title) from movie join casting on (movie.id=casting.movieid)join actor on (actor.id=casting.actorid) where actor.name like 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=casting.movieid join actor on actor.id=casting.actorid where actor.name like john travolta group by yr)as t);

要找出最忙的一年，我們就需要查找他在同一年裡電影數量最多的那一年。

所以要按yr分組；讓我們回想一下前面說的，sql會從最裡面的括弧開始執行，所以先執行的是

select yr,count(title) as c from movie join casting on movie.id=casting.movieid join actor on actor.id=casting.actorid where actor.name like john travolta group by yr

這一塊代碼返回的是yr列和c列，c列表示統計了一年中John拍的電影的數量。

c列出來後，使用max(c) 查找這一列中最大值，即John每年拍電影的最大值。

然後完善語法結構，使其將count(title)傳遞給前面select語句。

4.List the film title and the leading actor for all of the films Julie Andrews played in.

Did you get "Little Miss Marker twice"?

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).

提示：電影Little Miss MARKER會出現兩次，因為按照title篩選，會有兩次，但是有一次並沒有 Julie Andrews參演，所以用括弧里的語句篩選出有julie參演的。

select distinct title,actor.name from moviejoin casting on movie.id = casting.movieidjoin actor on actor.id = casting.actoridand ord=1 where movieid in (select mx.id from movie mx join casting on mx.id=casting.movieidjoin actor on actor.id = casting.actorid where name like julie andrews)

此題也是，通過連接進行查找。

5.找出至少擔任過30次及以上的主角的演員名字，按字母排序

Obtain a list, in alphabetical order, of actors whove had at least 30 starring roles.

SELECT actor.name from actor join casting on actor.id=casting.actoridjoin movie on movie.id=casting.movieidwhere ord=1group by namehaving count(title)>=30order by name;

where 是對所有單元進行的條件約束，

having是對分組後的進行篩選。

6.列出按演員數量排序的電影名，先按人數排序，再按電影名稱排。

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

select title,count(actorid) from movie join casting on movie.id = casting.movieidjoin actor on actor.id = casting.actoridwhere yr=1978group by title order by count(actorid)desc,title;

記得在count後面使用desc，因為是要降序排列。

7.列出與『Art Garfunkel』合作過的人的名字

List all the people who have worked with Art Garfunkel.

select name from actorjoin casting on actor.id=casting.actoridwhere movieid in (select movieid from actor join castingon actor.id = actoridwhere name like art garfunkel) and name !=art garfunkel;

合作過 ，即和他在同一部電影里出現過，也就是movieid一樣的。

但是我們不需要知道是什麼電影，所以不用對movie進行鏈接。

並且是要找和他合作過的，他自己就不算，所以要去掉他自己的名字。