Hệ Cơ sở dữ liệu

Tài liệu: sẽ cập nhật từ từ

Bài 1. Tạo CSDL thông tin sinh viên đơn giản bằng Access và viết 8 câu truy vấn:

(video hướng dẫn ở bên dưới)

1. Hiển thị thông tin sinh viên gồm mã sv, họ tên, ngày sinh, quê quán, lớp. Tên là cau_1

select masv, hoten,ngaysinh, ten_lop,ten_qq
from sinhvien a, lop b, quequan c
where a.ma_lop = b.ma_lop and a.ma_qq = c.ma_qq

2. từ bảng sinh viên hiển thị những sinh viên sinh tháng 2 năm 1995. Tên là cau_2

SELECT *

FROM SINHVIEN

WHERE MONTH(NGAYSINH) = 2 AND YEAR(NGAYSINH) = 1995

3. Lấy ra những sinh viên học lớp cntt8 và quê ở AG. Tên là cau_3

select *

from sinhvien

where ma_lop='cntt8' and ma_qq='ag'

4. Lấy ra những sinh viên học lớp đại học công nghệ thông tin 8 và quê ở an giang. Tên là cau_4

select masv,hoten,ngaysinh, ten_lop,ten_qq

from sinhvien a, lop b, quequan c

where a.ma_lop=b.ma_lop and a.ma_qq = c.ma_qq

and ten_lop='đại học công nghệ thông tin 8' and ten_qq='an giang'

5. Thống kê xem có bao nhiêu sinh viên ở ag. Tên là cau_5

select count(*)

from sinhvien

where ma_qq='ag'

6. Thống kê ở mỗi quê quán (theo ma_qq) có bao nhiêu sv. Tên là cau_6

select ma_qq,count(*) as SL_SV

from sinhvien

group by ma_qq

7. Cho biết quê quán nào có nhiều sinh viên học nhất. Tên là cau_7

SELECT ma_qq

FROM cau_6

WHERE SL_SV = (select max(SL_SV) FROM cau_6) ;

8. Cho biết quê quán nào có ít sinh viên học nhất. Tên là cau_8

SELECT ma_qq

FROM cau_6

WHERE SL_SV = (select min(SL_SV) FROM cau_6) ;

Coi clip nhớ like giùm nghen mấy bạn.

Bài 2. Restore CSDL dữ liệu Northwind: http://vhlong.blogspot.com/2015/11/co-so-du-lieu-quan-ly-ban-hang-northwind.html

**** Cách Restore 1 CSDL: http://vhlong.blogspot.com/2015/12/6-co-so-du-lieu-phuc-vu-hoc-mon-he-co.html

Bài 3. Tạo CSDL từ một file SQL Script

File SQL Script: https://drive.google.com/file/d/0B7ajrVO_lAt7dnl2Tndxcm1xZ1E/view?usp=sharing

Clip hướng dẫn:

Bài 4. Kiểu dữ liệu trong SQL Server
Khi thực hành với SQL server thì đa số chúng ta có người thì hiểu lơ mơ, có người thì chỉ chép lại bài thực hành mà không hiểu kiểu biến đó được dùng để làm gì. Cái đó rất là bất cập, để có thể hiểu rõ hơn về các kiểu dữ liệu khi ép kiểu mình xin đưa ra những tổng hợp mà mình tìm hiểu và biết được.

Diễn giải:

Binary: Là kiểu dữ liệu chứa dạng số ở hệ hexa, gồm 3 kiểu dữ liệu Binary, Varbinary, Image.

Text: Là kiểu ký tự, chứa chữ cái, ký hiệu, số, gồm những kiểu dữ liệu sau:

– Char: Kiểu ký tự, khi xác định độ dài thì độ dài trong CSDL sẽ xác định theo độ dài đặt trước mà không theo độ dài dữ liệu thực có, không sử dụng với ký tự dạng Unicode, độ dài tối đa là 8000.

– Nchar: Tương tự như Char nhưng sử dụng với ký tự Unicode, độ dài tối đa 4000.

– Nvarchar: Tương tự như NChar nhưng kích thước trong CSDL sẽ là kích thước thực dữ liệu hiện có, không tính theo kích thước đặt trước, kích thước tối đa là 4000.

– Varchar: Tương tự như Nvarchar nhưng không hỗ trợ Unicode.

– Text: Kiểu văn bản, chứa cả ký tự xuống dòng, lưu trữ theo dạng văn bản, có kích thước lớn, có thể lên đến vài Gb, cơ chế quản lý kiểu dữ liệu theo dạng con trỏ và cách thức chèn và cập nhật sẽ khác, kiểu dữ liệu này không hỗ trợ cho Unicode.

