USE [QLBanHang]
GO
--TAO BANG AO
--CREATE VIEW TenBangAo
--AS
--Select...
--GO

-- Vi du: tao bang ao liet ke thong tin cua vat tu
-- chua co trong chi tiet don dat hang.
CREATE VIEW ViewThongTinVatTuChuaDuocDatHang
AS
   Select * From VatTu
   Where Not Exists (Select * From CTDonDH Where VatTu.mavtu = CTDonDH.mavtu)
GO
Select * From VatTu
   Where Not Exists (Select * From CTDonDH Where VatTu.mavtu = CTDonDH.mavtu)

Select * From VatTu left join CTDonDH on VatTu.mavtu = CTDonDH.mavtu
Where CTDonDH.sldat is null

--Tao bang ao bao gom cac thong tin: SoPhieuNhap, NgayNhap, TongThanhTien
--Nhung chi in ra cac phieu nhap co tong thanh tien > 60 trieu.
Create VIEW ViewPhieuNhapTongTienLonHon60Trieu
AS
Select
   PNhap.sopn,
   PNhap.ngaynhap,
   Sum(CTPNhap.dgnhap * CTPNhap.slnhap) AS TongThanhTien
From
   PNhap,
   CTPNhap
Where
   PNhap.sopn = CTPNhap.sopn
Group by
   PNhap.sopn,
   PNhap.ngaynhap
Having
   Sum(CTPNhap.dgnhap * CTPNhap.slnhap) > 60000000
GO

--Case: can cu vao gia tri cua cot da co --> suy ra nhung gia tri moi.
--TenCotMoi = Case
--When DieuKien1 Then GiaTri1
--When DieuKien2 Then GiaTri2
--When DieuKien3 Then GiaTri3
--...
--Else GiaTriKhac.
--End

---------------------------

--Vi du:
--Tao bang ao the hien thong tin:
--NamThang, MaVatTu, GhiChu
--Voi cot ghi chu duoc tinh nhu sau:
--Neu mat hang nao co tong so luong xuat
--Lon hon 9: hang ban chay
--Lon hon 6: trung binh
--Con lai: ban cham.

Select
   TonKho.namthang,
   TonKho.mavtu,
   GhiChu = Case
      When TonKho.tongslnhap > 9 Then 'Ban Chay'
      When TonKho.tongslnhap between 6 and 9 Then 'Trung Binh'
      Else 'Ban Cham'
   End
From
   TonKho
   
-- Lap trinh CSDL
-- Khai bao bien
-- Gan gia tri cho bien: Dung tu khoa Set hoac Select @TenBien = GiaTri.
-- Neu gia tri duoc lay tu bang nao do thi chi co the dung tu khoa Select.
-- Select @TenBien = TenCot From Table.

--Vi du:
Declare @x int
Set @x = 10
Print N'Gia Tri cua x La: ' + Convert(nvarchar(10), @x)
Print N'Gia Tri cua x La: ' + Cast(@x As nvarchar(10))
Declare @MaVatTu nvarchar(20)
Select Top 1
   @MaVatTu = VatTu.mavtu
From
   VatTu
Print N'Gia Tri cua MaVatTu La: ' + @MaVatTu
GO


--Cac cau truc lap trinh.
--If DieuKien1
--Begin
   --Cac cau lenh duoc thuc hien khi thoa DieuKien1
--End
--Else
--Begin
   --Cac cau lenh duoc thuc hien khi thoa DieuKien2
--End

--Vi du:
--Viet ma lenh in thong tin vat tu co ma vat tu tuy y.
--Neu khong tim thay ma vat tu: thong bao la khong tim thay.

Declare @MaVatTu nvarchar(20)
Set @MaVatTu = 'DD01'
If Exists(Select * From VatTu Where VatTu.mavtu = @MaVatTu)
Begin
   Select *
   From VatTu
   Where VatTu.mavtu = @MaVatTu
End
Else
Begin
   Print N'Khong tim thay vat tu co ma ' + @MaVatTu
End


--While
--While DieuKien
--Begin
   --Cau lenh
   --Co the dung Break
--End

--Vi du: viet chuong trinh tinh tong S = 1 + 2 + 3 + ... + n

Declare @n int
Set @n = 10
Declare @Sum int
Set @Sum = 0
Declare @i int
Set @i = 1
While @i <= @n
Begin
   Set @Sum = @Sum + @i
   Set @i = @i + 1
End
Print N'Tong la: ' + Convert(Nvarchar(10), @Sum)

--Procedure: 5 dang
--Proc dang 1:
   --Dac diem:
   --   Co tham so dau vao.
   --   Co xu ly, tinh toan.
   --   In ra ket qua bang Print
   --Cu phap:
   --   Create Procedure TenProcedure
   --   @ThamSoDauVao KDL
   --   As
   --      Khai bao bien.
   --      Xy ly tinh toan.
   --      Print.
   --   Go
   --Vi du: Tao Proc in tong so luong xuat cua ma vat tu tuy y trong CTPXuat
Use [QLBanHang]
Go
Alter Proc TongSoLuongXuat
@MaVatTu nchar(4)
As
   Declare @Sum int
   Select @Sum = SUM(CTPXuat.slxuat) From CTPXuat Where CTPXuat.mavtu = @MaVatTu
   Print N'Tong so luong la: ' + Convert(nvarchar(10), @Sum)
Go
Exec TongSoLuongXuat 'DD01'
Go
   
   
--Dang 2: xuat ra 1 bang
--Viet doan ma cho phep hien the thong tin gom:
--MaVatTu, TenVatTu, TongSoLuongXuat cua VatTu co ma tuy y
Alter Proc spuThongTinVatTuTheoMa
@MaVatTu nvarchar(20)
As
   Select VatTu.mavtu, VatTu.tenvtu, SUM(CTPXuat.slxuat) As TongSoLuong
   From VatTu, CTPXuat
   Where VatTu.mavtu = CTPXuat.mavtu And CTPXuat.mavtu = @MaVatTu
   Group By VatTu.mavtu, VatTu.tenvtu
Go
Exe