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