– Ntext: Tương tự như Text nhưng có hỗ trợ Unicode.

Data/Time: Kiểu dữ liệu ngày, thời gian, ngày và thời gian, gồm 2 kiểu:

– DateTime: Đầy đủ cả ngày và thời gian.

– SmallDataTime: Chỉ ngày hoặc thời gian.

Numeric: Dữ liệu kiểu số, gồm các kiểu dữ liệu sau:

– Int, smallint, tinyint, bigint: Số nguyên

– Float, real, decimal, numeric: Số thực.

Monetary: Tiền tệ:

– Money, Smallmoney.

Bit: Kiểu số 0, 1.

Sql_variant: Là kiểu dữ liệu xác định theo kiểu dữ liệu khác, một cột dữ liệu được định nghĩa dữ liệu kiểu này có thể lưu trữ nhiều dữ liệu có kiểu khác nhau trong cùng một bảng. Ví dụ có thể lưu trữ nhiều kiểu dữ liệu int, binary, char, nhưng không chứa dữ liệu kiểu text, ntext, image, timestamp, sql_variant.

Timestamp: Là kiểu dữ liệu có kích thước 8 byte, lưu trữ dạng số nhị phân do hệ thống tự sinh ra, mỗi giá trị timestamp trong CSDL là duy nhất.

XML: Là kiểu dùng để lưu trữ dữ liệu dưới dạng xm

Bài 5. Script Quản Lý Bài (chưa có hoàn thiện)USE master;GOcreate database QuanLyBaiBao;GOuse QuanLyBaiBao;GOcreate table TacGia( MaTacGia nvarchar(10) PRIMARY KEY, TenTacGia nvarchar(50) NOT NULL, SDTTacGia nvarchar(11) NOT NULL,);create table TheLoai( MaTheLoai nvarchar(10) PRIMARY KEY, MoTa nvarchar(50) NOT NULL);create table BaiBao( MaBaiBao nvarchar(20) PRIMARY KEY, TieuDe nvarchar(200) NOT NULL, NoiDung ntext NOT NULL, NgayGoi datetime NOT NULL, MaTacGia nvarchar(10) REFERENCES TacGia(MaTacGia));-- SỬA CẤU TRÚC BẢNGALTER TABLE TacGiaADD DiaChi nvarchar(200)ALTER TABLE BaiBaoADD MaTheLoai nvarchar(10) REFERENCES TheLoai(MaTheLoai);-- Thêm dữ liệu vào bảngINSERT INTO TacGia VALUES('TuNhi',N'Tú Nhi','0988988810',N'Chắc Cà Đao') ;INSERT INTO TacGia VALUES('LamPhuong',N'Lam Phương','0989989898',N'Kiên Giang') ;INSERT INTO TacGia VALUES('HoangTTho',N'Hoàng Thi Thơ','0949494949',N'TP. HCM') ;---INSERT INTO TacGia(MaTacGia,TenTacGia,SDTTacGia) VALUES ('PDuy',N'Phạm Duy','0191965323');INSERT INTO TacGia(TenTacGia,MaTacGia,SDTTacGia) VALUES (N'Trần Thiện Thanh','3T','0987564321');select * from TacGiaBài 5. Bài tập về nhà
Bài Tập Về Nhà: (ví dụ từ BaiBao2 đã vừa học rồi Biểu tượng cảm xúc smile )
1: Cú pháp xóa 1 cột đã có trong bảng.
*Cú pháp: ALTER TABLE table_name
DROP COLUMN column_name;
Trong đó:
table_name: là tên bảng chứa cột sẽ bị xóa.
colunm_name : là tên cột sẽ bị xóa khỏi bảng.
Ví dụ: (lấy vd từ bảng BaiBao2)
ALTER TABLE BaiBao2
DROP COLUMN NoiDung;
=> Xóa cột NoiDung trong bảng BaiBao2.
2: Cú pháp thay đổi kiểu dữ liệu của 1 cột đã có trong bảng.
*Cú pháp: ALTER TABLE table_name
ALTER COLUMN column_name datatype;
Trong đó:
table_name: là tên bảng có cột cần thay đổi kiểu dữ liệu.
colunm_name : là tên cột sẽ được thay đổi kiểu dữ liệu.
datatype: là kiểu dữ liệu của cột, ví dụ int, varchar, nvarchar, decimal, …
Ví dụ: (lấy ví dụ từ bảng BaiBao2)
ALTER TABLE BaiBao2
ALTER COLUMN TieuDe ntext;
=> Thay đổi kiểu dữ liệu của cột TieuDe từ “nvarchar(200)” thành “ntext”.
3:Cú pháp thay đổi tên cột trong bảng.
*Cú Pháp: ALTER TABLE table_name
ALTER COLUMN column_name1 RENAME TO column_name2;
Trong đó:
table_name: là tên bảng có cột cần thay đổi tên.
colunm_name1 : là tên cột ban đầu sẽ thay đổi.
column_name2: là tên cột mà bạn đã được thay đổi.
Ví dụ: (lấy ví dụ từ bảng BaiBao2)
ALTER TABLE BaiBao2
ALTER COLUMN TieuDe RENAME TO TuaDe;
=> Thay đổi tên cột “TieuDe” thành “TuaDe”.
4:Cú pháp thay đổi tên bảng.
*Cú pháp: ALTER TABLE table_name1 RENAME TO table_name2;
Trong đó:
table_name1: là tên ban đầu sẽ thay đổi của bảng.
table_name2: là tên sau khi thay đổi của bảng.
Ví dụ: (lấy ví dụ từ bảng BaiBao2)
ALTER TABLE BaiBao2 RENAME TO BaiPhongSu;
=> Đổi tên bảng “BaiBao2” thành “BaiPhongSu”.
**câu 3 và 4: suy diễn thôi Biểu tượng cảm xúc pacman Biểu tượng cảm xúc pacman Biểu tượng cảm xúc colonthree **Bài 6. Lệnh Select

