/*tao database moi*/
createdatabase Quanlysinhvien
/*xóa database*/
dropdatabase Quanlysinhvien
/*Sửa tên của database*/
alterdatabase quanlysinhvien
modify name = QLSV
/* sử dụng database cụ thể*/
use Quanlysinhvien
GO
/* thêm bảng*/
createtable Sinhvien
(
MaSV intnotnullprimarykeyidentity,
TenSV nvarchar(100)notnull,
Gioitinh nvarchar(3)default'NAM',
Ngaysinh date,
Que nvarchar(500),
Lop nvarchar(10)
)
createtable Monhoc
(
MaMH intnotnull,
TenMH nvarchar(50)notnull,
DVHT int
)
createtable Ketqua
(
MaSV int,
MaMH int,
Diem intcheck (Diem>=0 and Diem<=10)/*Biểu thức luận lý */

constraint primary_key primarykey (MaSV, MaMH)
)
/* Đổi tên cột */
execsp_rename"sinhvien.tensv", "hoten"
/*Đổi tên bảng*/
execsp_renamesv,"sinhvien"
/* Xóa bảng*/
/*drop table Sinhvien*/
/* Sửa bảng*/
/*Chèn thêm 1 cột*/
Altertable Sinhvien
1


ADD
Dienthoai varchar(11),
abc nvarchar(40)
/*thay đổi kiểu dữ liệu của cột*/
Altertable sinhvien
Altercolumn abc intnotnull
/*thêm khóa*/
altertable Monhoc
ADD
constraint pk_monhoc primarykey(MaMH)
altertable sinhvien
addconstraint u_sinhvien_abc unique (abc)
/*Xóa cột*/
Altertable Sinhvien
Dropcolumn abc, Dienthoai
/*Xóa ràng buộc*/

Altertable sinhvien
Dropconstraint pk_monhoc
/* tắt các constraint*/
ALTERTABLE Tên_bảng
NOCHECKCONSTRAINTALL| Tên_constraint [ , ...]
/*Bật các Constraint */
ALTERTABLE Tên_bảng
CHECKCONSTRAINTALL| Tên_constraint [ , ...]
/*Tạo khóa ngoại kết nối giữa 2 bảng */
Altertable Ketqua
ADD
constraint foreignkey_SV_KQ foreignkey (MaSV)references Sinhvien(MaSV)
altertable ketqua
ADD
constraint foreignkey_MH_KQ foreignkey (MaMH)references Monhoc(MaMH)
/*chèn dữ liệu
Insert into Tên_Bảng [(tên trường cần chèn)] values (Giá trị của trường)
*/
insertinto sinhvien
values (N'Phạm Trung Tính','Nam','03/30/1996','Quảng Ninh','AT10D')
insertinto sinhvien(TenSV,Gioitinh,Ngaysinh, Que, Lop)
values (N'Trần Bảo Trọng','Nam','2000/12/14','Hà Giang','AT10D')

2


insert sinhvien
values (N'Lê Thùy Dung',N'Nữ','05/12/1997','Hà Nội','AT10D')
insert Sinhvien
values (N'Lê Trường An',N'Nam','11/20/1995',N'Ninh Bình','AT10D')

insert sinhvien
values (N'Phạm Thị Hương Giang',N'Nữ','2/21/1999',N'Hòa Bình','AT10C')
insert sinhvien
values (N'Đoàn Duy Thức',N'Nam','4/12/1993',N'Hà Nội','AT10C')
insert sinhvien
values (N'Dương Tuấn Thông',N'Nam','4/12/1991',N'Nam Định','AT10D')
insert sinhvien
values (N'Lê Thành Đạt',N'Nam','4/15/1993',N'Phú Thọ','AT10A')
insert Monhoc(MaMH,TenMH)
values (1,N'Lý Thuyết Cơ sở dữ liệu')
insert Monhoc
values (2,N'Toán cao cấp',3)
insert Monhoc
values (3,N'Mạng máy tính',3)
insert Monhoc
values (4,N'Tin đại cương',4)
insert Ketqua
values (1,1,8)
insert Ketqua
values (1,2,5)
insert Ketqua
values (2,2,1)
insert Ketqua
values (3,2,7)
insert Ketqua
values (4,2,3)
insert Ketqua
values (1,3,7)

3



