-- 7.
create proc calAvSalary(@roonname nvarchar(20), @avgsalary money out ) as
	
	select @avgsalary = avg(LUONG) from nhanvien, phongban
	where phg = MAPHG and TENPHG = @roonname


-- 7.a

declare @avgluong money
exec calAvSalary N'Điều hành ', @avgluong out

print convert( nvarchar(20), @avgluong)

-- 7.b

declare @avgluong money
exec calAvSalary N'Điều hành ', @avgluong out


select MAPHG, TENPHG, avg(LUONG) from nhanvien, phongban
where phg = maphg
group by MAPHG, TENPHG
having avg(LUONG) > @avgluong



-- 7.c

declare @avgluong money
exec calAvSalary N'Điều hành ', @avgluong out

select HONV,TENNV, LUONG from nhanvien
where LUONG > @avgluong


-- 8.

create proc cau6(@tenda nvarchar(20),@n int out)
as
	select @n=COUNT(*) from phancong, dean
	where phancong.mada=dean.mada
	and tenda=@tenda


create proc cau8( @name nvarchar(30)) as
	
	declare @count int

	select @count=COUNT(*) from phancong, dean
	where phancong.mada=dean.mada
	and tenda= @name

	select MADA, TENDA, count(MANV) from NHANVIEN, DEAN
	where PHG = PHONG
	group by MADA, TENDA
	having count(MANV) > @count


exec cau8 N'Sản phẩm X '