Câu lệnh SELECT (mang nghĩa lấy dữ liệu hay câu lệnh truy vấn) trong SQL trả về một tập kết quả các bản ghi từ một hoặc nhiều bảng.

Nó được sử dụng để lấy dữ liệu từ một hoặc nhiều bảng trong cơ sở dữ liệu, SELECT là lệnh thường dùng nhất của ngôn ngữ sửa đổi dữ liệu (tiếng Anh: Data Manipulation Language - DML). Trong việc tạo ra câu truy vấn SELECT, người sử dụng phải đưa ra mô tả cho những dữ liệu mình muốn lấy ra chứ không chỉ ra những hành động vật lý nào bắt buộc phải thực hiện để lấy ra kết quả đó. Hệ thống cơ sở dữ liệu, hay chính xác hơn là bộ tối ưu hoá câu truy vấn (tiếng Anh: query optimizer) sẽ dịch từ câu truy vấn sang kế hoạch truy vấntối ưu.

Những từ khóa liên quan tới SELECT bao gồm:

FROM dùng để chỉ định dữ liệu sẽ được lấy ra từ những bảng nào, và các bảng đó quan hệ với nhau như thế nào.
WHERE điều kiện để liên kết các bảng hoặc điều kiện để lấy những thuộc tính từ bảng from.
GROUP BY dùng để kết hợp các bản ghi có những giá trị liên quan với nhau thành các phần tử của một tập hợp nhỏ hơn các bản ghi.
HAVING dùng để xác định những bản ghi nào, là kết quả từ từ khóa GROUP BY, sẽ được lấy ra.
ORDER BY dùng để xác định dữ liệu lấy ra sẽ được sắp xếp theo những cột nào.

Ví dụ

Bảng "T" Câu truy vấn Kết quả

C1	C2
1	a
2	b

SELECT * FROM T;

C1	C2
1	a
2	b

C1	C2
1	a
2	b

SELECT C1 FROM T;

C1
1
2

C1	C2
1	a
2	b

SELECT * FROM T WHERE C1 = 1;

C1	C2
1	a

Cho một bảng T, câu truy vấn SELECT * FROM T; sẽ trả về kết quả là tất cả các phần tử của tất cả các bản ghi trong bảng T.

Với cùng bảng đó, câu truy vấn SELECT C1 FROM T; sẽ trả về tất cả các phần tử của trường (cột) C1 của tất cả các bản ghi trong bảng T— Theo thuật ngữ Đại số quan hệ, một projection đã được thực hiện.

Cũng bản đó, câu truy vấn SELECT * FROM T WHERE C1 = 1; sẽ trả về kết quả là tất cả các phần tử của tất cả các bản ghi có giá trị của trường (cột) C1 bằng '1' — Theo thuật ngữ Đại số quan hệ, một selection được thực hiện vì đã có từ khóa WHERE.

Tham khảo thêm về Select

1. http://www.w3schools.com/sql/sql_select.asp

2. http://www.tutorialspoint.com/sql/sql-select-query.htm

Bài 7. Các câu truy vấn cơ bản cho CSDL Quản lý công trình xây dựng (CSDL1)

-- Lấy dữ liệu bảng công trình (cgtrinh)

SELECT * FROM CGTRINH;

