Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 1

create database QLNXTONKHO
on primary
(
name=QLNXTONKHO_DATA,
filename='D:\QLNXTONKHO_DATA.MDF',
size=50MB,
maxsize=200MB,
filegrowth=10MB
)
log on
(
name=QLNXTONKHO_LOG,
filename='D:\QLNXTONKHO_LOG.LDF',
size=10MB,
maxsize=unlimited,
filegrowth=5MB
)

use QLNXTONKHO

create table VATTU
(
Mavtu char(4) not null,
Tenvtu nvarchar(50),
Dvitinh nvarchar(10),
Phantram real
)

Ngaynhap datetime,
Sodh char(4) not null
)

create table CTPNHAP
(
Sopn char(4) not null,
Mavtu char(4) not null,
Slnhap int,
Dgnhap money
) create table PXUAT
(
Sopx char(4) not null,
Ngayxuat datetime,
Tenkh nvarchar(100)
)

create table CTPXUAT
(
Sopx char(4) not null,
Mavtu char(4) not null,
Slxuat int,
Dgxuat money
)

create table TONKHO
(

constraint FK_CTDH_MAVT foreign key(Mavtu) references VATTU(Mavtu)

alter table PNHAP
add
constraint PK_PNHAP primary key(Sopn),
constraint FK_PNHAP_Sodh foreign key(Sodh) references DONDH(Sodh)

alter table CTPNHAP
add
constraint PK_CTPNHAP primary key(Sopn,Mavtu),
constraint FK_CTPNHAP_SOPN foreign key(Sopn) references
PNHAP(Sopn),
constraint FK_CTPNHAP_MAVT foreign key(Mavtu) references
VATTU(Mavtu)

alter table PXUAT
add constraint PK_PXUAT primary key(Sopx)

alter table CTPXUAT
add
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 4

constraint PK_CTPXUAT primary key(Sopx,Mavtu),
constraint FK_CTPXUAT_SOPX foreign key(Sopx) references
PXUAT(Sopx),
constraint FK_CTPXUAT_MAVT foreign key(Mavtu) references
VATTU(Mavtu)


constraint CHK_CTPX_DGX check(Dgxuat>0) alter table TONKHO
add
constraint CHK_TONKHO_SLD check(Sldau>=0),
constraint CHK_TONKHO_TSLN check(Tongslnhap>=0),
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 5

constraint CHK_TONKHO_TSLX check(Tongslxuat>=0),
constraint DF_TONKHO_SLD default 0 for Sldau,
constraint DF_TONKHO_TSLN default 0 for Tongslnhap,
constraint DF_TONKHO_TSLX default 0 for Tongslxuat

Chèn d liu
insert into VATTU values('BU01',N'Bàn i Philip A',N'Cái',17)
insert into VATTU values('BU02',N'Bàn i Philip B',N'Cái',17)
insert into VATTU values('BU03',N'Bàn i Philip C',N'Cái',17)
',40)
',40)
u Karaoke',N'B',30)
u Karaoke 6 s',N'B',30)
i IK-2002',N'B',NULL)
insert into VATTU values('TL15',N'T lnh Sanyo 150 lít',N'Cái',25)
insert into VATTU values('TL90',N'T lnh Sanyo 90 lít',N'Cái',20)
insert into VATTU values('TV14',N'Tivi Sony 14 inches',N'Cái',15)
insert into VATTU values('TV21',N'Tivi Sony 21 inches',N'Cái',10)
insert into VATTU values('TV29',N'Tivi Sony 29 inches',N'Cái',10)

insert into DonDH Values('D003','2/10/2006','C02')
insert into DONDH Values('D004','2/17/2006','C05')
insert into DONDH Values('D005','3/1/2006','C02')
insert into DONDH Values('D001', '7/1/2006','C04') THEM GIA TRI VAO BANG CTDONDH
insert into CTDONDH Values('D001','DD01',20)
insert into CTDONDH Values('D001','DD02',15)
insert into CTDONDH Values('D002','VD02',30)
insert into CTDONDH Values('D001','TV14',30)
insert into CTDONDH Values('D003','TV14',10)
insert into CTDONDH Values('D003','TV29',20)
insert into CTDONDH Values('D004','TL90',10)
insert into CTDONDH Values('D005','TV14',10)
insert into CTDONDH Values('D005','TV29',20) THEM GIA TRI VAO BANG PNHAP
insert into PNHAP Values('N001','1/17/2006','D001')
insert into PNHAP Values('N002','1/20/2006','D004')
insert into PNHAP Values('N003','1/31/2006','D002')
insert into PNHAP Values('N004','2/15/2006','D003')
insert into PNHAP Values('N005','2/15/2007','D003')
insert into PNHAP Values('N006','2/28/2007','D005')
THEM GIA TRI VAO BANG CTPNHAP
insert into CTPNHAP Values('N001','DD01',8,2500000)

insert into TONKHO Values(200601,'VD02',0,30,10,20)
insert into TONKHO Values(200602,'DD01',4,0,0,4)
insert into TONKHO Values(200602,'DD02',8,0,0,8)
insert into TONKHO Values(200602,'TV14',5,0,0,5)
insert into TONKHO Values(200602,'VD01',20,0,0,20)
insert into TONKHO Values(200603,'DD01',0,190,0,190)
BÀI TP TRUY VN

1. Thêm d liu vào các bng bng lnh INSERT INTO.

2. Thêm ct tgnhap kiu numeric vào bng PNHAP
alter table PNHAP
add Tgnhap numeric

3. Thêm ct tgxuat kiu numeric vào bng PXUAT
alter table PXUAT
add Tgxuat numeric

4. Làm li tt c nhng ví d p và nhng ví d yêu cu phi làm
mà cha hoàn tt,
n qua câu tip theo

5. Cho bit thông tin sopn,mavtu, slgnhap, dgnhap, thanh tien ca tng mavtu.
select sopn,mavtu, slnhap, dgnhap,slnhap*dgnhap as [thanh tien]
from CTPNHAP
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM


select Sopx,Tenvtu, Slxuat, Dgxuat,Slxuat*Dgxuat as [thanh tien]
from CTPXUAT C, VATTU V
where C.Mavtu=V.Mavtu

11. Cho bit thông tin sopx,ngayxuat,mavtu, slgxuat, dgxuat, thanh tien ca
tng mavtu.
select P.Sopx,Ngayxuat=convert(char(10),Ngayxuat,103),Mavtu, Slxuat,
Dgxuat,Slxuat*Dgxuat as [thanh tien]
from CTPXUAT C, PXUAT P
where C.Sopx=P.Sopx

12. Cho bit thông tin sopx,ngayxuat,ten vt t, slgxuat, dgxuat, thanh tien ca
tng mavtu
t n 03/03/2006.Yêu cu ngày nhnh dng dd-mm-
yyyy
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 9

select P.Sopx,Ngayxuat=convert(char(10),Ngayxuat,103),Tenvtu, Slxuat,
Dgxuat,Slxuat*Dgxuat as [thanh tien]
from CTPXUAT C, PXUAT P, VATTU V
where C.Sopx=P.Sopx and V.Mavtu=C.Mavtu and Ngayxuat between
'01/15/2006' and '03/03/2006'

13. Cp nht ct tng tr giá nhp(tgnhap) cho bng PNHAP.
ng ctpnhap.Ch cp nht cho các
SOPN là N001,N002
update PNHAP
set Tgnhap= (

ng ctpxuat.Ch cp nht cho các
phiu xuat tr-01.
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 10

update PXUAT
set Tgxuat = (
select sum(Slxuat*Dgxuat)
from CTPXUAT C
where C.Sopx = PXUAT.Sopx
)
where convert(char(7),PXUAT.Ngayxuat,111)='2006/01'

17. Xoá mã vt t 
delete from VATTU where dvitinh=N'B'

18. Insert li nhng mu tin va b xoá
',40)
',40)
u Karaoke',N'B',30)
insert into VATTU values('KO04u Karaoke 6 s',N'B',30)
i IK-2002',N'B',NULL)
',30)
',15)

19. Th hin thông tin : mã vt t, tên vt t
select Mavtu,Tenvtu
from VATTU



25. Cho bit s lt hàng ca tng nhà cung cp.Thông tin gm tên
nhà cung cp, s lt hàng.
select Tenncc,count(Sodh) as 'SO LUONG DON DAT HANG'
from DONDH D, NHACC N
where D.Manhacc=N.Manhacc
group by Tenncc

26. Cho bit thông tin sodh, tennhacc,tenvtu, sldat
select D.Sodh, Tenncc,Tenvtu, Sldat
from DONDH D, NHACC N, CTDONDH C,VATTU V
where D.Manhacc=N.Manhacc and V.Mavtu=C.Mavtu and C.Sodh=D.Sodh

27. Cho bit thông tin sodh,tennhacc,tenvtu, tongsldat trong tháng 1 và 2
select
D.Sodh,Ngaydh=convert(char(10),Ngaydh,103),Tenncc,Tenvtu,TongSoLuongDat
=sum(Sldat)
from DONDH D, NHACC N, CTDONDH C,VATTU V
where D.Manhacc=N.Manhacc and V.Mavtu=C.Mavtu and C.Sodh=D.Sodh and
month(Ngaydh) in (1,2)
group by D.Sodh,convert(char(10),Ngaydh,103),Tenncc,Tenvtu

28. Th hin thông tin khách hàng C01 và C02
select *
from NhaCC
where Manhacc in ('C01','C02')

29. Th hit hàng(DONDH) trong tháng 01 và 02
select Sodh,Ngaydh=convert(char(10),Ngaydh,103),Manhacc
from DONDH

33. Hin th danh sách vt t trong bng VATTU, sp xp tên vt t gim dn
select *
from VATTU
order by Tenvtu desc

34. Hin th danh sách các nhà cung cp  Q1 HCM , sp h tên nà cung cp
n.
select *
from NhaCC
where Diachi like'%Q1,HCM'
order by Tenncc

35. Hin th danh sách thông tin trong bng CTPNHAP có thêm ct thành
tin(ct biu thc)
bit rng thành tin= SLNHAP * DGNHAP
select *, Slnhap*Dgnhap as 'ThanhTien'
from CTPNHAP

36. Hin th danh sách thông tin trong bng PNHAP có thêm ct tr giá,
bit rng tr giá = tng thành tin ca các mu tin chi tit tng ng trong bng
CTPNHAP.
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 13

select P.Sopn, Ngaynhap=convert(char(7),Ngaynhap,111),Sodh,Tgnhap,
sum(Slnhap*Dgnhap) as 'TriGia'
from CTPNHAP C,PNHAP P
where C.SOPN=P.SOPN
group by P.Sopn, Ngaynhap,Sodh,tgnhap

Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 14

BÀI TẬP VIEW 1. To view có tên vw_DMVT bao gm các thông tin sau; mã vt t, tên vt t.
 lit kê danh sách các vt t hin có trong bng VATTU.
if exists (select * from sysobjects where name='vw_DMVT')
drop view vw_DMVT
Go
create view vw_DMVT
as
select Mavtu,tenvtu
from VATTU

2. To view có tên vw_dondh_tongsldat bao gm các thông tin sau : s t
hàng, tng s lt .
 thng kê tng s lt hàng.
if exists (select * from sysobjects where name='vw_dondh_tongsldat')
drop view vw_dondh_tongsldat
Go
create view vw_dondh_tongsldat
as
select Sodh,TongSLDat=sum(sldat)
from CTDONDH
group by Sodh

3. To view có tên vw_dondh_tongslnhap bao gm các thông tin sau: s t

5. To view có tên vw_dondh_tongsldatnhap_daydu bao gm các thông tin sau
: s t hàng.
 lit kê nh hàng(tng
lt bng tng lng nhp)
if exists (select * from sysobjects where
name='vw_dondh_tongsldatnhap_daydu')
drop view vw_dondh_tongsldatnhap_daydu
GO
create view vw_dondh_tongsldatnhap_daydu
as
select Sodh as 'Don Hang Day Du'
from CTDONDH C
group by Sodh
having sum(sldat)= (select sum(slnhap) from PNHAP P,CTPNHAP CT
where C.Sodh=P.Sodh and P.Sopn=CT.Sopn)

6. To view có tên vw_tongnhap bao g
vt t , tng s lng nhp.
 thng kê tng s lng nhp ca các vt t trong t
ng yyyy-mm).
chú ý không dùng bng tn kho.
if exists (select * from sysobjects where name='vw_tongnhap')
drop view vw_tongnhap
GO
create view vw_tongnhap
as
select
NamThang=convert(char(7),Ngaynhap,111),Mavtu,TSL_Nhap=sum(Slnhap)
from PNHAP P,CTPNHAP C
where P.Sopn=C.Sopn

as
select D.Sodh,Ngaydh,V.Mavtu,Tenvtu,Sldat,TongSL_Nhap= (select
sum(Slnhap)
from PNHAP P,CTPNHAP CT
where P.Sopn=CT.Sopn and
D.Sodh=P.Sodh and V.Mavtu=CT.Mavtu
)
from DONDH D,CTDONDH C,VATTU V
where D.Sodh=C.Sodh and C.Mavtu=V.Mavtu

Kt hp các view  các câu trên , thc hin các truy vn chn l tr li các
câu hi sau:

1. Cho bit danh sách các phit hàng cha tc nhp hàng.
if exists (select * from sysobjects where name='vw_Sodh_chua_nhaphang')
drop view vw_Sodh_chua_nhaphang
GO
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 17

create view vw_Sodh_chua_nhaphang
as
select Sodh
from vw_dondh_tongsldat
where Sodh not in (select Sodh from vw_dondh_tongslnhap)


where V.Mavtu=C.Mavtu
group by V.Mavtu,Tenvtu
order by sum(Slxuat) desc

t hàng nào có nhiu mt hàng nht.
if exists (select * from sysobjects where name='vw_Sodh_nhieu_VT_nhat')
drop view vw_Sodh_nhieu_VT_nhat
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 18

GO
create view vw_Sodh_nhieu_VT_nhat
as
select Top 1 with ties Sodh,TSL_Vatu=count(Mavtu)
from CTDONDH
group by Sodh
order by count(Mavtu) desc

6. Cho bit tình hình nhp xut ca vt t , thông tin g-
mm), mã vt t, tên vt t, tng s lng nhp, tng s lng xut.
if exists (select * from sysobjects where name='vw_tinhhinh_nhapxuat')
drop view vw_tinhhinh_nhapxuat
GO
create view vw_tinhhinh_nhapxuat
as
select Namthang,V.Mavtu,Tenvtu,TSL_Nhap,TSL_Xuat = ( select
sum(Slxuat)
from CTPXUAT C,PXUAT P
where V.Mavtu=C.Mavtu and

from DONDH D,VATTU V,CTDONDH C
where D.Sodh=C.Sodh AND C.Mavtu=V.Mavtu
group by convert(char(7),Ngaydh,111),V.Mavtu,Tenvtu

Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 20

Bài tập về BIẾN và IF
Dùng cả hai cách IF….ELSE và IF EXISTS 1. Tính xem có vt t nào bán ra s lng nhiu hn 4 hay không? Nu có in
t t có s lng > 4
ng thi Mã vt t, tên vt t.Nu không thy thì xut thông báo
không tìm thy.

if (select count(*) from CTPXUAT where SLXUAT >4) >0
begin
print N' DANH SÁCH CÁC VT T CÓ S LNG XUT > 4 LÀ: '
select V.MAVTU,TENVTU,SLXUAT
from VATTU V, CTPXUAT C
where V.MAVTU=C.MAVTU and SLXUAT >4
end
else
print N' Không tìm thy'


end
else
print N' Không tìm thy '

IF EXISTS

if exists ( select * from PNHAP where datename(dw,NGAYNHAP)='Friday' )
begin
print N' Nhn hàng nhp vào ngày Friday là: '
select
SOPN,NGAYNHAP=convert(char(10),NGAYNHAP,103),SODH,THU=datename(
dw,NGAYNHAP)
from PNHAP
where datename(dw,NGAYNHAP)='Friday'
end
else
print N' Không tìm thy'

3. Tìm nhng phit hàng t n tháng 3/2006. Nu tìm
thng phit
hàng t i các thông tin ca các
phit hàng tìm thy.
nu không thy thì xuy.Khi in thông báo nh
truyn bin vào thông báo.

DECLARE @TUNGAY CHAR(7),@DENNGAY CHAR(7)
SET @TUNGAY = '2006/01'
SET @DENNGAY = '2006/03'
if (select count(*) from DONDH where year(NGAYDH)=2006 and
month(NGAYDH) between 1 and 3) >0

print N' Không tìm thy nhng phit hàng t n
ngày '+ @DENNGAY

4. Tìm nhng phit hàng cha tng nhp hàng trong tháng 01/2006. Nu
tim thy thì xut thông báo: Nhng phit
hàng cha tng nhp hàng trong tháng 01/2006 , kèm thông tin các phit
hàng cha tng nhp hàng trong 01/2006.
nu không thy xuy nha
tng nhp hàng 01/2006.
Dùng bi xut thông báo.

declare @NGAYDH char(7)
set @NGAYDH = '2006/01'
if (select count(*) from DONDH where
convert(char(7),NGAYDH,111)=@NGAYDH and SODH not in(select SODH from
PNHAP)) >0
begin
print N' Nhng phit hàng cha tng nhp hàng trong '+
@NGAYDH +': '
select SODH,NGAYDH=convert(char(10),NGAYDH,103),MANHACC
from DONDH
where convert(char(7),NGAYDH,111)=@NGAYDH and SODH not
in(select SODH from PNHAP)
end
else
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 23

print N' Không tìm thy nhng phit hàng cha tng nhp hàng trong

convert(char(7),NGAYNHAP,111)=@NGAYNHAP) >0
begin
print N' Nhng phiu nhp trong '+ @NGAYNHAP +': '
select
NAMTHANG=convert(char(7),NGAYNHAP,111),MAVTU,TSL_NHAP=SUM(SLN
HAP)
from PNHAP P,CTPNHAP C
where C.SOPN=P.SOPN and
convert(char(7),NGAYNHAP,111)=@NGAYNHAP
group by convert(char(7),NGAYNHAP,111),MAVTU
end
else
Thực Hành Hệ Quản Trị Cơ Sở Dữ Liệu(SQL SERVER) – 11 LT-TM

