, Trưởng nhóm at Nha Trang University
Published on
1. Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom Bài tập tổng hợp SQL -And Đáp án Sử dụng câu lệnh SELECT viết các yêu cầu truy vấn dữ liệu sau đây: 2. 1 Cho biết danh sách các đối tác cung cấp hàng cho công ty. 2. 2 Mã hàng, tên hàng và số lượng của các mặt hàng hiện có trong công ty. 2. 3 Họ tên và điạ chỉ và năm bắt đầu làm việc của các nhân viên trong công ty. 2. 4 địa chỉ và điện thoại của nhà cung cấp có tên giao dch VINAMILK là gì? 2. 5 Cho biết mã và tên của các mặt hàng có giá lớn hơn 100000 và số lượng có ít hn 50. 2. 6 Cho biết mỗi mặt hàng trong công ty do ai cung cấp. 2. 7 Công ty Vit Tin đã cung cp nhng mt hàng nào? 2. 8 Loại hàng thực phẩm do những công ty nào cung cấp và địa chỉ của các công ty đó là gì? 2. 9 Những khách hàng nào (tên giao dịch) đã đặt mua mặt hàng Sữa hộp XYZ của công ty? 2. 10 đơn đặt hàng số 1 do ai đặt và do nhân viên nào lập, thi gian và địa điểm giao hàng là ở đâu? 2. 11 Hãy cho biết số tiền lương mà công ty phải trả cho mỗi nhân viên là bao nhiêu (lương = lương cơ bn + phụ cấp). 2. 12 Trong đơn đặt hàng số 3 đặt mua nhưng mặt hàng nào và số tiền mà khách hàng phải trả cho mỗi mặt hàng là bao nhiêu (số tiền phải trả cho mõi mặt hang tính theo công thức SOLUONG×GIABAN SOLUONG×GIABAN×MUCGIAMGIA/100) 2. 13 Hãy cho bit có những khách hàng nào lại chính là đối tác cung cấp hàng của công ty (tức là có cùng tên giao dịch).
2. Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom 2. 19 Những nhân viên nào của công ty có lương cơ bản cao nhất? 2. 20 Tổng số tiền mà khách hàng phải trả cho mỗi đơn đặt hàng là bao nhiêu? 2. 21 Trong nm 2003, những mặt hàng nào chỉ được đặt mua đúng một lần.
5. Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom 2.17 SELECT mahang,tenhang FROM mathang WHERE NOT EXISTS (SELECT mahang FROM chitietdathang WHERE mahang=mathang.mahang) 2.18 SELECT manhanvien,ho,ten FROM nhanvien WHERE NOT EXISTS (SELECT manhanvien FROM dondathang WHERE manhanvien=nhanvien.manhanvien) 2.19 SELECT manhanvien,ho,ten,luongcoban FROM nhanvien WHERE luongcoban=(SELECT MAX(luongcoban) FROM nhanvien) 2.20 SELECT dondathang.sohoadon,dondathang.makhachhang, tencongty,tengiaodich, SUM(soluong*giaban-soluong*giaban*mucgiamgia/100) FROM (khachhang INNER JOIN dondathang ON khachhang.makhachhang=dondathang.makhachhang) INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY dondathang.makhachhang,tencongty, tengiaodich,dondathang.sohoadon 2.21 SELECT mathang.mahang,tenhang FROM (mathang INNER JOIN chitietdathang ON mathang.mahang=chitietdathang.mahang) iNNER JOIN dondathang ON chitietdathang.sohoadon=dondathang.sohoadon WHERE YEAR(ngaydathang)=2003 GROUP BY mathang.mahang,tenhang HAVING COUNT(chitietdathang.mahang)=1 2.22 SELECT khachhang.makhachhang,tencongty,tengiaodich, SUM(soluong*giaban-soluong*giaban*mucgiamgia/100) FROM (khachhang INNER JOIN dondathang ON khachhang.makhachhang = dondathang.makhachhang) INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY khachhang.makhachhang,tencongty,tengiaodich 2.23 SELECT nhanvien.manhanvien,ho,ten,COUNT(sohoadon) FROM nhanvien LEFT OUTER JOIN dondathang ON nhanvien.manhanvien=dondathang.manhanvien GROUP BY nhanvien.manhanvien,ho,ten 2.24 SELECT MONTH(ngaydathang) AS thang, SUM(soluong*giaban-soluong*giaban*mucgiamgia/100) FROM dondathang INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon WHERE year(ngaydathang)=2003 GROUP BY month(ngaydathang) Tổng hợp SQL – SGL – Plassma :
7. Software Group Leader SGL By Member: htplasma, Plassma :for Vn-zoom FROM (dondathang AS a INNER JOIN chitietdathang AS b ON a.sohoadon = b.sohoadon) INNER JOIN mathang AS c ON b.mahang = c.mahang ORDER BY a.sohoadon COMPUTE SUM(b.soluong*giaban- b.soluong*giaban*mucgiamgia/100) BY a.sohoadon 2.31 SELECT loaihang.maloaihang,tenloaihang, mahang,tenhang,soluong FROM loaihang INNER JOIN mathang ON loaihang.maloaihang=mathang.maloaihang ORDER BY loaihang.maloaihang COMPUTE SUM(soluong) BY loaihang.maloaihang COMPUTE SUM(soluong) 2.32 SELECT b.mahang,tenhang, SUM(CASE MONTH(ngaydathang) WHEN 1 THEN b.soluong ELSE 0 END) AS Thang1, SUM(CASE MONTH(ngaydathang) WHEN 2 THEN b.soluong ELSE 0 END) AS Thang2, SUM(CASE MONTH(ngaydathang) WHEN 3 THEN b.soluong ELSE 0 END) AS Thang3, SUM(CASE MONTH(ngaydathang) WHEN 4 THEN b.soluong ELSE 0 END) AS Thang4, SUM(CASE MONTH(ngaydathang) WHEN 5 THEN b.soluong ELSE 0 END) AS Thang5, SUM(CASE MONTH(ngaydathang) WHEN 6 THEN b.soluong ELSE 0 END) AS Thang6, SUM(CASE MONTH(ngaydathang) WHEN 7 THEN b.soluong ELSE 0 END) AS Thang7, SUM(CASE MONTH(ngaydathang) WHEN 8 THEN b.soluong ELSE 0 END) AS Thang8, SUM(CASE MONTH(ngaydathang) WHEN 9 THEN b.soluong ELSE 0 END) AS Thang9, SUM(CASE MONTH(ngaydathang) WHEN 10 THEN b.soluong ELSE 0 END) AS Thang10, SUM(CASE MONTH(ngaydathang) WHEN 11 THEN b.soluong ELSE 0 END) AS Thang11, SUM(CASE MONTH(ngaydathang) WHEN 12 THEN b.soluong ELSE 0 END) AS Thang12, SUM(b.soluong) AS CaNam FROM (dondathang AS a INNER JOIN chitietdathang AS b ON a.sohoadon=b.sohoadon) INNER JOIN mathang AS c ON b.mahang=c.mahang WHERE YEAR(ngaydathang)=1996 GROUP BY b.mahang,tenhang 2.33 UPDATE dondathang Tổng hợp SQL – SGL – Plassma :
11. 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 :
12. 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 :