insert Ketqua
values (2,1,9)
insert Ketqua
values (4,1,2)
insert Ketqua
values (3,1,4)
insert Ketqua
values (2,3,2)
insert Ketqua
values (5,1,4)
insert Ketqua
values (6,1,2)
insert Ketqua
values (6,3,9)
insert Ketqua
values (6,2,7)
insert Ketqua
values (6,5,10)
/* update dữ liệu
Update Ten_Bang set giá_trị_gán_cho_các_trường [where điều_kiện]
*/
update Sinhvien set TenSV=N'Lê Trung Tính', Lop=N'At10D'where MaSV='SV001'
select*from Sinhvien
update Sinhvien set Gioitinh =N'Nữ'where MaSV=2
/* xóa bản ghi (dữ liệu)
Delete Ten_bang [where điều_kiện]
*/
delete Sinhvien where Gioitinh='Nữ'

delete Ketqua where Diem<4
/*chuyển ID tự tăng về 1 giá trị */
DBCC CHECKIDENT('Sinhvien', RESEED,0)/*chuyển id về 0 */
/*//////////////////////////////////////////////////////////////////////////// */
/* Truy vấn dữ liệu */
/*Cấu trúc câu lệnh Select
Select [Ten_Bang.]Ten_Cot[,…]
4


From Ten_Bang1 <Liên kết > Ten_Bang2
Where <Điều kiện>
Group by <các cột gom nhóm>
HaVing <Điều kiện lọc nhóm>
Order by [Ten_Bang.]Ten_cot [asc/desc,…]
Compute Hàm thống kê [By Ten_cot] */
/* lấy dữ liệu từ bảng*/
select*from Sinhvien
select*from Monhoc
select*from Ketqua
/* đặt định danh cho cột, bảng*/
select sv.MaSV, TenSV as'Tên Sinh Viên'
from Sinhvien as sv
/* Kết nối dữ liệu từ nhiều bảng */
/* kết nối sử dụng where*/
select sv.MaSV, TenSV as'Tên Sinh Viên', TenMH, Diem
from Sinhvien as sv, Monhoc as Mh, Ketqua as kq
Where sv.MaSV = kq.MaSV and kq.MaMH = mh.MaMH
/* Kết nối dùng JOIN chính là inner join*/
select sv.MaSV, TenSV as'Tên Sinh Viên', TenMH, Diem

from ((Sinhvien as sv join Ketqua as kq on sv.MaSV = kq.MaSV)join Monhoc as
Mh on kq.MaMH=mh.MaMH)
/* Kết nối dùng left JOIN*/
select sv.MaSV, TenSV as'Tên Sinh Viên', TenMH, Diem
from ((Sinhvien as sv leftjoin Ketqua as kq on sv.MaSV = kq.MaSV)leftjoin
Monhoc as Mh on kq.MaMH=mh.MaMH)
/* Kết nối dùng right JOIN*/
select sv.MaSV, TenSV as'Tên Sinh Viên', TenMH, Diem
from ((Sinhvien as sv leftjoin Ketqua as kq on sv.MaSV = kq.MaSV)rightjoin
Monhoc as Mh on kq.MaMH=mh.MaMH)
/* Kết nối dùng full JOIN*/
select sv.MaSV, TenSV as'Tên Sinh Viên', TenMH, Diem
from ((Sinhvien as sv fulljoin Ketqua as kq on sv.MaSV = kq.MaSV)fulljoin
Monhoc as Mh on kq.MaMH=mh.MaMH)
/* Sử dụng cấu trúc lệnh trong Select */
select Masv, TenSV,
case
when Gioitinh=N'NAM'thenN'đây là nam'
when Gioitinh =N'Nữ'thenN'Đây là nữ'
elseN'Không Xác định'endas'thông tin'
from Sinhvien

5


select sinhvien.Masv, TenSV, Monhoc.TenMH, Diem,
case
when Diem>=8 thenN'Đạt điểm giỏi'
when Diem>=7 and Diem<8 thenN'Đạt điểm khá'
when Diem>=5 and Diem<7 thenN'Đạt điểm trung bình'

