NH P MÔN CSDL QUAN HẬ Ệ So n b i b môn Công ngh ph n m m ạ ở ộ ệ ầ ề
4. BµI TËP VÒ sql
M C TIÊU C A BÀI NÀY GIÚP NG I H CỤ Ủ ƯỜ Ọ
Hi u và phân bi t 3 nhóm l nh c a ngôn ng SQLể ệ ệ ủ ữ
Gi i m t s bài t p thao tác trên quan h có s d ng 3 nhóm l nhả ộ ố ậ ệ ử ụ ệ
trên.
V n d ng gi i quy t các bài toán t ng h p.ậ ụ ả ế ổ ợ
A/ NH C L I LÝ THUY TẮ Ạ Ế
I. CÁC NHÓM L NH C A NGÔN NG SQL Ệ Ủ Ữ
Phân bi t các nhóm câu l nh sau:ệ ệ
- Các l nh DDL: CREATE, ALTER, DROP.ệ
a. L nh CREATEệ
L nh này dùng đ t o ra các quan h nh TABLE,VIEW,INDEXệ ể ạ ệ ư
CREATE TABLE
-B ng là m t c u trúc c bn đ c t gi trong h th ng quan h .Có khuôn d ng hai chi u g mả ộ ấ ể ấ ữ ệ ố ệ ạ ề ồ
có các c t và hàng.Nó là y u t c b n cho các thao tác khác nhau.Có th nói vi c t o b ngộ ế ố ơ ả ể ệ ạ ả
là b c đ u tiên quan tr ng nh t đ thi t l p CSDL.ướ ầ ọ ấ ể ế ậ
-Cú pháp c a l nh này:ủ ệ
CREATE TABLE table-name(colom_name type(size)..)
Khi t o ra b ng chúng ta ph i ch ra ki u d li u c a c t và m i c t ch có th có môt ki uạ ả ả ỉ ể ữ ệ ủ ộ ỗ ộ ỉ ể ể
d li u duy nh t.Khi t o b ng ta có th đ a ra các ràng bu cữ ệ ấ ạ ả ể ư ộ
Các ràng bu c c a các tr ng có th là : primary key,foreign key ,unique,not null ... ộ ủ ườ ể
VD:T o b ng nhân viênạ ả
CREATE TABLE NHAN_VIEN (#NV varchar(4) constraint NV_Primary key,ho_tên
Varchar(25),Ng_sinh date,ch cv varchar(20),đia_ch varchar(30).l ng number(7));ứ ụ ỉ ư
trong VD trên ta t o ra m t ràng bu c là #NV đ c đ nh nghĩa là primary keyạ ộ ộ ượ ị
-Ta cũng có th t o ra b ng m i v i c u trúc và d li u t 1 b ng khác.ể ạ ả ớ ớ ấ ữ ệ ừ ả
Cú pháp:
CREATE TABLE TABLE_name[(colum_name..)]AS
SELECT statement;
VD:T o ra 1 b ng m i có tên là NVN (#NV,h _tên) t b ng NHAN_VIENạ ả ớ ọ ừ ả
CREATE TABLE NVN AS SELECT #NV,h _tên FROM NHAN_VIEN;ọ
b. L nh ALTERệ
-Dùng đ ho c là thêm m t hay nhi u tr ng vào bng ho c s a đ i m t c t hi nể ặ ộ ề ườ ặ ử ổ ộ ộ ệ
t i.SQL ANSI chu n không cho phép hu b các c t.ạ ẩ ỷ ỏ ộ
-Cú pháp:
ALTER TABLE TABLE_name ADD | MODIFY | DROP option (colum Datatype..)
+ADD:thêm c t m iộ ớ
+MODIFY:s a đ i c tử ổ ộ
+DROP option xoá b các ràng bu cỏ ộ
VD1:thêm tr ng gia đình ki u char(1) vào R1ườ ể
ALTER TABLE R1 ADD gia đình char(1);
VD2:Thay đ i tr ng Đ a_ch Varchar(30) trong R1 thành Đ a_Ch (20):ổ ườ ị ỉ ị ỉ
ALTER TABLE R1 MODIFY Đ a_Ch varchar(20);ị ỉ
Trang 1
NH P MÔN CSDL QUAN HẬ Ệ So n b i b môn Công ngh ph n m m ạ ở ộ ệ ầ ề
VD3:Hu b ràng bu c tr ng khoá #NV trong R1ỷ ỏ ộ ườ
ALTER TABLE R1 DROP constraint NV_prim
c. L nh DROPệ
-Dùng đ xoá b m t quan h ,khi ta xoá b m t bng c s thì t t c các VIEW,INDEX đ cể ỏ ộ ệ ỏ ộ ở ấ ượ
đ nh nghĩa trên bng đó s b xoá b .ị ẽ ị ỏ
Cú phap:
DROP TABLE/VIEW/INDEX Name;
VD:Xoá b Nhân_viên_id;ỏ
DROP INDEX Nhân_viên_id;
- Các l nh DML: SELECT, UPDATE, INSERT, DELETE, …ệ
a. L nh SELECTệ
M nh đ SELECT t ng ng v i toán t project(phép chi u p) c a đ i s quan h . Kh iệ ề ươ ứ ớ ử ế ủ ạ ố ệ ố
l nh SELECT g m có ba m nh đ chính:ệ ồ ệ ề
+SELECT:xác đ nh n i dung c a các c t c n đ a ra.ị ộ ủ ộ ấ ư
+FROM:danh sách các quan h đ c quét quaệ ượ
+WHERE: ng v i m t kh ng đ nh l a ch n c a đ i s quan h . ứ ớ ộ ẳ ị ự ọ ủ ạ ố ệ
-L nh SELECT th ng có d ng:ệ ườ ạ
SELECT [distinct]*/A1..An FROM R1, R2 ...,Rm
[WHERE p];
Trong đó :
Ai là các thu c tính ộ
Rj là các quan h (có th là các TABLEs,VIEWs..)Ta có th dùng các bí danh cho các Ai,rj.ệ ể ể
p:là đi u ki n ràng bu c.ề ệ ộ
đây WHERE có th có ho c không.ở ể ặ
Dùng *đ ch t t c các thu c tính c a các quan h đ c ch nể ỉ ấ ả ộ ủ ệ ượ ọ
-H i đáp này t ng đ ng v i bi u di n sau trong đ i s quan h :ỏ ươ ư ớ ể ễ ạ ố ệ
pA1..An[S p(r1..rm)]
-Đ lo i b các b giá tr (các hàng) trùng nhau ta thêm t khoá Distinct vào sau SELECTể ạ ỏ ộ ị ừ
(tr c đây SQL thêm t khoá unique).ướ ừ
-Trong kh ng đ nh p:ta có th dùng các liên t logic and,or,not khi k t h p nhi u đi u ki nẳ ị ể ừ ế ợ ề ề ệ
VD1:Đ hi n các thông tin v m t nhân viên nào đóể ệ ề ộ
g m(#,H _tên,N_sinh,Ch c_v ,đ a_ch ,l ng)ồ ọ ứ ụ ị ỉ ư
SELECT Distinc * FROM R1;
Đ a ra (h _tên,Nsinh,ch c_v ,đ a_ch ,l ng,tên_phòng) v i đi u ki n l ng. 500.000 vàư ọ ứ ụ ị ỉ ư ớ ề ệ ư
đia_ch không Hà n iỉ ở ộ
SELECT Ho_tên,Nsinhn,ch c_v ,đ a_ch ,l ng,tên_phòng ứ ụ ị ỉ ư
FROM Nhânviên R1,Liênkêt R2,Phong R3
WHERE (R1.l ng. 500.000) and (not R1.đ a_ch =’Hà n i’) andư ị ỉ ộ
(R1.#NV=R2.#NV) and (R2.#MP=R3.#MP);
-Trong l nh trên ta đã dùng R1,R2,R3 làm bí danh cho Nhânviên, Liênkêt,Phongệ
Các bí danh đó ch có tác d ng trong m t câu l nh ỉ ụ ộ ệ
b. Nhóm l nh INSERT,UPDATE,DELETE:ệ
Thêm m t b vào quan hộ ộ ệ
Cu phap: INSERT INTO Tên_Bang(Danh sach tên c t)( ( ) ( ộ
VALUES(Danh sach cac tri) [câuu hoi con]( ( * )
VD:chèn 1 hàng (‘020’,’Nguy n tr ng Nghĩa’,B o v ’,’Hà n i’,’800.000’) vào R1ễ ọ ả ệ ộ
INSERT INTO R1 VALUES(‘020’,’Nguy n tr ng Nghĩa’,B o v ’,’Hà n i’,800.000);ễ ọ ả ệ ộ
Xóa các b ng ả
Dùng đ xoá b 1 ho c nhi u b trong quan hể ỏ ặ ề ộ ệ
DELETE FROM R[WHERE P]
Nh ng b nào tho mãn đk P thì m i b hu b kh i quan h Rữ ộ ớ ị ỷ ỏ ỏ ệ
VD: DELETE FROM R1 WHERE ng_sinh. ’01-01-1935’;
Trang 2
NH P MÔN CSDL QUAN HẬ Ệ So n b i b môn Công ngh ph n m m ạ ở ộ ệ ầ ề
Xoá b t t c các nhân viên ta dùng l nh:ỏ ấ ả ệ
DELETE FROM R1;
S a d li uử ữ ệ
Cu phap: UPDATE [Tên_bang]( ( )
SET [Tên_c t=Bi u th c,...]ộ ể ư(
[FROM Tên_Bang])
[WHERE btđk]
- Các l nh DCL: GRANT, REVOKE.ệ
GRANT Quy n truy c p ON Tên_b ng/view TO Tên_User [ with GRANTề ậ ả
option]
- Các quy n truy nh p CSDL g m:ề ậ ồ
+Read(đ c)ọ
+ SELECT(ch n)ọ
+ Write(ghi)
+ INSERT(b sung)ổ
+ UPDATE(s a đ i)ử ổ
+ DELETE,run.
- Tên ng i s d ng:Tên c a m t ng i ,m t nhóm ng i ho c danh sách ng iườ ử ụ ủ ộ ườ ộ ườ ặ ườ
public:cho t t c m i ng i cùng đ c s d ng.ấ ọ ườ ượ ử ụ
- T khoá :with grant OPTION:đm bo đ ng i s d ng có th ti p t c trao quy n s d ngừ ể ườ ử ụ ể ế ụ ề ử ụ
cho ng i khác.ườ
VD:Cho phép SELECT,INSERT,UPDATE trên b ng R1 cho Nghĩa,Khôiả
GRANT SELECT,INSERT,UPDATE ON R1 TO Nghĩa,Khôi with grant option;
T c là Nghĩa,Khôi có th trao quy n trên cho ng i khác.ứ ể ề ườ
*Đ hu b quy n truy nh pể ỷ ỏ ề ậ
REVOKE privileges ON object FROM user
VD: Đ hu b quy n UPDATE t Nghĩa:ể ỷ ỏ ề ừ
REVOKE UPDATE ON R1 FROM Nghĩa;
II. CÁC VÍ DỤ
Ví d 1:ụ
Cho quan h SINHVIEN (#masv char(10), hoten char(25), ngaysinh datetime, d1ệ
double, d2 double, d3 double). Trong đó, masv là thu c tính khóa c a quan h trên.ộ ủ ệ
a) Hãy t o l p c u trúc trên.ạ ậ ấ
b) Chèn m t c t gt boolean vào b ng trên.ộ ộ ả
L i gi i:ờ ả
a) Create Table SINHVIEN (MaSV Char(10), Hoten Char(25) not null, Ngaysinh
Date, d1 double, d2 double, d3 double, CONSTRAINT [khoa] Primary Key ([MaSV]))
b) Alter table sinhvien add gt yesno;
Ví d 2:ụ
Cho CSDL g m 2 quan h :ồ ệ
LOP (#Malop char (10), tenlop char(20))
SINHVIEN (malop char (10), #masv char(10), hoten char(20), ngaysinh datetime, d1 double,
d2 double, d3 double)
a) Hãy đ a ra các thông tin c a các sinh viên bao g m: tenlop, masv, hoten, dtb c aư ủ ồ ủ
m i sinh viên.ỗ
b) Đ a ra t ng s sinh viên c a m i l p.ư ổ ố ủ ỗ ớ
Trang 3
NH P MÔN CSDL QUAN HẬ Ệ So n b i b môn Công ngh ph n m m ạ ở ộ ệ ầ ề
L i gi i:ờ ả
a) SELECT lop.tenlop, sv.masv, ([d1]+[d2]+[d3])/3 AS dtb
FROM lop, sv WHERE lop.malop = sv.malop;
b) SELECT lop.tenlop, Count(sv.masv) AS CountOfmasv
FROM lop, sv WHERE lop.malop = sv.malop
GROUP BY lop.tenlop;
III. M T S L U ÝỘ Ố Ư
Các câu l nh này có th th nghi m trên m t s h qu n tr CSDL nh SQL, Access,..ệ ể ử ệ ộ ố ệ ả ị ư
Phân bi t đi u ki n sau m nh đ Where và sau m nh đ Having. ệ ề ệ ệ ề ệ ề
B/ BÀI T P M UẬ Ẫ
Bài s 1:ố
Cho CSDL c a h th ng Qu n lý nhân s :ủ ệ ố ả ự
DONVI(MaDV C(3), TenDV C(20), Diachi C(20), MaNPT C(4))
NHANVIEN(MaNV C(4), Hoten C(20), NHVu C(20), Luong N(8), Phucap N(6), MaDV C(3))
Hãy đ a ra danh sách t t c các đ n v có trong t ch c này.ư ấ ả ơ ị ổ ứ
H ng d n:ướ ẫ
Ta th y các thông tin l y trong b ng đ n v và câu l nh thu c nhóm khai thác d li u.ấ ấ ả ơ ị ệ ộ ữ ệ
L i gi i: ờ ả
SELECT TenDV, Diachi FROM DONVI
Bài s 2:ố
Đ qu n lý kinh doanh dùng các b ng sau:ể ả ả
+ HH(hàng hoá): MaHH C(3), TenHH C(20), Qcach C(20), DVT C(5), DGIA N(10)
+ CH(c a hàng): MaCH C(3), TenCH C(20), DDiem C(20), PTrach C(4)ử
+ KH(khách hàng): MaKH C(4), TenKH C(20), Loai C(2), Diachi C(20)
+ CT(ch ng t ): Sohieu C(12), Ngay D, LoaiCT C(1), MaKH C(4), MaCH C(3), MaHH C(3),ứ ừ
SoLuong N(6).
a) Xem trong bng CT có nh ng lo i hàng hoá nào đ c xu t.ữ ạ ượ ấ
H ng d n: ướ ẫ
Ta th y trong b ng CT, m i ch ng t có th bao g m nhi u MaHH khác nhau, nhấ ả ỗ ứ ừ ể ồ ề ư
v y trong b ng CT s có nhi u MaHH gi ng nhau, v i yêu c u trên ta ch c n đ a ra cácậ ả ẽ ề ố ớ ầ ỉ ầ ư
MaHH khác nhau.
L i gi i:ờ ả
SELECT DISTINCT MaHH FROM CT
SELECT DISTINCT CT.MaHH, TenHH FROM CT, HH WHERE CT.MaHH = HH.MaHH
b) Đ a ra danh sách các nhân viên có l ng >=200000ư ư
SELECT * FROM NHANVIEN WHERE Luong >= 200000
Trang 4
NH P MÔN CSDL QUAN HẬ Ệ So n b i b môn Công ngh ph n m m ạ ở ộ ệ ầ ề
c) Cho xem danh sách g m 3 c t Mã đ n v , h tên, nhi m v t b ng nhân viên và đ cồ ộ ơ ị ọ ệ ụ ừ ả ượ
s p x p theo mã đ n v , cùng đ n v theo nhi m v :ắ ế ơ ị ơ ị ệ ụ
SELECT MaDV, Hoten, NHVu FROM NHANVIEN ORDER BY MaDV, NHVu
Mã đ n v , h tên, l ng t b ng NHANVIEN đ c s p x p theo mã đ n v , cùng đ n vơ ị ọ ươ ừ ả ượ ắ ế ơ ị ơ ị
theo l ng gim d n:ươ ầ
SELECT MaDV, Hoten, Luong FROM NHANVIEN ORDER BY MaDV, Luong DESC
Chú ý:
1. Tên các c t trong <đi u ki n ràng bu c> sau WHERE không nh t thi t phi có sauộ ề ệ ộ ấ ế
SELECT, các c t này không nh t thi t ph i có trong b ng k t qu .ộ ấ ế ả ả ế ả
2. Tên các c t sau ORDER BY… b t bu c ph i có sau SELECT, t c là các c t này b t bu cộ ắ ộ ả ứ ộ ắ ộ
ph i có trong b ng k t qu .ả ả ế ả
*) GROUP BY <tên c t>: N u có dùng đ nhóm các hàng có cùng giá tr c a tên c t đ i v iộ ế ể ị ủ ộ ố ớ
m i nhóm thì cùng th c hi n m t thao tác tính toán nào đó.ỗ ự ệ ộ
3. Cho xem mã hàng hoá, tên hàng hoá và t ng s ti n bán đ c c a t ng m t hàng:ổ ố ề ượ ủ ừ ặ
SELECT MaCT, MaHH, TenHH, SUM(Soluong*Dongia) FROm CT, HH WHERE CT.MaHH =
HH.MaHH And Loai = “X” GROUP BY CT.MaHH
Cho xem m• đn v , tên đn v , m c l ng bình quân và s nhân viên c a t ng đn v :ị ị ứ ư ố ủ ừ ị
SELECT a.MaDV, TenDV, AGV(Luong), Cont (A.*) FROM NHANVIEN a, DONVI b WHERE
a.MaDV = b.MaDV GROUP BY a.MaDV
*) Ph n HAVING <đi u ki n ràng bu c> ch ph c v cho GROUP BYầ ề ệ ộ ỉ ụ ụ
Bài s 3:ố
R1=Nhân viên (#NV, Ho_tên, Nsinh, ngh nghi p, Đ a ch , l ng)ề ệ ị ỉ ươ
R2=Liên k t (#NV, #MP)ế
R3=Phong (#Mp, Tên_phong, tel)
1. Đ hi n các thông tin v m t nhân viên nào đó g m(#NV , H _tên, N_sinh, Ch c_v ,ể ệ ề ộ ồ ọ ứ ụ
đ a_ch , l ng)ị ỉ ươ
SELECT Distinc * FROM R1;
2. Đ a ra (h _tên,Nsinh,ch c_v ,đ a_ch ,l ng,tên_phòng) v i đI u ki n l ng. 500.000 vàư ọ ứ ụ ị ỉ ư ớ ề ệ ươ
đia_ch không Hà n i.ỉ ở ộ
SELECT Ho_tên,Nsinhn,ch c_v ,đ a_ch ,l ng,tên_phòng ứ ụ ị ỉ ư
FROM Nhânviên R1,Liênkêt R2,Phong R3
WHERE (R1.l ng. 500.000) and (not R1.đ a_ch =’Hà n i’) andư ị ỉ ộ
(R1.#NV=R2.#NV) and (R2.#MP=R3.#MP);
- Trong l nh trên ta đã dùng R1,R2,R3 làm bí danh cho Nhânviên, Liênkêt, Phongệ
Các bí danh đó ch có tác d ng trong m t câu l nh ỉ ụ ộ ệ
Các ví d sau này ta dùng R1,R2,R3 đ thay cho các b ng trên cho g n ụ ể ả ọ
Có 4 toán t hay đ c dùng v i các ki u d li u.Trong m nh đ WHERE là:ử ượ ớ ể ữ ệ ệ ề
In (not In)
Between..and..(not between..)
Like(not like)
Is null (not is Null).
+ Toán t In (not In):Dùng đ ki m tra giá tr trong (không n m trong) m t danh sách đ cử ể ể ị ằ ộ ượ
ch ra.ỉ
3. Đ a ra nh ng ng i có đia_ch Hà n i và Hà tây.ư ữ ườ ỉ ở ộ
SELECT * FROM R1 WHERE đia_ch in (‘Hà n i’,’Hà tây’);ỉ ộ
Trang 5