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

ASM2

CREATE DATABASE ASM2

GO
USE ASM2

GO
CREATE TABLE Production
(
 Masohang varchar(10),
 TenSP varchar(50),
 Diachi varchar(50),
 Phone char(10),
 CONSTRAINT kt_Masohang PRIMARY KEY (Masohang)
)
GO
CREATE TABLE List
(
 STT int,
 Masohang varchar(10),
 MaSP varchar(10),
 Tenhang nvarchar(100),
 Mota nvarchar(50),
 Donvi nvarchar(10),
 Gia money,
 Soluongdangco int,
 CONSTRAINT kt_Masohangsanpham PRIMARY KEY (MaSP),
 CONSTRAINT kp_Masohang FOREIGN KEY (Masohang) REFERENCES Production(Masohang)
)
GO
SELECT *FROM Production
/*3. Viết các câu lệnh để thêm dữ liệu vào các bảng
Cho vào hai dữ liệu tưng tự như bảng đề bài trên*/
INSERT INTO Production VALUES ('123','Asus','USA','983232')
INSERT INTO Production VALUES ('124','DELL','Germany','983210')
INSERT INTO Production VALUES ('125','HP','USA','0983211')
GO
INSERT INTO List VALUES (1,'123','T001',N'Máy tính T450',N'Máy nhập cũ',N'Chiếc',1000,10)
INSERT INTO List VALUES (2,'123','T002',N'Điện thoại Nokia 5670',N'Điện thoại đang hot',N'Chiếc',200,200)
INSERT INTO List VALUES (3,'123','T003',N'Máy in samsung 450',N'Máy in đang loại bình',N'Chiếc',100,10)
INSERT INTO List VALUES (3,'124','T004',N'Màn hình DELL',N'Loại đời mới',N'Chiếc',2000,10)
INSERT INTO List VALUES (3,'124','T005',N'Loa JBL',N'Khuếch đại âm thanh',N'Chiếc',200,100)
INSERT INTO List VALUES (6,'125','T006',N'Probook HP',N'Máy tính VIP',N'Chiếc',1200,0)
GO
/*4. Viết các câu lênh truy vấn để
a) Hiển thị tất cả các hãng sản xuất.*/
SELECT TenSP FROM Production
--b) Hiển thị tất cả các sản phẩm.
SELECT Tenhang FROM List
/*5. Viết các câu lệnh truy vấn để
a) Liệt kê danh sách hãng theo thứ thự ngược với alphabet của tên.*/
SELECT TenSP FROM Production ORDER BY (TenSP)
--b) 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 STT,Tenhang FROM List ORDER BY (STT) DESC
--c) Hiển thị thông tin của hãng Asus.
SELECT *FROM Production WHERE TenSP LIKE 'Asus'
--d) Liệt kê danh sách sản phẩm còn ít hơn 11 chiếc trong kho
SELECT Tenhang FROM List WHERE Soluongdangco < 11
--e) Liệt kê danh sách sản phẩm của hãng Asus
SELECT b.Tenhang FROM Production a INNER JOIN List b ON a.Masohang=b.Masohang
WHERE a.TenSP LIKE 'Asus'
/*6. Viết các câu lệnh truy vấn để lấy
a) Số hãng sản phẩm mà cửa hàng có.*/
SELECT COUNT(TenSP) AS SoHangCuaHangCo FROM Production
--b) Số mặt hàng mà cửa hàng bán.
SELECT COUNT(Tenhang) AS SoHangCuaHangBan FROM List
--c) Tổng số loại sản phẩm của mỗi hãng có trong cửa hàng.
SELECT a.TenSP,COUNT(b.Tenhang) AS TongSoSPMoiHang FROM Production a INNER JOIN List b
ON a.Masohang=b.Masohang
GROUP BY a.TenSP
--d) Tổng số đầu sản phẩm của toàn cửa hàng
SELECT COUNT(Tenhang) AS TongSPToanCuaHang FROM List
/*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 giá tiền của từng mặt hàng là dương(>0).
c) Viết các câu lệnh để xác định các khóa ngoại và khóa chính của các bảng.*/
ALTER TABLE List
ADD CONSTRAINT rp_gia CHECK (Gia>0)
GO
--b) Viết câu lệnh để thay đổi số điện thoại phải bắt đầu bằng 0.
ALTER TABLE Production
ADD CONSTRAINT rp_Phone CHECK (Phone LIKE '0%')
GO
/*8. Thực hiện các yêu cầu sau
a) Thiết lập chỉ mục (Index) cho các cột sau: Tên hàng và Mô tả hàng để tăng hiệu suất truy vấn
dữ liệu từ 2 cột này*/
CREATE CLUSTERED INDEX IX_TenSP ON List(Tenhang)

