DATA

JOIN

SELECT * FROM game JOIN goal ON (game.id=goal.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 matchid from goal must match id from game.

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Show teamname and the total number of goals scored.

SELECT teamname, COUNT(teamid) FROM eteam
JOIN goal ON id = teamid
GROUP BY teamname

For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

SELECT matchid, mdate, COUNT(teamid) FROM game
JOIN goal ON id = matchid
WHERE teamid = 'GER'
GROUP BY matchid

List every match with the goals scored by each team as shown. 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 and team2

Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).

SELECT player, teamid, COUNT(*) FROM game 
JOIN goal ON matchid = id
WHERE (team1 = "GRE" OR team2 = "GRE")
AND teamid != 'GRE'
GROUP BY player, teamid

Movie Database

More JOIN

Obtain the cast list for the film 'Alien'.

SELECT name FROM actor
JOIN casting ON actor.id = actorid
JOIN movie ON movie.id = movieid
WHERE title = 'Alien'

List the films where 'Harrison Ford' has appeared, but not in the starring role.

SELECT title FROM movie
JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE name = 'Harrison Ford'
AND ord != 1

List the films together with the leading star for all 1962 films.

SELECT title,name FROM movie  # name字段不屬于movie表堤框，但是可以被索引，因為JOIN的關系昆汹，三個表被看作是一個表
JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE yr = 1962
AND ord = 1

Which were the busiest years for 'Rock Hudson', 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 casting ON movie.id = movieid
        JOIN actor   ON actorid = actor.id
WHERE name = 'Rock Hudson'
GROUP BY yr  # 找each
HAVING COUNT(title) > 2

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

SELECT title,name FROM movie
JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE movieid IN (SELECT movieid FROM casting
                  JOIN actor ON actorid = id
                  WHERE name = 'Julie Andrews')
AND ord = 1

Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

SELECT name FROM actor
JOIN casting ON actorid = id
WHERE ord = 1
GROUP BY actorid
HAVING COUNT(ord) >= 15  # count里面可以填ord/actorid/name，因為前面的where已經限制了ord=1
ORDER BY name  # 這個注釋的顏色是隨機的??

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 = movieid
JOIN actor ON actor.id = actorid
WHERE yr = 1978
GROUP BY movieid
ORDER BY COUNT(actorid) DESC, title

GROUP的邏輯：ordered by the number of actors (each movie)

List all the people who have worked with 'Art Garfunkel'.
索引邏輯：1. 找到'Art Garfunkel'參演過的電影; 2. 以電影id為條件索引參演人員name

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

INNER JOIN = JOIN

SELECT name
FROM actor INNER JOIN movie ON actor.id = director
WHERE gross < budget

存疑

SELECT name, COUNT(movieid)
FROM casting JOIN actor ON actorid = actor.id
WHERE name LIKE 'John %'
GROUP BY name ORDER BY 2 DESC  # 這個order by 2指的是按照第二列進行排序

???

SELECT title, yr
FROM movie, casting, actor 
WHERE name = 'Robert De Niro' AND movieid = movie.id AND actorid = actor.id AND ord = 3

NULL

1. IS NULL/IS NOT NULL 關鍵字(var = NULL為錯誤用法)
2. COALESCE(x,y,z...): COALESCE takes any number of arguments and returns the first value that is not null.

Show the teacher name and department name. Use the string 'None' where there is no department.

# 空值以null顯示被替換為了'None'字符串
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(name), COUNT(mobile) FROM teacher

Edinburgh_Buses

SELF JOIN

Show the services from Craiglockhart to London Road without changing routes

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 IN (SELECT id FROM stops
               JOIN route ON stop = id
               WHERE name = 'London Road')

By joining two copies of the stops table we can refer to stops by name rather than by number.

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.

SELECT DISTINCT R1.company, R1.num FROM route R1
JOIN route R2 ON (R1.num = R2.num AND R1.company = R2.company)  # all pairs of stops that share the same service
WHERE R1.stop = '115'
AND R2.stop = '137'

Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'.

SELECT DISTINCT R1.company, R1.num FROM route R1
JOIN route R2 ON (R1.num = R2.num AND R1.company = R2.company)
JOIN stops stops1 ON R1.stop = stops1.id
JOIN stops stops2 ON R2.stop = stops2.id
WHERE stops1.name = 'Craiglockhart'
AND stops2.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 stops2.name, R1.company, R1.num FROM route R1
JOIN route R2 ON (R1.num = R2.num AND R1.company = R2.company)
JOIN stops stops1 ON R1.stop = stops1.id
JOIN stops stops2 ON R2.stop = stops2.id
WHERE stops1.name = 'Craiglockhart'

Find the routes involving two buses that can go from Craiglockhart to Lochend.
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.
從Craiglockhart出發(fā)經過一個中轉站到達Lochend的公車信息
拆解為三步：

1. 列出以Craiglockhart作為起始點婴栽，任意站點為終止點的公車信息

SELECT R1.num, R1.company, R1.stop AS start, R2.stop AS end 
FROM route R1 JOIN route R2 ON 
(R1.num = R2.num AND R1.company = R2.company AND R1.stop != R2.stop)
WHERE R1.stop = (SELECT id 
                 FROM stops WHERE name = 'Craiglockhart')

2. 列出以Lochend為終止點满粗，任意站點為起始點的公車信息

SELECT R1.num, R1.company, R1.stop AS start, R2.stop AS end
FROM route R1 JOIN route R2 ON
(R1.num = R2.num AND R1.company = R2.company AND R1.stop != R2.stop)
WHERE R2.stop = (SELECT id
                 FROM stops WHERE name = 'Lochend')

3. 將兩表連接(排序和答案有點出入)

SELECT DISTINCT Start.num, Start.company, name, End.num, End.company FROM(
SELECT R1.num, R1.company, R1.stop AS start, R2.stop AS end 
FROM route R1 JOIN route R2 ON 
(R1.num = R2.num AND R1.company = R2.company AND R1.stop != R2.stop)
WHERE R1.stop = (SELECT id 
                 FROM stops WHERE name = 'Craiglockhart')
) AS Start
JOIN (
SELECT R1.num, R1.company, R1.stop AS start, R2.stop AS end
FROM route R1 JOIN route R2 ON
(R1.num = R2.num AND R1.company = R2.company AND R1.stop != R2.stop)
WHERE R2.stop = (SELECT id
                 FROM stops WHERE name = 'Lochend')
) AS End
ON Start.end = End.start
JOIN stops ON Start.end = stops.id