透明藍樂摸

上課時間:2005/11/24(四) , 11/25(五) AM 9:30 –PM 4:30
上課地點:台北市民生東路二段 149 號 8 F
講師:Kuo-Rong Peng老師
聯絡電話:02-2502-6000*172 Sammi

授課內容:
11/24(四)AM 9:30 –PM 4:30
Basel II overview
SAS 9 overview

11/25(五)PM 1:00 –PM 4:30
ETL overview

交通資訊:
停車位:民生東路上停車格.建國高架橋下
捷運:捷運雙連站轉乘518(救國團二)
公車:民生松江路口279.290.109.203.214.222.226.280.41.49.5.505.527.642.72

上課時間:2005/11/24(四) , 11/25(五) AM 9:30 –PM 4:30
上課地點:台北市民生東路二段 149 號 8 F
講師:Kuo-Rong Peng老師
聯絡電話:02-2502-6000*172 Sammi

授課內容:
11/24(四)AM 9:30 –PM 4:30
Basel II overview
SAS 9 overview

11/25(五)PM 1:00 –PM 4:30
ETL overview

交通資訊:
停車位:民生東路上停車格.建國高架橋下
捷運:捷運雙連站轉乘518(救國團二)
公車:民生松江路口279.290.109.203.214.222.226.280.41.49.5.505.527.642.72

Taipei Metro Hash Run 台北捷運兔晚上跑步
　
日期: 2005年11月23日(星期三) 19:45 兔子開跑

地點：捷運板南線.江子翠站 出口 4

兔子: 李財福 (F.M.D.)


DATE : 2005.NOV. 23 (WED) 19:45 HARE RUN

SITE : MRT Bannan Line Jiangzicai Station Exit 4

HARE : 李財福 (F.M.D.)

Taipei Metro Hash Run 台北捷運兔晚上跑步
　
日期: 2005年11月23日(星期三) 19:45 兔子開跑

地點：捷運板南線.江子翠站 出口 4

兔子: 李財福 (F.M.D.)


DATE : 2005.NOV. 23 (WED) 19:45 HARE RUN

SITE : MRT Bannan Line Jiangzicai Station Exit 4

HARE : 李財福 (F.M.D.)

活動型態 : 郊山
活動地 : 進入-桃園縣
活動日期 : 11/13(日) 共1天

領隊 : 楊文騫,郭安琪
活動費用 : 500 (新台幣)
報名期限 : 11/05(六) 23:59 為確保您的權益，請務必於報名期限前報名
報名電話 : 02-26295724
報名 : 需報名
難度 : 適合大眾參加
集合地點時間 : 11/13 AM06:00台北車站東三門
網站 :山茼蒿戶外休閒協會

說明及備註
去過北插也ㄧ定要走走南插，雖為陡上路段但山頂展望良好，為北部完整的中海拔森林生態系，闊葉林林相完整，野生動物棲地環境優良，秋天可賞台灣山毛櫸。

Tags: mountain

活動型態 : 郊山
活動地 : 進入-桃園縣
活動日期 : 11/13(日) 共1天

領隊 : 楊文騫,郭安琪
活動費用 : 500 (新台幣)
報名期限 : 11/05(六) 23:59 為確保您的權益，請務必於報名期限前報名
報名電話 : 02-26295724
報名 : 需報名
難度 : 適合大眾參加
集合地點時間 : 11/13 AM06:00台北車站東三門
網站 :山茼蒿戶外休閒協會

說明及備註
去過北插也ㄧ定要走走南插，雖為陡上路段但山頂展望良好，為北部完整的中海拔森林生態系，闊葉林林相完整，野生動物棲地環境優良，秋天可賞台灣山毛櫸。

Tags: mountain

Taipei Metro Hash Run 台北晚上特別跑步
日期: 11月09日(星期三) 19:45 兔子開跑
地點：捷運新店線.景美站2號 出口
兔子:BVS (Campbell)
DATE : NOV. 09 (WED) 19:45 HARE RUN
SITE : MRT Xindian Line Jingmei Station Exit 2
HARE : BVS (Campbell)
活動內容:每次夜跑都是間隔兩個禮拜的星期三晚上19:30,地點都是捷運站集合