GO
CREATE CLUSTERED INDEX IX_Mota ON List(Mota)

GO
/*b) Viết các View sau:
◦ View_SanPham: với các cột Mã sản phẩm, Tên sản phẩm, Giá bán*/
CREATE VIEW View_SanPham AS SELECT MaSP,Tenhang,Gia FROM List

GO
--◦ View_SanPham_Hang: với các cột Mã SP, Tên sản phẩm, Hãng sản xuất
CREATE VIEW View_SanPham_Hang AS SELECT a.MaSP,a.Tenhang,b.TenSP
FROM List a INNER JOIN Production b ON a.Masohang=b.Masohang
GO
/*c) Viết các Store Procedure sau:
◦ SP_SanPham_TenSP: Liệt kê các sản phẩm với tên hãng truyền vào store*/
CREATE PROCEDURE SP_SanPham_TenSP AS
SELECT a.Tenhang,b.TenSP FROM List a INNER JOIN Production b ON a.Masohang=b.Masohang
GO
EXECUTE SP_SanPham_TenSP

GO
--◦ SP_SanPham_Gia: Liệt kê các sản phẩm có giá bán lớn hơn hoặc bằng giá bán truyền vào
CREATE PROCEDURE SP_SanPham_Gia AS
SELECT Tenhang,Gia FROM List
WHERE Gia
GO
--◦ SP_SanPham_HetHang: Liệt kê các sản phẩm đã hết hàng (số lượng = 0)
CREATE PROCEDURE SP_SanPham_HetHang AS
SELECT Tenhang,Soluongdangco FROM List
WHERE Soluongdangco = 0
GO
EXECUTE SP_SanPham_HetHang

GO
/*d) Viết Trigger sau:
◦ TG_Xoa_Hang: Ngăn không cho xóa hãng*/
CREATE TRiGGER TG_Xoa_Hang
ON Production
FOR DELETE
AS
 IF update(Masohang)
 PRINT N'Khong Duoc Xoa';
 ROLLBACK TRANSACTION;
GO
--◦ TG_Xoa_SanPham: Chỉ cho phép xóa các sản phẩm đã hết hàng (số lượng = 0)
CREATE TRIGGER TG_Xoa_SanPham
ON List
FOR DELETE
AS
 BEGIN
  IF EXISTS(SELECT *FROM List WHERE Soluongdangco =0)
  BEGIN
   PRINT N'Không được xóa sản phẩm vẫn còn hàng';
   ROLLBACK TRANSACTION;
  END
 END
GO

ASM1

CREATE DATABASE asm1

GO
USE asm1