-- Lấy dữ liệu ten_ctr, diachi_ctr trong bảng công trình

SELECT TEN_CTR,DIACHI_CTR

FROM CGTRINH;

-- Lấy dữ liệu công trình ở ‘can tho’

SELECT *

FROM CGTRINH

WHERE TINH_THANH='can tho'

-- Công trình ở cần thơ và có kinh phí lớn hơn 200

SELECT *

FROM CGTRINH

WHERE TINH_THANH='can tho' AND KINH_PHI>200

-- Công trình ở cần thơ hoặc công trình do thầu ‘tran khai hoan’ xây dựng

SELECT *

FROM CGTRINH

WHERE TINH_THANH='can tho' AND TEN_THAU='tran khai hoan'

-- Những công trình có ngày bắt đầu trong giai đọan tháng 6 đến tháng 9 năm 1994

SELECT *

FROM CGTRINH

WHERE NGAY_BD>='1994-06-01' AND NGAY_BD<'1994-10-01'

-- Những công trình có ngày bắt đầu trong giai đọan tháng 6 đến tháng 9 năm 1994

SELECT *

FROM CGTRINH

WHERE NGAY_BD BETWEEN '1994-06-01' AND '1994-09-30'

-- Những công trình có ngày bắt đầu KHÔNG TRONG giai đọan tháng 6 đến tháng 9 năm 1994

SELECT *

FROM CGTRINH

WHERE NGAY_BD NOT BETWEEN '1994-06-01' AND '1994-09-30'

-- Những công trình có ngày bắt đầu trong giai đọan tháng 6 đến tháng 9 năm 1994

SELECT * FROM CGTRINH

WHERE MONTH(NGAY_BD) IN (6,7,8,9) AND YEAR(NGAY_BD)=1994

-- Lấy dữ liệu những công trình ở ‘can tho’ và ‘vinh long’

SELECT *

FROM cgtrinh

WHERE tinh_thanh IN ('can tho','vinh long')

-- Công nhân họ ‘le’

select * from CONGNHAN

where HOTEN_CN like 'le %'

-- Công nhân có từ ‘chi’ trong họ tên

select * from CONGNHAN

where HOTEN_CN like '%chi%'

-- Công nhân có họ bắt đầu là ‘d’ hoặc ‘l’

select * from CONGNHAN

where HOTEN_CN like '[d,l]%'

-- Công nhân có họ có đúng 2 ký tự và bắt đầu bằng chữ ‘v’

select * from CONGNHAN

where HOTEN_CN like 'v_ %'

-- Những công nhân có chuyên môn ‘dien’, sắp xếp theo thứ tự giảm dần năm sinh

select * from congnhan

where ch_mon='dien'

order by nams_cn desc

-- Cho biết tên các tỉnh thành có xây dựng công trình trong CSDL

select distinct tinh_thanh

from cgtrinh

-- Cho biết tên thầu và tỉnh thành mà thầu đó xây dựng công trình

select distinct ten_thau,tinh_thanh

from cgtrinh

-- Top 3 công trình đầu tiên trong hệ thống

select top 3 * from cgtrinh

-- Top 3 công trình có kinh phí thấp nhất

select top 3 * from cgtrinh

order by kinh_phi

-- Top 3 công trình có kinh phí lớn nhất

select top 3 * from cgtrinh

order by kinh_phi desc

Bài 8. Tài liệu Thực hành Hệ CSDL Đại học Cần Thơ: Tải về

Bài 9. Thực hành CSDL 2

-- tìm tên các báo/tạp chí có đăng bài của tác giả Tô Phan

-- Cho biết đăng trên số nào? Được phát hành ngày nào

select ten, d.so_bao_tc,ngay

from baotchi a, vietbai b, phathanh c , dangbai d

where a.ma_bao_tc = c.ma_bao_tc

and b.stt_bai = d.stt_bai

and c.ma_bao_tc = d.ma_bao_tc

and c.so_bao_tc = d.so_bao_tc

and but_hieu = 'to phan'

-- nội dung của tạp chí kiến thức ngày nay số 156

-- gồm những bài tựa gì ? Đăng từ trang nào?

select tua,trang

from baotchi a, dangbai b, baibao c

where a.ma_bao_tc = b.ma_bao_tc

and b.stt_bai = c.stt_bai

and ten='kien thuc ngay nay'

and so_bao_tc = '156'

-- cho biết tựa và thể loại tất cả các bài báo do tác giả nguyễn trọng vinh

-- sáng tác đã được đăng trên các báo / tạp chí xuất bản hàng tuần.

-- Cho biết đồng thời tên và số của tờ báo / tạp chí

-- tương ứng mỗi bài được đăng