捷兔簡介:這是一個外國傳來的休閒跑步團體──捷兔俱樂部（Hash House Harriers）。1930年代的末期，一群在馬來西亞的英國殖民地官員，相約在週末跑步，然後回到Hash House吃東西，Harriers這個字在古英文?，就是「跑者」的意思。

　隨著英國殖民地官員遷移到亞洲其他國家，這個運動散布出去。現在世界各地都有Hash，形式大同小異，都是先找到一條路徑跑步，跑回來以後，就喝酒、講笑話，再一起吃晚餐。每個週末固定玩一次，風雨無阻，即使是颱風天也照常舉行。

　1973年，駐琉球的美軍把Hash這項活動帶來台灣，和在台美商成立了台灣的「捷兔俱樂部」。現在的成員來自世界各地，有美國、加拿大、英國、德國、澳洲、紐西蘭、巴西、智利和土耳其，也有一小部份的台灣人參加，像極了小型的聯合國。

Taipei Metro Hash Run 台北晚上特別跑步
日期: 11月09日(星期三) 19:45 兔子開跑
地點：捷運新店線.景美站2號 出口
兔子:BVS (Campbell)
DATE : NOV. 09 (WED) 19:45 HARE RUN
SITE : MRT Xindian Line Jingmei Station Exit 2
HARE : BVS (Campbell)
活動內容:每次夜跑都是間隔兩個禮拜的星期三晚上19:30,地點都是捷運站集合

捷兔簡介:這是一個外國傳來的休閒跑步團體──捷兔俱樂部（Hash House Harriers）。1930年代的末期，一群在馬來西亞的英國殖民地官員，相約在週末跑步，然後回到Hash House吃東西，Harriers這個字在古英文?，就是「跑者」的意思。

　隨著英國殖民地官員遷移到亞洲其他國家，這個運動散布出去。現在世界各地都有Hash，形式大同小異，都是先找到一條路徑跑步，跑回來以後，就喝酒、講笑話，再一起吃晚餐。每個週末固定玩一次，風雨無阻，即使是颱風天也照常舉行。

　1973年，駐琉球的美軍把Hash這項活動帶來台灣，和在台美商成立了台灣的「捷兔俱樂部」。現在的成員來自世界各地，有美國、加拿大、英國、德國、澳洲、紐西蘭、巴西、智利和土耳其，也有一小部份的台灣人參加，像極了小型的聯合國。

這是我的DVD清單
有的是全新的
有的是二手DVD
有的是網路上面抓的
持續更新中。

更新日期：2005/11/06
中文片名：哈利波特：神祕的魔法石.英文片名：Harry Potter and the Sorcerer's Stone
中文片名：哈利波特3：阿茲卡班的逃犯.英文片名：Harry Potter and the Prisoner of Azkaban
中文片名：花樣年華（國片）.英文片名：in the mood for love
中文片名：蘭花賊.英文片名：Adaptation
中文片名：安德魯洛伊韋伯之歌劇魅影.英文片名：Andrew Lloyd Webber's The Phantom of the Opera
中文片名：大智若魚.英文片名：Big Fish
中文片名：旭日東昇.英文片名：Rising Sun
中文片名：親親小媽.英文片名：Stepmom

更新日期：2005/04/04

2005/04/02
中文片名：機械公敵.英文片名：I Rebot
中文片名：麥兜故事：My Life As McDull(Mak dau goo si)

2005/02/27
中文片名：機動殺人. 英文片名：Taking Lives
中文片名：慾望城市.英文片名：State and Main
中文片名：完美女人. 英文片名： CQ
中文片名：天才雷普利.英文片名：The Talented Mr. Ripley
中文片名：人性污點. 英文片名： The Human Stain

2005/02/23
中文片名：凡赫辛.英文片名：Van Helsing
中文片名：特洛伊.木馬屠城.英文片名：Troy
中文片名：鬼影人.英文片名：Gothika
中文片名：晴空戰士.英文片名：Wonderful Days:韓國動畫

