Bài Tập Quản Lý Thư Viện SQL (Bài 5)

Bài Tập Quản Lý Thư Viện SQL (Bài 5)

 

 Code: Tải về Code

use QuanLyThuVien
-- Quan Ly thu vien SQL by jundat95
-- View doc gia het han nhung chua tra
Create view View_HetHan
AS
Select *From tblDocGia Where MaDG In
(
 Select MaDG From tblMuonTra Where NgayHenTra < NgayTra
)


-- View Danh muc sach chua tung ai muon
Create view View_ChuaMuon 
AS
Select *From tblSach Where MaSach Not In 
(
 Select MaSach From tblMuonTra Where MaDG In 
 (
  Select MaDG From tblDocGia
 )
)


-- Tung loai sach dang co doc gia dang muon
Create View View_DangMuon
AS
 Select tblSach.MaSach,TenSach,TenTG, Sum(SL) AS Tong From tblSach,tblMuonTra 
 Where (tblSach.MaSach = tblMuonTra.MaSach) And tblSach.MaSach in
 (
  Select MaSach From tblMuonTra Where (NgayTra is Null) or (NgayHenTra > GETDATE() )
 )
 Group by tblSach.MaSach,TenSach,TenTG



-- Proc dua sach ra doc gia @MaDG dang muon 
Create Proc SPDMSM
  @MaDG nvarChar(50)
AS
 Select *From tblSach Where MaSach In
 (
  Select MaSach From tblMuonTra Where MaDG In
  (
   Select MaDG From tblDocGia Where MaDG = @MaDG
  )
 ) 

Exec SPDMSM 'MDG01' 


-- Dua ra Doc gia da muon vao ngay tren
Create Proc SPMVN
 @NgayMuon smalldatetime 
AS
 Select *From tblDocGia Where MaDG In 
 (
  Select MaDG From tblMuonTra 
  Where CONVERT(nvarchar(50),NgayMuon) = CONVERT(nvarchar(50),@NgayMuon)
  --NgayMuon ='03/30/2015'
 ) 

Exec SPMVN '03/30/2015'


--  Doc gia ngay hom nay la ngay cuoi cung phai tra
Create Proc SPNCC
AS
 Select *From tblDocGia Where MaDG In
 (
  Select MaDG From tblMuonTra Where DATEDIFF(day,NgayHenTra,GETDATE()) = 0 
 )   

 
-- Tao trigger de bat loi so luong nhap

Create  trigger TRKiemTraSL
on tblMuonTra
For Insert 
As
 if exists (Select SL From tblMuonTra 
    Where SL < 0 
    )
 print 'So luong nhap khong dung xin moi nhap lai'   
 
 else 
 print 'Nhap Du Lieu Thanh Cong'
 
Set DateFormat dmy
Insert Into tblMuonTra(MaDG,MaSach,NgayMuon,SL,NgayHenTra) 
Values(N'MDG06', N'MaSach06', '20/02/1995', -3, '30/04/2015')