Bài giảng Ôn tập Cơ sở dữ liệu

Ệ CÔNG NGH THÔNG TIN

ÔN T PẬ

ơ ở ữ ệ C s d li u

Giảng viên: ThS. Trần Sơn Hải Email: transonhai@uit.edu.vn Heavily reference to Database Recap Slides: Nguyen Kim Phung

ộ

N i dung

1. Đại số quan hệ

2. Ngôn ngữ truy vấn SQL 3.Ràng buộc toàn vẹn

1. Đại số quan hệ

Ạ Ố

Ệ

1. Đ I S QUAN H

 Là một mô hình toán học dựa trên lý thuyết tập hợp  Đối tượng xử lý là các quan hệ trong cơ sở dữ liệu quan hệ  Cho phép sử dụng các phép toán rút trích dữ liệu từ các quan hệ  Tối ưu hóa quá trình rút trích dữ liệu  Gồm có:

 Các phép toán đại số quan hệ  Biểu thức đại số quan hệ

ứ

ể

1. ĐSQH Các phép toán ĐSQH, bi u th c ĐSQH

ệ ỏ

ề

ệ

ọ

ọ ọ

(cid:0) (cid:0)

i v i nhau.

(cid:0)

ệ ạ ớ ư

ệ

ằ

(cid:0)

ộ ủ

ứ ứ

ệ

• Có năm phép toán cơ bản: – Chọn ( ) Ch n ra các dòng (b ) trong quan h th a đi u ki n ch n. ộ – Chiếu ( ) Ch n ra m t s c t. ộ ố ộ – Tích Descartes ( ) K t hai quan h l ế – Trừ ( ) Ch a các b c a quan h 1 nh ng không n m trong quan h 2. ệ ộ ủ – Hội ( ) Ch a các b c a quan h 1 và các b c a quan h 2. ệ ộ ủ • Các phép toán khác:

– Giao ( (cid:0)

(cid:0)

), kết ( ), chia ( / hay (cid:0)

), đổi tên ( ): là các phép toán không cơ bản (được suy từ 5 phép toán trên, trừ phép đổi tên).

• Biểu thức đại số quan hệ:

– Là một biểu thức gồm các phép toán ĐSQH. – Biểu thức ĐSQH được xem như một quan hệ (không có tên) – Kết quả thực hiện các phép toán trên cũng là các quan hệ, do đó có thể kết hợp giữa các phép toán này để tạo nên các quan hệ mới!

(cid:0) 1. ĐSQH Phép ch n ọ

Câu hỏi 1: Cho biết các nhân viên nam ?

 Biểu diễn cách 1 :

Cú pháp :

(cid:0) (Quan hệ) (Điều kiện 1 (cid:0) điều kiện 2 (cid:0) ….)

(cid:0)

(NhanVien)

Câu hỏi 1:

Phai=‘Nam’

 Ngoài ra, có thể biểu diễn cách 2:

Cú pháp : (Quan hệ: điều kiện chọn)

Câu hỏi 1:

NHANVIEN

Kết quả phép chọn NHANVIEN

MANV

HOTEN

NTNS

PHAI

(NhanVien: Phai=‘Nam’)

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

MANV

HOTEN

NTNS

PHAI

NV002

Trần Đông Anh

01/08/1981

Nữ

NV001

Nguyễn Tấn Đạt

10/12/1970 Nam

NV003

Lý Phước Mẫn

02/04/1969

Nam

NV003

Lý Phước Mẫn

02/04/1969 Nam

(cid:0) 1. ĐSQH Phép ch n ọ

Câu hỏi 2: Cho biết các nhân viên nam sinh sau năm 1975 ?

 Biểu diễn cách 1 :

Câu hỏi 2:

(cid:0) (Phai=‘Nam’ (cid:0) Year(NTNS)>1975)

 Biểu diễn cách 2:

(NhanVien)

Câu hỏi 2:

NHANVIEN

Kết quả phép chọn

MANV

HOTEN

NTNS

PHAI

NHANVIEN

(NhanVien: Phai=‘Nam’ (cid:0) Year(NTNS)>1975)

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

MANV

HOTEN

NTNS

PHAI

NV002

Trần Đông Anh

01/08/1981

Nữ

NV003

Lý Phước Mẫn

02/04/1969

Nam

(không có bộ nào thỏa)

(cid:0) 1. ĐSQH Phép chi u ế

Câu hỏi 3: Cho biết họ tên nhân viên và giới tính ?

(cid:0)

 Biểu diễn cách 1 :

(Quan hệ) Cú pháp :

Cột1, cột2, cột 3, ….

Câu hỏi 3 :

 Ngoài ra, có thể biểu diễn cách 2:

Cú pháp : Quan hệ [cột1,cột2,cột3,…]

Câu hỏi 3:

NHANVIEN

MANV

HOTEN

NTNS

PHAI

HOTEN

PHAI

Kết quả

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

Nguyễn Tấn Đạt Nam

NV002

Trần Đông Anh

01/08/1981

Nữ

Trần Đông Anh

Nữ

phép chiếu

NV003

Lý Phước Mẫn

02/04/1969

Nam

Lý Phước Mẫn

Nam

(cid:0) 1. ĐSQH Phép chi u ế

Câu hỏi 4: Cho biết họ tên và ngày tháng năm sinh của các nhân viên nam?

 Biểu diễn cách 1:

Bước 1:

Kết quả phép chọn (còn gọi là biểu thức ĐSQH) được đổi tên thành quan hệ Q