2005/02以前
中文片名：弄假成真. 英文片名：Grownups *
中文片名：北京樂與路. 英文片名：Beijing Rocks
中文片名：夢遊夏威夷. 英文片名：Holiday Dreaming:台灣 *
中文片名：異次元殺陣. 英文片名：CUBE
中文片名：新天堂樂園 完整版. 英文片名：Cinema Paradiso
中文片名：天降奇兵. 英文片名：The League of Extraordinary Gentlemen
中文片名：蝴蝶效應. 英文片名：The Butterfly Effect
中文片名：神秘河流. 英文片名：Mystic River
中文片名：當我們黏在一起. 英文片名：Stuck to You
中文片名：寶萊塢生死戀. 英文片名：Devdas：印度
中文片名：愛是你愛是我. 英文片名：Love Actually
中文片名：航越地平線. 英文片名：DEAD CALM
中文片名：星際大戰Ⅳ,Ⅴ,Ⅵ. 英文片名：Star War Ⅳ,Ⅴ,Ⅵ
星際大戰第四部曲新希望
星際大戰第五部曲帝國大反擊
星際大戰第六部曲絕地大反攻
中文片名：救命. 英文片名：KOMA
中文片名：人民公廁. 英文片名：Public Toilet：香港
中文片名：你那邊幾點？. 英文片名：What time is it there?：台灣
中文片名：燕尾蝶. 英文片名：SwallowTail Butterfly：日本
中文片名：單車失竊記. 英文片名：The Bicycle Thief
中文片名：霹靂嬌娃.全速進攻. 英文片名：Charlie's Angels-Full Throttle
中文片名：海底總動員. 英文片名：Finding Nemo
中文片名：少林足球. 英文片名：Shaolin Soccer：香港
中文片名：咒怨. 英文片名：JU-ON:The Grudge：日本
中文片名：海上鋼琴師. 英文片名：The Lengend of 1900
中文片名：單刀直入. 英文片名：A Man Apart
中文片名：絕命鈴聲. 英文片名：PHONEbooth
中文片名：飆風再起. 英文片名：2 FAST 2 FURIOUS
中文片名：紐約黑幫. 英文片名：GANGS OF NEW YORK
中文片名：黑色追緝令. 英文片名：Pulp Fiction
中文片名：芝加哥. 英文片名：CHICAGO
中文片名：神鬼奇航：鬼盜船魔咒. 英文片名：Pirates of the Caribbean : The Curseof the Black Pearl
中文片名：永遠的一天. 英文片名：Eternity And A Day
中文片名：王牌大間諜. 英文片名：AUSTIN POWERS *
中文片名：偷天換日. 英文片名：THE ITALIAN JOB
中文片名：X戰警2. 英文片名：X2
中文片名：悄悄告訴她. 英文片名：talk to her
中文片名：魔戒三部曲：王者再臨. 英文片名：The Lord of Rings : THE RETURN OF THE KING
中文片名：駭客任務完結篇：最後戰役. 英文片名：The Matrix: Revolutions
中文片名：愛你在心眼難開. 英文片名：SOMETHING'S GOTTA GIVE
中文片名：戰地琴人. 英文片名：THE PIANIST
中文片名：秘窗. 英文片名：SECRET WINDOW
中文片名：神鬼交鋒. 英文片名：catch me if you can
中文片名：非關女孩. 英文片名：pieces of APRIL

這是我的DVD清單
有的是全新的
有的是二手DVD
有的是網路上面抓的
持續更新中。

更新日期：2005/11/06
中文片名：哈利波特：神祕的魔法石.英文片名：Harry Potter and the Sorcerer's Stone
中文片名：哈利波特3：阿茲卡班的逃犯.英文片名：Harry Potter and the Prisoner of Azkaban
中文片名：花樣年華（國片）.英文片名：in the mood for love
中文片名：蘭花賊.英文片名：Adaptation
中文片名：安德魯洛伊韋伯之歌劇魅影.英文片名：Andrew Lloyd Webber's The Phantom of the Opera
中文片名：大智若魚.英文片名：Big Fish
中文片名：旭日東昇.英文片名：Rising Sun
中文片名：親親小媽.英文片名：Stepmom

更新日期：2005/04/04

2005/04/02
中文片名：機械公敵.英文片名：I Rebot
中文片名：麥兜故事：My Life As McDull(Mak dau goo si)