select tua,dgiai, ten, so_bao_tc

from vietbai a, baibao b, theloai c, baotchi d, dangbai e

where a.stt_bai = b.stt_bai

and b.ma_thloai = c.ma

and d.ma_bao_tc = e.ma_bao_tc

and e.stt_bai = a.stt_bai

and but_hieu='ng trong vinh'

and d.dinh_ky = '1 tuan'

-- cho biết số báo tạp chí và các ngày phát hành tương ứng trong năm 1994

-- của tờ báo lao động

select so_bao_tc,ngay

from phathanh a, baotchi b

where a.ma_bao_tc = b.ma_bao_tc

and YEAR(ngay)=1994

and b.ten='lao dong'

-- tờ tuổi trẻ chủ nhật số 23 có những bút ký nào? Cho biết tựa của chúng

select tua

from baotchi a, dangbai b, baibao c, theloai d

where a.ma_bao_tc = b.ma_bao_tc and a.ten='tuoi tre chu nhat'

and c.ma_thloai = d.ma

and c.stt_bai = b.stt_bai

and so_bao_tc = '23'

and dgiai='but ky'

-- cho biết ngày đăng, tên của tờ báo / tạp chí đã đăng bài chuc vui nam lon

select ngay,ten

from baibao a, dangbai b, phathanh c, baotchi d

where a.stt_bai = b.stt_bai

and b.so_bao_tc = c.so_bao_tc

and b.ma_bao_tc = d.ma_bao_tc

and tua='chuc vui nam lon'

-- cho biết tên , địa chỉ và số điện thoại của tờ báo tạp chí đã đăng bài tìm

-- hiểu chất độc sarin trong tuần lễ 23/04/95 đến 30/04/95

select ten,dchi_ts

from baibao a, dangbai b, phathanh c, baotchi d

where a.stt_bai = b.stt_bai

and b.so_bao_tc = c.so_bao_tc

and b.ma_bao_tc = d.ma_bao_tc

and tua = 'tim hieu ve chat doc sarin'

and ngay between '1995/04/23' and '1995/04/30'

-- tác giả hàn ngọc cẩm có những truyện nào đã được đăng trên báo/tạp chí?

-- cho biết tựa của chúng

select tua

from vietbai a, baibao b, theloai c, dangbai d

where a.stt_bai = b.stt_bai

and b.ma_thloai = c.ma

and a.stt_bai = d.stt_bai

and dgiai like 'truyen%'

and a.but_hieu = 'han ngoc cam'

-- cho biết số lượng bài báo mà

-- tạp chí kiến thức ngày nay đã đăng trên tất cả các số báo

select COUNT(*) as SoLuong

from dangbai a, baotchi b

where a.ma_bao_tc = b.ma_bao_tc

and ten='kien thuc ngay nay'

-- cho biết thể loại của các bài báo do các tác giả họ lê sáng tác

select distinct dgiai

from vietbai a, baibao b , theloai c

where a.stt_bai=b.stt_bai

and b.ma_thloai =c.ma

and but_hieu like 'le%'

-- tìm tựa các bài thơ đã được đăng trên các báo/tạp chí

-- trong tuần lễ 23/4/95 đến 30/4/95

select tua

from phathanh a, dangbai b, baibao c, theloai d

where a.so_bao_tc = b.so_bao_tc

and b.stt_bai = c.stt_bai

and c.ma_thloai = d.ma

and ngay between '1995/04/23' and '1995/04/30'

and dgiai='tho'

-- tìm tên các tác giả đã đăng bài trên báo cựu chiến binh

select but_hieu

from dangbai a, vietbai b, baotchi c

where a.stt_bai=b.stt_bai

and a.ma_bao_tc = c.ma_bao_tc

and ten='cuu chien binh'

-- tìm tên những báo/tạp chí có đăng xã luận.

-- in ra tên các bài xã luận đó và các số báo/tạp ch tương ứng

select ten,tua,so_bao_tc

from dangbai a, baibao b, theloai c,baotchi d

where a.stt_bai = b.stt_bai

and a.ma_bao_tc=d.ma_bao_tc

and b.ma_thloai=c.ma

and c.dgiai='xa luan'

Bài 10. Thực hành CSDL 3

-- tìm tên những đại lý vừa có bán coca cola vừa có bán pepsi cola

select TEN_DL

from MUA a,HANGHOA b, DAILY c

where a.MA_HANG = b.MA_HANG

and a.STT_DL = c.STT_DL

and TEN_HG = 'coca cola'

intersect

select TEN_DL

from MUA a,HANGHOA b, DAILY c

where a.MA_HANG = b.MA_HANG