Bước 2:

 Biểu diễn cách 2:

Câu hỏi 4:

NHANVIEN

Kết quả

MANV

HOTEN

NTNS

PHAI

HOTEN

NTNS

Nguyễn Tấn Đạt

10/12/1970

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

phép chiếu

NV002

Trần Đông Anh

01/08/1981

Nữ

Lý Phước Mẫn

02/04/1969

NV003

Lý Phước Mẫn

02/04/1969

Nam

1. ĐSQH Phép tích Descartes

(cid:0)

Câu hỏi 5: Tính tích Descartes giữa 2 quan hệ nhân viên và phòng ban

Cú pháp : Quan-hệ-2 Quan-hệ-1

(cid:0) …Quan-hệ-k

(cid:0)

Câu hỏi 5 được viết lại:

NHANVIEN

PHONGBAN

MANV

HOTEN

NTNS

PHAI PHONG

MAPH

TENPH

TRPH

PHONGBAN (cid:0) NHANVIEN

NV001

Nguyễn Tấn Ðạt

10/12/1970

Nam

NC

Nghiên cứu

NV001

NV002

Trần Ðông Anh

01/08/1981

Nữ

DH

Điều hành

NV002

NC

NV003

Lý Phước Mẫn

02/04/1969

Nam

NHANVIEN X PHONGBAN

MAPH

MANV

HOTEN

NTNS

PHAI

PHONG

TENPH

TRPH

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

NC

Nghiên cứu

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

NC

DH

Điều hành

NV002

Trần Đông Anh

01/08/1981

Nữ

DH

NC

Nghiên cứu

NV001

NV002

Trần Đông Anh

01/08/1981

Nữ

DH

Điều hành

NV002

NV003

Lý Phước Mẫn

02/04/1969

Nam

NC

Nghiên cứu

NV001

NV003

Lý Phước Mẫn

02/04/1969

Nam

NC

DH

Điều hành

NV002

1. ĐSQH Phép k tế

(ThetaJoin)

Câu hỏi 6: Cho biết mã nhân viên, họ tên và tên phòng mà n/v trực thuộc. -Đặt vấn đề: trở lại ví dụ 5, ta thấy nếu thực hiện phép tích Decartes NHANVIEN X PHONGBAN thì mỗi nhân viên đều thuộc 2 phòng (vì có tổng cộng là 2 phòng ban, nếu có 3, 4,…phòng ban thì số dòng cho một nhân viên trong NHANVIEN X PHONGBAN sẽ là 3, 4,..dòng. - Thực tế mỗi nhân viên chỉ thuộc duy nhất 1 phòng ban do ràng buộc khóa ngoại (PHONG), do đó để lấy được giá trị MAPH đúng của mỗi nhân viên  phải có điều kiện chọn:

NHANVIEN.PHONG = PHONGBAN.MAPH

biểu diễn phép chọn theo cách 2

MAPH

((NHANVIEN X PHONGBAN) : NHANVIEN.PHONG=PHONGBAN.MAPH) TRPH

PHONG

HOTEN

TENPH

NTNS

PHAI

MANV

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

NC

Nghiên cứu

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

NC

DH

Điều hành

NV002

Trần Đông Anh

01/08/1981

Nữ

DH

NC

Nghiên cứu

NV001

NV002

Trần Đông Anh

01/08/1981

Nữ

DH

Điều hành

NV002

NV003

Lý Phước Mẫn

02/04/1969

Nam

NC

Nghiên cứu

NV001

NV003

Lý Phước Mẫn

02/04/1969

Nam

NC

DH

Điều hành

NV002

1. ĐSQH Phép k tế

(ThetaJoin)

 Cách 1:

(cid:0)

(NHANVIEN X PHONGBAN)

NHANVIEN.PHONG=PHONGBAN.MAPH

 Cách 2:

PHONGBAN): (NHANVIEN.PHONG=PHONGBAN.MAPH)

(NHANVIEN (cid:0)

* Phép kết được định nghĩa là phép tích Decartes và có điều kiện chọn liên quan đến các thuộc tính giữa 2 quan hệ, cú pháp :

Quan-hệ-2 Quan-hệ-1

Điều kiện kết có thể là (cid:0)

, =, >, <, >=, <=. Nếu đk kết là phép so sánh = thì gọi là kết bằng)