2005/02/27
中文片名：機動殺人. 英文片名：Taking Lives
中文片名：慾望城市.英文片名：State and Main
中文片名：完美女人. 英文片名： CQ
中文片名：天才雷普利.英文片名：The Talented Mr. Ripley
中文片名：人性污點. 英文片名： The Human Stain

2005/02/23
中文片名：凡赫辛.英文片名：Van Helsing
中文片名：特洛伊.木馬屠城.英文片名：Troy
中文片名：鬼影人.英文片名：Gothika
中文片名：晴空戰士.英文片名：Wonderful Days:韓國動畫

2005/02以前
中文片名：弄假成真. 英文片名：Grownups *
中文片名：北京樂與路. 英文片名：Beijing Rocks
中文片名：夢遊夏威夷. 英文片名：Holiday Dreaming:台灣 *
中文片名：異次元殺陣. 英文片名：CUBE
中文片名：新天堂樂園 完整版. 英文片名：Cinema Paradiso
中文片名：天降奇兵. 英文片名：The League of Extraordinary Gentlemen
中文片名：蝴蝶效應. 英文片名：The Butterfly Effect
中文片名：神秘河流. 英文片名：Mystic River
中文片名：當我們黏在一起. 英文片名：Stuck to You
中文片名：寶萊塢生死戀. 英文片名：Devdas：印度
中文片名：愛是你愛是我. 英文片名：Love Actually
中文片名：航越地平線. 英文片名：DEAD CALM
中文片名：星際大戰Ⅳ,Ⅴ,Ⅵ. 英文片名：Star War Ⅳ,Ⅴ,Ⅵ
星際大戰第四部曲新希望
星際大戰第五部曲帝國大反擊
星際大戰第六部曲絕地大反攻
中文片名：救命. 英文片名：KOMA
中文片名：人民公廁. 英文片名：Public Toilet：香港
中文片名：你那邊幾點？. 英文片名：What time is it there?：台灣
中文片名：燕尾蝶. 英文片名：SwallowTail Butterfly：日本
中文片名：單車失竊記. 英文片名：The Bicycle Thief
中文片名：霹靂嬌娃.全速進攻. 英文片名：Charlie's Angels-Full Throttle
中文片名：海底總動員. 英文片名：Finding Nemo
中文片名：少林足球. 英文片名：Shaolin Soccer：香港
中文片名：咒怨. 英文片名：JU-ON:The Grudge：日本
中文片名：海上鋼琴師. 英文片名：The Lengend of 1900
中文片名：單刀直入. 英文片名：A Man Apart
中文片名：絕命鈴聲. 英文片名：PHONEbooth
中文片名：飆風再起. 英文片名：2 FAST 2 FURIOUS
中文片名：紐約黑幫. 英文片名：GANGS OF NEW YORK
中文片名：黑色追緝令. 英文片名：Pulp Fiction
中文片名：芝加哥. 英文片名：CHICAGO
中文片名：神鬼奇航：鬼盜船魔咒. 英文片名：Pirates of the Caribbean : The Curseof the Black Pearl
中文片名：永遠的一天. 英文片名：Eternity And A Day
中文片名：王牌大間諜. 英文片名：AUSTIN POWERS *
中文片名：偷天換日. 英文片名：THE ITALIAN JOB
中文片名：X戰警2. 英文片名：X2
中文片名：悄悄告訴她. 英文片名：talk to her
中文片名：魔戒三部曲：王者再臨. 英文片名：The Lord of Rings : THE RETURN OF THE KING
中文片名：駭客任務完結篇：最後戰役. 英文片名：The Matrix: Revolutions
中文片名：愛你在心眼難開. 英文片名：SOMETHING'S GOTTA GIVE
中文片名：戰地琴人. 英文片名：THE PIANIST
中文片名：秘窗. 英文片名：SECRET WINDOW
中文片名：神鬼交鋒. 英文片名：catch me if you can
中文片名：非關女孩. 英文片名：pieces of APRIL

SQL Reference
Examples of subselects


Example A1: Select all columns and rows from the EMPLOYEE table.

SELECT * FROM EMPLOYEE

Example A2: Join the EMP_ACT and EMPLOYEE tables, select all the columns from the EMP_ACT table and add the employee's surname (LASTNAME) from the EMPLOYEE table to each row of the result.