and a.STT_DL = c.STT_DL

and TEN_HG = 'pepsi cola'

-- tìm tên những mặt hàng được mua nhưng chưa được bán

select distinct TEN_HG

from MUA a, HANGHOA b

where a.MA_HANG = b.MA_HANG

and TEN_HG not in (

select TEN_HG

from BAN a, HANGHOA b

where a.MA_HANG = b.MA_HANG

)

-- tìm tên và địa chỉ đại lý có mua cùng mặt hàng với Vạn Lợi mua

-- cách hiểu 1: chỉ cần đại lý nào đó có mua trùng 1 hoặc nhiều mặt hàng với vạn lợi là thỏa yêu cầu

select distinct TEN_DL,DCHI_DL

from MUA a, DAILY b

where a.STT_DL = b.STT_DL

and MA_HANG in (select MA_HANG

from MUA a, DAILY b

where a.STT_DL = b.STT_DL

and b.TEN_DL = 'van loi')

and TEN_DL <> 'van loi'

-- cách hiểu 2: nếu vạn lợi mua n mặt hàng,

-- thì đại lý X nào đó cũng phải mua n mặt hàng giống y n mặt hàng vạn lợi đã mua

select distinct MA_HANG into#tam1

from MUA a, DAILY b

where a.STT_DL = b.STT_DL

and b.TEN_DL = 'van loi'

select TEN_DL,DCHI_DL

from MUA a, DAILY b

where a.STT_DL = b.STT_DL

and b.TEN_DL <>'van loi'