elseN'Thi lại'endas'thông tin'
from Sinhvien, Monhoc, Ketqua
where Sinhvien.MaSV=Ketqua.MaSV and Ketqua.MaMH=Monhoc.MaMH
/* Sử dụng các toán tử trong select
Distinct : loại bỏ các dòng dữ liệu trùng
Top n : lấy n dòng đầu tiên
Top n with ties : lấy những dòng thuộc n cấp đầu tiên
Top n PERCENT : lấy N % dòng đầu tiên */
selectdistinct TenSV
from Sinhvien, Ketqua
where Sinhvien.MaSV = Ketqua.MaSV
selecttop (2) TenSV,TenMH, Diem
from Sinhvien, Ketqua, Monhoc
where Sinhvien.MaSV = Ketqua.MaSV and Ketqua.MaMH = Monhoc.MaMH
selecttop (50)percent TenSV,TenMH, Diem
from Sinhvien, Ketqua, Monhoc
where Sinhvien.MaSV = Ketqua.MaSV and Ketqua.MaMH = Monhoc.MaMH
/* Sử dụng mệnh đề where để lọc dữ liệu hoặc để gom nhóm*/
select TenMH,DVHT from Monhoc where DVHT<>''
select MaSV, TenSV from Sinhvien where Gioitinh=N'Nam'and Que=N'Hà nội'
/*Có thể sử dụng các toán tử:
like: tìm chuỗi gần đúng
%: Thay thế 1 chuỗi
-: thay thế 1 ký tự
Between GT1 AND GT2: giá trị nằm giữa 2 giá trị. */
select*from Sinhvien where TenSV like'%trung%'
select*from Sinhvien where TenSV like'%tí[k-o][^M-Z]'
select TenSV,TenMH, Diem
from Sinhvien, Ketqua, Monhoc
where Sinhvien.MaSV = Ketqua.MaSV and Ketqua.MaMH = Monhoc.MaMH and(Diem

between 5 and 8)
selectdistinct tensv
from Sinhvien, Ketqua, Monhoc

6


where Sinhvien.MaSV = Ketqua.MaSV and Ketqua.MaMH = Monhoc.MaMH
and(Monhoc.MaMH=1 or Monhoc.MaMH=2)
selectdistinct tensv
from Sinhvien, Ketqua, Monhoc
where Sinhvien.MaSV = Ketqua.MaSV and Ketqua.MaMH = Monhoc.MaMH and
Monhoc.MaMH=1
Intersect/*EXCEPT phép trừ, phép hợp là UNION, phép giao là Intersect */
selectdistinct tensv
from Sinhvien, Ketqua, Monhoc
where Sinhvien.MaSV = Ketqua.MaSV and Ketqua.MaMH = Monhoc.MaMH and
Monhoc.MaMH=3
/* sắp xếp theo thứ tự tăng, giảm*/
select*from Ketqua
orderby Diem DESC,MaSV ASC
select TenSV, TenMH,Diem
from sinhvien, Monhoc,Ketqua
where Sinhvien.MaSV = Ketqua.MaSV and Ketqua.MaMH = Monhoc.MaMH
orderby Diem DESC
/* Mệnh đề Group by : mệnh đề gom nhóm dữ liệu
Mệnh đề này phải có khi trong câu truy vấn có sử dụng hàm thống kê và trên mệnh đề Select có
lấy các giá trị không sử dụng hàm thống kê
Cú pháp : Group By Danh sách cột,Biểu thức
Với Danh sách cột,Biểu thức :được lấy trên mệnh đề Select không sử dụng hàm thống kê

Các Hàm thống kê:
Sum(Ten_Cot) :tính tổng với cột có kiểu số
Count(Ten_Cot/*) :tính tổng số dòng
AVG(Ten_Cot) :tính giá trị trung bình
Max(Ten_cot) : giá trị lớn nhất
Min(Ten_Cot) :giá trị nhỏ nhất)
*/ */
/*
Mệnh đề HaVing : mệnh đề Lọc nhóm dữ liệu
Mệnh đề này chỉ tồn tại khi trong câu truy vấn có mệnh đề Group By và điều kiện lọc dữ liệu có
hàm thống kê
Chú ý : trong câu truy vấn có hai mệnh đề lọc dữ liêu là Where và havinh
Where :lọc dữ liệu để tạo nhóm ( có điều kiện không sữ dụng hàm thống kê)
Having :lọc nhóm( các điều kiện có thống kê)
Mệnh đề OrDer By : Mệnh đề sắp xếp dữ liệu
Cú pháp Order by [Ten_bang.]Ten_Cot [asc/Desc] [,…]
Mệnh đề Compute /Compute .. By : là mệnh đề tạo giá trị thống kê cuối kết quả hoặc thống kê
theo cột
Cú pháp
Compute Hàm thống kê : tạo thống kê cuối kết quả
7


Compute Hàm thống kê By [Ten_Bang.]Ten_cot :thống kê theo cột,dữ liệu phải được sắp xếp
theo cột này
Chú ý: Trong View không có mệnh đề này
*/
selectCOUNT(*)
from Ketqua
/* sử dụng group by*/

