Bài tập tổng hợp SQL kèm đáp án

khachhang.dienthoai = nhacungcap.dienthoai,
 khachhang.fax = nhacungcap.fax,
 khachhang.email = nhacungcap.email
 FROM nhacungcap
 WHERE khachhang.tencongty = nhacungcap.tencongty AND
 khachhang.tengiaodich = nhacungcap.tengiaodich
2.37 UPDATE nhanvien
 SET luongcoban=luongcoban*1.5
 WHERE manhanvien =
 (SELECT manhanvien
 FROM dondathang INNER JOIN chitietdathang
 ON dondathang.sohoadon=chitietdathang.sohoadon
 WHERE manhanvien=nhanvien.manhanvien
 GROUP BY manhanvien
 HAVING SUM(soluong)>100)
2.38 UPDATE nhanvien
 SET phucap=luongcoban/2
 WHERE manhanvien IN
 (SELECT manhanvien
 FROM dondathang INNER JOIN chitietdathang
 ON dondathang.sohoadon=chitietdathang.sohoadon
 GROUP BY manhanvien
 HAVING SUM(soluong)>=ALL
 (SELECT SUM(soluong)
 FROM dondathang INNER JOIN chitietdathang
 ON dondathang.sohoadon=chitietdathang.sohoadon
 GROUP BY manhanvien))
2.39 UPDATE nhanvien
 SET luongcoban=luongcoban*0.85
 WHERE NOT EXISTS (SELECT manhanvien
 FROM dondathang
 WHERE manhanvien=nhanvien.manhanvien)
Tổng hợp SQL - SGL – Plassma :
Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom
2.40 UPDATE dondathang
 SET sotien =
 (SELECT SUM(soluong*giaban+soluong*giaban*mucgiamgia)
 FROM chitietdathang
 WHERE sohoadon=dondathang.sohoadon
 GROUP BY sohoadon)
2.41 DELETE FROM nhanvien
 WHERE DATEDIFF(YY,ngaylamviec,GETDATE())>40
2.42 DELETE FROM dondathang
 WHERE ngaydathang<'1/1/2000'
2.43 DELETE FROM loaihang
 WHERE NOT EXISTS (SELECT mahang
 FROM mathang
 WHERE maloaihang=loaihang.maloaihang)
2.44 DELETE FROM khachhang
 WHERE NOT EXISTS (SELECT sohoadon FROM dondathang
 WHERE makhachhang=khachhang.makhachhang)
2.45 DELETE FROM mathang
 WHERE soluong=0 AND
 NOT EXISTS (SELECT sohoadon
 FROM chitietdathang
 WHERE mahang=mathang.mahang)
 _______________________________________ 
Bài 4
Q1: Bổ sung ràng buộc thiết lập giá trị mặc định bằng 1 cho cột SOLUONG và bằng 0 
cho cột MUCGIAMGIA trong bảng CHITIETDATHANG 
Q2: Bổ sung cho bảng DONDATHANG ràng buộc kiểm tra ngày giao hàng và ngày 
chuyển hàng phải sau hoặc bằng với ngày đặt hàng. 
Q3 Bổ sung ràng buộc cho bảng NHANVIEN để đảm bảo rằng một nhân viên chỉ có 
thể làm việc trong công ty khi đủ 18 tuổi và không quá 60 tuổi. 
Q4 Với các bảng đã tạo được, câu lệnh: 
 DROP TABLE nhacungcap
 có thể thực hiện được không? Tại sao? 
Q5 Cho khung nhìn được định nghĩa như sau: 
 CREATE VIEW view_donhang
 AS
 SELECT dondathang.sohoadon,makhachhang,manhanvien,
 ngaydathang,ngaygiaohang,ngaychuyenhang,
 noigiaohang,mahang,
 giaban,soluong,mucgiamgia
 FROM dondathang INNER JOIN chitietdathang
 ON dondathang.sohoadon = chitietdathang.sohoadon
 a. Có thể thông qua khung nhìn này để bổ sung dữ liệu cho bảng 
 DONDATHANG được không? 
 b. Có thể thông qua khung nhìn này để bổ sung dữ liệu cho bảng 
Tổng hợp SQL - SGL – Plassma :
Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom
 CHITIETDATHANG được không? 
Q6 Với khung nhìn được định ngha như sau: 
 CREATE VIEW view_donhang
 AS
 SELECT dondathang.sohoadon,makhachhang,manhanvien,
 ngaydathang,ngaygiaohang,ngaychuyenhang,
 noigiaohang,mahang,
 giaban*soluong as thanhtien,
 mucgiamgia
 FROM dondathang INNER JOIN chitietdathang
 ON dondathang.sohoadon = chitietdathang.sohoadon
 a. Có thể thông qua khung nhìn này để xoá hay cập nhật dữ liệu trong bảng 
 DONDATHANG được không? 
 b. Có thể thông qua khung nhìn này để cập nhật dữ liệu trong bảng 
 CHITIETDATHANG được không? 
 Lời giải
