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

+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ệ
Cú pháp: INSERT INTO Tên_Bảng(Danh sách tên cột)
VALUES(Danh sách các trị) [câuu hỏi con]

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

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)

và ‘_’cho 1 ký tự bất kỳ.
5. Tìm những người có tên mà có ký tự đầu tiên bất kỳ,ký tự tiềp theo là OA và tiếp theo là
dãy ký tự bất kỳ:
SELECT *FROM R1 WHERE hoten=’_OA%’;
+ Toán tử Is Null (not is Null):kiểm tra cho các giá trị rỗng (không rỗng);
C/ BÀI TẬP TỰ GIẢI
Bài tập 1:
Cho CSDL gồm có ba quan hệ như sau
NCC(MaNCC, TenNCC, DCNCC, DT)
SP(MaSP, TenSP, Loai)
SP_NCC(MaNCC, MaSP, SL)
Giải thích một số từ viết tắt:
- MaNCC là mã số nhà cung cấp
- TenNCC là tên nhà cung cấp có mã số tương ứng
- DCNCC là địa chỉ của nhà cung cấp
- DT là điện thoại nhà cung cấp
- MaSP là mã số sản phẩm
- TenSP là tên của sản phẩm
- Loại là chủng loại của mặt hàng
- SL là số lượng đã cung cấp
- Quan hệ NCC ( nhà cung cấp ) dùng để lưu trữ một số thông tin về các nhà cung cấp
- Quan hệ SP ( sản phẩm ) dùng để lưu trữ một số thông tin của các mặt hàng
- Quan hệ SPỴNCC dùng để lưu trữ một số thông tin về việc cung ứng sản phẩm của
NCC
Hãy viết biểu thức đại số quan hệ cho biết
a) Cho biết tên của nhà cung cấp có địa chỉ là Hà Nôi
b) Cho biết tên của các sản phẩm đã cung ứng bởi nhà cung cấp có mã số là HP.
c) Cho biết tên của các nhà cung ứng đã cung ứng các sản phẩm với số lượng 20
d) Cho biết tên của các nhà cung cấp đã cung ứng các sản phẩm
Bài tập 2:

BEER) LÀ quan hệ cho biết các loại bia thường bán ở các quán. Còn T( DRINKER, BEER)
cho biết những loại bia mà một khách hàng ưu thích.
Hãy viết các câu vấn tin sau bằng ngôn ngữ SQL:
a. In các quán có loại bia Long thích.
b. In những khách hàng thường đi uống ít nhất một quán có bia họ thích.
c. In ra những khách hàng không đến uống ít nhất tại một quán có bia họ ưu thích.
d. Xoa tất cả loại bia tiger ra khỏi quan hệ S(DRINKER, BEER)
e. Chèn thông tin Long thích bia Tiger.
f. Chèn tất thông tin Long thích tất cả các loại bia bán ở quán "San hô tím"
Bài số 4
Giả sử trong CSDL bia ở trên ta có thêm quan hệ BAN (BAR, BEER, SL) quan hệ cho biết số
lượng từng loại bia đã bán ở các quán. Hãy viết bằng SQL các vấn tin sau:
a. Tổng số bia của mỗi loại bia đã bán.
b. Số lượng trung bình mỗi loại bia được bán ở các quán.
c. Số lượng loại được bán ra nhiều nhất (bán chạy nhất)
Bài số 5
Giả sử có quan hệ S(F, S, O) với ý nghĩa là tập tin S có kích thước S thuộc chủ nhân O và
quan hệ FTD(F, T, D) với ý nghĩa F có kiểu T và nằm trong thư mục D. Hãy dùng ngôn ngữ
SQL để viết các câu vấn tin sau:
a. In ra chủ nhân và kiểu tin của tất cả các tập tin có kích thước tối thiểu là 10.000/
b. In ra tất c ả các tập tin được ông Tomax sở hữu/
c. In ra kích thước trung bình của các tập tin có trong thư mục BIN.
d. In ra tất cả các tập tin có trong thư mục f với tên chứa chuỗi con abc.
Bài số 6
Hãy dịch câu vấn tin sau sang đại số quan hệ.
SELECT OWNER
Trang 7
NHẬP MÔN CSDL QUAN HỆ Soạn bởi bộ môn Công nghệ phần mềm
FROM
WHERE FILE IN