Ngô Tất Đạt Page 24

print N' Không tìm thy nhng phiu nhp trong '+ @NGAYNHAP

IF EXISTS

declare @NGAYNHAP char(7)
set @NGAYNHAP = '2006/01'
if exists (select * from PNHAP P,CTPNHAP C where C.SOPN=P.SOPN and
convert(char(7),NGAYNHAP,111)=@NGAYNHAP)
begin
print N' Nhng phiu nhp trong '+ @NGAYNHAP +': '
select
NAMTHANG=convert(char(7),NGAYNHAP,111),MAVTU,TSL_NHAP=SUM(SLN
HAP)
from PNHAP P,CTPNHAP C


Ngô Tất Đạt Page 25

print N' Không tìm thy nhng phiu xut trong '+ @NGAYXUAT

IF EXISTS

declare @NGAYXUAT char(7)
set @NGAYXUAT = '2006/01'
if exists (select * from PXUAT P,CTPXUAT C where C.SOPX=P.SOPX and
convert(char(7),NGAYXUAT,111)=@NGAYXUAT)
begin
print N' Nhng phiu xut trong '+ @NGAYXUAT +': '
select
NAMTHANG=convert(char(7),NGAYXUAT,111),MAVTU,TSL_XUAT=SUM(SLX
UAT)
from PXUAT P,CTPXUAT C
where C.SOPX=P.SOPX and
convert(char(7),NGAYXUAT,111)=@NGAYXUAT
group by convert(char(7),NGAYXUAT,111),MAVTU
end
else
print N' Không tìm thy nhng phiu xut trong '+ @NGAYXUAT

7. Cho bit danh sách vt t có s lng xut nhiu nht.In ra câu thông báo,
kèm thông tin
ca vt t có s lng xut bán nhiu nht.

if (select count(*) from CTPXUAT) >0
begin