lab2.txt

2.5
第一种可能：先T1，再T2，则查询结果为148，37
第二种可能：先T2，再T1，则查询结果为150，50
第二种可能：先T2，第一条select语句执行完后，再T1提交，最后第二条select，
则查询结果为150，37
2.6
/*编写一个依据演员编号计算在其指定年份参演的电影数量的存储函数，并利用其查询2017年至少参演过2部电影的演员信息。*/
CREATE FUNCTION NUM_ACTOR(@ACTOR_ID INT,@YEAR INT)
RETURNS INT
AS
BEGIN
DECLARE @NUM INT
	SELECT @NUM = COUNT(*) 
	FROM ACTIN
	WHERE ACTIN.ACTID=@ACTOR_ID AND ACTIN.FID IN 
	(
		SELECT SHOW.FID
		FROM SHOW
		WHERE YEAR = @YEAR
	)

	RETURN @NUM
END

SELECT *
FROM ACTOR
WHERE DBO.NUM_ACTOR(ACTOR.ACTID,2017)>2;
/*建立每家影院的上映电影总数的统计表，并编写一个存储过程来更新该表。*/
CREATE PROCEDURE SHOW_THE 
AS
BEGIN
	SELECT SHOW.TID,COUNT(*) AS NUM INTO ALL_THEATER
	FROM SHOW,THEATER
	WHERE SHOW.TID=THEATER.TID
	GROUP BY SHOW.TID
END

EXEC SHOW_THE

/*编写一个触发器，用于实现对电影表的完整性控制规则：当增加一部电影时，若导演的姓名为周星驰，则电影类型自动设置为“喜剧”。*/
CREATE TRIGGER CHANGE
ON FILM
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @FNAME CHAR(30);
	DECLARE @FTYPE CHAR(10);
	SELECT @FNAME = (SELECT DNAME FROM INSERTED);
	SELECT @FTYPE = (SELECT FTYPE FROM INSERTED);
	
	 
	IF(@FNAME='周星驰' AND @FTYPE!='喜剧')
	BEGIN
		
		SET NOCOUNT ON;
		SELECT * INTO #TMP FROM INSERTED;
		UPDATE #TMP SET FTYPE = '喜剧'
		INSERT INTO FILM SELECT * FROM #TMP;
		DROP TABLE #TMP;
	END
	ELSE INSERT INTO FILM SELECT * FROM INSERTED;
END

INSERT INTO FILM VALUES(20197396, '新喜剧之王', '爱情', '周星驰', 93, 'N', 73);