and MA_HANG in (select MA_HANG from #tam1)

group by TEN_DL, DCHI_DL

having COUNT (*) >= (select COUNT(*) from #tam1)

-- ĐỀ KIỂM CHỨNG THỬ ĐƯỢC CÁCH HIỂU, HÃY THÊM 2 DÒNG DỮ LIỆU SAU

insert into MUA values ('003',1,'2016-03-29',100,100000),

('005',1,'2016-03-29',120,200000)

-- cho biết tên các mặt hàng được ít nhất là hai đại lý mua vào với số lượng trên 50

-- cách hiểu 1: lọc ra những mặt hàng có solg mua > 50, rồi xem mặt hàng nào được ít nhất 2 đl mua

select TEN_HG

from MUA a, HANGHOA b

where a.MA_HANG = b.MA_HANG

and SOLG_MUA > 50

group by TEN_HG

having COUNT(distinct STT_DL)>1

-- cách hiểu 2: thống kê xem những mặt hàng được ít nhất 2 đại lý mua với tổng số lượng mua trên 50

select TEN_HG

from MUA a, HANGHOA b

where a.MA_HANG = b.MA_HANG

and a.MA_HANG in (

select MA_HANG

from MUA a

group by MA_HANG

having COUNT(distinct STT_DL)>1

)

group by TEN_HG

having SUM(TRIGIA_MUA)>50

--- tìm tổng trị giá mua coca cola ở tân hiệp hưng

select SUM (TRIGIA_MUA)

from MUA a, DAILY b, HANGHOA c

where a.STT_DL = b.STT_DL and a.MA_HANG = c.MA_HANG

and TEN_HG='coca cola' and TEN_DL = 'tan hiep hung'

-- tìm tổng trị giá bán coca cola ở tân hiệp hưng

select SUM (TRIGIA_BAN)

from BAN a, DAILY b, HANGHOA c

where a.STT_DL = b.STT_DL and a.MA_HANG = c.MA_HANG

and TEN_HG='coca cola' and TEN_DL = 'tan hiep hung'

-- tìm tên những mặt hàng được bán ở đl tân hiệp hưng vào cả hai ngày 15/12/1994 và 31/12/1994

-- cách hiểu 1: giả sử ngày 15/12/94 bán mặt hàng a,b,c

-- ngày 31/12/94 bán mặt hàng c,d,e,f

-- thì mặt hàng c là thỏa điều kiện

select TEN_HG

from BAN a, DAILY b, HANGHOA c

where a.STT_DL = b.STT_DL

and a.MA_HANG = c.MA_HANG

and TEN_DL = 'tan hiep hung'

and NGAY_BAN = '1994-12-15'

intersect

select TEN_HG

from BAN a, DAILY b, HANGHOA c

where a.STT_DL = b.STT_DL

and a.MA_HANG = c.MA_HANG

and TEN_DL = 'tan hiep hung'

and NGAY_BAN = '1994-12-31'

-- cách hiểu 2: giả sử ngày 15/12/94 bán mặt hàng a,b,c

-- ngày 31/12/94 bán mặt hàng c,d,e,f

-- thì mặt hàng a,b,c,d,e,f là thỏa điều kiện

select TEN_HG

from BAN a, DAILY b, HANGHOA c

where a.STT_DL = b.STT_DL

and a.MA_HANG = c.MA_HANG

and TEN_DL = 'tan hiep hung'

and NGAY_BAN = '1994-12-15'

union

select TEN_HG

from BAN a, DAILY b, HANGHOA c

where a.STT_DL = b.STT_DL

and a.MA_HANG = c.MA_HANG

and TEN_DL = 'tan hiep hung'

and NGAY_BAN = '1994-12-31'

-- có bao nhiêu mặt hàng được mua vào nhưng chưa được bán ra

-- ở đại lý tân hiệp hưng trong tháng 12 năm 1994

select COUNT(*)

from MUA a, DAILY b

where a.STT_DL = b.STT_DL

and TEN_DL='tan hiep hung'

and YEAR(NGAY_MUA) =1994 and MONTH(NGAY_MUA) = 12

and MA_HANG not in (select MA_HANG

from BAN a, DAILY b

where a.STT_DL = b.STT_DL

and TEN_DL='tan hiep hung'

and YEAR(NGAY_BAN) =1994 and MONTH(NGAY_BAN) = 12

)

-- tìm tên các mặt hàng có mua và bán trong cùng một ngày ở cùng một đại lý

select distinct TEN_HG -- (viết thêm khúc này để coi dữ liệu,NGAY_BAN,NGAY_MUA,a.STT_DL,b.STT_DL)

from MUA a, BAN b, HANGHOA c

where a.NGAY_MUA = b.NGAY_BAN

and a.STT_DL = b.STT_DL

and a.MA_HANG = c.MA_HANG

-- tìm tên và địa chỉ của các đại lý và

-- những mặt hàng có số lượng mua và bán bằng nhau trong cùng một ngày

select a.TEN_DL,a.DCHI_DL

from (

select TEN_DL,DCHI_DL,MA_HANG,NGAY_MUA,SUM(SOLG_MUA) as TONG_SLG_MUA

from MUA a, DAILY b

where a.STT_DL = b.STT_DL

group by TEN_DL,DCHI_DL,MA_HANG,NGAY_MUA

) a, (

select TEN_DL,DCHI_DL,MA_HANG,NGAY_BAN,SUM(SOLG_BAN) as TONG_SLG_BAN

from BAN a, DAILY b

where a.STT_DL = b.STT_DL

group by TEN_DL,DCHI_DL,MA_HANG,NGAY_BAN

) b

where a.ten_dl = b.ten_dl

and a.ma_hang = b.ma_hang

and a.ngay_mua = b.ngay_ban

andTONG_SLG_MUA = TONG_SLG_BAN

-- tìm tên đại lý đã mua coca cola với số lượng nhiều hơn tất cả các đại lý khác

select TEN_DL, SUM(SOLG_MUA) as TONGMUACOLA into#tam2

from MUA a, HANGHOA b, DAILY c

where a.MA_HANG = b.MA_HANG

and a.STT_DL = c.STT_DL

and TEN_HG='coca cola'

group by TEN_DL

select ten_dl

from #tam2

wheretongmuacola = (select MAX(tongmuacola) from #tam2)

Bài 10. Thực hành CSDL 3 tiếp theo

--tìm đơn giá mua trung bình của bia sài gòn trên tất cả các đại lý

select avg(trigia_mua/solg_mua) as 'DG_Mua_TB'

from mua a, hanghoa b

where a.ma_hang = b.ma_hang

and ten_hg = 'bia saigon'

--tìm đơn giá mua trung bình của bia sài gòn trên từng đại lý

select ten_dl,AVG(trigia_mua/solg_mua) asDonGiaTrungBinhDL

from mua a, hanghoa b, daily c

where a.ma_hang = b.ma_hang

and a.stt_dl = c.stt_dl

and ten_hg = 'bia saigon'

group by ten_dl

--hiển thị số lượng tồn kho của từng mặt hàng theo từng đại lý

select ma_hang,stt_dl,SUM(solg_mua) as SoLuongMua into #tMua

from mua a

group by ma_hang,stt_dl

select ma_hang,stt_dl,SUM(solg_ban) as SoLuongBan into #tBan

from ban a

group by ma_hang,stt_dl

select ten_dl,ten_hg, SoLuongBan,SoLuongMua, SoLuongTon

from daily a, hanghoa b, (

select a.stt_dl,a.ma_hang,SoLuongMua,SoLuongBan,SoLuongMua -

(case

whenSoLuongBan is NULLthen 0

elseSoLuongBan

end) as SoLuongTon

from #tmua a left join #tban b

on a.stt_dl = b.stt_dl and a.ma_hang = b.ma_hang

where a.stt_dl = c.stt_dl and b.ma_hang = c.ma_hang

--tìm tên, địa chỉ của đại lý

-- có tổng trị giá mua trong một ngày lớn hơn 700000

select ten_dl,dchi_dl

from daily a, (

select stt_dl,ngay_mua, SUM(trigia_mua) TongTGMua

from mua

group by stt_dl,ngay_mua

having SUM (trigia_mua)>700000

) b

where a.stt_dl = b.stt_dl

--với mỗi đại lý, hãy cho biết ngày nào

-- có số lượng mặt hàng bán ra là ít nhất

select stt_dl,ngay_ban,COUNT(distinct ma_hang) as SLMH into #tam3

from ban

group by stt_dl,ngay_ban

select a.stt_dl,ngay_ban

from #tam3 a, (

select stt_dl,MIN(slmh) as MHMin

from #tam3

group by stt_dl

) b

where a.stt_dl = b.stt_dl and a.slmh = b.MHMin

--tìm tên và địa chỉ của những đại lý có tổng trị giá bán cao nhất

select stt_dl,SUM(Trigia_ban) as TongTGBan into#tam4

from ban

group by stt_dl

select ten_dl,dchi_dl

from daily a, #tam4 b

where a.stt_dl = b.stt_dl

and b.TongTGBan = (select MAX(TongTGBan) from #tam4)

-- tìm tổng số tiền mà mỗi đại lý đã chi để mua hàng theo từng tháng

select stt_dl,MONTH(ngay_mua) Thang,YEAR(ngay_mua) Nam, SUM(trigia_mua) TongTien

from mua

group by stt_dl,MONTH(ngay_mua),YEAR(ngay_mua)

--tìm tên và tổng số lượng bán ra của

--các mặt hàng đã bán được với số lượng nhiều nhất của từng đại lý

select stt_dl,ma_hang,SUM(solg_ban) as TongSLGBan into #tam5

from ban

group by stt_dl,ma_hang

select ten_hg,TongSLGBan

from #tam5 a, (

select stt_dl,MAX(TongSLGBan) as TongSLGBanMax

from #tam5

group by stt_dl

) b, hanghoa c

where a.stt_dl = b.stt_dl and a.TongSLGBan =TongSLGBanMax

and a.ma_hang = c.ma_hang

--cho biết tổng số món hàng mà mỗi đại lý kinh doanh (mua va ban

select stt_dl,COUNT(*) as TongSoMonHang

from (

select stt_dl, ma_hang

from mua

union

select stt_dl,ma_hang

from ban

group by stt_dl

--tìm tên đại lý đã mua vào mặt hàng bia heineken với đon giá mua cao nhất

select ten_dl

from daily a, mua b, hanghoa c

where a.stt_dl = b.stt_dl

and b.ma_hang = c.ma_hang

and ten_hg = 'bia heineken'

and(TRIGIA_MUA/SOLG_MUA) = (

select MAX(TRIGIA_MUA/SOLG_MUA)

from mua a, hanghoa b

where a.ma_hang = b.ma_hang

andten_hg = 'bia heineken'

)

NỘI DUNG ÔN THI

- Tìm sự thông thương dữ liệu giữa các bảng: xác định khóa chính, khóa ngoại. Sẽ có bảng có khóa chính được tạo bởi nhiều cột.

- Thực hiện các câu Insert, Delete, Update dữ liệu
- Insert dữ liệu đầy đủ vào 1 dòng, hoặc không đầy đủ

- Delete, Update dữ liệu có thể có điều kiện Where
- Chú ý cách viết điều kiện dạng số, chuỗi, ngày tháng

- Các dạng câu truy vấn::

- Where:

- Một Điều Kiện

- Hai điều kiện kết hợp với nhau bằng phép toán And, Or

- Sử dụng hàm trong cột điều kiện: Month, Year, Day, Between,...

- Sử dụng ký tự đại diện %, ?
- Thực hiện tính toán dạng như tính tuổi dựa vào năm sinh,...

- Nối bảng + Where

- TOP, DISTINCT

- Phép toán Intersect, Union, Except

- Các dạng select lồng

- Bảng tạm

- Group by, Having

Chú ý:

- Các bạn ưu tiên ôn các nội dung theo thứ tự trình bày bên dưới. Nội dung nào ghi trước thì ôn kỹ hơn

- Dựa vào các bài tập + bài giải trong quá trình học đề làm cơ sở ôn các nội dung đã nêu.

VN SEEDER

Hệ Cơ sở dữ liệu

Bài liên quan