[DBM] Assignment 05

Create Database De05

Go
Use De05

Go
Drop Table Person
Create Table Person
(
ID smallint Constraint PK_Contact Primary Key (ID),
HoVaTen varchar(60) NOT NULL,
NgaySinh date,
DiaChi varchar(120)
)

Go
Drop Table PhoneNumber
Create Table PhoneNumber
(
ID smallint Constraint FK_Contact Foreign Key (ID) References Person(ID),
DienThoai numeric
)

Go
Insert Into Person Values ('101', 'Vu Hoang Nam', '1996-01-08', 'Hoang Dao Thuy, Ha Noi')
Insert Into Person Values ('102', 'Chu Bao Ngoc', '1996-07-11', 'Minh Khai, Ha Noi')
Insert Into Person Values ('103', 'Nguyen Thanh Tung', '1996-07-09', 'Thuy Khue, Ha Noi')
Insert Into Person Values ('104', 'Hoang Dung', '1996-04-20', 'Nguyen Thi Thap, Ha Noi')
Insert Into Person Values ('105', 'Bui Huu Viet Hung', '1996-05-07', NULL)
Insert Into Person Values ('106', 'Vu Hoang Nam', '1996-01-08', 'Hoang Dao Thuy, Ha Noi')

Go
Insert Into PhoneNumber Values ('101', 0917463454)
Insert Into PhoneNumber Values ('102', 01653486187)
Insert Into PhoneNumber Values ('103', 0946510796)
Insert Into PhoneNumber Values ('104', 0984075186)
Insert Into PhoneNumber Values ('105', 01658904232)
Insert Into PhoneNumber Values ('106', 0942998858)

Go
--Liệt kê danh sách những người trong danh bạ
Select * From Person

Go
--Liệt kê danh sách số điện thoại có trong danh bạ
Select * From PhoneNumber

Go
--Liệt kê các số điện thoại của người có tên là Nguyễn Văn An.
Select HoVaTen, DienThoai From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where HoVaTen like 'Chu Bao Ngoc'

Go
--Liệt kê những người có ngày sinh là 12/12/09
Select * From Person
Where NgaySinh = '1996-04-20'

Go
--Liệt kê danh sách người trong danh bạ theo thứ thự alphabet.
Select * From Person
Order By HoVaTen ASC

Go
--Tìm số lượng số điện thoại của mỗi người trong danh bạ.
Select HoVaTen, Count(*) As SoLuongSoDT From PhoneNumber As B
Inner Join Person As A On A.ID = B.ID
Group By HoVaTen

Go
--Tìm tổng số người trong danh bạ sinh vào thang 12.
Select Count(HoVaTen) From Person
Where DatePart(mm, NgaySinh) = 7

Go
--Viết câu lệnh để thay đổi trường ngày sinh là trước ngày hiện tại.
Alter Table Person
Add Constraint dateofbirth Check (NgaySinh < GetDate())

Go
--Viết câu lệnh để xác định các trường khóa chính và khóa ngoại của các bảng.
Alter Table PhoneNumber Drop Constraint FK_Contact
Alter Table Person Drop Constraint PK_Contact

Alter Table Person Add Constraint PK_Contact Primary Key (ID)
Alter Table Person Add Constraint FK_Contact Foreign Key (ID) References Person(ID)

Go
--Viết câu lệnh để thêm trường ngày bắt đầu liên lạc.
Alter Table PhoneNumber
Add NgayBatDauLienLac date

Go
--IX_HoTen: Đặt chỉ mục cho cột Họ và tên
Create NonClustered Index IX_FullName
On Person(HoVaTen)

Go
--IX_SoDienThoai: Đặt chỉ mục cho cột Số điện thoại
Create NonClustered Index IX_PhoneNumber
On PhoneNumber(DienThoai)

Go
--View_SoDienThoai: hiển thị các thông tin gồm Họ tên, Số điện thoại
Create View View_SoDienThoai
As
Select HoVaTen, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID

Go
Select * From View_SoDienThoai

Go
--View_SinhNhat: Hiển thị những người có sinh nhật trong tháng hiện tại (Họ tên, Ngày sinh, Số điện thoại)
Create View View_SinhNhat
As
Select HoVaTen, NgaySinh, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where DatePart(mm, NgaySinh) = GetDate()

Go
Select * From View_SinhNhat

Go
--SP_Them_DanhBa: Thêm một người mới vào danh bạ
Create Procedure SP_Them_DanhBa
@ID smallint,
@Name char(60),
@BirthDate date,
@Address char(120)
As
Begin
Insert Into Person (ID, HoVaTen, NgaySinh, DiaChi)
Values (@ID, @Name, @BirthDate, @Address)
End