Q1 ALTER TABLE chitietdathang
 ADD
 CONSTRAINT df_chitietdathang_soluong
 DEFAULT(1) FOR soluong,
 CONSTRAINT df_chitietdathang_mucgiamgia
 DEFAULT(0) FOR Mucgiamgia
Q2 ALTER TABLE dondathang
 ADD
 CONSTRAINT chk_dondathang_ngay
 CHECK (ngaygiaohang>=ngaydathang AND
 ngaychuyenhang>=ngaydathang)
Q3 ALTER TABLE nhanvien
 ADD
 CONSTRAINT chk_nhanvien_ngaylamviec
 CHECK (datediff(yy,ngaysinh,ngaylamviec)
 BETWEEN 18 AND 60)
Q4 Câu lệnh không thực hiện được do bảng cần xoá đang được tham chiếu bởi bảng 
MATHANG 
Q5 a. Không. b. Không
Q6 a.Có thể cập nhật nhưng không thể xoá b. Có thể được 
Bài 
Dụa trên cơ sở dữ liệu bài tập chương 2, thực hiện các yêu cầu sau: 
5.1 Tạo thủ tục lưu trữ để thông qua thủ tục này có thể bổ sung thêm một bản ghi mới 
cho bảng MATHANG (thủ tục phải thực hiện kiểm tra tính hợp lệ cửa dữ liệu cần bổ 
sung: không trùng khoá chính và đảm bảo toàn vẹn tham chiếu) 
5.2 Tạo thủ tục lưu trữ có chức năng thống kê tổng số lượng hàng bán được của một 
mặt hàng có mã bất kỳ (mã mặt hàng cần thống kê là tham số
Tổng hợp SQL - SGL – Plassma :
Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom
 Của thủ tục). 
5.3 Viết hàm trả về một bảng trong đó cho biết tổng số lượng hàng bán của mỗi mặt 
hàng. Sử dụng hàm này thống kê xem tổng số lượng hàng (hiện có và đã bán) của mỗi 
mặt hàng là bao nhiêu. 
5.4 Viết trigger cho bảng CHITIETDATHANG theo yêu cầu sau: 
 · Khi một bản ghi mới được bổ sung vào bảng này thì giảm số lượng hàng hiện có 
nếu số lượng hàng hiện có lớn hơn hoặc bằng số lượng hàng được bán ra. Ngược lại thì 
huỷ bỏ thao tác bổ sung. 
 · Khi cập nhật lại số lượng hàng đươc bán, kiểm tra số lượng hàng được cập nhật 
lại có phù hợp hay không (số lượng hàng bán ra không
Được vượt quá số lượng hàng hiện có và không được nhỏ hơn 1). Nếu dữ liệu hợp lệ thì 
giảm (hoặc tăng) số lượng hàng hiện có trong công ty, ngượ lại thì huỷ bỏ thao tác cập 
nhật. 
5.5 Viết trigger cho bảng CHITIETDATHANG sao cho chỉ chấp nhận giá hàng bán ra 
phải nhỏ hơn hoặc bằng giá gốc (giá của mặt hàng trong bảng MATHANG) 
 5.6 quản lý các bản tin trong một Website, người ta sử dụng hai bảng sau: 
 Bảng LOAIBANTIN (loại bản tin) 
 CREATE TABLE loaibantin
 (
 maphanloai INT NOT NULL
 PRIMARY KEY,
 tenphanloai NVARCHAR(100) NOT NULL ,
 bantinmoinhat INT DEFAULT(0)
 )
 Bng BANTIN (bn tin) 
 CREATE TABLE bantin
 (
 maso INT NOT NULL
 PRIMARY KEY,
 ngayduatin DATETIME NULL ,
 tieude NVARCHAR(200) NULL ,
 noidung NTEXT NULL ,
 maphanloai INT NULL
 FOREIGN KEY
 REFERENCES loaibantin(maphanloai)
 )
 Trong bng LOAIBANTIN, giá trị cột BANTINMOINHAT cho biết mã số của bản 
tin thuộc loại tương ứng mới nhất (dược bổ sung sau cùng). 
 Hãy viết các trigger cho bảng BANTIN sao cho: 
 · Khi một bản tin mới được bổ sung, cập nhật lại cột BANTINMOINHAT Của 
dòng tương ứng với loại bản tin vừa bổ sung. 
 · Khi một bản tin bị xoá, cập nhật lại giá trị của cột BANTINMOINHAT trong 
