[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