Go
Execute SP_Them_DanhBa '107', 'Do Huy Phong', '1995-01-25', 'Lang Ha, Ha Noi'
Execute SP_Them_DanhBa '108', 'Trinh Bach', '1995-01-30', NULL

Go
Insert Into PhoneNumber (ID, DienThoai) Values ('107', '0982336668')
Insert Into PhoneNumber (ID, DienThoai) Values ('108', '01697151420')

Go
--SP_Tim_DanhBa: Tìm thông tin liên hệ của một người theo tên (gần đúng)
Create Procedure SP_Tim_DanhBa
@FullName char(60)
As
Select HoVaTen, NgaySinh, DiaChi, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where HoVaTen = @FullName

Go
Execute SP_Tim_DanhBa 'Trinh Bach'

Go
--Hiển thị toàn bộ thông tin về người, của từng số điện thoại.
Select HoVaTen, NgaySinh, DiaChi, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID

Go
--Hiển thị toàn bộ thông tin về người, của số điện thoại 123456789.
Declare @Number bigint
Set @Number = '0984075186'
Select HoVaTen, NgaySinh, DiaChi, DienThoai
From Person As A
Inner Join PhoneNumber As B On A.ID = B.ID
Where DienThoai = @Number

[DBM] Assignment 03

Create Database De03

Go
Use De03

Go
Create Table KhachHang
(
MaKH smallint Constraint PK_Customer Primary Key (MaKH),
TenKH varchar(60) NOT NULL,
SoCMND int Constraint CK_IdentityCard Check (SoCMND > 0),
DiaChi varchar(120)
)

Create Table ThueBao
(
MaTB char(5) Constraint PK_Subscription Primary Key (MaTB),
SoThueBao bigint Constraint CK_Number Check (SoThueBao > 0) NOT NULL,
LoaiThueBao varchar(30) NOT NULL
)

Create Table DangKy
(
MaKH smallint Constraint FK_Customer Foreign Key (MaKH) References KhachHang(MaKH),
MaTB char(5) Constraint FK_Subscription Foreign Key (MaTB) References ThueBao(MaTB),
NgayDangKy date
)

Go
Insert Into ThueBao Values ('N1001', '0917463454', 'Tra truoc')
Insert Into ThueBao Values ('N1002', '0942998858', 'Tra truoc')
Insert Into ThueBao Values ('N1003', '0913812802', 'Tra truoc')
Insert Into ThueBao Values ('N1004', '01247610319', 'Tra truoc')
Insert Into ThueBao Values ('N1005', '0936255596', 'Tra truoc')
Insert Into ThueBao Values ('N1006', '01259586979', 'Tra truoc')

Go
Insert Into DangKy Values ('101', 'N1001', '2015-02-14')
Insert Into DangKy Values ('102', 'N1003', '2014-12-31')
Insert Into DangKy Values ('103', 'N1005', '2013-12-31')
Insert Into DangKy Values ('104', 'N1006', '2011-09-05')

Go
Insert Into KhachHang Values ('101', 'Vu Hoang Nam', '013273654', 'Hoang Dao Thuy, Ha Noi')
Insert Into KhachHang Values ('102', 'Ngo Minh Duc', NULL, 'Phuong Mai, Ha Noi')
Insert Into KhachHang Values ('103', 'Nguyen Van Hoc', NULL, 'Le Van Luong, Ha Noi')
Insert Into KhachHang Values ('104', 'Nguyen Viet Anh', NULL, 'Cu Loc, Ha Noi')

Go
--Hiển thị toàn bộ thông tin của các khách hàng của công ty.
Select * From KhachHang

Go
--Hiển thị toàn bộ thông tin của các số thuê bao của công ty.
Select * From ThueBao

Go
--Hiển thị toàn bộ thông tin của thuê bao có số: 0123456789
Declare @Number bigint
Set @Number = 0942998858
Select * From ThueBao
Where SoThueBao = @Number

Go
--Hiển thị thông tin về khách hàng có số CMTND: 123456789
Declare @Card int
Set @Card = 013273654
Select * From KhachHang
Where SoCMND = @Card

Go
--Hiển thị các số thuê bao của khách hàng có số CMTND:123456789
Select TenKH, SoCMND, SoThueBao, LoaiThueBao From DangKy As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join ThueBao As B On B.MaTB = C.MaTB
Where SoCMND = 013273654

Go
--Liệt kê các thuê bao đăng ký vào ngày 12/12/09
Select SoThueBao, LoaiThueBao, NgayDangKy From DangKy As C
Inner Join ThueBao As B On B.MaTB = C.MaTB
Where NgayDangKy = '2014-12-31'

