ASM3

CREATE DATABASE ASM3

GO
USE ASM3

GO
CREATE TABLE CusInfo
(
Makh varchar(10),
Tenkh nvarchar(50),
CMND varchar(10),
diachi nvarchar(100),
CONSTRAINT kt_Makh PRIMARY KEY (Makh)
)
GO
CREATE TABLE Register
(
Makh varchar(10),
Phonenum varchar(10),
Loaithuebao nvarchar(50),
Ngaydk datetime,
CONSTRAINT kp_Makh FOREIGN KEY (Makh) REFERENCES CusInfo(Makh)
)
GO
/*3. Viết các câu lệnh để thêm dữ liệu vào các bảng
Chèn thêm dữ liệu tương tự như đề bài*/
INSERT INTO CusInfo VALUES ('T001',N'Nguyễn Nguyệt Nga','123456789',N'Hà Nội')
INSERT INTO CusInfo VALUES ('T002',N'Đinh Nhật Đức','123456710',N'Hải Phòng')
GO
INSERT INTO Register VALUES ('T001','093456789',N'Trả cước','12/12/2012')
INSERT INTO Register VALUES ('T001','097654555',N'Trả cước','12/12/2012')
INSERT INTO Register VALUES ('T001','097654321',N'Trả cước','06/03/2015')
INSERT INTO Register VALUES ('T002','097647854',N'Trả cước','06/03/2015')
GO
/*4. Viết các câu lênh truy vấn để
a) Hiển thị toàn bộ thông tin của các khách hàng của công ty.
b) Hiển thị toàn bộ thông tin của các số thuê bao của công ty.*/
SELECT *FROM CusInfo

GO
SELECT *FROM Register

GO
/*5. Viết các câu lệnh truy vấn để lấy
a) Hiển thị toàn bộ thông tin của thuê bao có số: 0123456789*/
SELECT *FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
WHERE Phonenum LIKE '123456789'
GO
--b) Hiển thị thông tin về khách hàng có số CMTND: 123456789
SELECT a.*FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
WHERE CMND LIKE '123456789'
GO
--c) Hiển thị các số thuê bao của khách hàng có số CMTND:123456789
SELECT b.Phonenum FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
WHERE CMND LIKE '123456789'
GO
--d) Liệt kê các thuê bao đăng ký vào ngày 12/12/09
SELECT b.Phonenum FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
WHERE Ngaydk = '12/12/2012'
GO
--e) Liệt kê các thuê bao có địa chỉ tại Hà Nội
SELECT b.Phonenum FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
WHERE diachi = N'Hà Nội'
GO
/*6. Viết các câu lệnh truy vấn để lấy
a) Tổng số khách hàng của công ty.*/
SELECT COUNT(a.Tenkh) AS TongSoKhacHangCuaCongTy FROM CusInfo a

GO
--b) Tổng số thuê bao của công ty.
SELECT COUNT(a.Phonenum) AS TongPhonenumCuaCongTy FROM Register a

GO
--c) Tổng số thuê bào đăng ký ngày 12/12/09.
SELECT COUNT(a.Phonenum) AS TongPhonenumCuaCongTy FROM Register a
WHERE Ngaydk = '12/12/2012'
GO
--d) 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 *FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh

GO
/*7. Thay đổi những thay đổi sau trên cơ sở dữ liệu
a) Viết câu lệnh để thay đổi trường ngày đăng ký là not null.*/
ALTER TABLE Register
ALTER COLUMN Ngaydk datetime NOT NULL
GO
--b) 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 Register
ADD CONSTRAINT rp_Ngaydk CHECK (Ngaydk >= getdate())
GO
--c) Viết câu lệnh để thay đổi số điện thoại phải bắt đầu 09
ALTER TABLE Register
ADD CONSTRAINT rp_Phonenum CHECK (Phonenum LIKE '09%')
GO
--d) Viết câu lệnh để thêm trường số điểm thưởng cho mỗi số thuê bao.
ALTER TABLE Register
ADD SoDiemThuong int
GO
/*8. Thực hiện các yêu cầu sau
a) Đặ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*/
CREATE CLUSTERED INDEX IX_TenKhachHang ON CusInfo(Tenkh)

GO
/*b) Viết các View sau:
◦ 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 CusInfo

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 a.Makh,a.Tenkh,b.Phonenum
FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
GO
SELECT *FROM View_KhachHang_ThueBao

GO
/*c) Viết các Store Procedure sau:
◦ 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 AS SELECT a.*
FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
WHERE Phonenum LIKE '097654555'
GO
EXECUTE SP_TimKH_ThueBao

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 AS SELECT b.Phonenum
FROM CusInfo a INNER JOIN Register b ON a.Makh=b.Makh
WHERE a.Tenkh LIKE N'Nguyễn Nguyệt Nga'
GO
EXECUTE SP_TimTB_KhachHang

GO
--◦ SP_ThemTB: Thêm mới một thuê bao cho khách hàng
CREATE PROCEDURE SP_ThemTB AS INSERT INTO Register VALUES ('T002','097647899',N'Trả cước','06/03/2015',99)

GO
EXECUTE SP_ThemTB

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 AS DELETE FROM Register WHERE Makh = 'T002'

GO
EXECUTE SP_HuyTB_MaKH

GO