[DBM] Assignment 01

Create Database De01

Go
Use De01

Create Table KhachHang
(
MaKH char(3) Constraint PK_Customer Primary Key (MaKH),
TenKH varchar(50),
DiaChi varchar(120),
SoDienThoai char(11)
)

Create Table SanPham
(
MaSP char(10) Constraint PK_Product Primary Key (MaSP),
TenSP varchar(120),
DonVi char(20),
Gia money
)

Create Table HoaDon
(
MaKH char(3) Constraint FK_Customer Foreign Key References KhachHang(MaKH),
MaSP char(10) Constraint FK_Product Foreign Key References SanPham(MaSP),
SoLuong char(2),
NgayDatHang date,
ThanhTien money
)

Go
Drop Table HoaDon
Drop Table SanPham
Drop Table KhachHang

Go
Insert Into KhachHang Values (101, 'Vu Hoang Nam', 'Hoang Dao Thuy, Ha Noi', 0942998858)
Insert Into KhachHang Values (102, 'Do Huy Phong', 'Lang Ha, Ha Noi', 0982336668)
Insert Into KhachHang Values (103, 'Tran Hoang Linh', 'Lang Ha, Ha Noi', 0949975730)

Go
Insert Into SanPham Values ('P1001', 'Sony XBR-84X900 (84-inch, Full HD, 3D, 4K Resolution, LCD TV)', 'Chiec', 800000000)
Insert Into SanPham Values ('P1002', 'Sony Bravia KD-65X9004A (65-Inch, LED TV)', 'Chiec', 85000000)
Insert Into SanPham Values ('P1003', 'Apple iMac Retina 5K (Intel Core i5-4690 3.5GHz, 8GB RAM, 1TB HDD, VGA AMD Radeon R9 M290X, 27 inch, Mac OSX 10.10)','Chiec', 56000000)
Insert Into SanPham Values ('P1004', 'Apple iPhone 6 64GB Space Gray (International)', 'Chiec', 16000000)
Insert Into SanPham Values ('P1005', 'Sony PlayStation 4 500GB', 'Chiec', 10000000)
Insert Into SanPham Values ('P1006', 'Apple iPhone 5 16GB Black', 'Chiec', 8500000)

Go
Insert Into HoaDon Values (101, 'P1005', 2, '2015-02-03', 10000000 * 2)
Insert Into HoaDon Values (101, 'P1004', 1, '2015-02-14', 16000000)
Insert Into HoaDon Values (102, 'P1006', 1, '2013-01-05', 8500000)
Insert Into HoaDon Values (103, NULL, 0, '2015-02-14', 0)

Go
--Liệt kê danh sách khách hàng đã mua hàng ở cửa hàng.
Select TenKH, SoLuong, NgayDatHang, ThanhTien From HoaDon
Inner Join KhachHang On HoaDon.MaKH = KhachHang.MaKH
Where SoLuong > 0

Go
--Liệt kê danh sách sản phẩm của của hàng.
Select TenSP From SanPham

Go
--Liệt kê danh sách các đơn đặt hàng của cửa hàng.
Select TenKH As NguoiDatHang, DiaChi, SoDienThoai As DienThoai, NgayDatHang
From HoaDon
Inner Join KhachHang
On HoaDon.MaKH = KhachHang.MaKH

Go
--Liệt kê danh sách khách hàng theo thứ thự alphabet.
Select * From KhachHang
Order By TenKH ASC

Go
--Liệt kê danh sách sản phẩm của cửa hàng theo thứ thự giá giảm dần.
Select * From SanPham
Order By Gia DESC

Go
--Liệt kê các sản phẩm mà khách hàng Nguyễn Văn An đã mua.
Declare @Name char(60)
Set @Name = 'Do Huy Phong'
Select TenKH, TenSP From HoaDon As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join SanPham As B On B.MaSP = C.MaSP
Where TenKH = @Name

Go
--Số mặt hàng mà cửa hàng bán.
Select Count(*) As SoMatHang
From SanPham

Go
--Tổng tiền của từng đơn hàng.
Select TenKH, Sum(ThanhTien) As TongTien
From KhachHang As A Inner Join HoaDon As C
On A.MaKH = C.MaKH
Group By TenKH

Go
--Viết câu lệnh để thay đổi trường giá tiền của từng mặt hàng là dương(>0).
Alter Table SanPham
Add Constraint CK_Price Check (Gia > 0)

Go
--Viết câu lệnh để thay đổi ngày đặt hàng của khách hàng phải nhỏ hơn ngày hiện tại.
Alter Table HoaDon
Add Constraint CK_PurchaseDate Check (NgayDatHang < GetDate())

Go
--Viết câu lệnh để thêm trường ngày xuất hiện trên thị trường của sản phẩm.
Alter Table SanPham
Add NgayXuatHien datetime

Go
--Đặt chỉ mục (index) cho cột Tên hàng và Người đặt hàng để tăng tốc độ truy vấn dữ liệu trên các cột này.
Create NonClustered Index IX_MatHang
On SanPham(TenSP)

Create NonClustered Index IX_KhachHang
On KhachHang(TenKH)

Exec sp_helpindex 'SanPham'
Exec sp_helpindex 'KhachHang'

Go
--View_KhachHang với các cột: Tên khách hàng, Địa chỉ, Điện thoại.
Create View View_KhachHang
As
Select TenKH As TenKhachHang, DiaChi, SoDienThoai
From KhachHang

Select * From View_KhachHang

Go
--View_SanPham với các cột: Tên sản phẩm, Giá bán.
Create View View_SanPham
As
Select TenSP As TenSanPham, Gia As GiaBan
From SanPham

Select * From View_SanPham

Go
--View_KhachHang_SanPham với các cột: Tên khách hàng, Số điện thoại, Tên sản phẩm, Số lượng, Ngày mua.
Create View View_KhachHang_SanPham
As
Select TenKH As TenKhachHang, SoDienThoai, TenSP As TenSanPham, SoLuong, NgayDatHang As NgayMua
From HoaDon As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join SanPham As B On B.MaSP = C.MaSP

Select * From View_KhachHang_SanPham

Go
--SP_TimKH_MaKH: Tìm khách hàng theo mã khách hàng
Create Procedure SP_TimKH_MaKH
@CustomerID int
As
Select MaKH, TenKH From KhachHang
Where MaKH = @CustomerID

Execute SP_TimKH_MaKH 101

Go
Alter Table HoaDon
Add MaHD int Constraint PK_Bill Primary Key (MaHD) Identity (201, 1)

Go
--SP_TimKH_MaHD: Tìm thông tin khách hàng theo mã hóa đơn
Create Procedure SP_TimKH_MaHD
@Bill int
As
Select TenKH, DiaChi, SoDienThoai
From KhachHang As A
Inner Join HoaDon As C On A.MaKH = C.MaKH
Where MaHD = @Bill

Execute SP_TimKH_MaHD 203

Go
--SP_SanPham_MaKH: Liệt kê các sản phẩm được mua bởi khách hàng có mã được truyền vào Store.
Create Procedure SP_SanPham_MaKH
@Customer char(50)
As
Select TenKH, TenSP
From HoaDon As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join SanPham As B On B.MaSP = C.MaSP
Where TenKH = @Customer

Execute SP_SanPham_MaKH 'Vu Hoang Nam'

Go
--Số khách hàng đã mua ở cửa hàng.
Select Count(TenKH) As TongKhachHang
From KhachHang