[Database-Tự Học SQL] COM2034_SQL Server - Lab 2: Sử Dụng Biến

NỘI DUNG

A. Chương trình tính diện tích, chu vi hình chữ nhật khi biết chiều dài và chiều rộng.

B. Dựa trên csdl QLDA thực hiện truy vấn, các giá trị truyền vào và trả ra phải dưới dạng sử dụng biến.

1. Cho biêt nhân viên có lương cao nhất
2. Cho biết họ tên nhân viên (HONV, TENLOT, TENNV) có mức lương trên mức lương trung bình của phòng "Nghiên cứu”
3. Với các phòng ban có mức lương trung bình trên 30,000, liệt kê tên phòng ban và số lượng nhân viên của phòng ban đó.
4. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó chủ trì

THỰC HIỆN

A. Chương trình tính diện tích, chu vi hình chữ nhật khi biết chiều dài và chiều rộng.

/*
Chu vi (C) = (dai + rong ) * 2
Dien tich (S) = dai * rong
*/

DECLARE @dai INT, @rong INT, @chu_vi INT, @dien_tich INT
SET @dai = 30
SET @rong = 20
SET @chu_vi = (@dai + @rong	) * 2
SET @dien_tich = @dai * @rong

PRINT @chu_vi
PRINT @dien_tich

-- hoặc
PRINT 'Chu vi: ' + CONVERT(CHAR(10),@chu_vi)
PRINT 'Dien tich: ' + CONVERT(CHAR(10),@dien_tich)

-- hoặc
SELECT @chu_vi AS Chu_vi, @dien_tich AS Dien_tich

Biến dùng để lưu trữ các giá trị tạm thời trong quá trình thực thi chương trình

B. Dựa trên csdl QLDA thực hiện truy vấn, các giá trị truyền vào và trả ra phải dưới dạng sử dụng biến.

1. Cho biêt nhân viên có lương cao nhất

-- chuẩn bị
SELECT  MAX(luong) FROM NHANVIEN 

SELECT * 
FROM NHANVIEN 
WHERE 
	LUONG = (SELECT  MAX(luong) FROM NHANVIEN )

-- dùng biến
DECLARE @max_luong INT
SET @max_luong = (SELECT  MAX(luong) FROM NHANVIEN)
                    
SELECT * 
FROM NHANVIEN 
WHERE 
	LUONG = @max_luong

2. Cho biết họ tên nhân viên (HONV, TENLOT, TENNV) có mức lương trên mức lương trung bình của phòng "Nghiên cứu”

-- chuẩn bị
SELECT  AVG(luong) 
FROM NHANVIEN, PHONGBAN 
WHERE 
		PHONGBAN.MAPHG = NHANVIEN.PHG 
	AND PHONGBAN.TENPHG LIKE N'Nghiên cứu' /* (1) */

SELECT CONCAT(HONV,' ', TENLOT,' ', TENNV) AS Ho_va_Ten 
FROM NHANVIEN 
WHERE 
	LUONG > /* (1) */ (SELECT  AVG(luong) 
				FROM NHANVIEN, PHONGBAN 
				WHERE 
						PHONGBAN.MAPHG = NHANVIEN.PHG 
					AND PHONGBAN.TENPHG LIKE N'Nghiên cứu')

-- dùng biến
DECLARE @avg_luong INT
/* gán (1) vào biến @avg_luong */
SET @avg_luong = (SELECT  AVG(luong) 
					FROM NHANVIEN, PHONGBAN 
					WHERE 
							PHONGBAN.MAPHG = NHANVIEN.PHG 
						AND PHONGBAN.TENPHG LIKE N'Nghiên cứu' /* (1) */
					)
SELECT CONCAT(HONV,' ', TENLOT,' ', TENNV) AS Ho_va_Ten, LUONG FROM NHANVIEN WHERE LUONG > @avg_luong

