學習筆記專區

--Cross Joins

--兩邊表格可以不用有共同欄位

select last_name,DEPARTMENT_name

from EMPLOYEES , DEPARTMENTS--last_name(20筆)是EMPLOYEES的資料，DEPARTMENT_name(8筆)是DEPARTMENTS的資料

--結果是交叉相乘，因為兩個資料欄沒有對應的編號，所以只是變成每個資料都對應所有資料，共20*8=160筆資料，cross join 一樣可略

--Equijoins

select

EMPLOYEE_ID,last_name,location_id,departments.DEPARTMENT_ID--DEPARTMENT_ID是兩邊的共有欄位，所以必須指定是要哪一邊的

from employees join DEPARTMENTS--join不可省略

on EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID--告知電腦兩邊的DEPARTMENT_ID是一樣的

--"on"指定合併的"表格.欄位"名稱

order by EMPLOYEE_ID --可以使用排序order by

--Table Alias(別名)

select e.EMPLOYEE_ID,e.last_name,d.location_id,d.DEPARTMENT_ID--已經取了別名就不可以再寫全名了departments.DEPARTMENT_ID(X)

from employees e join DEPARTMENTS d --employees別名e  DEPARTMENTS別名d

on e.DEPARTMENT_ID = d.DEPARTMENT_ID;

--輸出結果跟沒有別名一樣

--額外條件

select e.EMPLOYEE_ID,e.last_name,d.location_id,d.DEPARTMENT_ID

from employees e join DEPARTMENTS d

on e.DEPARTMENT_ID = d.DEPARTMENT_ID

where location_id>1500--設定篩選條件，where不能有兩個，只有一個資料表有的欄目可以不用加別名(欄目名)(也可以加)

and d.DEPARTMENT_ID<=90--可以用"and"增加條件

--ON語法─合併三個表格

select EMPLOYEE_ID,city,DEPARTMENT_NAME--EMPLOYEE_ID(EMPLOYEES)、city(LOCATIONS)、DEPARTMENT_NAME(DEPARTMENTS)

from EMPLOYEES e join DEPARTMENTS d --擷取這兩個資料表，並給予別名

on d.DEPARTMENT_ID = e.DEPARTMENT_ID--篩選條件：當兩方相等時(where d.DEPARTMENT_ID = e.DEPARTMENT_ID)

join LOCATIONS L--加入擷取新資料表

on d.LOCATION_ID = L.LOCATION_ID--篩選條件：當兩方相等時(d.LOCATION_ID = L.LOCATION_ID)

order by EMPLOYEE_ID

--↑↓一樣

select EMPLOYEE_ID,city,DEPARTMENT_NAME

from EMPLOYEES e, DEPARTMENTS d,LOCATIONS L

where d.DEPARTMENT_ID = e.DEPARTMENT_ID

and d.LOCATION_ID = L.LOCATION_ID

order by EMPLOYEE_ID

--Non-Equijoins

--有join就有on，以下四者均一樣結果

select e.LAST_NAME,e.SALARY,j.GRADE_LEVEL--3.所要顯示的欄位

from EMPLOYEES e join JOB_GRADES j--1.所需資料表

on e.SALARY between j.LOWEST_SAL and j.HIGHEST_SAL--2.篩選條件：查詢salary符合在兩個欄位值之間時

select e.LAST_NAME,e.SALARY,j.GRADE_LEVEL--3.所要顯示的欄位

from EMPLOYEES e join JOB_GRADES j--1.所需資料表

on e.SALARY >=j.LOWEST_SAL and  e.SALARY<=j.HIGHEST_SAL--2.篩選條件：查詢salary符合在兩個欄位值之間時

select e.LAST_NAME,e.SALARY,j.GRADE_LEVEL

from EMPLOYEES e , JOB_GRADES j

where e.SALARY between j.LOWEST_SAL and j.HIGHEST_SAL

select e.LAST_NAME,e.SALARY,j.GRADE_LEVEL

from EMPLOYEES e , JOB_GRADES j

where  e.SALARY >=j.LOWEST_SAL and  e.SALARY<=j.HIGHEST_SAL