SELECT EMP_ACT.*, LASTNAME
FROM EMP_ACT, EMPLOYEE
WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO

Example A3: Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO), employee surname (LASTNAME), department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the DEPARTMENT table) and department name (DEPTNAME) of all employees who were born (BIRTHDATE) earlier than 1930.

SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE WORKDEPT = DEPTNO
AND YEAR(BIRTHDATE)
Example A4: Select the job (JOB) and the minimum and maximum salaries (SALARY) for each group of rows with the same job code in the EMPLOYEE table, but only for groups with more than one row and with a maximum salary greater than or equal to 27000.

SELECT JOB, MIN(SALARY), MAX(SALARY)
FROM EMPLOYEE
GROUP BY JOB
HAVING COUNT(*) > 1
AND MAX(SALARY) >= 27000

Example A5: Select all the rows of EMP_ACT table for employees (EMPNO) in department (WORKDEPT) 'E11'. (Employee department numbers are shown in the EMPLOYEE table.)

SELECT *
FROM EMP_ACT
WHERE EMPNO IN
(SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT = 'E11')

Example A6: From the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary for all employees.

SELECT WORKDEPT, MAX(SALARY)
FROM EMPLOYEE
GROUP BY WORKDEPT
HAVING MAX(SALARY) FROM EMPLOYEE)

The subquery in the HAVING clause would only be executed once in this example.

Example A7: Using the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary in all other departments.

SELECT WORKDEPT, MAX(SALARY)
FROM EMPLOYEE EMP_COR
GROUP BY WORKDEPT
HAVING MAX(SALARY) FROM EMPLOYEE
WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)

In contrast to Example A6, the subquery in the HAVING clause would need to be executed for each group.

Example A8: Determine the employee number and salary of sales representatives along with the average salary and head count of their departments.

This query must first create a nested table expression (DINFO) in order to get the AVGSALARY and EMPCOUNT columns, as well as the DEPTNO column that is used in the WHERE clause.

SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT
FROM EMPLOYEE THIS_EMP,
(SELECT OTHERS.WORKDEPT AS DEPTNO,
AVG(OTHERS.SALARY) AS AVGSALARY,
COUNT(*) AS EMPCOUNT
FROM EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT
) AS DINFO
WHERE THIS_EMP.JOB = 'SALESREP'
AND THIS_EMP.WORKDEPT = DINFO.DEPTNO

Using a nested table expression for this case saves the overhead of creating the DINFO view as a regular view. During statement preparation, accessing the catalog for the view is avoided and, because of the context of the rest of the query, only the rows for the department of the sales representatives need to be considered by the view.

Example A9: Display the average education level and salary for 5 random groups of employees.

This query requires the use of a nested table expression to set a random value for each employee so that it can subsequently be used in the GROUP BY clause.

SELECT RANDID , AVG(EDLEVEL), AVG(SALARY)
FROM ( SELECT EDLEVEL, SALARY, INTEGER(RAND()*5) AS RANDID
FROM EMPLOYEE
) AS EMPRAND
GROUP BY RANDID

SQL Reference
Examples of subselects


Example A1: Select all columns and rows from the EMPLOYEE table.

SELECT * FROM EMPLOYEE

Example A2: Join the EMP_ACT and EMPLOYEE tables, select all the columns from the EMP_ACT table and add the employee's surname (LASTNAME) from the EMPLOYEE table to each row of the result.

SELECT EMP_ACT.*, LASTNAME
FROM EMP_ACT, EMPLOYEE
WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO

Example A3: Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO), employee surname (LASTNAME), department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the DEPARTMENT table) and department name (DEPTNAME) of all employees who were born (BIRTHDATE) earlier than 1930.

SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE WORKDEPT = DEPTNO
AND YEAR(BIRTHDATE)
Example A4: Select the job (JOB) and the minimum and maximum salaries (SALARY) for each group of rows with the same job code in the EMPLOYEE table, but only for groups with more than one row and with a maximum salary greater than or equal to 27000.

SELECT JOB, MIN(SALARY), MAX(SALARY)
FROM EMPLOYEE
GROUP BY JOB
HAVING COUNT(*) > 1
AND MAX(SALARY) >= 27000

