Bài giảng Cơ sở dữ liệu

tên có tên ‘Nguy n La_’ (ví d Lam, Lan)ễ ụ
Select MaNV, HoTen from NhanVien where HoTen like ‘Nguy n La_’ễ
Câu h i 24 (tt)ỏ : Tìm nh ng nhân viên có tên Lan.ữ
Select MaNV, HoTen from NhanVien where HoTen like ‘% Lan’
Câu h i 25 (tt)ỏ : Tìm nh ng nhân viên có tên lót là “Văn”.ữ
Select MaNV, HoTen from NhanVien where HoTen like ‘% Văn %’
 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.ỏ ế ứ ươ ớ ấ
Select COUNT(MaNV) as SoNV from NhanVien
Select COUNT (DISTINCT Ma_NQL) from NhanVien
Select MAX(Luong), AVG(Luong), SUM(Luong) from NhanVien
Select HoTen from NhanVien 
Where Luong = (Select MAX(Luong) from NhanVien )
 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.ủ
Select HoTen from NhanVien where Luong > (Select 
AVG(Luong) from NhanVien )
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.
– Thực hiện các phép toán trên nhóm như: Count (thực 
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Ệ Ề
nh ó m
Các thuộc tính GROUP BY: Q
a
a
b
b
c
c
c
c
c
d
d
d
Chia các dòng thành các 
nhóm dựa trên tập thuộc 
tính chia nhóm
Q Count(S)
Q    S
a
b
c
d
2
2
5
3
10
2
9
5
10
8
6
4
10
16 Câu SQL: 
Select Q, count(S) 
From NV
Group by Q
Quan hệ NV
18
50
Tương tự cho các 
hàm SUM, MIN, 
MAX, AVG
 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
Select Phai, count(Manv) as SoNV from NhanVien 
Group by Phai
Select MaPH, count(Manv) from NhanVien Group by MaPH
 2. SQL – M NH Đ GROUP BYỆ Ề
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).ấ 
Select MaPH, Phai, count(Manv) from NhanVien 
Group by Phong, Phai
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?ế ố ượ ừ
Select TenPH, count(Manv) as SoLuongNV 
From NhanVien n, PhongBan p Where n.MaPh=p.MaPH
Group by TenPH
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.ộ
 2. SQL – M NH Đ GROUP BYỆ Ề
Câu h i 36ỏ : Đ m s đ án c a t ng nhân viên tham gia? ế ố ề ủ ừ
Select MaNV, count(MaDA) as SoDATG From PhanCong
Group by MaNV
- 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? ế ố ề
Select n.MaNV, HoTen, count(MaDA) as SoDATG 
From PhanCong pc, NhanVien n where pc.manv=n.manv 
Group by MaNV, HoTen
 2. SQL – M NH Đ HAVINGỆ Ề
Câu h i 38ỏ : Cho bi t nh ng nhân viên tham gia t 2 đ ế ữ ừ ề
án tr lên? ở
 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.
Select MaNV, count(MaDA) as SoDATG From PhanCong
Group by MaNV
Having count(MaDA) >=2
Select MaPH, count(Manv) from NhanVien Group by MaPH
Having count(Manv)>4
Câu h i 39ỏ : Cho bi t mã phòng ban có trên 4 nhân viên? ế
1. 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:
Nội dung : phát biểu bằng ngôn ngữ hình thức 
(phép tính quan hệ, đại số quan hệ, mã giả,…)
Bối cảnh: là những quan hệ có khả năng 
làm cho RBTV bị vi phạm.
Tầm ảnh hưởng: là bảng 2 chiều, xác 
định các thao tác ảnh hưởng (+) và thao 
tác không ảnh hưởng (­) lên các quan hệ 
nằm trong bối cảnh.
3. RBTV – B NG T M NH H NGẢ Ầ Ả ƯỞ
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 được
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Ố Ả Ệ
• Xét l c đ quan hượ ồ ệ
– 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 ’ủ ỉ ể ặ ữ
– N i dungộ :
∀∀n ∈ NHANVIEN: n.PHAI IN {‘Nam’,’N ’}ữ
– B i c nhố ả : quan h NHANVIENệ
– B ng t m nh h ng (TAH):ả ầ ả ưở
3.1. Ràng bu c toàn v n mi n giá trộ ẹ ề ị
Thêm Xóa S aử
NHANVIEN +(PHAI) - +(PHAI)
3. RBTV – TRÊN B I C NH LÀ 1 QUAN HỐ Ả Ệ
3.2. Ràng bu c toàn v n liên thu c tính: ộ ẹ ộ ràng bu c ộ
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ộ : 
∀d ∈ DEAN, d.NGBD_DK <= d.NGKT_DK
3. RBTV – TRÊN B I C NH LÀ 1 QUAN HỐ Ả Ệ
– B i c nhố ả : quan h DEAN ệ
– B ng t m nh h ngả ầ ả ưở :
Thêm Xóa S aử
DEAN + (NGBD_DK,
NGKT_DK)
- +(NGBD_DK,
NGKT_DK)
3.3. Ràng bu c toàn v n liên b : ộ ẹ ộ ràng bu c gi a các ộ ữ
b giá tr trong cùng m t quan h .ộ ị ộ ệ
Cho l c đ quan h : ượ ồ ệ
NHANVIEN(MaNV, HoTen, HESO, MucLuong)
Câu h i 42ỏ : các nhân viên có cùng h s l ng thì có cùng ệ ố ươ
m c l ng.ứ ươ
3. RBTV – TRÊN B I C NH LÀ 1 QUAN HỐ Ả Ệ
– N i dungộ :
• ∀n1,n2 ∈ NHANVIEN: n1.HESO=n2.HESO thì
 (n1.MUCLUONG = n2.MUCLUONG) 