sẽ tổng kết xem đã bán được những mặt hàng nào với số lượng và trị giá bán là bao nhiêu.
Trang 8
NHẬP MÔN CSDL QUAN HỆ Soạn bởi bộ môn Công nghệ phần mềm
STT
FIELD NAME TYPE WIDTH DEC DIỄN GIẢI
1 STT_DL Number 3 Số thứ tự đại lý
2 MA_HANG Character 3 Mã hàng
3 NGAY_BAN Date 8 Ngày bán
4 SOLG_BAN Number 6 Số lượng bán
5 TRIGIA_BAN Number 10 Trị giá bán
Yêu cầu : Viết các câu hỏi sau dưới dạng ngôn ngữ hỏi SQL
1. Tìm những mặt hàng đã bán trong tháng 1/95 tại đại lý số 3.
2. Tìm những mặt hàng đã mua trước năm 1995 và có trị giá mua > 500000.
3. Tìm tên và địa chỉ đại lý có mua bia Heineken.
4. Tìm tất cả các mặt hàng mà đại lý số 2 đã bán trong năm 1994.
5. Tìm tên những mặt hàng mà đại lý Vạn Lợi đã mua trước 01/01/95 và có số lượng
mua lớn hơn 150.
6. Tìm những mặt hàng đã được mua và bán trong cùng một ngày ở cùng một đại lý.
7. Tìm tên và địa chỉ đại lý có tổng giá trị mua trong một ngày lớn hơn 700000.
8. Tìm tổng giá trị mua và tổng giá trị bán của mặt hàng Coca Cola ở đại lý Tân Hiệp Hưng.
9. Tìm đơn giá mua trung bình của bia Sài Gòn trên các đại lý.
10. Tìm dơn giá mua trung bình của bia Sài gòn trên các đại lý.
11. Tìm tên, địa chỉ của đại lý và những mặt hàng có số lượng mua và số lượng bán
bằng nhau trong cùng một ngày.
12. Tìm tổng thu nhập từng ngày trên từng đại lý.
13. Tìm tổng giá trị mua trong tháng 1/95 tại đại lý Vạn Lợi.
14. Tìm số mặt hàng có bán ở từng đại lý.
15. Tìm tên và địa chỉ của đại lý có bán nhiều mặt hàng nhất.
Gợi ý :
Câu 1 sele dist a.ma_hang, a.ten_hg, b.ngay_ban;

from hanghoa a, mua b, ban c;
where a.ma_hang=b.ma_hang;
and a.ma_hang=c.ma_hang;
and b.stt_dl=c.stt_dl;
and b.ngay_mua=c.ngay_ban
Câu 7 sele dist a.*,b.ngay_mua, sum(b.trigia_mua) as tong_mua;
from daily a, mua b;
where a.stt_dl=b.stt_dl;
group by b.stt_dl, b.ngay_mua;
having sum(b.trigia_mua)>700000
Câu 8 sele sum(a.trigia_mua) as tong_mua,
sum(b.trigia_ban) as tong_ban;
from mua a, ban b, daily c, hanghoa d;
where a.stt_dl=b.stt_dl;
and a.stt_dl=c.stt_dl;
and a.ma_hang=b.ma_hang;
and a.ma_hang=d.ma_hang;
and upper(d.ten_hg)='COCA COLA';
and upper(c.ten_dl)='TAN HIEP HUNG'
Câu 9 sele dist a.*;
from daily a, ban b, hanghoa c;
where a.stt_dl=b.stt_dl;
and b.ma_hang=c.ma_hang;
and upper(c.ten_hg)='BIA TIGER';
and b.stt_dl in;
Trang 11
NHẬP MÔN CSDL QUAN HỆ Soạn bởi bộ môn Công nghệ phần mềm
(sele b.stt_dl;
from daily a, ban b, hanghoa c;
where a.stt_dl=b.stt_dl;

Câu 13 sele sum(b.trigia_mua) as tong_mua;
from daily a, mua b;
where a.stt_dl=b.stt_dl;
and upper(a.ten_dl)='VAN LOI';
and b.ngay_mua>={01/01/95};
and b.ngay_mua<={01/31/95};
group by a.stt_dl
Câu 14sele dist a.stt_dl as stt_dl, b.ten_dl as ten_dl,
b.dchi_dl as dchi_dl, a.ma_hang as ma_hang
into dbf tam;
from ban a, daily b;
where a.stt_dl=b.stt_dl
sele stt_dl, ten_dl,count(ma_hang) as so_mat_hg;
from tam;
group by stt_dl
Câu 15 sele stt_dl, ten_dl,dchi_dl, count(ma_hang)
as so_mat_hg into dbf tam1;
from tam;
group by stt_dl
Trang 13
NHẬP MÔN CSDL QUAN HỆ Soạn bởi bộ môn Công nghệ phần mềm
sele max(so_mat_hg) as max_so_mat_hg into dbf tam2;
from tam1
sele a.stt_dl, a.ten_dl, a.dchi_dl;
from tam1 a, tam2 b;
where a.so_mat_hg=b.max_so_mat_hg
Trang 14