Example A5: Select all the rows of EMP_ACT table for employees (EMPNO) in department (WORKDEPT) 'E11'. (Employee department numbers are shown in the EMPLOYEE table.)

SELECT *
FROM EMP_ACT
WHERE EMPNO IN
(SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT = 'E11')

Example A6: From the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary for all employees.

SELECT WORKDEPT, MAX(SALARY)
FROM EMPLOYEE
GROUP BY WORKDEPT
HAVING MAX(SALARY) FROM EMPLOYEE)

The subquery in the HAVING clause would only be executed once in this example.

Example A7: Using the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary in all other departments.

SELECT WORKDEPT, MAX(SALARY)
FROM EMPLOYEE EMP_COR
GROUP BY WORKDEPT
HAVING MAX(SALARY) FROM EMPLOYEE
WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)

In contrast to Example A6, the subquery in the HAVING clause would need to be executed for each group.

Example A8: Determine the employee number and salary of sales representatives along with the average salary and head count of their departments.

This query must first create a nested table expression (DINFO) in order to get the AVGSALARY and EMPCOUNT columns, as well as the DEPTNO column that is used in the WHERE clause.

SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT
FROM EMPLOYEE THIS_EMP,
(SELECT OTHERS.WORKDEPT AS DEPTNO,
AVG(OTHERS.SALARY) AS AVGSALARY,
COUNT(*) AS EMPCOUNT
FROM EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT
) AS DINFO
WHERE THIS_EMP.JOB = 'SALESREP'
AND THIS_EMP.WORKDEPT = DINFO.DEPTNO

Using a nested table expression for this case saves the overhead of creating the DINFO view as a regular view. During statement preparation, accessing the catalog for the view is avoided and, because of the context of the rest of the query, only the rows for the department of the sales representatives need to be considered by the view.

Example A9: Display the average education level and salary for 5 random groups of employees.

This query requires the use of a nested table expression to set a random value for each employee so that it can subsequently be used in the GROUP BY clause.

SELECT RANDID , AVG(EDLEVEL), AVG(SALARY)
FROM ( SELECT EDLEVEL, SALARY, INTEGER(RAND()*5) AS RANDID
FROM EMPLOYEE
) AS EMPRAND
GROUP BY RANDID

SQL Reference
Examples of Joins


Example B1: This example illustrates the results of the various joins using tables J1 and J2. These tables contain rows as shown.

SELECT * FROM J1

W X
--- ------
A 11
B 12
C 13

SELECT * FROM J2

Y Z
--- ------
A 21
C 22
D 23


The following query does an inner join of J1 and J2 matching the first column of both tables.

SELECT * FROM J1 INNER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
C 13 C 22

In this inner join example the row with column W='C' from J1 and the row with column Y='D' from J2 are not included in the result because they do not have a match in the other table. Note that the following alternative form of an inner join query produces the same result.

SELECT * FROM J1, J2 WHERE W=Y

The following left outer join will get back the missing row from J1 with nulls for the columns of J2. Every row from J1 is included.

SELECT * FROM J1 LEFT OUTER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
B 12 - -
C 13 C 22

The following right outer join will get back the missing row from J2 with nulls for the columns of J1. Every row from J2 is included.

SELECT * FROM J1 RIGHT OUTER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
C 13 C 22
- - D 23

The following full outer join will get back the missing rows from both J1 and J2 with nulls where appropriate. Every row from both J1 and J2 is included.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
C 13 C 22
- - D 23
B 12 - -

Example B2: Using the tables J1 and J2 from the previous example, examine what happens when and additional predicate is added to the search condition.

SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=13

W X Y Z
--- ------ --- ------
C 13 C 22

The additional condition caused the inner join to select only 1 row compared to the inner join in Example B1.

Notice what the impact of this is on the full outer join.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=13

W X Y Z
--- ------ --- ------
- - A 21
C 13 C 22
- - D 23
A 11 - -
B 12 - -

The result now has 5 rows (compared to 4 without the additional predicate) since there was only 1 row in the inner join and all rows of both tables must be returned.

The following query illustrates that placing the same additional predicate in WHERE clause has completely different results.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y
WHERE X=13

W X Y Z
--- ------ --- ------
C 13 C 22