select tensv,avg(ketqua.Diem)
from Sinhvien, Monhoc, Ketqua
where Sinhvien.masv = Ketqua.MaSV and Monhoc.MaMH = Ketqua.MaMH
Groupby tensv
/* truy vấn lồng nhau*/
select tensv, Diem from Ketqua, sinhvien
where sinhvien.masv = Ketqua.MaSV and MaMH ='MH001'and
Diem =(selectMAX(Diem)from Ketqua where MaMH ='MH001')
(select tensv, Diem from Ketqua, sinhvien
where sinhvien.masv = Ketqua.MaSV)
EXCEPT/*EXCEPT phép trừ, dùng phép hợp là UNION, phép giao là Intersect */
(select tensv, Diem from Ketqua, sinhvien
where sinhvien.masv = Ketqua.MaSV and Diem<5)
select tensv, Diem from Ketqua, sinhvien
where sinhvien.masv = Ketqua.MaSV
and Diem notIN(select Diem from Ketqua, sinhvien
where sinhvien.masv = Ketqua.MaSV and Diem<5)
select TenSV
from Sinhvien where Masv ='SV001'
/*Update dữ liệu*/
update Sinhvien set Gioitinh=N'Nữ', Que ='HCM'where Masv ='SV001'
selectCOUNT(distinct Masv)
from Ketqua
selecttop 50 percent*from sinhvien
selectTop 1 Masv
from ketqua
select MaSV,avg(Diem)
from Ketqua
groupby Masv
Đề 1

Lý Thuyết:
8


1, Câu 1
2,
Thực hành:
1, Tạo cơ sở dữ liệu quản lý đơn vị gồm 3 bảng: Nhân viên, đơn vị và dự án có cấu trúc như sau:
NHANVIEN (masoNV, hoten , ngaysinh , gioitinh, luong, masoDV,masoDA)
DONVI (masoDV, tenDV , nguoiQL, ngaybatdau)
DUAN (masoDA, tenDA, diadiemDA, masoDV)
-

Kiểu dữ liệu của các trường sinh viên tự thiết kế
Các trường có gạch chân là khóa chính của các bảng.
Mặc định địa điểm dự án là ‘141 Duong Chien Thang’.
Tên đơn vị không được để trống.

2, Thiết lập mối quan hệ giữa các bảng vừa tạo ra.
3, Nhập dữ liệu vào các bảng trên, mỗi bảng ít nhất 3 bản ghi.
4, Hiển thị họ tên, ngày sinh, giới tính của những nhân viên có tên ‘A’
5, Cho biết danh sách những đơn vị tham gia một dự án cụ thể nào?
6, Đưa ra danh sách các nhân viên có lương lớn hơn lương trung bình
7, Tạo một view để đưa ra mã nhân viên, họ tên, ngày sinh, tên đơn vị có lương lớn hơn 3500.
Có thể dùng view này để bổ sung bản ghi có được không?ql

9


ĐÁp ÁN

create database qldonvi;
use qldonvi;
create table NHANVIEN
(
manv int primary key,
madv int null,
mada int null,
hoten varchar(20) null,
ngaysinh datetime null,
gioitinh varchar(3) null,
luong int null
)
create table DONVI
(
madv int primary key,
tendv varchar(20) not null,
nguoiql varchar(20) null,
ngaybatdau datetime null,
)
create table DUAN
(
mada int primary key,
madv int null,
tenda varchar(20) null,
diadiem varchar(25) default '141 Duong Chien Thang',
)
10


alter table NHANVIEN

add constraint FK1
foreign key(madv) references DONVI(madv)
alter table NHANVIEN
add constraint FK2
foreign key(mada) references DUAN(mada)
alter table DUAN
add constraint FK3
foreign key(madv) references DONVI(madv)
insert into DONVI
values (001,'banhang','a',2000/9/11)
insert into DONVI
values (002,'suachua','nguyen van a',2000/9/11)
insert into DONVI
values (003,'thungan','nguyen van b',2000/9/11)
insert into DUAN (mada,madv,tenda)
values (001,001,'du dan a')
insert into DUAN
values (002,002,'du dan a','yen xa ha noi')
insert into DUAN (mada,madv,tenda)
values (003,003,'du dan a')
insert into NHANVIEN
values (001,001,001,'nguyen van A',1993/9/11,'nam',4000)
insert into NHANVIEN
values (002,003,002,'nguyen van b',1993/9/11,'nam',4000)
insert into NHANVIEN
values (003,003,003,'nguyen van c',1993/9/11,'nam',4000)
insert into NHANVIEN
values (004,002,002,'nguyen van d',1993/9/11,'nam',4000)
insert into NHANVIEN
values (005,002,002,'nguyen van e',1993/9/11,'nam',10000)

