SQL第1堂課 資料查詢

順便來紀錄一下Red上課的內容, 相關連結資料來源 WebDesign

資料查詢

• SQL* BETWEEN
• SQL* INNER JOIN 
• SQL* LEFT JOIN
• SQL* UNION

---

練習1
請寫出一個SQL, 使用日期函數及Between語法, 從USER_LIST table, 找出生日(birth)欄位月份在08到09之間, 並且部門代碼(dept_code)為ITBD開頭的人員, 顯示下列四個欄位

dept_code	emp_id	emp_cname	birth

解答:

select dept_code, emp_id, emp_cname, birth
from USER_LIST
where month(birth) between '8' and '9' and dept_code like 'ITBD%'

---

練習2
請寫出一個SQL, 找出USER_LIST table, 中文姓名(emp_cname)姓'王',並且姓名第2個字為'俊'或'國',顯示下列四個欄位:(此題目限制只能有一個where condition like)

dept_code	emp_id	emp_cname	birth

解答:

select dept_code,emp_id,emp_cname,birth
from User_List
where emp_cname
like('王[俊國]%')

---

練習3
A公司在AUS以及AUT各有一套薪資系統, 請使用下列Table,寫一個table join + union的SQL, 並寫出執行的Result
Result

DEPT_NAME	EMP_NAME	MONTH	Salary_AMT

AUT tables

Employee_AUT

EMP_ID	EMP_NAME	DEPT_ID
1	Alisa	A
2	Charles	B

DEPT_AUT

DEPT_ID	DEPT_NAME
A	IT
B	Sales

Salary_AUT

EMP_ID	Month	Salary_AMT
1	201005	100
1	201006	105
2	201005	120
2	201006	125

AUS tables

Employee_AUS

EMP_ID	EMP_NAME	DEPT_ID
1	Jason	A
2	CK	C

DEPT_AUS

DEPT_ID	DEPT_NAME
A	IT
C	MFG

Salary_AUS

EMP_ID	Month	Salary_AMT
1	201005	100
1	201006	105
2	201005	120
2	201006	125

解答:

select d.DEPT_NAME,  e.EMP_NAME,  s.MONTH,  s.Salary_AMT
from Employee_AUT e
inner join DEPT_AUT d
on e.DEPT_ID = d.DEPT_ID
inner join Salary_AUT s
on e.EMP_ID = s.EMP_ID
UNION
select d.DEPT_NAME,  e.EMP_NAME,  s.MONTH,  s.Salary_AMT
from Employee_AUS e
inner join DEPT_AUS d
on e.DEPT_ID = d.DEPT_ID
inner join Salary_AUS s
on e.EMP_ID = s.EMP_ID

Result

DEPT_NAME	EMP_NAME	MONTH	Salary_AMT
IT	Alisa	201005	100
IT	Alisa	201006	105
Sales	Charles	201005	120
Sales	Charles	201006	125
IT	Jason	201005	100
IT	Jason	201006	105
MFG	CK	201005	120
MFG	CK	201006	125

---

練習4
請使用left outer join ,join下列四個table: order_mst, order_dtl, product, customer, 列出所有customer的資訊,如result的顯示欄位,並將結果填入result表格

Order_mst

order_no	customer_ID	ship_addr	order_date	sales
1	2	xx	10/1/1	red
2	1	aa	10/1/1	charles

 

order_dtl

order_no	product_ID	qty
1	a	10
1	b	20
2	a	100
2	c	200

Product

product_ID	Product_name	unit_price
a	LCD panel	1000
b	mouse	50
c	keyboard	100

 

customer

Customer_ID	Customer_name
1	Acer
2	BenQ
3	Sony

 

result

Customer_name

Order_no

ship_addr

order_date

product_name

unit_price

qty

total_amt

 

解答:

select c.customer_name, t.order_no, t.ship_addr,  t.order_date ,p.product_name, p.unit_price, d.qty, d.qty * p.unit_price as  total_amt 
from customer c
left outer join order_mst t 
on  t.customer_ID = c.customer_ID
left outer join order_dtl d
on t.order_no =  d.order_no
left outer join product p
on d.product_ID =  p.product_ID

Customer_name	Order_no	ship_addr	order_date	product_name	unit_price	qty	total_amt
BenQ	1	xx	10/1/1	LCD panel	1000	10	10000
BenQ	1	xx	10/1/1	mouse	50	20	1000
Acer	2	aa	10/1/1	LCD panel	1000	100	100000
Acer	2	aa	10/1/1	keyboard	100	200	20000
Sony	null	null	null	null	null	null	null

---

感謝Red提供教學~