The WHERE clause is applied after the intermediate result of the full outer join. This intermediate result would be the same as the result of the full outer join query in Example B1. The WHERE clause is applied to this intermediate result and eliminates all but the row that has X=13. Choosing the location of a predicate when performing outer joins can have significant impact on the results. Consider what happens if the predicate was X=12 instead of X=13. The following inner join returns no rows.

SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=12

Hence, the full outer join would return 6 rows, 3 from J1 with nulls for the columns of J2 and 3 from J2 with nulls for the columns of J1.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=12

W X Y Z
--- ------ --- ------
- - A 21
- - C 22
- - D 23
A 11 - -
B 12 - -
C 13 - -

If the additional predicate is in the WHERE clause instead, 1 row is returned.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y
WHERE X=12

W X Y Z
--- ------ --- ------
B 12 - -

Example B3: List every department with the employee number and last name of the manager, including departments without a manager.

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
ON MGRNO = EMPNO

Example B4: List every employee number and last name with the employee number and last name of their manager, including employees without a manager.

SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E LEFT OUTER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO

The inner join determines the last name for any manager identified in the DEPARTMENT table and the left outer join guarantees that each employee is listed even if a corresponding department is not found in DEPARTMENT.

SQL Reference
Examples of Joins


Example B1: This example illustrates the results of the various joins using tables J1 and J2. These tables contain rows as shown.

SELECT * FROM J1

W X
--- ------
A 11
B 12
C 13

SELECT * FROM J2

Y Z
--- ------
A 21
C 22
D 23


The following query does an inner join of J1 and J2 matching the first column of both tables.

SELECT * FROM J1 INNER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
C 13 C 22

In this inner join example the row with column W='C' from J1 and the row with column Y='D' from J2 are not included in the result because they do not have a match in the other table. Note that the following alternative form of an inner join query produces the same result.

SELECT * FROM J1, J2 WHERE W=Y

The following left outer join will get back the missing row from J1 with nulls for the columns of J2. Every row from J1 is included.

SELECT * FROM J1 LEFT OUTER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
B 12 - -
C 13 C 22

The following right outer join will get back the missing row from J2 with nulls for the columns of J1. Every row from J2 is included.

SELECT * FROM J1 RIGHT OUTER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
C 13 C 22
- - D 23

The following full outer join will get back the missing rows from both J1 and J2 with nulls where appropriate. Every row from both J1 and J2 is included.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y

W X Y Z
--- ------ --- ------
A 11 A 21
C 13 C 22
- - D 23
B 12 - -

Example B2: Using the tables J1 and J2 from the previous example, examine what happens when and additional predicate is added to the search condition.

SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=13

W X Y Z
--- ------ --- ------
C 13 C 22

The additional condition caused the inner join to select only 1 row compared to the inner join in Example B1.

Notice what the impact of this is on the full outer join.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=13

W X Y Z
--- ------ --- ------
- - A 21
C 13 C 22
- - D 23
A 11 - -
B 12 - -

The result now has 5 rows (compared to 4 without the additional predicate) since there was only 1 row in the inner join and all rows of both tables must be returned.

The following query illustrates that placing the same additional predicate in WHERE clause has completely different results.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y
WHERE X=13

W X Y Z
--- ------ --- ------
C 13 C 22

The WHERE clause is applied after the intermediate result of the full outer join. This intermediate result would be the same as the result of the full outer join query in Example B1. The WHERE clause is applied to this intermediate result and eliminates all but the row that has X=13. Choosing the location of a predicate when performing outer joins can have significant impact on the results. Consider what happens if the predicate was X=12 instead of X=13. The following inner join returns no rows.

SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=12

Hence, the full outer join would return 6 rows, 3 from J1 with nulls for the columns of J2 and 3 from J2 with nulls for the columns of J1.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=12

W X Y Z
--- ------ --- ------
- - A 21
- - C 22
- - D 23
A 11 - -
B 12 - -
C 13 - -

If the additional predicate is in the WHERE clause instead, 1 row is returned.

SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y
WHERE X=12

W X Y Z
--- ------ --- ------
B 12 - -

Example B3: List every department with the employee number and last name of the manager, including departments without a manager.

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
ON MGRNO = EMPNO

