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