insert into NHANVIEN
values (006,002,002,'nguyen van f',1993/9/11,'nam',3400)
insert into NHANVIEN
values (007,002,002,'nguyen van g',1993/9/11,'nam',3600)
select * from donvi
select * from nhanvien
select * from duan

11


--4, hien thi ho ten, ngay sinh, gioi tinh cua nhan vien co ten 'a'
select hoten,ngaysinh,gioitinh
from nhanvien
where hoten='nguyen van A'
--5, cho biet danh sach don vi va du an cac don vi do tham gia
select da.tenda,dv.tendv
from duan da inner join donvi dv on da.madv=dv.madv
--6, danh sach nhan vien co luong lon hon luong trung binh
select hoten
from nhanvien
where luong>(select avg(luong)
from nhanvien)
--7, tao mot view de dua ra ma nhan vien, ho ten, ngay sinh,ten don vi
--co luong lon hon 3500; co the dung view nay de bo sung ban ghi duoc khong?
create view khungnhin
as
select nv.manv,nv.hoten,nv.ngaysinh,dv.tendv
from nhanvien nv inner join donvi dv on nv.madv=dv.madv
where nv.luong>3500

--khong the dung view nay de bo sung ban ghi vi no tac dong den nhieu bang
ĐỀ 3
Lý thuyết:
1, Câu 2 trong đề cương.
Thực hành:
Tạo cơ sở dữ liệu quản lý thư viện gồm 3 bảng: Bạn đọc, sách và phiếu mượn
BANDOC (maBD , tenBD , lop , ngaycap)
SACH (masach , tensach, tacgia, namXB, nhaXB)
PHIEUMUON (maBD , masach, ngaymuon, ngaytra, datra)
-

Kiểu dữ liệu của các trường sinh viên tự thiết kế
Các trường có gạch chân là khóa chính của bảng
12


-

Ngày mượn (ngaymuon ) có giá trị mặc định là ngày hệ thống.
Tên bạn đọc không được để trống.

2, Thiết lập mối quan hệ giũa các bảng vừa tạo ra.
3, Nhập dữ liệu vào các bảng trên, mỗi bảng ít nhất 3 bản ghi.
4, Cho biết những tên sách của nhà xuất bản Giáo dục được xuất bản vào năm 2000
5, Liệt kê những tên sách mà bạn đọc có tên là ‘AAA’ đã mượn của thư viện.
6. Danh sách những bạn đọc đã đăng ký thẻ nhưng chưa có lần nào mượn sách
7, Tạo một view để hiển thị các thông tin về mã bạn đọc, tên bạn đọc và lớp của bạn đọc mượn
sách quá hạn. Có thể dùng view này để bố sung dữ liệu vào bảng BANDOC được không?

Đề 4

Lý thuyết:
1, câu 5 trong đề cương
Thực hành
Phần mô hình cơ sở dữ liệu giống câu 1

13