-- HOẶC, nói chung có nhiều cách giải nhá các bạn, chủ yếu là biết cách dùng biến thế nào, tạm thời chúng ta không xét phần data type trong chủ đề này
BEGIN
	DECLARE @avg_luong INT;
	
	SELECT @avg_luong = AVG(luong) FROM PHONGBAN 
	INNER JOIN NHANVIEN on PHONGBAN.MAPHG = NHANVIEN.PHG
	WHERE PHONGBAN.TENPHG = N'Nghiên cứu';
    SELECT * FROM NHANVIEN WHERE luong > @avg_luong;
	-- SELECT CONCAT(honv, tenlot) AS 'Họ Tên Lót', luong FROM NHANVIEN WHERE luong > @avg_luong;

-- có thể dùng thêm biến bảng
DECLARE @ket_qua TABLE (honv NVARCHAR(20), tenlot NVARCHAR(20), luong FLOAT);
	INSERT INTO @ket_qua
	SELECT HONV,TENLOT,LUONG FROM NHANVIEN WHERE luong > @avg_luong;

	SELECT CONCAT(honv, tenlot) AS 'Họ Tên Lót', luong FROM @ket_qua;

END;

-- hoặc

DECLARE @NGHIENCUU INT
SET @NGHIENCUU = (SELECT  PHONGBAN.MAPHG FROM PHONGBAN WHERE PHONGBAN.TENPHG = N'NGHIÊN CỨU')

-- SELECT * FROM  NHANVIEN WHERE NHANVIEN.PHG = 5;
-- SELECT AVG(NHANVIEN.LUONG) FROM  NHANVIEN WHERE NHANVIEN.PHG = @NGHIENCUU;

DECLARE @LUONGTB  FLOAT;

SET @LUONGTB = (SELECT AVG(NHANVIEN.LUONG) FROM  NHANVIEN WHERE NHANVIEN.PHG = @NGHIENCUU)

-- SELECT @LUONGTB

SELECT CONCAT(NHANVIEN.HONV, ' ', NHANVIEN.TENLOT, ' ', NHANVIEN.TENNV) AS [Họ và tên], LUONG as [Lương] FROM NHANVIEN WHERE LUONG > @LUONGTB

3. Với các phòng ban có mức lương trung bình trên 30,000, liệt kê tên phòng ban và số lượng nhân viên của phòng ban đó.

-- chuẩn bị
SELECT PHONGBAN.TENPHG, COUNT(NHANVIEN.MANV) FROM PHONGBAN,NHANVIEN 
WHERE PHONGBAN.MAPHG = NHANVIEN.PHG  
GROUP BY PHONGBAN.TENPHG

SELECT PHONGBAN.TENPHG, COUNT(NHANVIEN.MANV), AVG(Luong)  FROM PHONGBAN,NHANVIEN 
WHERE (PHONGBAN.MAPHG = NHANVIEN.PHG)  
GROUP BY PHONGBAN.TENPHG 
HAVING AVG(Luong) > 31000

-- dùng biến bảng
DECLARE @cau3 TABLE (ten_pb NVARCHAR(20), so_luong INT, avg_luong INT)

INSERT INTO @cau3
SELECT PHONGBAN.TENPHG, COUNT(NHANVIEN.MANV), AVG(Luong)  FROM PHONGBAN,NHANVIEN 
WHERE (PHONGBAN.MAPHG = NHANVIEN.PHG)  
GROUP BY PHONGBAN.TENPHG 
HAVING AVG(Luong) > 31000

SELECT * FROM @cau3

4. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó chủ trì

-- chuẩn bị bảng
SELECT * FROM DEAN

SELECT COUNT(mada) FROM DEAN GROUP BY phong

SELECT phongban.TENPHG, COUNT(mada) FROM DEAN, PHONGBAN WHERE DEAN.PHONG = PHONGBAN.MAPHG GROUP BY phongban.TENPHG

-- dùng biến
DECLARE @cau4 TABLE (ten_pb NVARCHAR(20), so_dean INT)
INSERT INTO @cau4
SELECT phongban.TENPHG, COUNT(mada) FROM DEAN, PHONGBAN WHERE DEAN.PHONG = PHONGBAN.MAPHG GROUP BY phongban.TENPHG

SELECT * FROM @cau4

Tham khảo bài subquery/truy vấn con/truy vấn lồng tại đây

Xong!