Database Hw2

這作業是用Microsoft Access從一個國外實驗室建好的StarWars資料庫裡撈資料。首先一開始光是怎麼用Access就讓我摸很久。

最重要的是就是在Access裡面怎麼叫出SQL查詢介面，直接開只看到像Excel的表格，每個可能的地方都點一下也沒有看到SQL的字樣，直接查google，好像問題太簡單了，而且SQL又是資料庫裡面很常見的字，結果找到一堆不相關的東西，後來才找到原來在這裡：

靠...有夠難找…

接下來就是查詢，課本的範例欄位名稱都是一個單字而已，不過這個資料庫的欄位名稱有空白，又讓我查很久。在google打「欄位名稱 空白」查到的都是怎麼處理空白欄位null的說明…自己試幾種方式：加底線的T.Planet’s_Name、用雙引號括起來T.”Planet’s Name” 、用反斜線T.Planet’s\ Name，通通不行。後來才知道要用中括號T.[Planet’s Name]…

沒人可以問真的是蠻麻煩的…~_~

1. Which planet(s) does R2-D2 go to in movie 3?
SELECT    T.[Planet's Name]
FROM        TimeTable T
WHERE     T.[Character's Name]="R2-D2"   AND   T.Movie=3;

2. How many characters visited Tatooine in movie 3?
SELECT    COUNT(*)
FROM        TimeTable T
WHERE     T.[Planet's Name]="Tatooine"   AND   T.Movie=3;

3. Who visited his/her homeworld in movie 1?
SELECT    DISTINCT C.Name
FROM        TimeTable T, Characters C
WHERE     T.[Planet's Name]=C.Homeworld   AND   T.Movie=1;

4. Find all characters that have been on all neutral planets.
SELECT    C.Name
FROM        Characters C
WHERE     NOT EXISTS
    (SELECT    P.Name
    FROM         Planets  P
    WHERE      P.Affiliation="neutral" AND NOT EXISTS

            (SELECT     T.[Planet's Name]

             FROM         TimeTable T

             WHERE      T.[Planet's Name]=P.Name  AND  T.[Character's Name]=C.Name ));
這種all的SQL查法還是很難想：
全部的中立星球-有被拜訪過的星球=沒有被拜訪過的中立星球
沒有拜訪過「沒有被拜訪過的中立星球」的人=拜訪過全部中立星球的人
另外發現Access不支援SQL語法的INTERSECTS，EXCEPT，UNION

5. Find distinct names of the planets visited by empire affiliated humans.
SELECT     DISTINCT T.[Planet's Name]
FROM         Characters C, TimeTable T
WHERE      C.Affiliation="empire"  AND  T.[Character's Name]=C.Name

6.  For  each  character  and  for  each  neutral  planet,  how  much  time  total  did  the character spend on the planet?
SELECT         T.[Character's Name], P.Name, SUM(T.[Time of Departure] - T.[Time of Arrival] +1) AS TimeAmount

FROM             TimeTable T, Planets P
WHERE          T.[Planet's Name]=P.Name AND P.Affiliation="neutral"
GROUP BY    T.[Character's Name], P.Name

7. On which planets and in which movies has Luke Skywalker been at the same time on the planet as Darth Vader.
SELECT     DISTINCT T1.[Planet's Name], T1.Movie
FROM         TimeTable T1, TimeTable T2
WHERE      T1.[Character's Name]="Luke Skywalker" 

            AND T2.[Character's Name]="Darth Vader"
            AND T1.Movie=T2.Movie
            AND T1.[Planet's Name]=T2.[Planet's Name]
            AND ((T1.[Time of Arrival]<=T2.[Time of Departure]  AND T1.[Time of Departure]>=T2.[Time of Arrival])
                OR
                    (T2.[Time of Arrival]<=T1.[Time of Departure]  AND T2.[Time of Departure]>=T1.[Time of Arrival]) )
這題算時間重複只有算一邊的重疊條件，忘了另外一邊。

8. Find humans that visited desert planets and droids that visited swampy planets. List the movies when it happened and the names of the characters. The output should be sorted by the movie and the character's name.
SELECT       DISTINCT T.Movie, C.Name

FROM           TimeTable T, Characters C, Planets P
WHERE        C.Name=T.[Character's Name]  AND P.Name=T.[Planet's Name]
        AND(( C.Race="Human" AND P.Type="desert") OR (C.Race="Droid" AND P.Type="swamp"))
ORDER BY   T.Movie, C.Name

這題忘記P.Name=T.[Planet’s Name]，結果多一堆不要的東西，還有原來有ORDER BY可以用

9.  For  each  movie  1,  2,  and  3,  which  character(s)  visited  the  highest  number  of planets?
SELECT     T1.Movie, T1.[Character's Name]
FROM
    (SELECT     T1.Movie, MAX(T1.PlanetVisit) AS MaxVisit
      FROM (
            SELECT        T.[Character's Name], T.Movie, COUNT(T.[Planet's Name]) AS PlanetVisit
            FROM            TimeTable T
            GROUP BY   T.[Character's Name], T.Movie
            )AS T1
    GROUP BY T1.Movie
    ) AS T2,
    (SELECT        T.[Character's Name], T.Movie, COUNT(T.[Planet's Name]) AS PlanetVisit
    FROM             TimeTable T
    GROUP BY    T.[Character's Name], T.Movie
    )AS T1
WHERE     T1.Movie=T2.Movie AND T1.PlanetVisit=T2.MaxVisit

這題遇到了2個難處，第一個是算出來的表格，不知道怎麼另外取名，後來才知道原來可以放在FROM裡面然後加AS另外取名。另一個是，算出來了每部電影拜訪最多星球的次數，但是不知道怎麼同時把人名列上去。看了答案才知道要另外再重算一次，然後當做2個表格去比較。問題是最下面的一個表格，不知道為什麼要上面的FROM裡面一樣的東西copy出來而且名字也要取一樣才可以，不copy這個多的T1或是改命名成T3都不行。

10. Which planet(s) have not been visited by any characters in all movies (1, 2 and 3)?
SELECT     P.Name
FROM         Planets P
WHERE      P.Name NOT IN (
        SELECT     T.[Planet's Name]
        FROM         TimeTable T );

11. Find the character(s) that have appeared in all movies.
我原本是這樣寫
SELECT     DISTINCT T.[Character's Name]
FROM         TimeTable T
WHERE     T.Movie=1 AND  T.[Character's Name] IN
    (SELECT     T.[Character's Name]
      FROM         TimeTable T
      WHERE     T.Movie=2 AND  T.[Character's Name] IN
            (SELECT     T.[Character's Name]
             FROM         TimeTable T
             WHERE     T.Movie=3))

不過答案的寫法比較好看
SELECT     DISTINCT T1.[Character's Name]
FROM         TimeTable T1,TimeTable T2,TimeTable T3
WHERE      T1.Movie=T2.Movie AND T1.[Character's Name]=T2.[Character's Name] AND
                    T2.Movie=T3.Movie AND T2.[Character's Name]=T3.[Character's Name]

12. For Luke Skywalker, for each movie that Luke appears in, what is the planet that has the different affiliation with him and that he travels to for the longest length of time?
SELECT     T.Movie, T.[Planet's Name]
FROM         TimeTable T, Planets P, Characters C
WHERE      T.[Character's Name]="Luke Skywalker" AND C.Name="Luke Skywalker" 
            AND T.[Planet's Name]=P.Name AND P.Affiliation <> C.Affiliation
            AND T.[Time of Departure]-T.[Time of Arrival]+1>= ALL(
            SELECT     T2.[Time of Departure]-T2.[Time of Arrival]+1
            FROM         TimeTable T2, Planets P2
            WHERE      T2.[Character's Name]=C.Name
                    AND T2.[Planet's Name]=P2.Name
                    AND P2.Affiliation<>C.Affiliation 
                    AND T.Movie = T2.Movie)

這題一開始想用GROUP BY的方式把3部分開，不過後來知道怎麼寫下去。看答案這種把產生出來的Table放在WHERE裡面比還是不太會。為什麼第二個新增的Table不用FROM Characters C2而可以直接用C的原因還是不知道。

13.  Who  visited  the  planet  Star  Destroyer  earlier  and  leave  later  than  Obi-Wan Kanobi?
SELECT     DISTINCT T2.[Character's Name]
FROM         TimeTable T1, TimeTable T2
WHERE      T1.[Character's Name]="Obi-Wan Kanobi" AND 

                     T1.[Planet's Name]="Star Destroyer" AND T2.[Planet's Name]="Star Destroyer" AND

                     T2.[Time of Arrival]<T1.[Time of Arrival] AND

                     T2.[Time of Departure]>T1.[Time of Departure]

14.  Find  all  characters  that  is  rebels  affiliates  humans  and  his/her  homeworld  is known.
SELECT     C.Name
FROM         Characters C
WHERE      C.Affiliation="rebels" AND C.Race="Human" AND C.Homeworld<>"Unknown"

15. Which planet(s) has been visited by less than three different characters?
本來是這樣寫
SELECT         T2.[Planet's Name]
FROM (

            SELECT     DISTINCT T.[Character's Name], T.[Planet's Name]

             FROM        TimeTable T) AS T2
GROUP BY     T2.[Planet's Name]
HAVING           COUNT (*)<3

不過題目要求能不用DISTINCT就不要用，答案有一個不用DISTINCT的寫法
SELECT     T.[Planet's Name]
FROM (

        SELECT         [T.Planet's Name], [T.Character's Name]
        FROM             TimeTable T 

     GROUP BY     [T.Planet's Name], [T.Character's Name])
GROUP BY     T.[Planet's Name]
HAVING          COUNT(*) < 3
原來用GROUP BY之後就有像是只取GROUP裡面的代表的效果啊...
--
寫完只覺得SQL真是博大精深....