4, Hiển thị họ tên, lương của nhân viên nữ có lương <500
5,Hiển thị tên và địa điểm của các dự án của một đơn vị cụ thể nào đó
6, Cho biết tên những đơn vị đang thực hiện trên 3 dự án
7, Tạo thủ tục lưu trũ để thông qua thủ tục này có thể bổ sung thêm 1 bản ghi vào NHANVIEN
(thủ tục phải thực hiện kiểm tra tính hợp lệ của dữ liệu cần
create database qldonvi1;
use qldonvi1;
create table NHANVIEN
(
manv int primary key,
madv int null,
hoten varchar(20) null,
ngaysinh datetime null,
gioitinh varchar(3) null,
luong int null
)
create table DONVI
(
madv int primary key,
tendv varchar(20) not null,
nguoiql varchar(20) null,
ngaybatdau datetime null,

)
14


create table DUAN
(
mada int primary key,
madv int null,
tenda varchar(20) null,
diadiem varchar(25) default '141 Duong Chien Thang',
)
alter table NHANVIEN
add constraint FK
foreign key(madv) references DONVI(madv)
alter table DUAN
add constraint FK0
foreign key(madv) references DONVI(madv)
insert into DONVI
values (001,'banhang','a',2000/9/11)
insert into DONVI
values (002,'suachua','nguyen van a',2000/9/11)
insert into DONVI
values (003,'thungan','nguyen van b',2000/9/11)
insert into DUAN (mada,madv,tenda)
values (001,001,'du dan a')
insert into DUAN
values (002,002,'du dan a','yen xa ha noi')
insert into DUAN (mada,madv,tenda)
values (003,003,'du dan a')
insert into DUAN (mada,madv,tenda)

values (004,001,'du dan f')
insert into DUAN
values (005,001,'du dan g','nhon ha noi')
insert into DUAN
values (006,001,'du dan h','ba dinh ha noi')
insert into NHANVIEN
values (001,001,'nguyen van A',1993/9/11,'nam',4000)
insert into NHANVIEN
values (002,003,'nguyen van b',1993/9/11,'nam',4000)
insert into NHANVIEN
values (003,003,'nguyen van c',1993/9/11,'nam',4000)
insert into NHANVIEN
values (004,002,'nguyen van d',1993/9/11,'nam',4000)
insert into NHANVIEN
15


values (005,002,'nguyen van e',1993/9/11,'nam',10000)
insert into NHANVIEN
values (006,002,'nguyen van f',1993/9/11,'nam',3400)
insert into NHANVIEN
values (007,002,'nguyen van g',1993/9/11,'nam',3600)
insert into NHANVIEN
values (008,001,'nguyen thi a',1993/9/11,'nu',400)
select * from donvi
select * from nhanvien
select * from duan
--4, hien thi ho ten, luong cua nhan vien nu co luong < 500
select hoten,luong
from nhanvien

where (luong<500)and(gioitinh='nu')
--5, hien thi ten va dia diem cua cac du an cua mot don vi cu the
select da.tenda,da.diadiem
from donvi dv inner join duan da on dv.madv=da.madv
where dv.tendv='banhang'
--6, cho biet ten nhung don vi dang thuc hien tren 3 du an
select dv.tendv
from duan da inner join donvi dv on da.madv=dv.madv
group by dv.tendv
having count(dv.tendv)>3
--7, tao mot thu tuc luu tru de bo sung 1 ban ghi vao NHANVIEN
--kiem tra tinh hop le cua du lieu can dua vao
create procedure sp_bosung ( @manv int,
@madv int,
@hoten varchar(20),
@ngaysinh datetime,
@gioitinh varchar(3),
@luong int)
as
begin
if (@manv not in (select manv from nhanvien))and(@madv in (select madv from
donvi))
insert into NHANVIEN values
(@manv,@madv,@hoten,@ngaysinh,@gioitinh,@luong)
16


else
print ' du lieu khong hop le '
end

-- 8, su dung thu tuc de them mot bang ghi vao NHANVIEN
sp_bosung @manv=009,
@madv=002,
@hoten='nguyen van f',
@ngaysinh='1993/9/11',
@gioitinh='nam',
@luong=3400
Đề 5
Lý thuyết:
1, Câu 5 trong đề cương
Thực hành:
1, Tạo cơ sở dữ liệu quản lý sinh viên gồm 3 bảng Sinh viên, Giáo viên và Lớp học. Cấu trúc các
bảng như sau:
SINHVIEN ( maSV, hoten , ngaysinh, gioitinh, diem, malop)
GIAOVIEN (maGV , tenGV, trinhdoCM, malop )
LOPHOC ( malop, tenlop, diadiem, GVCN)
-

Kiểu dữ liệu của các trường sinh viên tự thiết kế
Các trường có gạch chân là khóa chính của các bảng
Giới tính có giá trị mặc định là ‘Nam’
Tên lớp không được để trống

2, thiết lập mối quan hệ giữa các bảng vừa tạo ra.
3, nhập dữ liệu vào các bảng trên, mỗi bảng ít nhất 3 bản ghi.
4, đưa ra danh sách các sinh viên nữ đạt điểm giỏi ( điểm >=9 )
5, Hiển thị tên giáo viên, trình độ của tất cả những giáo viên dạy lớp có tên là at5
6, Cho biết họ tên, điểm của các sinh viên có điểm lớn hơn điểm trung bình của lớp
7, tạo một thủ tục lưu trữ có chức năng hiển thị danh sách sinh viên của một lớp bất kỳ


17


8, Sử dụng thủ tục để đưa ra danh sách sinh viên của lớp có mã ‘AT03’
ĐÁP ÁN
create database truonghoc;
use truonghoc;
create table sinhvien
(
masv int primary key,
malop int null,
hoten varchar(20) null,
ngaysinh datetime null,
gioitinh varchar(10) default 'nam',
diem int null,
)
create table giaovien
(
magv int primary key,
malop int null,
tengv varchar(20) null,
trinhdocm varchar(20) null,
)
create table lophoc
(
malop int primary key,
tenlop varchar(20) not null,
diadiem varchar(20) null,
gvcn varchar(20) null,
)

alter table sinhvien
add
constraint fk1 foreign key(malop) references lophoc(malop)
alter table giaovien
add
constraint fk2 foreign key(malop) references lophoc(malop)
insert into lophoc
values (001,'at8a','phong 101','quynh')
insert into lophoc
values (002,'at8b','phong 103','quynh')
insert into lophoc
values (003,'at8c','phong 104','quynh')
insert into sinhvien
values (001,001,'nguyen van a',1993/11/9,'nu',6)
18


insert into sinhvien
values (002,001,'nguyen van b',1993/11/9,'nam',9)
insert into sinhvien
values (003,002,'nguyen hoang a',1993/11/9,'nu',10)
insert into sinhvien
values (004,003,'nguyen a',1993/11/9,'nam',9)
insert into giaovien
values (001,001,'nguyen hoa a','tien si')
insert into giaovien
values (002,002,'nguyen van a','cao dang')
insert into giaovien
values (003,002,'nguyen xuan a','thac si')
insert into giaovien

values (004,003,'nguyen hoang a','giao su')
select * from sinhvien
-- 4, danh sach cac sinh vien nu dat diem >=9
select masv,hoten
from sinhvien
where (diem>=9)and(gioitinh='nu')
-- 5, hien thi ten giao vien, trinh do giao vien lop at8b
select gv.tengv,gv.trinhdocm
from giaovien gv inner join lophoc lh on gv.malop=lh.malop
where lh.tenlop='at8b'
-- 6, cho biet ho ten, diem cua cac sinh vien co diem lon hon trung binh cua lop
select hoten,diem
from sinhvien
where (malop=001)and(diem>(select avg(diem)
from sinhvien))
-- 7, tao mot thu tuc co chuc nang hien thi danh sach sinh vien cua mot lop bat ky
create procedure sp_dssinhvien (@tenlop varchar(20))
as
begin
select sv.masv,sv.hoten,sv.ngaysinh
from sinhvien sv inner join lophoc lh on sv.malop=lh.malop
where lh.tenlop=@tenlop
19


print ' danh sach sinh vien cua lop ' + @tenlop
end
sp_dssinhvien @tenlop='at8a'
Đề 6
Lý thuyết:

Không thấy trong đề cương
Thực hành:
1, Mô hình cơ sở dữ liệu

4, Hiển thị danh sách bạn đọc được cấp thẻ vào ngày 20/11/2007
5, Cho biết danh sách tên bạn đọc và lớp của những bạn đọc mượn sách quá hạn. Biết rằng mỗi
quyển sách bạn đọc được mượn trong 5 ngày.
6, Hiển thị số lượt mượn của từng quyển sách (những quyển sách chưa từng được mượn ghi là 0)
. danh sách gồm mã sách, tên sách, số lượt mượn
7, Tạo một thủ tục lưu trữ có chức năng hiển thị những tên sách có trong thư viện
8, Sử dụng thủ tục trên để đưa ra danh sách các sản phẩm của nhà xuất bản giáo dục
ĐÁP ÁN
create database thuvien1;
use thuvien1;
create table bandoc
(
mabd int primary key,
tenbd varchar(20) not null,
20


lop varchar (10) null,
ngaycap datetime null
)
create table sach
(
masach int primary key,
tensach varchar(20) null,
tacgia varchar(20) null,
namxb int null,

nhaxb varchar(20) null,
)
create table phieumuon
(
mabd int not null,
masach int not null,
constraint khoachinh primary key (mabd,masach),
ngaymuon datetime default getdate(),
ngaytra datetime null,
datra varchar(20) null,
)
alter table phieumuon
add constraint fk1
foreign key(mabd) references bandoc(mabd)
alter table phieumuon
add constraint fk2
foreign key(masach) references sach(masach)
insert into bandoc
values (001,'nguyen van a','at8c',2011/6/9)
insert into bandoc
values (002,'nguyen van b','at8b',2011/6/9)
insert into bandoc
values (003,'nguyen van d','at8a',2011/6/9)
insert into bandoc
values (004,'nguyen van e','at8a',2011/6/9)
insert into bandoc
values (005,'nguyen van f','at8c',2011/6/9)
insert into sach
values (001,'tin hoc','a',2010,'nxb giao duc')
insert into sach

values (002,'toan hoc','a',2008,'nxb giao duc')
insert into sach
21


values (003,'vat ly','e',2011,'nxb kim dong')
insert into sach
values (004,'phan cung','f',2000,'nxb giao duc')
insert into phieumuon
values (001,001,2013/11/18,2013/11/28,'chua')
insert into phieumuon
values (001,002,2013/11/18,2013/11/28,'chua')
insert into phieumuon
values (002,002,2013/11/8,2013/11/18,'roi')
insert into phieumuon
values (002,003,2013/11/29,2013/12/9,'chua')
insert into phieumuon
values (003,001,2013/11/28,2013/12/8,'chua')
insert into phieumuon
values (003,002,2013/11/28,2013/12/8,'roi')
insert into phieumuon
values (003,003,2013/11/8,2013/11/28,'roi')
insert into phieumuon
values (005,002,2013/11/28,2013/12/8,'roi')
insert into phieumuon
values (005,003,2013/11/8,2013/11/28,'roi')
insert into phieumuon
values (005,001,11/11/2013,21/11/2013,'roi')
insert into phieumuon
values (001,003,11/2013/11,2013/11/9,'roi')

insert into phieumuon
values (002,001,1900/11/8,1900/11/28,'roi')
insert into phieumuon
values (004,001,'1900/11/8','1900/11/28','roi')
insert into phieumuon
values (004,002,'2013/11/8','2013/11/28','roi')
insert into phieumuon
values (004,003,'2013/12/1','2013/12/11','roi')
select * from bandoc
select * from sach
select * from phieumuon
-- 4, hien thi danh sach ban doc duoc cap the vao ngay 2/7/1900
select tenbd
from bandoc
where ngaycap=2011/6/9

22


-- 5, danh sach ten ban doc va lop cua ban doc muon sach qua han. biet rang moi quyen
--sach duoc muon trong 5 ngay
select bd.tenbd,bd.lop,pm.mabd
from bandoc bd inner join phieumuon pm on bd.mabd=pm.mabd
where (year(getdate())-year(ngaymuon))*365 + (month(getdate())-month(ngaymuon))*30 +
(day(getdate())-day(ngaymuon))>5
-- 6, hien thi so lan muon tung quyen sach, nhung quyen chua duoc muon ghi la 0
--cac cot gom masach tensach va so lan muon
select s.masach,s.tensach,count(pm.masach) as solanmuon
from sach s full outer join phieumuon pm on s.masach=pm.masach
group by s.masach,s.tensach

-- 7, tao mot thu tuc co chuc nang dua ra ten sach co trong thu vien
create procedure sp_tensach (@nhaxb varchar(20))
as
begin
print 'ten sach co trong thu vien cua nxb' + @nhaxb
select tensach
from sach
where nhaxb=@nhaxb
end
-- 8, su dung thu tuc tren de dua ra cac sach cua nxb giao duc
sp_tensach @nhaxb='nxb giao duc'
ĐỀ 7
Lý thuyết:
1, Câu 8 trong đề cương
Thực hành:
1, Tạo cơ sở dữ liệu như hình sau:

23


-

Giới tính có giá trị mặc định là nam
Họ tên nhân viên không được để trống

4, Đưa ra họ tên, lương của các nhân viên có trong đơn vị ‘nghien cuu’
5, Cho biết số nhân viên có lương dưới 200 của từng đơn vị
6, Đưa ra danh sách các đơn vị chưa có dự án nào
7, Tạo một view để đưa ra mã nhân viên, họ tên, ngày sinh, tên đơn vị của những nhân viên có
lương trên 3500. Có thể dùng view này để bổ sung dữ liệu cho bảng NHANVIEN hay không?

ĐÁP ÁN
create database qldonvi2;
use qldonvi2;
create table NHANVIEN
(
manv int primary key,
madv int null,
hoten varchar(20) not null,
ngaysinh datetime null,
gioitinh varchar(3) default 'nam',
luong int null
)
24


create table DONVI
(
madv int primary key,
tendv varchar(20) null,
nguoiql varchar(20) null,
ngaybatdau datetime null,
)
create table DUAN
(
mada int primary key,
madv int null,
tenda varchar(20) null,
diadiem varchar(25) null,
)
alter table NHANVIEN

add constraint FK1
foreign key(madv) references DONVI(madv)
alter table DUAN
add constraint FK3
foreign key(madv) references DONVI(madv)
insert into DONVI
values (001,'banhang','a','2000/9/11')
insert into DONVI
values (002,'suachua','nguyen van a','2000/9/11')
insert into DONVI
values (003,'thungan','nguyen van b','2000/9/11')
insert into DONVI
values (004,'nghiencuu','nguyen hoang ha','2008/9/11')
insert into DONVI
values (005,'quan ly','nguyen hoang ha','2008/9/11')
insert into DUAN (mada,madv,tenda)
values (001,001,'du dan a')
insert into DUAN
values (002,002,'du dan a','yen xa ha noi')
insert into DUAN (mada,madv,tenda)
values (003,003,'du dan a')
insert into DUAN
values (004,004,'du dan a','hung thanh tuyen quang')
insert into DUAN
25