南师大GIS考研数据库2019年第五题

第一问

	select Score.cno, avg(degree) as avg 
	from Score, Course 
	Where Score.cno = Course. cno and Course.cname like '3%' and Score.cno in  
		(select Score.cno   
		from Score
		Group by Score.cno
		Having count(*)>=5)
	Group by Score.cno

第二问

	SELECt sno, cno,
		(case 
		when degree < 60 then 'E'
		when degree >= 60 and degree < 70 then 'D' 
		when degree >= 70 and degree < 80 then 'C'
		when degree >= 80 and degree < 90 then 'B'
		when degree >= 90 then 'A' 
		else '异常' end) as grade
	from Score

第三问

	create procedure procInsert
		@sno nvarchar(10),
		@cno nvarchar(10),
		@degree float
	as
	begin
		insert into Course(sno, cno, degree)
		values(@sno, @cno, @degree)
	end

第四问

	create procedure procGetScore
		@sno nvarchar(10),
	as
	begin
		select avg(degree)
		from Score
		where sno = @sno
	end

第五问

create trigger delStu after delete on Student
	referencing old oldi
	for each row
		begin	
			delete Score where sno = :oldi.sno
		end;