Go
--Liệt kê các thuê bao có địa chỉ tại Hà Nội
Select TenKH, DiaChi, SoThueBao, LoaiThueBao From DangKy As C
Inner Join ThueBao As B On B.MaTB = C.MaTB
Inner Join KhachHang As A On A.MaKH = C.MaKH
Where DiaChi like '%Ha Noi%'

Go
--Tổng số khách hàng của công ty
Select Count(*)
From KhachHang

Go
--Tổng số thuê bao của công ty
Select Count(*)
From ThueBao

Go
--Tổng số thuê bao đăng ký ngày 12/12/09
Select Count(SoThueBao) From DangKy As C
Inner Join ThueBao As B On B.MaTB = C.MaTB
Where NgayDangKy = '2013-12-31'

Go
--Viết câu lệnh để thay đổi trường ngày đăng ký là NOT NULL
Alter Table DangKy
Alter Column NgayDangKy date NOT NULL

Go
--Viết câu lệnh để thay đổi trường ngày đăng ký là trước hoặc bằng ngày hiện tại
Alter Table DangKy
Add Constraint CK_RegistrationDate Check (NgayDangKy < GetDate() or NgayDangKy = GetDate())

Go
--Viết câu lệnh để thêm trường số điểm thưởng cho mỗi số thuê bao
Alter Table ThueBao
Add SoDiemThuong tinyint Check (SoDiemThuong >= 0)

Go
--Đặt chỉ mục (Index) cho cột Tên khách hàng của bảng chứa thông tin khách hàng
Alter Table DangKy
Drop Constraint FK_Customer

Alter Table KhachHang
Drop Constraint PK_Customer

Create Unique Clustered Index IX_CustomerName
On KhachHang(TenKH)

Alter Table KhachHang
Add Constraint PK_Customer Primary Key (MaKH)

Alter Table DangKy
Add Constraint FK_Customer Foreign Key (MaKH) References KhachHang(MaKH)

Go
Alter Table ThongTinKhachHang
Add Constraint PK_CustomerID Primary Key (MaKhachHang)

Go
--View_KhachHang: Hiển thị các thông tin Mã khách hàng, Tên khách hàng, Địa chỉ
Create View View_KhachHang
As
Select MaKH, TenKH, DiaChi
From KhachHang

Go
Select * From View_KhachHang

Go
--View_KhachHang_ThueBao: Hiển thị thông tin Mã khách hàng, Tên khách hàng, Số thuê bao
Create View View_KhachHang_ThueBao
As
Select MaKH, TenKH, SoThueBao
From DangKy As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join ThueBao As B On B.MaTB = C.MaTB

Go
Select * From View_KhachHang_ThueBao

Go
--SP_TimKH_ThueBao: Hiển thị thông tin của khách hàng với số thuê bao nhập vào
Create Procedure SP_TimKH_ThueBao
@Number bigint
As
Select TenKH, SoCMND, DiaChi
From DangKy As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join ThueBao As B On B.MaTB = C.MaTB
Where SoThueBao = @Number

Go
Execute SP_TimKH_ThueBao '01259586979'

Go
--SP_TimTB_KhachHang: Liệt kê các số điện thoại của khách hàng theo tên truyền vào
Create Procedure SP_TimTB_KhachHang
@Name char(60)
As
Select TenKH, SoThueBao As SoDienThoai
From DangKy As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join ThueBao As B On B.MaTB = C.MaTB
Where TenKH = @Name

Go
Execute SP_TimTB_KhachHang 'Nguyen Van Hoc'

Go
--SP_ThemTB: Thêm mới một thuê bao cho khách hàng
Create Procedure SP_ThemTB
@SubscriptionID char(5),
@SubscriptionNumber bigint,
@SubscriptionType varchar(30)
As
Begin
Insert Into ThueBao (MaTB, SoThueBao, LoaiThueBao)
Values (@SubscriptionID, @SubscriptionNumber, @SubscriptionType)
End

Go
Execute SP_ThemTB 'N1007', '0984262810', 'Tra truoc'

Go
--SP_HuyTB_MaKH: Xóa bỏ thuê bao của khách hàng theo Mã khách hàng
Create Procedure SP_HuyTB_MaKH
@CustomerID int
As
Begin
Delete From DangKy
Where MaKH = @CustomerID
End

Go
Execute SP_HuyTB_MaKH 104

Go
--Viết câu lệnh để thay đổi số điện thoại phải bắt đầu 09
Update ThueBao Set SoThueBao = '0967404230' Where MaTB = 'N1007'

Go
--Hiển thị toàn bộ thông tin về khách hàng và thuê bao của tất cả các số thuê bao
Select TenKH, SoCMND, DiaChi, SoThueBao, LoaiThueBao
From DangKy As C
Inner Join KhachHang As A On A.MaKH = C.MaKH
Inner Join ThueBao As B On B.MaTB = C.MaTB

[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