– B i c nhố ả : quan h NHANVIENệ 
– B ng t m nh h ngả ầ ả ưở :
Thêm Xóa S aử
NHANVIEN + (HESO, 
MucLuong)
- +(HESO, MucLuong)
3. RBTV – B I C NH NHI U QUAN HỐ Ả Ề Ệ
• 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) 
Câu hỏi 43: M i tr ng phòng ph i là m t nhân viên trong công tyỗ ưở ả ộ .
– Nội dung: 
 ∀p ∈ PHONGBAN, ∃n ∈ NHANVIEN: 
p.TRPH= n.MANV
Hay: PHONGBAN[TRPH] ⊆ NHANVIEN[MANV])
3.4. Ràng bu c toàn v n tham chi uộ ẹ ế
3. RBTV – B I C NH NHI U QUAN HỐ Ả Ề Ệ
–B i c nhố ả : NHANVIEN, PHONGBAN
–B ng t m nh h ngả ầ ả ưở :
Thêm Xóa S aử
PHONGBAN +(TRPH) - +(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Ố Ả Ề Ệ
– B i c nhố ả : DATHANG, GIAOHANG
– B ng t m nh h ngả ầ ả ưở :
Câu h i 44ỏ : Ngày giao hàng không đ c tr c ngày đ t hàngượ ướ ặ
- N i dungộ :
∀g∈GIAO_HANG, 
∃!d∈DAT_HANG:d.MADH= g.MADH ∧ d.NGAYDH 
>= g.NGAYGH
Thêm Xóa S aử
DATHANG - - + (ngaydh)
GIAOHANG +(ngaygh) - + (ngaygh)
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) 
 Câu h i 45ỏ : M i phòng ban ph i có ít nh t m t đ a đi m phòngỗ ả ấ ộ ị ể
 - N i dungộ
• M i phòng ban ph i có ít nh t m t đ a đi m phòngỗ ả ấ ộ ị ể
∀∀p ∈ PHONGBAN, ∃d ∈ DIADIEM_PHG: 
p.MAPH = d.MAPH
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ộ ẹ ộ ổ ợ
– B i c nhố ả : PHONGBAN, DIADIEM_PHG
– B ng t m nh h ngả ầ ả ưở :
Thêm Xóa S aử
PHONGBAN + - -
DIADIEM_PHG - + + (MAPH)
PXUAT(SOPHIEU, NGAY, TONGTRIGIA)
CTIET_PX(SOPHIEU, MAHANG, SL, DG)
Câu h i 46ỏ : T ng tr giá c a 1 phi u xu t ph i b ng t ng ổ ị ủ ế ấ ả ằ ổ
tr giá các chi ti t xu t.ị ế ấ
3. RBTV – B I C NH NHI U QUAN HỐ Ả Ề Ệ
N i dungộ
∀ ∀px∈PXUAT,
 px.TONGTRIGIA = Σ(ct ∈ CTIET_PX ∧ ct.SOPHIEU = px.SOPHIEU) (ct.SL*ct.DG)
– B i c nhố ả : PXUAT,CTIET_PX 
– B ng t m nh h ngả ầ ả ưở :
Thêm Xóa S aử
PXUAT -(*) - + (tongtrigia)
CTIET_PX +(sl,dg) + + (sl,dg)
-(*) th i đi m thêm m t b vào PXUAT, Ở ờ ể ộ ộ
giá tr b đó t i TONGTRIGIA là tr ng.ị ộ ạ ố
GI I Ả BÀI T PẬ