GO
CREATE TABLE ddh(
msdh varchar(10),
mskh varchar(10),
ndh nvarchar(50),
diachi nvarchar(100),
phonenum varchar(12),
ngaydat datetime,
CONSTRAINT kt_msdh PRIMARY KEY (msdh)
)
GO
CREATE TABLE olist
(
Masosp varchar(10),
TenHang nvarchar(50),
CONSTRAINT kt_mssp PRIMARY KEY (Masosp)
)
GO
CREATE TABLE list
(
STT varchar(3),
msdh varchar(10),
Masosp varchar(10),
tenhang nvarchar(50),
mota nvarchar(50),
donvi nvarchar(10),
dongia money,
soluong money,
ThanhTien money,
CONSTRAINT kp_msdh FOREIGN KEY (msdh) REFERENCES ddh(msdh),
CONSTRAINT kp_mssp FOREIGN KEY (Masosp) REFERENCES olist(Masosp)
)
INSERT INTO ddh VALUES('123','G001',N'Nguyễn Văn An',N'111 Nguyễn Trãi,Thanh Xuân,Hà Nội','987654321','2011-08-09')
INSERT INTO ddh VALUES('234','G002',N'Trần Văn Bê',N'342 Khương Đình, Thanh Xuân, Hà Nội','0169679843','2009-10-24')
INSERT INTO ddh VALUES('345','G003',N'Đinh Minh Cê',N'175 Đông Các, Đống Đa, Hà Nội','0164276345','2009-05-29')
GO
INSERT INTO olist VALUES ('MT001',N'Máy Tính T450')
INSERT INTO olist VALUES ('MT002',N'Điện Thoại Nokia5670')
INSERT INTO olist VALUES ('MT003',N'Máy In Samsung 450')
GO
INSERT INTO list VALUES ('1','123','MT001',N'Máy Tính T450',N'Máy mới nhập',N'Chiếc','1000','1','1000')
INSERT INTO list VALUES ('2','123','MT002',N'Điện Thoại Nokia5670',N'Điện thoại đang hot',N'Chiếc','200','2','400')
INSERT INTO list VALUES ('3','123','MT003',N'Máy In Samsung 450',N'Máy in đang ế',N'Chiếc','100','1','100')
GO
--4. Viết các câu lênh truy vấn để
--a> hiển thị danh sách khách hàng mua sản phẩm
SELECT msdh,ndh FROM ddh
--b> hiển thị danh sách sản phẩm
SELECT tenhang FROM olist
--c> hiển thị danh sách đơn hàng
SELECT * FROM ddh
--*5. Viết các câu lệnh truy vấn để
--a> hiển thị tên người mua theo alphabet
SELECT ndh FROM ddh
ORDER BY ndh
--b> hiển thị giá sản phẩm theo thứ tự giảm dần
SELECT tenhang,dongia FROM list
ORDER BY dongia DESC
--c> hiển thị sản phẩm mà khách Nguyễn Văn An đã mua
SELECT tenhang FROM ddh a INNER JOIN list b ON a.msdh=b.msdh
WHERE ndh LIKE N'Nguyễn Văn An'
GO
--*6. Viết các câu lệnh truy vấn để
--a> Số khách hàng đã mua ở cửa hàng
SELECT COUNT (a.ndh) AS tongkh
FROM ddh AS a
--b> Số mặt hàng mà cửa hàng bán
SELECT COUNT (a.tenhang) AS tonghangdaban
FROM olist AS a
--c> Tổng số tiền của từng đơn hàng
SELECT a.msdh,SUM(b.thanhtien) as TongTien
FROM ddh AS a INNER JOIN list AS b ON a.msdh=b.msdh
GROUP BY a.msdh
GO
/*7. Thay đổi những thông tin sau từ cơ sở dữ liệu
a) 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 list
ADD CONSTRAINT rp_gia CHECK (Gia>0)
GO
--b) 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 ddh
ADD CONSTRAINT rp_ngaydathang CHECK (ngaydat < getdate())
GO
--c) 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 list
ADD Ngayramat datetime
GO
SELECT *FROM olist
GO
/*8. Thực hiện các yêu cầu sau
a) Đặ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 CLUSTERED INDEX IX_TenHang ON list(tenhang)

GO
CREATE CLUSTERED INDEX IX_DonDatHang ON ddh(ndh)

GO
/*b) Xây dựng các view sau đây:
◦ View_KhachHang với các cột: Tên khách hàng, Địa chỉ, Điện thoại
◦ View_SanPham với các cột: Tên sản phẩm, Giá bán
◦ 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 AS SELECT ndh,diachi,phonenum
FROM ddh
GO
SELECT *FROM View_KhachHang

GO
CREATE VIEW View_SanPham AS SELECT tenhang,dongia
FROM list
GO
SELECT *FROM View_SanPham

GO
CREATE VIEW View_KhachHang_SanPham AS SELECT a.ndh,a.phonenum,b.tenhang,b.soluong,a.ngaydat
FROM ddh AS a INNER JOIN list AS b ON a.msdh=b.msdh
GO
SELECT *FROM View_KhachHang_SanPham

GO
/*c) Viết các Store Procedure (Thủ tục lưu trữ) sau:
◦ SP_TimKH_MaKH: Tìm khách hàng theo mã khách hàng
◦ SP_TimKH_MaHD: Tìm thông tin khách hàng theo mã hóa đơn
◦ 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_TimKH_MaKH AS
SELECT ndh,diachi,phonenum FROM ddh
WHERE msdh LIKE 'TB001'
GO
EXECUTE SP_TimKH_MaKH

GO
CREATE PROCEDURE SP_TimKH_MaHD AS
SELECT ndh,diachi,phonenum FROM ddh
WHERE msdh LIKE '001'
GO
EXECUTE SP_TimKH_MaHD

GO