GIÀI BÀI TẬP CƠ SỞ SỮ LIỆU TRONG GIÁO TRÌNH --- oOo ---CÁC BẢNG(TABLE) SỬ DỤNG TRONG BÀI TẠO BẢNG SV CREATE TABLE SV (MaSV Text(12), Hoten Text, Nu Bit, Ngaysinh Date, Malop Text(12), Hocbong Long, Tinh Text, CONSTRAINT Pk_MaSV PRIMARY KEY(MaSV) ) SV MaSV K30TC00 1 K30TC00 2 K30TC00 3 K30TC00 4 K30TC00 5 K30TC00 6 K30TC00 7 K30TC00 8 K30TC00 9 K30TC01 0 K30TC011
Hoten
Nu
Ngaysinh
Nguyễn Hoài An
0
31049
Bùi Quốc Bảo
0
Nguyễn Văn Đạt
Hocbong
Tinh
TCTH30A
0
Lonh An
30795
TCĐT30A
1
Tp.HCM
0
31601
TCTH30B
1
Đồng Nai
Trần Thanh Giao
0
30362
TCĐ30
2
Tây Ninh
Nguyễn Lê Hằng
-1
31135
TCTH30B
3
Tp.HCM
Bùi Thị Bích Hằng
-1
31314
TCĐT30A
0
Tp.HCM
Lê Thị Lan
-1
30956
TCTH30A
2
Tây Ninh
Võ Thị Ngọc Mai
-1
31602
TCĐT30B
0
Long An
Nguyễn Quốc Nam
0
31255
TCĐ30
0
Tp.HCM
Phạm Minh Nhật
0
31240
TCĐT30A
2
Lonh An
-1
31048
TCTH30A
2
Long An
Lê Kim Oanh
Malop
TẠO BẢNG LỚP CREATE TABLE LOP ( MaLop Text(12), TenLop Text, MaKhoa Text(12), He Text, CONSTRAINT Pk_MaLop PRIMARY KEY(MaLop), CONSTRAINT Fk_MaKhoa FOREIGN KEY(MaKhoa) REFERENCES KHOA(MaKhoa) ) LOP MaLop
TenLop
MaKhoa
He
Trần Xuân Vinh | Lớp:06CĐ-TM2 | Tel:0984.924.642
Trang 1
TCĐ30
Trung cấp điện 30
Đ
TC2
TCĐT30A
Trung cấp điện tử 30 A
ĐT
TC4
TCĐT30B
Trung cấp điện tử 30 B
ĐT
TC2
TCTH30A
Trung cấp tin học 30 A
CNTT
TC2
TCTH30B
Trung cấp tin học 30B
CNTT
TC4
TẠO BẢNG KHOA CREATE TABLE KHOA ( MaKhoa Text(12), TenKhoa Text, DtKhoa Text(12), CONSTRAINT Pk_MaKhoa PRIMARY KEY(MaKhoa) ) TẠO BẢNG MÔN CREATE TABLE MON ( MaMH Text(12), TenMH Text, SoTiet Number, MonThi Bit, CONSTRAINT Pk_MaMH PRIMARY KEY(MaMH) )
KHOA MaKhoa
TenKhoa
DtKhoa
CNTT
Công nghệ thông tin
8809324
Đ
Điện
8821334
ĐT
Điện Tử
8876563
MON MaMH
TenMH
SoTiet
MonThi
ATĐ
AN toàn điện
30
0
CSDL
Cơ sở dữ liệu
45
-1
CTDL
Cấu trúc dữ liệu
45
0
ĐLĐ
Đo lường điện
30
0
ĐTCB
Điện tử cơ bản
60
-1
KTS
Kĩ thuật số
45
0
TẠO BẢNG KQ CREATE TABLE KQ ( MaSV Text(12), MaMH Text(12), DiemThi Single, CONSTRAINT Pk_SVMH PRIMARY KEY(MaSV,MaMH), CONSTRAINT Fk_MaSV FOREIGN KEY(MaSV) REFERENCES SV(MaSV), CONSTRAINT Fk_MaMH FOREIGN KEY(MaMH) REFERENCES MON(MaMH) ) MaSV K30TC00 1 K30TC00 2 K30TC00 2 K30TC00 3 K30TC00 3 K30TC00 4 K30TC00 4 K30TC00 5
KQ MaMH
DiemThi
CSDL
6
ĐTCB
8
KTS
9
CSDL
9
CTDL
10
ATĐ
6
ĐLĐ
7
CSDL
8
Trần Xuân Vinh | Lớp:06CĐ-TM2 | Tel:0984.924.642
Trang 2
K30TC00 6 K30TC00 6 K30TC00 7 K30TC00 7 K30TC00 8 K30TC00 8 K30TC00 9 K30TC00 9 K30TC01 0 K30TC01 0 K30TC011 K30TC011
ĐTCB
2
KTS
4
CSDL
8
CTDL
7
ĐTCB
5
KTS
4
ATĐ
0
ĐLĐ
6
ĐTCB
8
KTS CSDL CTDL
7 7 7
-CÂU LỆNH TRUY VẤN SQL 1) lập danh sách sinh viên (dssv) viên gồm MASV, HOTEN, MALOP, HOCBONG, được sắp xép tăng dần theo cột mã lớp SELECT SV.MaSV, SV.Hoten, SV.Malop, SV.Hocbong FROM SV ORDER BY SV.Malop; 2) lập dssv nữ khoa ‘CNTT’ và có học bổng SELECT SV.* FROM SV, LOP WHERE SV.MaLop=LOP.MaLop And Nu=yes And MaKhoa="CNTT" And HocBong>0; 3) lập dssv gồm MASV, HOTEN, TENKHOA SELECT MaSV, HoTen, TenKhoa FROM SV, LOP, KHOA WHERE SV.MaLop=LOP.MaLop And LOP.Makhoa=KHOA.MaKhoa; 4) lập dssv thi lại gồm MASV, HOTEN, TENMH, DIEMTHI và được sắp xếp giảm dần theo cột điểm thi SELECT S.MaSV, HoTen, TenMH, DiemThi FROM SV AS S, KQ AS K, MON AS M WHERE S.MaSV=K.MaSV And K.MaMH=M.MaMH And DiemThi<5 ORDER BY DiemThi DESC; 5) lập bảng điểm cho tất cả sinh viên khoa CNTT SELECT SV.MaSV, HoTen, MaMH, DiemThi, MaKhoa FROM SV, LOP, KQ WHERE SV.MaLop=LOP.MaLop And SV.MaSV=KQ.MaSV And MaKhoa="CNTT"; Trần Xuân Vinh | Lớp:06CĐ-TM2 | Tel:0984.924.642
Trang 3
6) lập dssv lớp ‘TCTH30A’ có điểm thi 2 môn ‘CSDL’ và ‘CTDL’ từ 7 điểm trở lên ▼ Trường Hợp 1: SELECT SV.MaSV, HoTen, MaMH, DiemThi, MaLop FROM SV, KQ WHERE SV.MaSV=KQ.MaSV And MaLop="TCTH30A" And (MaMH="CSDL" or MaMH="CTDL") And SV.MaSV Not In (SELECT DISTINCT MaSV FROM KQ WHERE DiemThi<7); ▼ Trường Hợp 2: SELECT SV.MASV, HOTEN, MAMH, DIEMTHI FROM SV, KQ WHERE SV.MASV=KQ.MASV AND MALOP="TCTH30A" AND (MAMH="CSDL" OR MAMH="CTDL") AND SV.MASV IN (SELECT MASV FROM KQ WHERE DIEMTHI>=7 AND (MAMH="CSDL" OR MAMH="CTDL") GROUP BY MASV HAVING COUNT(MAMH)=2); ▼ Tính tổng điểm sv lớp ‘TCTH30A’ có điểm thi 2 môn ‘CSDL’ và ‘CTDL’ từ 7 điểm trở lên TRANSFORM SUM(DIEMTHI) AS X SELECT SV.MASV, HOTEN, SUM(DIEMTHI) AS TONGDIEM FROM SV, KQ WHERE SV.MASV=KQ.MASV AND (MAMH="CSDL" OR MAMH="CTDL") AND MALOP="TCTH30A" AND SV.MASV NOT IN (SELECT MASV FROM KQ WHERE(MAMH="CSDL" OR MAMH="CTDL")AND DIEMTHI<7) GROUP BY SV.MASV, HOTEN PIVOT MAMH; 7) tìm các sv tên ‘Hằng’, ds gồm MASV, HOTEN, NGAYSINH, MALOP, MAKHOA, TINH SELECT MaSV,Hoten, Ngaysinh, SV.Malop, MaKhoa, Tinh FROM SV,LOP WHERE SV.Malop=LOP.MaLop And HoTen Like "* HẰNG" 8) lập dssv chí đăng ký 1 môn học SELECT SV.MASV, HOTEN, COUNT(MAMH) AS SOMONTHI FROM SV, KQ WHERE SV.MASV=KQ.MASV GROUP BY SV.MASV, HOTEN HAVING COUNT(MAMH)=1; 9) đếm số lượng sin viên nữ của mỗi tỉnh Trần Xuân Vinh | Lớp:06CĐ-TM2 | Tel:0984.924.642
Trang 4
SELECT SV.Tinh, Count(SV.MaSV) AS SoLuongSV FROM SV GROUP BY SV.Tinh; 10) những tỉnh nào có nhiềi hơn 3 sinh viên SELECT Tinh, COUNT(MaSV) AS SoLuongSV FROM SV GROUP BY Tinh HAVING COUNT(MaSV)>3; 11) bảng điểm trung bình của từng môn SELECT K.MaMH, TenMH, AVG(DiemThi) AS DiemTB FROM KQ AS K, MON AS M WHERE K.MaMH=M.MaMH GROUP BY K.MaMH, TenMH; 12) bảng điểm trung bình của từng sinh viên, sắp theo từng khoa và từng lớp SELECT SV.MaSV, HeTen, AVG(DiemThi) AS DiemTB, MaKhoa L.MaLop FROM SV, KQ AS K, LOP AS L WHERE K.MaSV=SV.MaSV And SV.MaLOP=L.MaLOP GROUP BY SV.MaSV, HoTen, MaKhoa, L.MaLop ORDER BY L.MaLop, MaKhoa; 13) những sv khoa ‘CNTT’ có điểm trung bình các môn >=8 SELECT S.Masv, HoTen, AVG(DiemThi) AS DiemTB FROM SV AS S, KQ AS K, LOP AS L WHERE K.MaSV=S.MaSV And S.MaLOP=L.MaLOP AND MaKhoa="CNTT" GROUP BY S.MaSV, HoTen HAVING AVG(DiemThi)>=8; 14) tính tổng điểm 2 môn ‘CSDL’ và ‘CTDL’ của các sv đăng ký học cả 2 môn này, sắp xép giảm dần theo tổng điểm SELECT SV.MaSV, HoTen, SUM(DiemThi) AS TongDiem FROM SV, KQ WHERE SV.MaSV=KQ.MaSV GROUP BY SV.MaSV, HoTen HAVING SV.MaSV In (SELECT MaSV FROM KQ WHERE MaMH="CTDL" And MASV In (SELECT MaSV FROM KQ WHERE MaMH="CSDL")); 15) lập dssv có điểm thi môn ‘CSDL’ cao nhất của từng lớp
Trần Xuân Vinh | Lớp:06CĐ-TM2 | Tel:0984.924.642
Trang 5
SELECT SV.MaSV, MaMH, DiemThi, MaLop FROM SV, KQ WHERE MAMH="CSDL" And SV.MaSV=KQ.MaSV And DiemThi>=ALL (SELECT DiemThi FROM SV S2,KQ K2 WHERE SV.MaSV=K2.MaSV And MaMH="CSDL" And SV.MaLop=S2.MaLop); 16) lập dssv có điểm thi trung bình các môn >=7, ds gồm : MASV, HOTEN, DIEMTRUNGBINH và được sắp giảm dần theo cột DIEMTRUNGBINH. SELECT SV.MaSV, HoTen, Avg(DiemThi) AS DIEMTRUNGBINH FROM SV, KQ WHERE SV.MaSV = KQ.MaSV GROUP BY SV.MaSV, HoTen HAVING Avg(DiemThi)>=7 ORDER BY Avg(DiemThi) DESC 17) lập dssv nữ có tuổi cao nhất, ds gồm MASV, HOTEN, NGAYSINH. ▼Cách 1: SELECT MaSV, HoTen, Ngaysinh FROM SV WHERE Nu=Yes And NgaySinh<=ALL (SELECT DISTINCT NgaySinh FROM SV WHERE Nu=Yes); ▼Cách 2: SELECT TOP 1 MaSV, HoTen, Ngaysinh FROM SV WHERE Nu=Yes ORDER BY NgaySinh 18) hãy cho biết sv có mã sv là ‘K30TC008’ thi lại môn nào?(nếu điểm thi <5 phải thi lại), ds gồm TENMH, DIEMTHI SELECT TenMH, DiemThi FROM SV, MON, KQ WHERE SV.MaSV = KQ.MaSV And KQ.MaMH=MON.MaMH And SV.MaSV="K30TC008" And DiemThi<5 19) ds những khoa có sv nữ theo học, ds gồm TENKHOA, SOLUONGNU. SELECT TenKhoa, Count(Nu) AS SOLUONGNU FROM SV, LOP, KHOA WHERE SV.MaLop=LOP.MaLop And Lop.MaKhoa=KHOA.MaKhoa And Nu=Yes GROUP BY TenKhoa 20) những môn học nào có nhiều hơn 3 sv đăng ký thi, ds gồm TENMH, SOLUONG thống kê sĩ số mỗi lớp. SELECT TenMH, Count(DiemThi) AS SoLuong Trần Xuân Vinh | Lớp:06CĐ-TM2 | Tel:0984.924.642
Trang 6
FROM MON, KQ WHERE MON.MaMH = KQ.MaMH GROUP BY TenMH HAVING Count(DiemThi)>3 21) thống kê sĩ số mỗi lớp SELECT TenLop, Count(MaSV) AS SiSo FROM SV, LOP WHERE LOP.MaLop=SV.Malop GROUP BY TenLop; 22) lập dssv sinh năm 1985 theo từng khoa SELECT MaSV, HoTen,Nu, Year(Ngaysinh) AS NamSinh, SV.Malop,Hocbong, Tinh,TenKhoa FROM SV, LOP, KHOA WHERE SV.Malop=LOP.MaLop And LOP.MaKhoa =KHOA.MaKhoa And Year(Ngaysinh)=1985 ORDER BY TenKhoa 23) lập bảng kết quả điểm thi các môn của từng sv lớp ‘TCTH30A’ SELECT HoTen,LOP.MaLop, MON.TenMH, DiemThi FROM SV, LOP, MON, KQ WHERE LOP.MaLop = SV.Malop And SV.MaSV = KQ.MaSV And MON.MaMH = KQ.MaMH And LOP.MaLop="TCTH30A" 24) tìm sv sinh ngày 27/7/85 SELECT MaSV, HoTen, Ngaysinh, Malop, Hocbong, Tinh FROM SV WHERE Ngaysinh=#07/27/1985#; 25) lập dssv nam, nữ có điểm thi cao nhất(bất kỳ môn nào), ds gồm: MaSV, HoTen, Nu, TenLop, TenMH, DiemThi SELECT S1.MaSV, HoTen, Nu, TenLop, TenMH, DiemThi FROM SV S1, LOP L, MON M, KQ K WHERE L.MaLop=S1.MaLop And S1.MaSV=K.MaSV And K.MaMH=M.MaMH And DiemThi>=ALL (SELECT DiemThi FROM KQ, SV S2 WHERE KQ.MASV=S2.MaSV And S1.Nu=S2.Nu) ORDER BY Nu 26)lập dssv thi môn ‘CSDL’ và không thi môn ‘CTDL’ SELECT SV.MaSV, HoTen, Nu, Ngaysinh, KQ.MaMH, DiemThi FROM SV, KQ WHERE SV.MaSV=KQ.MaSV And KQ.MaMH="CSDL" And SV.MaSV NOT IN (SELECT MaSV FROM KQ WHERE MaMH="CTDL") Trần Xuân Vinh | Lớp:06CĐ-TM2 | Tel:0984.924.642
Trang 7