--left outer join

select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME--所需資料欄位

from EMPLOYEES e left outer join DEPARTMENTS d --1.打出所需資料表，左邊有資料時不論右邊有沒有都回傳

on e.DEPARTMENT_ID = d.DEPARTMENT_ID--3.條件設定：只要兩者相等的資料

--4.因為1.的設定是左邊有資料不論右邊有沒有都要回傳，所以有Grant

--right outer join

select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME--2.取出這兩個表格中的這三欄，

from EMPLOYEES e right outer join DEPARTMENTS d--1.取出這兩個表格

on e.DEPARTMENT_ID = d.DEPARTMENT_ID--3.取出的三欄中，有一欄篩選成是兩個表格的共通項目，藉此聯繫

--因為設定是右邊(d.DEPARTMENT_ID)有資料不論左邊有沒有都要回傳，所以雖然共同欄目中e.DEPARTMENT_ID沒有190但因為0d.DEPARTMENT_ID有，所以還是有Contracting

--full outer join

select e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAME

from EMPLOYEES e , DEPARTMENTS d

where e.DEPARTMENT_ID = d.DEPARTMENT_ID

and e. DEPARTMENT_ID=Null

--因為設定是兩邊有資料不論另邊有沒有都要回傳，所以有Contracting跟Grant都有

--self Joins

select w.LAST_NAME,m.LAST_NAME--w.LAST_NAME是員工的名字，m.LAST_NAME是主管的名字

from EMPLOYEES w join EMPLOYEES m --1.把EMPLOYEES複製成兩份，一份是員工的(w)、一份是主管的(m)，同時看著兩份資料

on w.MANAGER_ID=m.EMPLOYEE_ID--2.員工中的主管ID，其實就是主管自己的員工ID

--進一步示範

select w.EMPLOYEE_ID,w.FIRST_NAME,w.MANAGER_ID,m.EMPLOYEE_ID,m.FIRST_NAME,m.MANAGER_ID

from EMPLOYEES w,EMPLOYEES m

where w.MANAGER_ID=m.EMPLOYEE_ID

--子查詢(單列)

--<>：not equal to

--誰的薪水比abel多？

select LAST_NAME

from EMPLOYEES

where salary>(select SALARY from EMPLOYEES where LAST_NAME='Abel')--1.括弧內尋找Abel的薪水

                                                                                                                                                                           ----2.括弧外設定尋找條件為薪水大於括弧內所得出數字

--誰的薪水最低？

select LAST_NAME

from EMPLOYEES

where salary=(select min(salary) from EMPLOYEES)

--查詢各部門最低薪水，且大於部門ID50的最低薪水

select DEPARTMENT_ID,min(salary)minn

from EMPLOYEES

group by DEPARTMENT_ID--以部門分組，分組用having而非where

having min(salary)>(select min(salary) from EMPLOYEES where DEPARTMENT_ID=110)--括弧內找出部門ID110中的最低薪水(8300)

--子查詢(多列)

--關鍵字：not、in、any=some、all

--any：任一符合條件即可

--all：全部須符合條件

--找出所有薪水比IT人員最高薪少的員工，不可是IT人員者

select LAST_NAME,SALARY,job_id

from EMPLOYEES

where salary<any(select SALARY

                  from EMPLOYEES

                   where JOB_ID='IT_PROG')--括號內為所有IT員工的薪資，括號外要求需要小於任何一個，所以至少要小於最大的(9000)

and job_id <> 'IT_PROG';--不要IT人員

--找出所有薪水比IT人員最低薪少的員工，不可是IT人員者

select LAST_NAME,SALARY,job_id

from EMPLOYEES

where salary<all(select SALARY

                 from EMPLOYEES

                where JOB_ID='IT_PROG')--括號內為所有IT員工的薪資，括號外要求需要小於所有IT，所以至少要小於最小的(4200)

and job_id <> 'IT_PROG';

--找出非主管職的員工

select e.LAST_NAME

from EMPLOYEES e

where e.EMPLOYEE_ID not in ( select m.MANAGER_ID from EMPLOYEES m where m.MANAGER_ID is not null)