bảng LOAIBANTIN của dòng ứng với loại bản tin vừa xóa là mã số của bản tin trước 
đó (dựa vào ngày đưa tin). Nếu không còn bản tin nào cùng loại thì giá trị của cột này 
bằng 0. 
Tổng hợp SQL - SGL – Plassma :
Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom
 · Khi cập nhật lại mã số của một bản tin và nếu nó là bản tin mới nhất thì cập 
nhật lại giá trị cột BANTINMOINHAT là mã số mới. 
Lời giải
5.1 CREATE PROCEDURE sp_insert_mathang(
 @mahang NVARCHAR(10),
 @tenhang NVARCHAR(50),
 @macongty NVARCHAR(10) = NULL,
 @maloaihang INT = NULL,
 @soluong INT = 0,
 @donvitinh NVARCHAR(20) = NULL,
 @giahang money = 0)
 AS
 IF NOT EXISTS(SELECT mahang FROM mathang
 WHERE mahang=@mahang)
 IF (@macongty IS NULL OR EXISTS(SELECT macongty
 FROM nhacungcap
 WHERE macongty=@macongty))
 AND
 (@maloaihang IS NULL OR
 EXISTS(SELECT maloaihang FROM loaihang
 WHERE maloaihang=@maloaihang))
 INSERT INTO mathang
 VALUES(@mahang,@tenhang,
 @macongty,@maloaihang,
 @soluong,@donvitinh,@giahang)
5.2 CREATE PROCEDURE sp_thongkebanhang(@mahang NVARCHAR(10))
 AS
 SELECT mathang.mahang,tenhang,
 SUM(chitietdathang.soluong) AS tongsoluong
 FROM mathang LEFT OUTER JOIN chitietdathang
 ON mathang.mahang=chitietdathang.mahang
 WHERE mathang.mahang=@mahang
 GROUP BY mathang.mahang,tenhang
5.3 nh ngha hàm: 
 CREATE FUNCTION func_banhang()
 RETURNS TABLE
 AS
 RETURN (SELECT mathang.mahang,tenhang,
 CASE
 WHEN sum(chitietdathang.soluong) IS NULL THEN 0
 ELSE sum(chitietdathang.soluong)
 END AS tongsl
Tổng hợp SQL - SGL – Plassma :
Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom
 FROM mathang LEFT OUTER JOIN chitietdathang
 ON mathang.mahang = chitietdathang.mahang
 GROUP BY mathang.mahang,tenhang)
S dng hàm ã nh ngha: 
 SELECT a.mahang,a.tenhang,soluong+tongsl
 FROM mathang AS a INNER JOIN dbo.func_banhang() AS b
 ON a.mahang=b.mahang
5.4 CREATE TRIGGER trg_chitietdathang_insert
 ON chitietdathang
 FOR INSERT
 AS
 BEGIN
 DECLARE @mahang NVARCHAR(100)
 DECLARE @soluongban INT
 DECLARE @soluongcon INT
 SELECT @mahang=mahang,@soluongban=soluong
 FROM inserted
 SELECT @soluongcon=soluong FROM mathang
 WHERE mahang=@mahang
 IF @soluongcon>=@soluongban
 UPDATE mathang SET soluong=soluong-@soluongban
 WHERE mahang=@mahang
 ELSE
 ROLLBACK TRANSACTION
 END
 CREATE TRIGGER trg_chitietdathang_update_soluong
 ON chitietdathang
 FOR UPDATE
 AS
 IF UPDATE(soluong)
 BEGIN
 IF EXISTS(SELECT sohoadon FROM inserted WHERE soluong<0)
 ROLLBACK TRANSACTION
 ELSE
 BEGIN
 UPDATE mathang
 SET soluong=soluong-
 (SELECT SUM(inserted.soluong-deleted.soluong)
 FROM inserted INNER JOIN deleted
 ON inserted.sohoadon=deleted.sohoadon AND
 inserted.mahang=deleted.mahang
 WHERE inserted.mahang=mathang.mahang
 GROUP BY inserted.mahang)
 WHERE mahang IN (SELECT DISTINCT mahang
Tổng hợp SQL - SGL – Plassma :
Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom
 FROM inserted)
 IF EXISTS(SELECT mahang FROM mathang
 WHERE soluong<0)
 ROLLBACK TRANSACTION
 END
 END
5.5 CREATE TRIGGER trg_chitietdathang_giaban
 ON chitietdathang
 FOR INSERT,UPDATE
 AS
 IF UPDATE(giaban)
 IF EXISTS(SELECT inserted.mahang
 FROM mathang INNER JOIN inserted
 ON mathang.mahang=inserted.mahang
 WHERE mathang.giahang>inserted.giaban)
 ROLLBACK TRANSACTION
 _______________________________________ 
Tổng hợp SQL - SGL – Plassma :