(Phép kết với đk tổng quát được gọi là (cid:0) kết, (cid:0)

Câu hỏi 6 viết lại cách 1:

Câu hỏi 6 viết lại cách 2:

ế ằ

ế ự

1. ĐSQH k t b ng, k t t

nhiên

Kết bằng:

( Kết bằng )

equijoin

NHANVIEN PHONG=MAPH PHONGBAN

Kết tự nhiên:

c đ i thành MAPH

ỏ ộ

Nếu PHONG trong NHANVIEN đ thì ta b đi 1 c t MAPH lúc này gọi là phép kết t ượ ổ ả ể thay vì ph i đ MAPH=MAPH, ự naturaljoin) nhiên (

( Kết tự nhiên )

NHANVIEN MAPH PHONGBAN

naturaljoin

Hoặc viết cách khác: NHANVIEN * PHONGBAN

1. ĐSQH Phép k tế

ọ

ưở

ủ ừ ng phòng c a t ng phòng ?

Câu h i 7ỏ : Tìm h tên các tr

ồ

ượ

c đ CSDL nh sau:

Câu h i 8ỏ : Cho l TAIXE (MaTX, HoTen, NgaySinh, GioiTinh, DiaChi) CHUYENDI (SoCD, MaXe, MaTX, NgayDi, NgayVe, ChieuDai, SoNguoi) Cho biết họ tên tài xế, ngày đi, ngày về của những chuyến đi có chiều dài

>=300km, chở từ12 người trở lên trong mỗi chuyến?

Cách 1:

ế

ả K t qu :

Cách 2:

ế 1. ĐSQH Phép k t ngoài (outer join)

ấ

ở ộ ự

ệ

ế

ệ

ộ

ớ

• M r ng phép k t đ tránh m t thông tin ế ể • Th c hi n phép k t và sau đó thêm vào k t qu ả ế ế ệ ộ ủ ủ c a phép k t các b c a quan h mà không phù ợ h p v i các b trong quan h kia.

• Có 3 lo i: ạ

ế

ố

t c tài x và s chuy n

• Ví dụ: In ra danh sách t

ế ế đi, mã xe mà tài x đó lái (n u có)

– Left outer join R S – Right outer join R S – Full outer join R S ấ ả ế

ế ấ ả ộ ủ

ệ

ấ

(l y h t t

t c b c a quan h bên trái)

1. ĐSQH – left outer join

matx

• TAIXE CHUYENDI

TAIXE

MaTX

Hoten

Matx

Hoten

SoCD Matx Maxe

TX01

Huynh Trong Tao

TX01 Huynh Trong Tao CD01

TX01

8659

TX02

Nguyen Sang

TX01 Huynh Trong Tao CD03

TX01

8659

TX03

Le Phuoc Long

TX02 Nguyen Sang

CD02

TX02

7715

TX04

Nguyen Anh Tuan

TX03

Le Phuoc Long

CD04

TX03

4573

TX04 Nguyen Anh Tuan Null

Null

{

CHUYENDI

SoCD

MaTX MaXe

CD01

TX01

8659

ệ ộ ủ

CD02

TX02

7715

CD03

TX01

8659

ượ ớ ế ợ

CD04

TX03

4573

c thêm B c a quan h TAIXE đ ả Vào dù không phù h p v i k t qu ệ ủ c a quan h CHUYENDI

ươ ự ả ấ T ng t right outer join và full outer join (l y c 2)

ậ ợ

ừ

ộ

1. ĐSQH Phép tr , phép h i, phép giao t p h p

ề ầ • T t c các phép toán này đ u c n hai quan h đ u vào

ệ ầ ả ấ ả ươ t

ng thích kh h p ố ả ợ , nghĩa là chúng ph i tho : ộ ả ề ộ ụ – Cùng s thu c tính. Ví d : R và S đ u có 2 thu c

tính.

– Các thu c tính `t

ộ ươ ứ ể ng ng ’ có cùng ki u.

R

S

HONV

TENNV

HONV

TENNV

Vuong

Quyen

Nhan

Nguyen

Tung

Vuong

Quyen

Bui

NHANVIEN (MaNV, HoTen, Phai, Luong,NTNS, Ma_NQL, MaPH) PHANCONG (MaNV, MaDA, ThoiGian)

(cid:0) (cid:0) Phép giao: R S Phép trừ: R S(cid:0) Phép h iộ : R S

ậ ợ

ừ

ộ

1. ĐSQH Phép tr , phép h i, phép giao t p h p

(cid:0)

(cid:0) Phép trừ: Q = R S(cid:0) = { t/ t(cid:0) R (cid:0) t(cid:0) S} Phép h iộ : Q = R S = { t/ t(cid:0) R (cid:0) t(cid:0) S} Phép giao: Q = R S = R – (R – S) = { t/t(cid:0) R (cid:0) t(cid:0) S}

R

S

HONV

TENNV

HONV

TENNV

Vuong

Quyen

Nhan

Nguyen

Tung

Quyen

Vuong

Bui

ừ ộ

ế ế ế

ả ả ả

K t qu phép tr Q ={Nguyen Tung} K t qu phép h i Q ={Vuong Quyen, Nguyen Tung, Le Nhan, Bui Vu} K t qu phép giao Q ={Vuong Quyen}

ộ ấ L u ýư : Phép h i và phép giao có tính ch t giao hoán

ậ ợ

ừ

ộ

1. ĐSQH Phép tr , phép h i, phép giao t p h p

ế

ệ

t nhân viên không làm vi c ?

Câu h i 9ỏ : Cho bi

(Phép tr )ừ

Cách 1:

(cid:0)

MANV(NHANVIEN) – (cid:0)

MANV(PHANCONG)

Cách 2:

ượ

ế

ề

t nhân viên đ ố

: Cho bi ề

(NHANVIEN[MANV]) – (PHANCONG[MANV])

Câu h i 10ỏ ặ ‘TH01’ ho c đ án có mã s ‘TH02’?

ố c phân công tham gia đ án có mã s (Phép h i)ộ

(cid:0)

ế

ượ

ề

ả

t nhân viên đ

c phân công tham gia c 2 đ án

((PHANCONG: MADA=‘TH01’)[MANV]) ((PHANCONG : MADA=‘TH02’)[MANV])

Câu h i 11ỏ : Cho bi ề ‘TH01’ và đ án ‘TH02’?

(Phép giao)

(cid:0)

((PHANCONG : MADA=‘TH01’)[MANV]) ((PHANCONG : MADA=‘TH02’)[MANV])

(cid:0)

ậ ợ

1. ĐSQH Phép chia t p h p ( / hay

)

ả ầ ệ ầ S) c n hai quan h đ u vào R, S tho :

ủ ộ

n (cid:0)

• Phép chia (R (cid:0) ộ ậ ụ ủ ậ ộ ộ

ị

ậ – T p thu c tính c a R là t p cha c a t p thu c tính S. Ví d : R có m thu c tính, S có n thu c tính : m Đ nh nghĩa

ộ

ủ

ệ

ề

ữ

ế

ả

(cid:0)

(

)

T 1

(cid:0)

(cid:0) (cid:0) (cid:0) (cid:0)

((

)

(cid:0) (cid:0) (cid:0) (cid:0) (cid:0) (cid:0) (cid:0) (cid:0) (cid:0) (cid:0) (cid:0)

R và S là hai quan h , Rệ + và S+ l n l ầ ượ ậ t là t p +(cid:0) ậ thu c tính c a R và S. Đi u ki n S là t p con không b ngằ c a Rủ +. Q là k t qu phép chia gi a R và S, Q+ = R+ S+ SRQ s

st ),(

t /{

R }

TS 1

T 2

(cid:0)

R S TT 2 1

(cid:0) (cid:0)

(cid:0)

ậ ợ

1. ĐSQH Phép chia t p h p (/ hay

)

S=DEAN MADA R=PHANCONG MADA

MANV

TH001

001

TH001

ế ả K t qu Q

TH002

001

TH002

Q= PHANCONG/DEAN

MANV

DT001

002

TH001

002

TH002

ệ

ế

t nhân viên làm vi c cho

002

DT001

ề

ượ

003

TH001

t c các đ án ? (đ ấ ả

Cho bi ấ ả t công tham gia t

c phân ề t c các đ án)

(cid:0) ế ặ Ho c vi t Q= PHANCONG DEAN

(cid:0)

ậ ợ

1. ĐSQH Phép chia t p h p (/ hay

)

R=KETQUATHI

S=MONHOC

Mahv

Mahv Mamh Diem

Mamh

Tenmh

HV01

HV01 CSDL

7.0

CSDL Co so du lieu

HV03

HV02 CSDL

8.5

CTRR Cau truc roi rac

Q=KETQUA/MONHOC

HV01 CTRR 8.5

THDC Tin hoc dai cuong

HV03 CTRR 9.0

HV01 THDC 7.0

HV02 THDC 5.0

KETQUA

KETQUATHI

Mamh

Mahv

]

HV03 THDC 7.5

(cid:0)

MONHOC

[ Mamh

HV03 CSDL

6.0

MONHOC [

]

* Vi

(cid:0)

ế t cách khác KETQUATHI[Mahv,Mamh] /MONHOC[Mamh]

ề

1. ĐSQH – Hàm tính toán trên 1 nhóm và tính toán trên nhi u nhóm (gom nhóm – group by)

ị

• Các hàm tính toán g m 5 hàm: avg(giátr ), min(giátr ),

ị

ồ max(giátr ), sum(giátr ), count(giátr ).

• Phép toán gom nhóm: (Group by)

)

,...,

(

),...,

()

AF ( n n

GG , 1

AFAF ), 1 2

(cid:0)

ể ứ ạ ố

ệ ế – E là bi u th c đ i s quan h – Gi là thu c tính gom nhóm (n u không có G

ề

i nào=> không chia ẽ i (nhi u nhóm) => hàm F s tính toán c chia b i t p thu c tính này)

ừ ượ ạ c l ỏ ượ ở ậ ộ ộ nhóm (1 nhóm), ng trên t ng nhóm nh đ

ộ – Fi là hàm tính toán – Ai là tên thu c tính

ề

1. ĐSQH – Hàm tính toán trên 1 nhóm và tính toán trên nhi u nhóm (gom nhóm – group by)

ấ

ể

• Đi m thi cao nh t, th p nh t, trung bình

ấ ủ c a môn CSDL ?

(cid:0)

KETQUATHI

(

)

Diem

agv

Diem

max(

min(

(

)

Mamh

CSDL'

ấ

• Đi m thi cao nh t, th p nh t, trung bình

ể ủ ừ c a t ng môn ?

(cid:0) (cid:0)

KETQUATHI

)

Mamh

Diem

avg

Diem

max(

min(

(

()

(cid:0)

2. Ngôn ngữ truy vấn SQL

Ữ

Ấ

2. NGÔN NG TRUY V N SQL

 Là ngôn ngữ chuẩn, có cấu trúc dùng để truy vấn và thao tác trên CSDL quan hệ.  Câu truy vấn tổng quát: SELECT [DISTINCT] danh_sách_cột | hàm FROM danh sách các quan hệ (hay bảng, table) [WHERE điều_kiện] [GROUP BY danh_sách_cột_gom_nhóm] [HAVING điều_kiện_trên_nhóm] [ORDER BY cột1 ASC | DESC, cột2 ASC | DESC,… ]

2. SQL

ử

 Toán t

so sánh: o =,>,<,>=,<=,<> o BETWEEN o IS NULL, IS NOT NULL o LIKE (%,_) o IN, NOT IN o EXISTS, NOT EXISTS o SOME, ALL, ANY

ử

AND, OR.

 Toán t logic:  Các phép toán: +, ,* , /  Các hàm x lý ngày ( ử

DAY( )), tháng (MONTH( )), năm

(YEAR( ))

2. SQL

 5 hàm: COUNT( ), SUM( ), MAX( ), MIN( ), AVG( )  Phân lo i câu SELECT:

ạ ề

ệ

ồ

ệ

ề

ả SELECT đ n gi n, SELECT có m nh đ ORDER BY, SELECT l ng (câu SELECT l ng câu SELECT khác), SELECT gom nhóm (GROUP BY), . SELECT gom nhóm (GROUP BY)có đi u ki n HAVING

ượ ồ

ề

ả

Bài t pậ : Cho l

c đ CSDL “qu n lý đ án công ty”

nh sauư

NHANVIEN (MaNV, HoTen, Phai, Luong,NTNS,

Ma_NQL, MaPH)

PHONGBAN (MaPH, TenPH, TRPH) DEAN (MaDA, TenDA, Phong, NamThucHien) PHANCONG (MaNV, MaDA, ThoiGian)

MANV

HOTEN

NTNS

PHAI

MA_NQL

MaPH

LUONG

NHANVIEN

Vuong Ngoc Quyen

22/10/1957

3.000.000

001

Nguyen Thanh Tung

09/01/1955

Nam

001

2.500.000

002

Le Thi Nhan

18/12/1960

001

2.500.000

003

Dinh Ba Tien

09/01/1968

Nam

002

2.200.000

004

Bui Thuy Vu

19/07/1972

Nam

003

2.200.000

005

Nguyen Manh Hung

15/09/1973

Nam

002

2.000.000

006

Tran Thanh Tam

31/07/1975

002

2.200.000

007

PHANCONG

Tran Hong Minh

04/07/1976

004

1.800.000

008

MANV

MADA

THOIGIAN

001

TH001

30,0

DEAN

001

TH002

12,5

MADA

TENDA

PHONG

NamThucHien

002

TH001

10,0

TH001

Tin hoc hoa 1

2002

002

TH002

10,0

TH002

Tin hoc hoa 2

2003

002

DT001

10,0

DT001

Dao tao 1

2004

002

DT002

10,0

DT002

Dao tao 2

2004

003

TH001

37,5

004

DT001

22,5

PHONGBAN

004

DT002

10,0

MAPH

TENPH

TRPH

006

DT001

30,5

001

Quan Ly

007

TH001

20,0

003

Dieu Hanh

007

TH002

10,0

002

Nghien Cuu

008

DT002

12,5

2. SQL – BETWEEN, ORDER BY, IS NULL

Câu hỏi 13: Sử dụng =,>,>=,… Danh sách các nhân viên sinh trong khoảng từ năm 1978 đến 1983?

Select MaNV, HoTen From NhanVien where Year(NTNS)>=1978 AND Year(NTNS)<=1983

Câu hỏi 14: Sử dụng BETWEEN, ORDER BY. Danh sách các nhân viên sinh trong khoảng từ năm 1978 đến 1983? Sắp xếp theo mức lương giảm dần.

Select * From NhanVien where Year(NTNS) BETWEEN 1978 and 1983 ORDER BY Luong DESC

Câu hỏi 15: Sử dụng IS NULL. Cho biết những nhân viên không có người quản lý trực tiếp? (không chịu sự quản lý trực tiếp của người nào)

Select MaNV, HoTen, NTNS, Ma_NQL from NhanVien where Ma_NQL is Null

2. SQL SO SÁNH IN & NOT IN

Câu hỏi 16: Sử dụng Is Not Null. Cho biết những nhân viên có người quản lý trực tiếp?Thông tin hiển thị gồm: mã nhân viên, họ tên, mã người quản lý.

Câu hỏi 17: Sử dụng IN (so sánh với một tập hợp giá trị cụ thể). Cho biết họ tên nhân viên thuộc phòng ‘NC’ hoặc phòng ‘DH’?

Câu hỏi 18: Sử dụng IN (so sánh với một tập hợp giá trị chọn từ câu SELECT khác). Cho biết họ tên nhân viên thuộc phòng ‘NC’ hoặc phòng ‘DH’?

2. SQL – SO SÁNH IN & NOT IN

Câu hỏi 19 (tt): Cho biết mã số, họ tên, ngày tháng năm sinh của những nhân viên đã tham gia đề án?

Câu hỏi 20: Sử dụng NOT IN. Cho biết mã số, họ tên, ngày tháng năm sinh của những nhân viên không tham gia đề án nào? Gợi ý cho mệnh đề NOT IN: thực hiện câu truy vấn “tìm nhân viên có tham gia đề án (dựa vào bảng PhanCong)”, sau đó lấy phần bù.

Câu hỏi 21 (tt): Cho biết tên phòng ban không chủ trì các đề án triển khai năm 2005? Gợi ý: thực hiện câu truy vấn “tìm phòng ban chủ trì các đề án triển khai năm 2005”, sau đó lấy phần bù.

2. SQL – SO SÁNH LIKE

Câu hỏi 22: so sánh chuỗi = chuỗi. Liệt kê mã nhân viên, ngày tháng năm sinh, mức lương của nhân viên có tên “Nguyễn Tường Linh”?

Câu hỏi 23: Sử dụng LIKE (%: thay thế 1 chuỗi ký tự). Tìm những nhân viên có họ Nguyễn.

Câu hỏi 24 (tt): Tìm những nhân viên có tên Lan.

Câu hỏi 25 (tt): Tìm những nhân viên có tên lót là “Văn”.

Câu hỏi 26: Sử dụng LIKE ( _: thay thế 1 ký tự bất kỳ). Tìm những nhân viên tên có tên ‘Nguyễn La_’ (ví dụ Lam, Lan)

2. SQL – HÀM COUNT,SUM,MAX,MIN,AVG

a) Sử dụng các hàm COUNT, SUM, MIN, MAX, AVG trên 1 nhóm lớn (trên toàn bộ quan hệ):

– Câu hỏi 27: Tính số nhân viên của công ty.

– Câu hỏi 28: Tính số lượng nhân viên quản lý trực tiếp nhân viên khác.

– Câu hỏi 29: Tìm mức lương lớn nhất, mức lương trung bình, tổng lương của công ty.

– Câu hỏi 30: Cho biết nhân viên có mức lương lớn nhất.

Ề

Ệ

2. SQL – M NH Đ GROUP BY

Câu hỏi 31: Cho biết nhân viên có mức lương trên mức lương trung bình của công ty.

b) Sử dụng các hàm COUNT, SUM, MIN, MAX, AVG trên từng nhóm nhỏ: mệnh đề GROUP BY

ỏ ự ậ

– Chia các dòng thành các nhóm nh d a trên t p thu c tính chia nhóm

. ộ

ệ ư

nh : Count (th c ị

– Th c ự hi n các phép toán trên nhóm ự ệ ỏ hi n phép đ m), Sum (tính t ng), Min(l y giá tr nh ấ nh t), Max(l y giá tr l n nh t), AVG (l y giá tr trung bình).

ấ ấ ổ ấ ế ấ ị ớ ị

Ệ

Ề

2. SQL – M NH Đ GROUP BY

ậ

ộ

Chia các dòng thành các ự nhóm d a trên t p thu c tính chia nhóm

ươ

cho các

ng t

Q Count(S) a

10 2 9 5 10

ự T hàm SUM, MIN, MAX, AVG

2 5

b c

m ó h n

8 6 4 10 16

18 50

Quan h NVệ Q S a a b b c c c c c d d d

ộ

Các thu c tính GROUP BY: Q

Câu SQL: Select Q, count(S) From NV Group by Q

Ề

Ệ

2. SQL – M NH Đ GROUP BY

Câu hỏi 32: Cho biết số lượng nhân viên theo từng phái?

Do cột phái có 2 giá trị “nam” và “nữ”, trường hợp này ta chia bảng NhanVien thành 2 nhóm nhỏ. Thuộc tính chia nhóm là thuộc tính “Phai”.

Câu hỏi 33: Cho biết số lượng nhân viên theo từng phòng?

Do cột MaPH có 3 giá trị “NC” và “DH” và “QL”, trường hợp này ta chia bảng nhân viên thành 3 nhóm nhỏ. Thuộc tính chia nhóm là thuộc tính “MaPH”.

Tương tự: cho biết tổng lương của mỗi phòng, cho biết mức lương thấp nhất của từng phòng, mức lương cao nhất, mức lương trung bình của từng phòng

Ề

Ệ

2. SQL – M NH Đ GROUP BY

Câu hỏi 34: Cho biết tên phòng và số lượng nhân viên theo từng phòng?

Giống câu 29 nhưng bổ sung thêm bảng PhongBan để lấy tên phòng. Thuộc tính chia nhóm là (TenPH) thay cho MaPH.

Câu hỏi 35: Với mỗi phòng, cho biết số lượng nhân viên theo từng phái?

Do cột MaPH có 3 giá trị “NC” và “DH” và “QL”, mỗi phòng chia nhỏ theo từng phái: 2 nhóm “Nam” và “Nữ”, trường hợp này ta chia bảng nhân viên thành 6 nhóm nhỏ. Như vậy, tập thuộc tính chia nhóm cho câu truy vấn là (Phong, Phai).

Ề

Ệ

2. SQL – M NH Đ GROUP BY

Câu hỏi 36: Đếm số đề án của từng nhân viên tham gia?

- Do cột MaNV có 7 giá trị “NV001”,…”NV008” (không có nhân viên “005”), trường hợp này ta chia bảng PhanCong thành 7 nhóm nhỏ. Với mỗi nhóm nhỏ (MaNV), ta đếm số đề án (count(MADA)) tham gia. Thuộc tính chia nhóm là thuộc tính “MaNV”. - Tương tự: tính tổng số giờ làm việc của mỗi nhân viên (SUM), thời gian làm việc thấp nhất của mỗi nhân viên (MIN), thời gian làm việc lớn nhất của mỗi nhân viên (MAX), thời gian làm việc trung bình,…

Câu hỏi 37: Cho biết mã, tên nhân viên và số đề án mà n/v đã tham gia?

Ệ

Ề

2. SQL – M NH Đ HAVING

ề

ế

ả

ọ

ệ  L c k t qu theo đi u ki n, sau khi đã gom nhóm

ề

ệ

ề

ủ

ề

ệ

ộ

 Đi u ki n c a HAVING là đi u ki n v các hàm tính toán trên nhóm (Count, Sum, Min, Max, AVG) và các thu c tính trong danh sách GROUP BY. Câu hỏi 38: Cho biết những nhân viên tham gia từ 2 đề án trở lên?

Câu hỏi 39: Cho biết mã phòng ban có trên 4 nhân viên?

3. Ràng buộc toàn vẹn

Ẹ

Ộ 3. RÀNG BU C TOÀN V N

ộ

ệ

• RBTV có b i c nh trên m t quan h

ố ả ề

ộ

– Ràng bu c mi n giá tr ị ộ – Ràng bu c liên b ộ – Ràng bu c liên thu c tính ộ

ề

ố ả

ệ

ộ

ế

ạ

ệ

ộ ộ ộ ộ

ổ

ợ

• RBTV có b i c nh trên nhi u quan h – Ràng bu c liên thu c tính liên quan h ệ – Ràng bu c khóa ngo i (tham chi u) – Ràng bu c liên b liên quan h ộ – Ràng bu c do thu c tính t ng h p (Count, Sum) ộ

Ư

Ặ

3. RBTV – CÁC Đ C TR NG

ủ

ữ

Các đ c tr ng c a 1 RBTV: : phát bi u b ng ể

ệ

ằ ngôn ng hình ệ ạ ố

ệ

ả

ặ ộ ứ ả ố ả : là nh ng quan h có kh năng

ữ ị

ạ

ề

ưở : là b ng 2 chi u, xác ưở

ầ ả ị

ả ưở

ng (+) và thao ệ ng () lên các quan h

ằ

N i dung th c (phép tính quan h , đ i s quan h , mã gi ,…) B i c nh làm cho RBTV b vi ph m. ả T m nh h đ nh các thao tác nh h ả tác không nh h ố ả n m trong b i c nh.

Ầ Ả

Ả

ƯỞ

3. RBTV – B NG T M NH H

Thêm

Xóa

S aử

Quan h 1ệ

(*)

………

Quan h nệ

+(A)

ể

ạ

ệ Ký hi u + : Có th gây ra vi ph m RBTV ệ ể Ký hi u : Không th gây ra vi ph m RBTV ệ Ký hi u +(A)

: Có th gây ra vi ph m RBTV khi thao

ộ

ạ ể tác trên thu c tính A

ệ

ạ

Ký hi u –(*) : Không th gây ra vi ph m RBTV do thao tác không

ệ

ượ

ể ự th c hi n đ

ầ ả ả ưở ủ ư ạ B ng t m nh h ng c a RBTV có d ng nh sau:

Ệ

Ố Ả 3. RBTV – TRÊN B I C NH LÀ 1 QUAN H

ề

ệ

ị ẹ ộ 3.1. Ràng bu c toàn v n mi n giá tr ượ ồ c đ quan h

• Xét l

– NHANVIEN (MANV, HONV, TENLOT, TENNV, NGSINH, PHAI, DCHI, MA_NQL, PHONG, MLUONG)

ủ ể ặ ỉ Câu h i 40ỏ ữ : Phái c a nhân viên ch có th là ‘Nam’ ho c ‘N ’

(cid:0) : NHANVIEN: n.PHAI IN {‘Nam’,’N ’}ữ

ầ ả ng (TAH):

Xóa

S aử

– N i dung ộ (cid:0) n (cid:0) – B i c nh ệ ố ả : quan h NHANVIEN – B ng t m nh h ưở ả Thêm

+(PHAI)

NHANVIEN

Ệ

Ố Ả 3. RBTV – TRÊN B I C NH LÀ 1 QUAN H

ẹ

ộ

ràng bu c ộ

ữ

ộ

3.2. Ràng bu c toàn v n liên thu c tính: ệ gi a các thu c tính trong cùng m t quan h .

ệ Xét l

ộ ộ ượ ồ c đ quan h DEAN (MADA, TENDA, DDIEM_DA, PHONG, NGBD_DK, NGKT_DK)

ớ ọ ề

ắ ầ ự ế ự ế ế ỏ ơ ả

Câu h i 41ỏ : V i m i đ án, ngày b t đ u d ki n (NGBD_DK) ph i nh h n ngày k t thúc d ki n (NGKT_DK)

ộ

N i dung (cid:0) d (cid:0) : DEAN, d.NGBD_DK <= d.NGKT_DK

Ệ

Ố Ả 3. RBTV – TRÊN B I C NH LÀ 1 QUAN H

Thêm

Xóa

S aử

DEAN

+ (NGBD_DK, NGKT_DK)

+(NGBD_DK, NGKT_DK)

ầ ả – B i c nh ố ả : quan h DEAN – B ng t m nh h ả ệ ưở : ng

ộ

ữ

ộ

ộ ràng bu c gi a các ộ 3.3. Ràng bu c toàn v n liên b : ệ b giá tr trong cùng m t quan h .

ượ ồ

ẹ ộ ệ

ị Cho l

c đ quan h : NHANVIEN(MaNV, HoTen, HESO, MucLuong)

ệ ố ươ : các nhân viên có cùng h s l ng thì có cùng

Câu h i 42ỏ ứ ươ ng. m c l

Ệ

Ố Ả 3. RBTV – TRÊN B I C NH LÀ 1 QUAN H

NHANVIEN: n1.HESO=n2.HESO thì

– N i dung ộ : • (cid:0) n1,n2 (cid:0) (n1.MUCLUONG = n2.MUCLUONG)

Thêm

Xóa

S aử

NHANVIEN

+(HESO, MucLuong)

+ (HESO, MucLuong)

ầ ả – B i c nh ệ ố ả : quan h NHANVIEN – B ng t m nh h ưở : ả ng

Ố Ả

Ề

Ệ

3. RBTV – B I C NH NHI U QUAN H

ộ

ế

ẹ

3.4. Ràng bu c toàn v n tham chi u

ộ ồ ạ

ụ

ế

ọ

ộ

• RBTV tham chi u còn g i là ràng bu c ph thu c t n t

i hay

ạ

ộ

ràng bu c khóa ngo i.

ượ

ồ

ệ

• Xét l

c đ quan h

PHONGBAN (MAPH, TENPH, TRPH, NGNC) NHANVIEN (MANV, HOTEN, NTNS, PHAI, MA_NQL, MAPH, LUONG)

ỗ ưở

ả

ộ

ng phòng ph i là m t nhân viên trong công ty

(cid:0)

Câu h i 43ỏ : M i tr – N i dung ộ : (cid:0) p (cid:0)

PHONGBAN, (cid:0) n (cid:0)

NHANVIEN:

p.TRPH= n.MANV

Hay: PHONGBAN[TRPH] (cid:0)

NHANVIEN[MANV])

Ố Ả

Ề

Ệ

3. RBTV – B I C NH NHI U QUAN H

ầ ả

–B i c nh –B ng t m nh h

ố ả : NHANVIEN, PHONGBAN ưở : ả ng

Thêm

Xóa

S aử

PHONGBAN

+(TRPH)

NHANVIEN

(*)

ộ

ẹ

ệ

3.5. Ràng bu c toàn v n liên thu c tính liên quan h

ượ ồ Xét các l

ệ c đ quan h : DATHANG(MADH, MAKH, NGAYDH) GIAOHANG(MAGH, MADH, NGAYGH)

Ố Ả

Ề

Ệ

3. RBTV – B I C NH NHI U QUAN H

ượ ướ ặ c tr c ngày đ t : Ngày giao hàng không đ

Câu h i 44ỏ hàng ộ N i dung :

(cid:0) g(cid:0) GIAO_HANG,

(cid:0) (cid:0) d(cid:0) DAT_HANG:d.MADH(cid:0) g.MADH (cid:0) d.NGAYDH

>= g.NGAYGH

Thêm

Xóa

S aử

DATHANG

+ (ngaydh)

GIAOHANG

+(ngaygh)

+ (ngaygh)

ầ ả – B i c nh ố ả : DATHANG, GIAOHANG – B ng t m nh h ưở : ả ng

Ố Ả

Ề

Ệ

3. RBTV – B I C NH NHI U QUAN H

ộ

ệ

ẹ 3.6. Ràng bu c toàn v n liên b , liên quan h ữ

ệ

ề

ệ

ộ

• RBTV liên b , liên quan h là đi u ki n gi a các b ộ

trên nhi u quan h khác nhau.

ệ

ề • Xét các l ượ ồ

ệ c đ quan h

– PHONGBAN (MAPH, TENPH, TRPH, NGNC) – DIADIEM_PHG (MAPH, DIADIEM)

ộ ị

ấ

ỗ

ả : M i phòng ban ph i có ít nh t m t đ a

ể

Câu h i 45ỏ đi m phòng

Ố Ả

Ề

Ệ

3. RBTV – B I C NH NHI U QUAN H

ổ

ợ

ộ

ẹ

3.7. Ràng bu c toàn v n do thu c tính t ng h p

ả ằ ủ ấ ổ ế : T ng tr giá c a 1 phi u xu t ph i b ng t ng

ị ế Câu h i 46ỏ ổ tr giá các chi ti ị ấ t xu t.

Bài giảng Ôn tập Cơ sở dữ liệu - ThS. Trần Sơn Hải

Ệ CÔNG NGH THÔNG TIN

ơ ở ữ ệ C s d li u

Giảng viên: ThS. Trần Sơn Hải Email: transonhai@uit.edu.vn Heavily reference to Database Recap Slides: Nguyen Kim Phung

ộ

1. Đại số quan hệ

Ạ Ố

Ệ

ứ

ể

), kết ( ), chia ( / hay (cid:0)

), đổi tên ( ): là các phép toán không cơ bản (được suy từ 5 phép toán trên, trừ phép đổi tên).

Câu hỏi 1: Cho biết các nhân viên nam ?

(cid:0) (Quan hệ) (Điều kiện 1 (cid:0) điều kiện 2 (cid:0) ….)

Câu hỏi 1:

Phai=‘Nam’

Câu hỏi 1:

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

NV002

Trần Đông Anh

01/08/1981

Nữ

NV001

Nguyễn Tấn Đạt

10/12/1970 Nam

NV003

Lý Phước Mẫn

02/04/1969

Nam

NV003

Lý Phước Mẫn

02/04/1969 Nam

Câu hỏi 2: Cho biết các nhân viên nam sinh sau năm 1975 ?

Câu hỏi 2:

Câu hỏi 2:

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

NV002

Trần Đông Anh

01/08/1981

Nữ

NV003

Lý Phước Mẫn

02/04/1969

Nam

Câu hỏi 3: Cho biết họ tên nhân viên và giới tính ?

Cột1, cột2, cột 3, ….

Câu hỏi 3 :

Câu hỏi 3:

NV001

Nguyễn Tấn Đạt

10/12/1970

Nam

Nguyễn Tấn Đạt Nam

NV002

Trần Đông Anh

01/08/1981

Nữ

Trần Đông Anh

Nữ

NV003

Lý Phước Mẫn

02/04/1969

Nam

Lý Phước Mẫn

Nam

Câu hỏi 4: Cho biết họ tên và ngày tháng năm sinh của các nhân viên nam?

Bước 1:

Kết quả phép chọn (còn gọi là biểu thức ĐSQH) được đổi tên thành quan hệ Q

Bước 2:

Câu hỏi 4:

Nguyễn Tấn Đạt

10/12/1970

NV001

Nguyễn Tấn Đạt