Không bao giờ trở lại

create database QL

create table theloaisach
( maloai char(10) Primary key,
tentheloai varchar(20) not null)

create table Tacgia
(MaTG varchar(10) primary key,
TenTG varchar(20) not null,
Diachi varchar(30) not null)

create table Sach
(Masach varchar(10) primary key,
Tensach varchar(20) not null,
NXB varchar(30) not null,
NamXB datetime,
Soluong int,
maloai char(10) not null,
constraint FK_maloai foreign key(maloai) references theloaisach(maloai),
MaTG varchar(10) not null,
constraint FK_MaTG foreign key(MaTG) references Tacgia(MaTG))

create table Docgia
(MaDG varchar(10) primary key,
TenDG varchar(20) not null,
Ngaysinh datetime,
Gioitinh bit,
Diachi varchar(30) not null)

create table Phieumuon
(SoPM varchar(10) primary key,
MaDG varchar(10) not null,
constraint FK_MaDG foreign key(MaDG) references Docgia(MaDG),
Ngaymuon datetime,
Ngayhentra datetime,
Ngaytra datetime)

create table Chitietphieumuon
(SoPM varchar(10) not null,
constraint FK_SoPM foreign key(SoPM) references Phieumuon(SoPM),
Masach varchar(10) not null,
constraint FK_Masach foreign key(Masach) references Sach(Masach),
constraint PK_abc primary key(SoPM,Masach),
Soluong int,
constraint ck_Soluong check(Soluong>0))


insert into theloaisach
values('003','tho')
insert into theloaisach
values('002','truyen')
insert into theloaisach
values('001','tieu thuyet')

insert into Tacgia
values('TG002','Pham van M','Dia nguc')
insert into Tacgia
values('TG003','Nguyen Van T','hjkf')
insert into Tacgia
values('TG001','Tran Thi C','lk')

insert into Sach
values('S002','ABC','Kim dong','1/1/2000',20,'001','TG001')
insert into Sach
values('S001','hhj','ha','1/1/2000',20,'001','TG001')
insert into Sach
values('S003','hay','Kim dong','1/1/2000',20,'001','TG001')

insert into Docgia
values('DG002','Nguyen van C','5/3/1989',1,'ha tay')
insert into Docgia
values('DG001','Nguyen Thi A','5/3/1989',0,'ha tay')

insert into Phieumuon
values('01','DG001','2/4/2002','8/4/2002','9/4/2002')
insert into Phieumuon
values('02','DG002','5/6/2002','8/6/2002','9/6/2002')

insert into Chitietphieumuon
values('01','S002',5)
insert into Chitietphieumuon
values('02','S002',8)

/////////////////////////////////////// câu 3
create proc themsachmoi
@Masach varchar(10),
@Tensach varchar(20),
@NXB varchar(30),
@NamXB datetime,
@Soluong int,
@maloai char(10),
@MaTG varchar(10)
as
begin
if(@Masach in(select Masach from Sach))
print 'ma da co,hay nhap lai'
else
begin
if(@maloai not in(select maloai from theloaisach))
print 'ma loai chua ton tai'
else
if(@MaTG not in(select MaTG from Tacgia))
print 'Ma TG chua ton tai'
else
if(year(@NamXB)>year(getdate()))
print 'nhap lai nam'
else
if(@Soluong<0)
print 'nhap lai so luong'
else
insert into Sach values(@Masach,@Tensach,@NXB,@NamXB,@Soluong,@maloai,@MaTG)
end

exec themsachmoi 'S006','ABC','Kim dong','1/2/2002',5,'001','TG001'

/////////////////////////////////////////////////////////////////////Câu 4 -output

create proc tongsach3
@maloai char(10),
@tongsosach int output
as
begin
select @tongsosach=sum(Sach.Soluong) from Sach,theloaisach
where Sach.maloai=theloaisach.maloai
and Sach.maloai=@maloai
group by theloaisach.maloai
end

declare @tongsach int
exec tongsach3 '001', @tongsach output
select @tongsach

////////////////////////////////////////Câu 4- return
create proc tongsosach2
as
begin
declare @tongsosach int
select @tongsosach=sum(Soluong) from Sach --where maloai=@maloai
return @tongsosach
end

declare @a int
exec @a=tongsosach2 '001'
select @a

///////////////////////////////////////////////// Câu 5
create function tongsosach
returns int
as
begin
declare @tongsosach int
select @tongsosach=sum(soluong) from Sach where maloai=@maloai
return @tongsosach
end

print dbo.tongsosach('002')


////////////////////////////////////////////Câu 6
create function tongsosach4()
returns table
as
return(select tentheloai,Sum(Soluong) as tongsosach from Sach,theloaisach
where Sach.maloai=theloaisach.maloai group by tentheloai)

select * from tongsosach4()

/////////////////////////////////////////Câu 7-thu tuc
create proc sachdamuon2
as
begin
select Masach,sum(Soluong) from Chitietphieumuon --where Masach=@Masach
group by Masach
end
exec sachdamuon2
////////////////////////////////////////////////// Câu 7-hàm
create function sachmuon()
returns table
as
return(select Masach,sum(Soluong)as tongsach from Chitietphieumuon --where Masach=@Masach
group by Masach)

select * from sachmuon()

//////////////////////////// Câu 8-thu tuc
create proc DSDS
as
begin
select Masach,TenTG from Sach,Tacgia where Sach.MaTG=Tacgia.MaTG
end

exec DSDS

////////////////////////////////////////////// Câu 8-hàm
create function DSDS1()
returns table
as
return(select Masach,TenTG from Sach,Tacgia where Sach.MaTG=Tacgia.MaTG)

select * from DSDS1()