Example B4: List every employee number and last name with the employee number and last name of their manager, including employees without a manager.

SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E LEFT OUTER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO

The inner join determines the last name for any manager identified in the DEPARTMENT table and the left outer join guarantees that each employee is listed even if a corresponding department is not found in DEPARTMENT.

“10.4.3 更新”為 Mac OS X v10.4 帶來了全面改進的穩定性和相容性，因此推薦所有的使用者皆安裝使用。
它包含對以下項目的修正：
- AFP、SMB/CIFS、NFS 和 FTP 網路檔案服務
- AirPort 和 Bluetooth 無線連線
- Core Graphics、Core Audio、Core Image、RAW 相機支援
- 製作和燒錄媒體時進行光碟錄製
- .Mac 同步服務
- Spotlight 的索引和搜尋功能
- Dashboard widget：Dictionary、Flight Tracker、“便條紙”和“單位換算器”
- “通訊錄”、AppleScript、Automator、“字典”、“字體簿”、iCal、iSync、Mail、Safari 應用程式
- “磁碟工具程式”、“鑰匙圈存取”、“系統移轉輔助程式”和“軟體更新”
- 與協力廠商應用程式和設備的相容性
- 之前獨立的安全更新項目
如需關於此“更新”的詳細資訊，請參訪網站：http://www.info.apple.com/kbnum/n301984-yh

“10.4.3 更新”為 Mac OS X v10.4 帶來了全面改進的穩定性和相容性，因此推薦所有的使用者皆安裝使用。
它包含對以下項目的修正：
- AFP、SMB/CIFS、NFS 和 FTP 網路檔案服務
- AirPort 和 Bluetooth 無線連線
- Core Graphics、Core Audio、Core Image、RAW 相機支援
- 製作和燒錄媒體時進行光碟錄製
- .Mac 同步服務
- Spotlight 的索引和搜尋功能
- Dashboard widget：Dictionary、Flight Tracker、“便條紙”和“單位換算器”
- “通訊錄”、AppleScript、Automator、“字典”、“字體簿”、iCal、iSync、Mail、Safari 應用程式
- “磁碟工具程式”、“鑰匙圈存取”、“系統移轉輔助程式”和“軟體更新”
- 與協力廠商應用程式和設備的相容性
- 之前獨立的安全更新項目
如需關於此“更新”的詳細資訊，請參訪網站：http://www.info.apple.com/kbnum/n301984-yh

2005/11/04
毀滅戰士：Doom
四眼天雞：Chicken Little(動畫)
娜娜：NANA(日本片)
春心蕩漾：Prime
愛在大雪紛飛時：It's All About Love
山豬飛鼠撒可努：The Sage Hunter(國片)
2005金馬影展

2005/11/11
驅魔：The Exorcism of Emily Rose
血與骨：Blood And Bones(日本片)
黑社會：Election(國片)
長恨歌：Everlasting Regret(國片)

2005/11/18
哈利波特4: 火盃的考驗：Harry Potter: Goblet of Fire
美麗待續：An Unfinished Life
11點14分：Eleven Flourteen

2005/11/25
40處男：The 40 Year Old Virgin
疾風禁區：Goal!
我心遺忘的節奏：The Beat That My Heart Skipped
我的夏日戀曲：Summer Storm
紐約奇蹟：Noel

2005/11/04
毀滅戰士：Doom
四眼天雞：Chicken Little(動畫)
娜娜：NANA(日本片)
春心蕩漾：Prime
愛在大雪紛飛時：It's All About Love
山豬飛鼠撒可努：The Sage Hunter(國片)
2005金馬影展

2005/11/11
驅魔：The Exorcism of Emily Rose
血與骨：Blood And Bones(日本片)
黑社會：Election(國片)
長恨歌：Everlasting Regret(國片)

2005/11/18
哈利波特4: 火盃的考驗：Harry Potter: Goblet of Fire
美麗待續：An Unfinished Life
11點14分：Eleven Flourteen

2005/11/25
40處男：The 40 Year Old Virgin
疾風禁區：Goal!
我心遺忘的節奏：The Beat That My Heart Skipped
我的夏日戀曲：Summer Storm
紐約奇蹟：Noel